发表于: 2008.08.17 23:49
分类: ORACLE
出处: http://yangtingkun.itpub.net/post/468/468948
---------------------------------------------------------------
今天在试图增加序列的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就不会执行。
