oracle对动态sql语句的处理

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;