1、静态SQL语句性能优于动态SQL语句,如果功能确定最好使用静态SQL语句
2、使用execute immediate语句,用于处理除了多行查询之外的任何动态SQL,包括DDL、DCL、DML、以及单行Select语句
3、使用动态引用游标OPEN-FOR、FETCH、CLOSE语句处理多行查询语句
4、使用批量的动态SQL语句,可以提高pl/sql性能
一、处理DDL语句
begin
declare
v_sql varchar2(2000);
begin
v_sql:='create table test as select * from dept';
execute immediate v_sql;
end;
end;
二、处理DCL语句
begin
declare
v_sql varchar2(2000);
begin
v_sql:='grant select on test to user_01';
execute immediate v_sql;
end;
end;
三、处理DML语句
根据DML语句是否有占位符、是否是returning子句处理方式不同
1、处理无占位符和returning子句的DML语句
begin
declare
v_sql varchar2(2000);
begin
v_sql:='update test set loc=''HELLOWORLD'' where deptno=10';
execute immediate v_sql;
end;
end;
2、处理有占位符无returning子句的DML语句
begin
declare
v_sql varchar2(2000);
begin
v_sql:='update test set loc=:loc where deptno=:deptno';
execute immediate v_sql using &1,&2;
end;
end;
3、处理无占位符有returning子句的DML语句
begin
declare
v_sql varchar2(2000);
v_dname dept.dname%type;
begin
v_sql:='update test set loc=''HELLOWORLD'' where deptno=10 returning dname into :name';
execute immediate v_sql returning into v_dname;
dbms_output.put_line(v_dname||'被更新');
end;
end;
4、处理含占位符有returning子句的DML语句
begin
declare
v_sql varchar2(2000);
v_dname dept.dname%type;
begin
v_sql:='update test set loc=''HELLOWORLD'' where deptno=:deptno returning dname into :name';
execute immediate v_sql using &deptno returning into v_dname;
dbms_output.put_line(v_dname||'被更新');
end;
end;
四、处理单行查询
begin
declare
v_sql varchar2(2000);
v_record dept%rowtype;
begin
v_sql:='select * from dept where deptno=:deptno';
execute immediate v_sql into v_record using &deptno ;
dbms_output.put_line('部门名称:'||v_record.dname);
end;
end;
通过记录变量来获取数据
五、处理多行查询
begin
declare
type dept_cur is ref cursor;
c_dept dept_cur;
v_sql varchar2(2000);
v_record dept%rowtype;
begin
v_sql:='select * from dept where deptno=:deptno';
open c_dept for v_sql using &deptno;
loop
fetch c_dept into v_record;
exit when c_dept%NOTFOUND;
dbms_output.put_line('部门名称:'||v_record.dname);
end loop;
close c_dept;
end;
end;
六、在动态SQL语句中使用批量绑定
在动态SQL语句中使用批量绑定,可以加快批量数据的处理速度,提高性能;
当使用批量绑定时,集合元素要使用SQL的数据类型而非PL/SQL数据类型,最好通过%TYPE,%ROWTYPE来
(1)、在动态DML语句上面使用批量绑定
语法:
FORALL index IN 1..v_tab.count
execute immediate v_sql using v_tab(i);
举例:
begin
declare
type dept_var is varray(3) of dept.deptno%type;
v_dept dept_var;
v_sql varchar2(2000);
begin
v_dept:=dept_var(1,2,3);
v_sql:='update dept set loc=''测试数据'' where deptno=:deptno';
forall i IN 1..v_dept.count
execute immediate v_sql using v_dept(i) ;
end;
end;
(2)、在DML返回子句上使用批量绑定
语法:
execute immediate v_sql
returning bulk collect into v_coll;
举例:
begin
declare
type dname_tab is table of dept.dname%type;
v_name dname_tab;
type loc_tab is table of dept.loc%type;
v_loc loc_tab;
v_sql varchar2(2000);
begin
v_sql:='update dept set loc=''测试数据DDDDD'' where deptno=:deptno returning dname,loc into :1,:2';
execute immediate v_sql using &deptno returning bulk collect into v_name,v_loc;
for i IN 1..v_name.count loop
dbms_output.put_line('部门名称:'||v_name(i)||' 地址:'||v_loc(i));
end loop;
end;
end;
(3)、在execute immediate语句上使用批量绑定查询语句
语法:
execute immediate v_sql bulk collect into v_coll;
举例:
begin
declare
type dname_tab is table of dept.dname%type;
v_name dname_tab;
type loc_tab is table of dept.loc%type;
v_loc loc_tab;
v_sql varchar2(2000);
begin
v_sql:='select dname,loc from dept where deptno=:deptno';
execute immediate v_sql bulk collect into v_name,v_loc using &deptno;
for i IN 1..v_name.count loop
dbms_output.put_line('部门名称:'||v_name(i)||' 地址:'||v_loc(i));
end loop;
end;
end;
(4)、在游标FETCH语句中使用批量提取
语法:
fetch v_cursor bulk collect into v_coll;
举例:
begin
declare
type c_ref is ref cursor;
c_dept c_ref;
type dname_tab is table of dept.dname%type;
v_name dname_tab;
type loc_tab is table of dept.loc%type;
v_loc loc_tab;
v_sql varchar2(2000);
begin
v_sql:='select dname,loc from dept where deptno=:deptno';
open c_dept for v_sql using &deptno;
fetch c_dept bulk collect into v_name,v_loc;
close c_dept;
for i IN 1..v_name.count loop
dbms_output.put_line('部门名称:'||v_name(i)||' 地址:'||v_loc(i));
end loop;
end;
end;