不包含INTO的動態SQL不會增加序列值

今天在試圖增加序列的NEXTVAL的時候，無意中發現了這個問題。

 

 

首先還是看看現象：

SQL> CREATE SEQUENCE SEQ_TEST;

序列已建立。

SQL> SELECT SEQ_TEST.NEXTVAL FROM DUAL;

   NEXTVAL
----------
         1

SQL> BEGIN
  2   FOR I IN 1..1000 LOOP
  3    EXECUTE IMMEDIATE 'SELECT SEQ_TEST.NEXTVAL FROM DUAL';
  4   END LOOP;
  5  END;
  6  /

PL/SQL 過程已成功完成。

SQL> SELECT SEQ_TEST.NEXTVAL FROM DUAL;

   NEXTVAL
----------
         2

可以看到，序列SEQ_TEST的NEXTVAL並不像想象中的提高了1000，而給人的感覺似乎根本沒有執行。

避免的方法也簡單，只需要新增INTO語句：

SQL> DECLARE
  2   TYPE T_ID IS TABLE OF NUMBER;
  3   V_ID T_ID;
  4  BEGIN
  5   FOR I IN 1..1000 LOOP
  6    EXECUTE IMMEDIATE 'SELECT SEQ_TEST.NEXTVAL FROM DUAL'
  7     BULK COLLECT INTO V_ID;
  8   END LOOP;
  9  END;
 10  /

PL/SQL 過程已成功完成。

SQL> SELECT SEQ_TEST.NEXTVAL FROM DUAL;

   NEXTVAL
----------
      1003

產生這種情況有兩種可能，一是由於Oracle發現動態SQL的執行沒有INTO語句，不需要返回結果，因此根本沒有執行；二是Oracle只是開啟遊標，並沒有FETCH資料，因此造成了序列的值沒有發生變化。

最後透過一個測試檢驗一下，到底是哪種情況造成了上面的現象：

SQL> CREATE OR REPLACE FUNCTION F_TEST RETURN NUMBER AS
  2  BEGIN
  3   DBMS_LOCK.SLEEP(10);
  4   RETURN 0;
  5  END;
  6  /

函式已建立。

SQL> SET TIMING ON
SQL> BEGIN
  2  EXECUTE IMMEDIATE 'SELECT F_TEST FROM DUAL';
  3  END;
  4  /

PL/SQL 過程已成功完成。

已用時間:  00: 00: 00.10
SQL> DECLARE
  2  V_ID NUMBER;
  3  BEGIN
  4  EXECUTE IMMEDIATE 'SELECT F_TEST FROM DUAL' INTO V_ID;
  5  END;
  6  /

PL/SQL 過程已成功完成。

已用時間:  00: 00: 10.29

透過對比就可以發現，如果缺少INTO語句，那麼動態SQL就不會執行。