源码-Oracle数据库管理-第十七章-动态SQL语句-Part 4（使用动态批量绑定）

批量绑定是PL/SQL中一个非常有用的特性，它可以显著地减少PL/SQL引擎和SQL引擎之间的交互。

--17.4 使用动态批量绑定
--17.4.1 使用EXECUTE IMMEDIATE批量绑定
--代码17.14 使用BULK COLLECT INTO子句处理多行查询

DECLARE
   TYPE ename_table_type IS TABLE OF VARCHAR2(20) INDEX BY BINARY_INTEGER;
   TYPE empno_table_type IS TABLE OF NUMBER(24) INDEX BY BINARY_INTEGER; 
   ename_tab ename_table_type;                              --定义保存多行返回值的索引表
   empno_tab empno_table_type;  
   v_deptno NUMBER(4) := 20;                          --定义部门编号绑定变量
   sql_stmt VARCHAR2(500);
BEGIN
   --定义多行查询的SQL语句
   sql_stmt:='SELECT empno, ename FROM emp '||'WHERE deptno = :1';
   EXECUTE IMMEDIATE sql_stmt 
   BULK COLLECT INTO empno_tab,ename_tab                --批量插入到索引表
   USING v_deptno;   
   FOR i IN 1..ename_tab.COUNT LOOP                       --输出返回的结果值 
      DBMS_OUTPUT.put_line('员工编号'||empno_tab(i)
                                         ||'员工名称：'||ename_tab(i));
   END LOOP;          
END;

--代码17.15 使用RETURNING BULK COLLECT INTO子句获取多行更新列
DECLARE
   --定义索引表类型，用来保存从DML语句中返回的结果
   TYPE ename_table_type IS TABLE OF VARCHAR2(25) INDEX BY BINARY_INTEGER;
   TYPE sal_table_type IS TABLE OF NUMBER(10,2) INDEX BY BINARY_INTEGER;   
   ename_tab ename_table_type;
   sal_tab sal_table_type;
   v_deptno NUMBER(4) :=20;                               --定义部门绑定变量
   v_percent NUMBER(4,2) := 0.12;                          --定义加薪比率绑定变量
   sql_stmt  VARCHAR2(500);                              --保存SQL语句的变量
BEGIN
   --定义更新emp表的sal字段值的动态SQL语句
   sql_stmt:='UPDATE emp SET sal=sal*(1+:percent) '
             ||' WHERE deptno=:deptno RETURNING ename,sal INTO :ename,:salary';
   EXECUTE IMMEDIATE sql_stmt USING v_percent, v_deptno
      RETURNING BULK COLLECT INTO ename_tab,sal_tab;     --使用批绑定子句获取返回值
   FOR i IN 1..ename_tab.COUNT LOOP                      --输出返回的结果值 
      DBMS_OUTPUT.put_line('员工'||ename_tab(i)||'调薪后的薪资：'||sal_tab(i));
   END LOOP;
END;

--17.4.2 使用批量FETCH语句
--代码 17.16 使用批量FETCH语句获取多行查询结果
DECLARE
   TYPE ename_table_type IS TABLE OF VARCHAR2(20) INDEX BY BINARY_INTEGER;
   TYPE empno_table_type IS TABLE OF NUMBER(24) INDEX BY BINARY_INTEGER;
   TYPE emp_cur_type IS REF CURSOR;                        --定义游标类型    
   ename_tab ename_table_type;                                --定义保存多行返回值的索引表
   empno_tab empno_table_type;  
   emp_cur emp_cur_type;                                      --定义游标变量
   v_deptno NUMBER(4) :=20;                                 --定义部门编号绑定变量
BEGIN
   OPEN emp_cur FOR                                         --打开动态游标
      'SELECT empno, ename FROM emp '||
      'WHERE deptno = :1'
   USING v_deptno;
   FETCH emp_cur BULK COLLECT INTO empno_tab, ename_tab; --批量提取游标数据  
   CLOSE emp_cur;                                           --关闭游标变量
   FOR i IN 1..ename_tab.COUNT LOOP                         --输出返回的结果值 
      DBMS_OUTPUT.put_line('员工编号'||empno_tab(i)
                                         ||'员工名称：'||ename_tab(i));
   END LOOP;       
END;

--17.4.3 使用批量FORALL语句
--代码17.17 使用FORALL语句更新多个员工薪资
DECLARE
   --定义索引表类型，用来保存从DML语句中返回的结果
   TYPE ename_table_type IS TABLE OF VARCHAR2(25) INDEX BY BINARY_INTEGER;
   TYPE sal_table_type IS TABLE OF NUMBER(10,2) INDEX BY BINARY_INTEGER;   
   TYPE empno_table_type IS TABLE OF NUMBER(4);         --定义嵌套表类型，用于批量输入员工编号  
   ename_tab ename_table_type;
   sal_tab sal_table_type;
   empno_tab empno_table_type;
   v_percent NUMBER(4,2) := 0.12;                           --定义加薪比率绑定变量
   sql_stmt  VARCHAR2(500);                               --保存SQL语句的变量
BEGIN
   empno_tab:=empno_table_type(7369,7499,7521,7566);       --初始化嵌套表
     --定义更新emp表的sal字段值的动态SQL语句
   sql_stmt:='UPDATE emp SET sal=sal*(1+:percent) '
             ||' WHERE empno=:empno RETURNING ename,sal INTO :ename,:salary';
   FORALL i IN 1..empno_tab.COUNT                                --使用FORALL语句批量输入参数
      EXECUTE IMMEDIATE sql_stmt USING v_percent, empno_tab(i)   --这里使用来自嵌套表的参数
      RETURNING BULK COLLECT INTO ename_tab,sal_tab;          --使用批量子句获取返回值
   FOR i IN 1..ename_tab.COUNT LOOP                              --输出返回的结果值 
      DBMS_OUTPUT.put_line('员工'||ename_tab(i)||'调薪后的薪资：'||sal_tab(i));
   END LOOP;
END;

select * from emp_history;