如何使用批量动态SQL（FORALL及BULK子句的使用）？
♣ 答案部分
批量动态SQL即在动态SQL中使用BULK子句，或使用游标变量时在FETCH中使用BULK，或在FORALL子句中使用BULK子句来实现。
如果一个循环内执行了INSERT、DELETE或UPDATE等语句引用了集合元素，那么可以将其移动到一个FORALL子句中。如果SELECT INTO、FETCH INTO或RETURNING INTO子句引用了一个集合，那么应该使用BULK COLLECT子句进行合并，从而来提高程序的性能。
1、动态SQL中使用BULK子句的语法
EXECUTE IMMEDIATE dynamic_string --dynamic_string用于存放动态SQL字符串
[BULK COLLECT INTO define_variable[,define_variable...]] --存放查询结果的集合变量
[USING bind_argument[,argument...]] --使用参数传递给动态SQL
[{RETURNING | RETURN} --返回子句
BULK COLLECT INTO return_variable[,return_variable...]]; --存放返回结果的集合变量
使用BULK COLLECT INTO子句处理动态SQL中的多行查询可以加快处理速度，从而提高应用程序的性能。当使用BULK子句时，集合类型可以是PL/SQL所支持的索引表、嵌套表和VARRY，但集合元素必须使用SQL数据类型。常用的三种语句支持BULK子句，分别为EXECUTE IMMEDIATE，FETCH和FORALL。
2、使用EXECUTE IMMEDIATE结合BULK子句处理DML语句返回子句
下面的例子，首先定义了两个索引表类型以及其变量，接下来使用动态SQL语句来更新T_20170104_LHR的薪水，使用EXECUTE IMMEDIATE配合BULK COLLECT INTO来处理结果集。
CREATE TABLE T_20170104_LHR AS SELECT EMPNO,ENAME,SAL,DEPTNO FROM SCOTT.EMP;--准备基础表
DECLARE
TYPE ENAME_TABLE_TYPE IS TABLE OF T_20170104_LHR.ENAME%TYPE INDEX BY BINARY_INTEGER; --定义类型用于存放结果集
TYPE SAL_TABLE_TYPE IS TABLE OF T_20170104_LHR.SAL%TYPE INDEX BY BINARY_INTEGER;
ENAME_TABLE ENAME_TABLE_TYPE;
SAL_TABLE SAL_TABLE_TYPE;
SQL_STAT VARCHAR2(120);
V_PERCENT NUMBER := 1;
V_DNO NUMBER := 10;
BEGIN
SQL_STAT := 'UPDATE T_20170104_LHR SET SAL = SAL * (1 + :PERCENT / 100)' --动态DML语句
|| ' WHERE DEPTNO = :DNO' ||
' RETURNING ENAME, SAL INTO :NAME, :SALARY'; --使用了RETURNING子句，有返回值
EXECUTE IMMEDIATE SQL_STAT
USING V_PERCENT, V_DNO --执行动态SQL语句
RETURNING BULK COLLECT
INTO ENAME_TABLE, SAL_TABLE; --使用BULK COLLECT INTO到集合变量
FOR I IN 1 .. ENAME_TABLE.COUNT --使用FOR循环读取集合变量的结果
LOOP
DBMS_OUTPUT.PUT_LINE('Employee ' || ENAME_TABLE(I) || ' Salary is: ' ||
SAL_TABLE(I));
END LOOP;
END;
/
运行以上程序输出结果如下所示：
Employee CLARK Salary is: 2474.5
Employee KING Salary is: 5050
Employee MILLER Salary is: 1313
3、使用EXECUTE IMMEDIATE结合BULK子句处理多行查询
在下面示例中，与前一个示例相同，只不过其动态SQL由查询语句组成，且返回多个结果集，同样使用了BULK COLLECT INTO来传递结果。
DECLARE
TYPE ENAME_TABLE_TYPE IS TABLE OF t_20170104_lhr.ENAME%TYPE INDEX BY BINARY_INTEGER; --定义类型用于存放结果集
TYPE SAL_TABLE_TYPE IS TABLE OF t_20170104_lhr.SAL%TYPE INDEX BY BINARY_INTEGER;
ENAME_TABLE ENAME_TABLE_TYPE;
SAL_TABLE SAL_TABLE_TYPE;
SQL_STAT VARCHAR2(100);
BEGIN
SQL_STAT := 'SELECT ENAME,SAL FROM T_20170104_LHR WHERE DEPTNO = :DNO'; --动态DQL语句，未使用RETURNING子句
EXECUTE IMMEDIATE SQL_STAT BULK COLLECT
INTO ENAME_TABLE, SAL_TABLE
USING 10; --使用BULK COLLECT INTO
FOR I IN 1 .. ENAME_TABLE.COUNT LOOP
DBMS_OUTPUT.PUT_LINE('Employee ' || ENAME_TABLE(I) || ' Salary is: ' ||SAL_TABLE(I));
END LOOP;
COMMIT;
END;
/
运行以上程序输出结果如下所示：
Employee CLARK Salary is: 2450
Employee KING Salary is: 5000
Employee MILLER Salary is: 1300
4、使用FETCH子句结合BULK子句处理多行结果集
下面的示例中首先定义了游标类型，游标变量以及复合类型，复合变量，接下来从动态SQL中OPEN游标，然后使用FETCH将结果存放到复合变量中。即使用OPEN，FETCH代替了EXECUTE IMMEDIATE来完成动态SQL的执行。
DECLARE
TYPE EMPCURTYPE IS REF CURSOR; --定义游标类型及游标变量
EMP_CV EMPCURTYPE;
TYPE ENAME_TABLE_TYPE IS TABLE OF t_20170104_lhr.ENAME%TYPE INDEX BY BINARY_INTEGER; --定义结果集类型及变量
ENAME_TABLE ENAME_TABLE_TYPE;
SQL_STAT VARCHAR2(120);
BEGIN
SQL_STAT := 'SELECT ENAME FROM T_20170104_LHR WHERE DEPTNO = :DNO'; --动态SQL字符串
OPEN EMP_CV FOR SQL_STAT USING 10; --从动态SQL中打开游标
FETCH EMP_CV BULK COLLECT INTO ENAME_TABLE;--使用BULK COLLECT INTO提取结果集
FOR I IN 1 .. ENAME_TABLE.COUNT LOOP
DBMS_OUTPUT.PUT_LINE('Employee Name is ' || ENAME_TABLE(I));
END LOOP;
CLOSE EMP_CV;
END;
/
运行以上程序输出结果如下所示：
Employee Name is CLARK
Employee Name is KING
Employee Name is MILLER
5、在FORALL子句中使用BULK子句
下面是FORALL子句的语法：
FORALL index IN lower bound..upper bound --FORALL循环计数
EXECUTE IMMEDIATE dynamic_string --结合EXECUTE IMMEDIATE来执行动态SQL语句
USING bind_argument | bind_argument(index) --绑定输入参数
[bind_argument | bind_argument(index)]...
[{RETURNING | RETURN} BULK COLLECT INTO bind_argument[,bind_argument...]]; --绑定返回结果集
FORALL子句允许为动态SQL输入变量，但FORALL子句仅支持DML（INSERT、DELETE、UPDATE）语句，不支持动态的SELECT语句。
下面的示例中，首先声明了两个复合类型以及复合变量，接下来为复合变量ENAME_TABLE赋值，以形成动态SQL语句。紧接着使用FORALL子句结合EXECUTE IMMEDIATE 来提取结果集。
DECLARE
--定义复合类型及变量
TYPE ENAME_TABLE_TYPE IS TABLE OF T_20170104_LHR.ENAME%TYPE;
TYPE SAL_TABLE_TYPE IS TABLE OF T_20170104_LHR.SAL%TYPE;
ENAME_TABLE ENAME_TABLE_TYPE;
SAL_TABLE SAL_TABLE_TYPE;
SQL_STAT VARCHAR2(100);
BEGIN
ENAME_TABLE := ENAME_TABLE_TYPE('BLAKE', 'FORD', 'MILLER'); --为复合类型赋值
SQL_STAT := 'UPDATE T_20170104_LHR SET SAL = SAL * 1.1 WHERE ENAME = :1' --定义动态SQL语句
|| ' RETURNING SAL INTO :2';
FORALL I IN 1 .. ENAME_TABLE.COUNT --为FORALL设定起始值
EXECUTE IMMEDIATE SQL_STAT USING ENAME_TABLE(I) --使用EXECUTE IMMEDIATE 结合RETURNING BULK COLLECT INTO获取结果集
RETURNING BULK COLLECT INTO SAL_TABLE
;
FOR J IN 1 .. ENAME_TABLE.COUNT LOOP
DBMS_OUTPUT.PUT_LINE('The new salary is ' || SAL_TABLE(J) || ' for ' ||ENAME_TABLE(J));
END LOOP;
COMMIT;
END;
/
运行以上程序输出结果如下所示：
The new salary is 3135 for BLAKE
The new salary is 3300 for FORD
The new salary is 1430 for MILLER
本文选自《Oracle程序员面试笔试宝典》，作者：李华荣。
---------------优质麦课------------
详细内容可以添加麦老师微信或QQ私聊。
● 本文作者：小麦苗，只专注于数据库的技术，更注重技术的运用
● 作者博客地址：http://blog.itpub.net/26736162/abstract/1/
● 本系列题目来源于作者的学习笔记，部分整理自网络，若有侵权或不当之处还请谅解
● 版权所有，欢迎分享本文，转载请保留出处
● QQ：646634621 QQ群：618766405
● 提供OCP、OCM和高可用部分最实用的技能培训
● 题目解答若有不当之处，还望各位朋友批评指正，共同进步
长按下图识别二维码或微信扫描下图二维码来关注小麦苗的微信公众号：xiaomaimiaolhr，学习最实用的数据库技术。
本文分享自微信公众号 - DB宝（lhrdba）。
如有侵权，请联系 support@oschina.cn 删除。
本文参与“OSC源创计划”，欢迎正在阅读的你也加入，一起分享。