开发动态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的新工资为3450PL/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;CLARKKINGMILLERBAKEMARYPL/SQL 过程已成功完成。

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

--语法如下：FETCH dynamic_cursorBULK 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';SMITHADAMSJAMESMILLERPL/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.6PL/SQL 过程已成功完成。