【DB笔试面试465】如何使用批量动态SQL（FORALL及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...]];  --存放返回结果的集合变量

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

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

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

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...]];  --绑定返回结果集

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