1 --动态SQL
2 create or replace function get_table_count
3 (table_name in varchar2) return pls_integer
4 is
5 sql_query varchar2(32767):='select count(*) from '||table_name;
6 l_return pls_integer;
7 begin
8 execute immediate sql_query into l_return;
9 return l_return;
10 end;
11 begin
12 dbms_output.put_line('表的行数是'||get_table_count('emp'));
13 end;
14 create table ddl_demo(id number,amt number);
15 select * from ddl_demo;
16 --执行动态PL/SQL语句
17 declare
18 plsql_block varchar2(500);
19 begin
20 plsql_block:='DECLARE I INTEGER:=10;
21 BEGIN
22 EXECUTE IMMEDIATE ''truncate table ddl_demo'';
23 for j in 1..i loop
24 insert into ddl_demo values(j,j*100);
25 end loop;
26 end;';
27 execute immediate plsql_block;
28 commit;
29 end;
30 --使用绑定变量
31 /* 重复使用相同的执行计划,避免了重复进行硬解析占用CPU资源
32 绑定变量实际上就是用于替代SQL语句中常量的替代变量*/
33
34 declare
35 v_empno number:=7997;
36 v_ename emp.ename%type:='吕四娘';
37 v_job emp.job%type:='剑客';
38 v_deptno emp.deptno%type :=20;
39 v_tbl_name varchar2(50) :='emp';
40 v_sql_str varchar2(1000);
41 begin
42 v_sql_str :='insert into '||v_tbl_name ||
43 '(empno,ename,job,deptno) values(:1,:2,:3,:4)';
44 execute immediate v_sql_str
45 using v_empno, v_ename,v_job,v_deptno;
46 end;
47 --使用returning into子句获取返回值
48 declare
49 v_empno number(4):=7369;
50 v_percent number(4,2):=0.12;
51 v_salary number(10,2);
52 sql_stmt varchar2(500);
53 begin
54 sql_stmt:='update emp set sal=sal*(1+:percent) '
55 ||' where empno=:empno returning sal
56 into :salary';
57 execute immediate sql_stmt using
58 v_percent,v_empno
59 returning into v_salary;
60 dbms_output.put_line('调整后的工资为:'||v_salary);
61 end;
62 --使用into子句
63 declare
64 sql_stmt varchar2(100);
65 v_deptno number(4):=20;
66 v_empno number(4):=7369;
67 v_dname varchar2(20);
68 v_loc varchar2(20);
69 emp_row emp%rowtype;
70 begin
71 sql_stmt:='select dname,loc from dept where deptno
72 =:deptno';
73 execute immediate sql_stmt into v_dname,v_loc using
74 v_deptno;
75 sql_stmt:='select * from emp where empno=:empno';
76 execute immediate sql_stmt into emp_row using v_empno;
77 dbms_output.put_line('查询的部门名称为:'||v_dname);
78 dbms_output.put_line('查询的员工编号为:'||emp_row.ename);
79 end;
80 create sequence deptno_seq
81 start with 1 increment by 1
82 create sequence deptno_seq
83 start with 1 increment by 1
84 --使用in out模式的参数
85 create or replace procedure create_dept(
86 deptno in out number, dname in varchar2, loc in varchar2
87 ) as
88 begin
89 if deptno is null then
90 select deptno_seq.nextval into deptno from dual;
91 end if;
92 insert into dept values(deptno,dname,loc);
93 end;
94 declare
95 plsql_block varchar2(500);
96 v_deptno number(2);
97 v_dname varchar2(14):='网络部';
98 v_loc varchar2(13) :='也门';
99 begin
100 plsql_block :='begin create_dept(:a,:b,:c);end;';
101 execute immediate plsql_block
102 using in out v_deptno,v_dname,v_loc;
103 dbms_output.put_line('新建部门的编号为:'||v_deptno);
104 end;
105
106 openfor 语句 ,fetch语句
107 --使用 fetch语句提取游标数据
108 declare
109 type empcurtyp is ref cursor; --定义一个弱游标类型
110 v_emp_cursor empcurtyp; --定义一个游标变量
111 emp_record emp%rowtype; --定义保存游标数据的记录类型
112 v_stmt_str varchar2(200);
113 v_e_job emp.job%type:='CLERK';
114 begin
115 --定义动态select语句和绑定占位符
116 v_stmt_str :='select * from scott.emp where job=:j';
117 --使用动态sql语句打开游标变量,用using子句指定变量
118 open v_emp_cursor for v_stmt_str using v_e_job;
119 loop
120 fetch v_emp_cursor into emp_record;
121 exit when v_emp_cursor%notfound;
122 dbms_output.put_line('员工工号:'||emp_record.empno||' '||
123 '员工姓名'||emp_record.ename);
124 end loop;
125 close v_emp_cursor;
126 end;
127
128 批量bulk语句
129 --使用BULK collect into子句处理多行查询
130 declare
131 type ename_table_type is table of varchar2(20)
132 index by binary_integer;
133 type empno_table_type is table of number(24)
134 index by binary_integer;
135 ename_tab ename_table_type; --定义保存多行返回值的索引表
136 empno_tab empno_table_type;
137 v_deptno number(4) :=20; --定义部门编号绑定变量
138 sql_stmt varchar2(500);
139 begin
140 sql_stmt:='select empno,ename from emp
141 '||' where deptno =:1';
142 execute immediate sql_stmt
143 bulk collect into empno_tab,ename_tab
144 using v_deptno;
145 for i in 1..ename_tab.count loop
146 dbms_output.put_line('员工编号'||empno_tab(i)
147 ||'员工名称:'||ename_tab(i));
148 end loop;
149 end;
150
151
152 --使用return bulk collect into子句获取多行更新列
153 declare
154 type ename_table_type is table of varchar2(25) index by
155 binary_integer;
156 type sal_table_type is table of number(10,2) index by
157 binary_integer;
158 ename_tab ename_table_type;
159 sal_tab sal_table_type;
160 v_deptno number(4) :=20;
161 v_percent number(4,2):=0.12;
162 sql_stmt varchar2(500);
163 begin
164 sql_stmt:='update emp set sal=sal*(1+:percent)'
165 ||' where deptno=:deptno returning ename,sal
166 into :ename,:salary';
167 execute immediate sql_stmt using v_percent,v_deptno
168 returning bulk collect into ename_tab,sal_tab;
169
170 for i in 1..ename_tab.count loop
171 dbms_output.put_line('员工'||ename_tab(i)
172 ||'调新后的薪资:'||sal_tab(i) );
173 end loop;
174 end;
175
176 批量fetch语句
177 --使用批量fetch语句获取多行查询结果
178 declare
179 type ename_table_type is table of varchar2(20) index by binary_integer;
180 type empno_table_type is table of number(24) index by binary_integer;
181 type emp_cur_type is ref cursor; --定义游标变量
182 ename_tab ename_table_type;
183 empno_tab empno_table_type;
184 emp_cur emp_cur_type;
185 v_deptno number(4) :=20;
186 begin
187 OPEN emp_cur for
188 'select empno, ename from emp'||
189 'where deptno=:1' using v_deptno;
190 fetch emp_cur bulk collect into empno_tab,ename_tab;
191 close emp_cur;
192 for i in 1..ename_tab.COUNT loop
193 dbms_output.put_line('员工编号'||empno_tab(i)
194 ||'员工名称'||ename_tab(i));
195 end loop;
196 end;
197
198 批量forall语句
199 --使用FORALL语句更新多个员工薪资
200 declare
201 type ename_table_type is table of varchar2(25)
202 index by binary_integer;
203 type sal_table_type is table of number(10,2)
204 index by binary_integer;
205 type empno_table_type is table of number(4);
206
207 ename_tab ename_table_type;
208 sal_tab sal_table_type;
209 empno_tab empno_table_type;
210 v_percent number(4,2) :=0.12;
211 sql_stmt varchar2(500);
212 begin
213 empno_tab:=empno_table_type(7369,7499,7521,7566);
214 sql_stmt:='update emp set sal=sal*(1+:percent) '
215 ||' where empno=:empno returning ename,sal
216 into :ename,:salary';
217 forall i in 1..empno_tab.count --使用Forall语句批量输入参数
218 execute immediate sql_stmt using v_percent,empno_tab(i)
219 returning bulk collect into ename_tab,sal_tab;
220 for i in 1..ename_tab.count loop
221 dbms_output.put_line('员工'||ename_tab(i)||'调薪后的薪资:'
222 ||sal_tab(i));
223 end loop;
224 end;
225
226
227 using语句绑定
228 1绑定比链接具有更快的性能
229 2绑定变量更容易编写和维护
230 3避免隐式类型转换
231 4 避免代码注入,绑定变量避免sql注入是攻击
232 --执行PLSQL动态语句时重复绑定占位符处理
233 declare
234 col_in varchar2(10):='sal';
235 start_in date;
236 end_in date;
237 val_in number;
238 dml_str varchar2(32767):='begin
239 update emp set ' || col_in||'=:val
240 where hiredate between:lodate and
241 :hidate and :val is not null;
242 end;';
243 begin
244 execute immediate dml_str
245 using val_in,start_in,end_in;
246 end;
247
248 --在执行动态SQL时使用异常处理机制
249 create or replace procedure ddl_execution(ddl_string in varchar2)
250 authid current_user is --使用调用者权限
251 begin
252 execute immediate ddl_string;
253 exception
254 when others
255 then
256 dbms_output.put_line(
257 '动态SQL语句错误:' || dbms_utility.format_error_stack);
258 dbms_output.put_line(
259 ' 执行的SQL语句为:"'||ddl_string ||'"');
260 raise;
261 end ddl_execution;
262
263 --命令行
264 set serveroutput on;
265 exec ddl_execution('alter table emp_test add emp_sal number null');
266
267 update emp set sal=1.21 where empno=7369;
268
269
270 select xid as "事务id",XIDUSN as "UNDO",XIDSLOT AS "事务槽",
271 xidsqn as "seq",status as "事务状态" from v$transaction;
272
273 --设置事务属性
274 set transaction read only;--只读事务,此事务中不能进行任何i增删改查
275 set transaction read write --建立读写事务
276 set transaction isolation level serializable;--设置序列隔离级别
277 set transaction isolation read committed; --设置读提交隔离级别
278
279 set transaction isolation level serializable;
280 update emp set sal=8500 where empno=7369;
281 --另外起一个sqlplus窗口,会话1没有提交回滚,会话2倍阻塞
282 update emp set sal=9000 where empno=7369;
283 --使用动态SQL语句实现数据处理
284 declare
285 v_sqlstr varchar2(200); --保存SQL语句的变量
286 v_id int; --保存临时字段值的变量
287 v_name varchar2(100);
288 begin
289 begin
290 v_sqlstr:='drop table temptable';
291 execute immediate v_sqlstr;
292 exception
293 when others
294 then
295 null;
296 end;
297 /* v_sqlstr :='create table temptable (id int not null primary key,
298 tmpname varchar2(100))';
299 execute immediate v_sqlstr;*/
300 v_sqlstr:='insert into temptable values(10,''临时名称1'')';
301 execute immediate v_sqlstr;
302 v_sqlstr:= 'select * from temptable where id=:tempId';
303 execute immediate v_sqlstr into v_id,v_name using &l;
304 dbms_output.put_line(v_id || ' '||v_name);
305 end;
306 select * from temptable ;
浙公网安备 33010602011771号