开发动态SQL之在动态SQL中使用BULK子句

        通过使用BULK子句，可以加快批量数据的处理速度，从而提高应用程序性能。当使用BULK子句时，实际是动态SQL语句将变量绑定为集合元素。当使用BULK子句时，集合类型可以是PL/SQL所支持的索引表、嵌套表和VARRAY，但集合元素必须使用SQL类型（例如NUMBER、CHAR等），而不能使用PL/SQL数据类型（例如BINARY_INTEGER,BOOLEAN等）。有三种语句支持BULK子句：EXECUTE IMMEDIATE，FETCH和FORALL，下面分别介绍这三种语句。

   

    

        1.在EXECUTE IMMEDIATE语句中使用动态BULK子句

/*当直接使用EXECUTE IMMEDIATE语句不能处理作用在多行上的动态DML返回子句。而通过引入BULK子句，不仅可以处理作用在多行上的动态DML返回子句，而且还可以处理多行查询语句。语法如下：*/
EXECUTE IMMEDIATE dynamic_string
[BULK COLLECT INTO define_variable[, define_variable...]]
[USING bind_argument[, bind_argument ...]]
[{RETURNING | RETURN}
BULK COLLECT INTO return_variable[,return_variable ...]];

        1），使用BULK子句处理DML语句返回子句

SQL>  declare
  2     type ename_table_type is table of emp.ename%type
  3       index by binary_integer;
  4     type sal_table_type is table of emp.sal%type
  5       index by binary_integer;
  6     ename_table ename_table_type;
  7     sal_table sal_table_type;
  8     sql_stat varchar2(100);
  9   begin
 10     sql_stat:='update emp set sal=sal*(1+:person/100)'
 11       ||' where deptno=:dno'
 12       ||' returning ename,sal into :name,:salary';
 13     execute immediate sql_stat using &percent,&dno
 14       returning bulk collect into ename_table,sal_table;
 15     for i in 1..ename_table.count loop
 16       dbms_output.put_line('雇员'||ename_table(i)
 17         ||'的新工资为'||sal_table(i));
 18     end loop;
 19   end;
 20  /
输入 percent 的值:  15
输入 dno 的值:  20
原值   13:    execute immediate sql_stat using &percent,&dno
新值   13:    execute immediate sql_stat using 15,20
雇员SMITH的新工资为2300
雇员JONES的新工资为3421.25
雇员SCOTT的新工资为2300
雇员ADAMS的新工资为3450
雇员FORD的新工资为3450

PL/SQL 过程已成功完成。

        2），使用BULK子句处理多行查询

SQL>  declare
  2     type ename_table_type is table of emp.ename%type
  3       index by binary_integer;
  4     ename_table ename_table_type;
  5     sql_stat varchar2(100);
  6   begin
  7     sql_stat:='select ename from emp where deptno=:dno';
  8     execute immediate sql_stat
  9       bulk collect into ename_table using &dno;
 10     for i in 1..ename_table.count loop
 11       dbms_output.put_line(ename_table(i));
 12     end loop;
 13   end;
 14  /
输入 dno 的值:  10
原值    9:      bulk collect into ename_table using &dno;
新值    9:      bulk collect into ename_table using 10;
CLARK
KING
MILLER
BAKE
MARY

PL/SQL 过程已成功完成。

        2. 在FETCH语句中使用BULK子句

--语法如下：
FETCH dynamic_cursor
BULK COLLECT INTO define_variable[, define_variable ...];

--动态返回特定岗位的雇员名为例：
SQL> declare
  2    type empcurtyp is ref cursor;
  3    emp_cv empcurtyp;
  4    type ename_table_type is table of emp.ename%type
  5      index by binary_integer;
  6    ename_table ename_table_type;
  7    sql_stat varchar2(100);
  8  begin
  9    sql_stat:='select ename from emp where job=:title';
 10    open emp_cv for sql_stat using '&job';
 11    fetch emp_cv bulk collect into ename_table;
 12    for i in 1..ename_table.count loop
 13      dbms_output.put_line(ename_table(i));
 14    end loop;
 15    close emp_cv;
 16  end;
 17  /
输入 job 的值:  CLERK
原值   10:   open emp_cv for sql_stat using '&job';
新值   10:   open emp_cv for sql_stat using 'CLERK';
SMITH
ADAMS
JAMES
MILLER

PL/SQL 过程已成功完成。

        3. 在FORALL语句中使用BULK子句

         使用FORALL语句，可允许在动态SQL语句中为输入变量同事提供多个数据，但FORALL语句只适用于动态的INSERT，UPDATE和DELETE语句，而不适用于动态的SELECT语句，并且FORALL语句和EXECUTE IMMEDIATE语句是结合使用的。

SQL>  declare
  2     type ename_table_type is table of emp.ename%type;
  3     type sal_table_type is table of emp.sal%type;
  4     ename_table ename_table_type;
  5     sal_table sal_table_type;
  6     sql_stat varchar2(100);
  7   begin
  8     ename_table:=ename_table_type('SCOTT','SMITH','CLARK');
  9     sql_stat:='update emp set sal=sal*1.1 where ename=:1'
 10       || ' returning sal into :2';
 11     forall i in 1..ename_table.count
 12       execute immediate sql_stat using ename_table(i)
 13         returning bulk collect into sal_table;
 14     for j in 1..ename_table.count loop
 15       dbms_output.put_line('雇员'||ename_table(j)
 16         ||'的新工资为'||sal_table(j));
 17     end loop;
 18   end;
 19  /
雇员SCOTT的新工资为2420
雇员SMITH的新工资为2420
雇员CLARK的新工资为2129.6

PL/SQL 过程已成功完成。