【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/

● 本系列題目來源於作者的學習筆記，部分整理自網路，若有侵權或不當之處還請諒解

● 版權所有，歡迎分享本文，轉載請保留出處

● 題目解答若有不當之處，還望各位朋友批評指正，共同進步