【DB笔试面试465】如何使用批量动态SQL（FORALL及BULK子句的使用）？

• 2019 年 10 月 11 日
• 筆記

题目部分

如何使用批量动态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程序员面试笔试宝典》，作者：李华荣。

About Me：小麦苗

● 本文作者：小麦苗，只专注于数据库的技术，更注重技术的运用

● 作者博客地址：http://blog.itpub.net/26736162/abstract/1/

● 本系列题目来源于作者的学习笔记，部分整理自网络，若有侵权或不当之处还请谅解

● 版权所有，欢迎分享本文，转载请保留出处

● 题目解答若有不当之处，还望各位朋友批评指正，共同进步