不包含INTO的动态SQL不会增加序列值

今天在试图增加序列的NEXTVAL的时候，无意中发现了这个问题。

 

 

首先还是看看现象：

SQL> CREATE SEQUENCE SEQ_TEST;

序列已创建。

SQL> SELECT SEQ_TEST.NEXTVAL FROM DUAL;

   NEXTVALITPUB个人空间5~;i3x(_!?3Z V
----------
PSC]Q;]Rj%fJ0         1

SQL> BEGINITPUB个人空间VY4vw[GG
  2   FOR I IN 1..1000 LOOP
\L+N4i^ Y/F0  3    EXECUTE IMMEDIATE 'SELECT SEQ_TEST.NEXTVAL FROM DUAL';
5D-ts$D WR8l0  4   ENDLOOP;ITPUB个人空间fV Ly)z
  5  END;ITPUB个人空间r.~'I&r xA J;yDc ]
  6  /

PL/SQL过程已成功完成。

SQL> SELECT SEQ_TEST.NEXTVAL FROM DUAL;

   NEXTVAL
2C |+qB&iEY0G0----------
JO.wocH0         2

可以看到，序列SEQ_TEST的NEXTVAL并不像想象中的提高了1000，而给人的感觉似乎根本没有执行。

避免的方法也简单，只需要添加INTO语句：

SQL> DECLAREITPUB个人空间3aDftU k
  2   TYPE T_ID IS TABLE OF NUMBER;ITPUB个人空间M(j]&K"L*`8En I
  3   V_ID T_ID;ITPUB个人空间uaK \U ?
  4  BEGIN
&X/IW|1G'\D k0  5   FOR I IN 1..1000 LOOPITPUB个人空间"X1xr1fO$H2a
  6    EXECUTE IMMEDIATE 'SELECT SEQ_TEST.NEXTVAL FROM DUAL'ITPUB个人空间+aM\1y m&ax
  7     BULK COLLECT INTO V_ID;
RX$rfdB+BN&Y1X0  8   END LOOP;
9|Z&V X{7A`q i0  9  END;ITPUB个人空间*|;j.Z'_Uh*\)a-de
 10  /

PL/SQL过程已成功完成。

SQL> SELECT SEQ_TEST.NEXTVAL FROM DUAL;

   NEXTVAL
z{2S1J Pa-X0----------
f_@T C1]4C0      1003

产生这种情况有两种可能，一是由于Oracle发现动态SQL的执行没有INTO语句，不需要返回结果，因此根本没有执行；二是Oracle只是打开游标，并没有FETCH数据，因此造成了序列的值没有发生变化。

最后通过一个测试检验一下，到底是哪种情况造成了上面的现象：

SQL> CREATE OR REPLACE FUNCTION F_TEST RETURN NUMBER AS
&iM/Y5C+UMXs0  2  BEGIN
5O:l2^ DL8^ j0  3   DBMS_LOCK.SLEEP(10);
$DiP2B Cz4\0  4   RETURN 0;ITPUB个人空间;D2dL2OV,V9t]
  5  END;ITPUB个人空间+wY h1LqO
  6  /

函数已创建。

SQL> SET TIMING ON
'H8~%D9PEk*Y ?0SQL> BEGIN
z9iq-Q;cRH2Y0  2  EXECUTE IMMEDIATE 'SELECT F_TEST FROM DUAL';ITPUB个人空间I?|v!IE
  3  END;ITPUB个人空间B/vcW0JksR9V
  4  /

PL/SQL过程已成功完成。

已用时间:  00: 00: 00.10
P'T:y/C*Z$[~^0SQL> DECLARE
sV;Rl*IP0  2  V_ID NUMBER;ITPUB个人空间@i8f,pYd
  3  BEGIN
:R(e(H D'[T+T0  4  EXECUTE IMMEDIATE 'SELECT F_TEST FROM DUAL' INTO V_ID;
3\} L;Bb0  5  END;ITPUB个人空间7p7O&c _oM
  6  /

PL/SQL过程已成功完成。

已用时间:  00: 00: 10.29

通过对比就可以发现，如果缺少INTO语句，那么动态SQL就不会执行。