在常规 SQL 中，每次调用 .NEXTVAL 时，我的序列都会递增:
SELECT PDF_DATOS_TITULO_ID_SEQ.CURRVAL FROM DUAL; -- 54
SELECT PDF_DATOS_TITULO_ID_SEQ.NEXTVAL FROM DUAL; -- 55
SELECT PDF_DATOS_TITULO_ID_SEQ.NEXTVAL FROM DUAL; -- 56
SELECT PDF_DATOS_TITULO_ID_SEQ.NEXTVAL FROM DUAL; -- 57
SELECT PDF_DATOS_TITULO_ID_SEQ.CURRVAL FROM DUAL; -- 57 (54+3, correct)
但是，在 PL/SQL 块内的动态 SQL 中，它不会递增:
SELECT PDF_DATOS_TITULO_ID_SEQ.CURRVAL FROM DUAL; -- 57
BEGIN
EXECUTE IMMEDIATE 'SELECT PDF_DATOS_TITULO_ID_SEQ.NEXTVAL FROM DUAL';
EXECUTE IMMEDIATE 'SELECT PDF_DATOS_TITULO_ID_SEQ.NEXTVAL FROM DUAL';
EXECUTE IMMEDIATE 'SELECT PDF_DATOS_TITULO_ID_SEQ.NEXTVAL FROM DUAL';
END;
/
SELECT PDF_DATOS_TITULO_ID_SEQ.CURRVAL FROM DUAL; -- 57
...除非我将值存储到变量中:
SELECT PDF_DATOS_TITULO_ID_SEQ.CURRVAL FROM DUAL; -- 57 (!)
DECLARE
FOO INTEGER;
BEGIN
EXECUTE IMMEDIATE 'SELECT PDF_DATOS_TITULO_ID_SEQ.NEXTVAL FROM DUAL' INTO FOO;
EXECUTE IMMEDIATE 'SELECT PDF_DATOS_TITULO_ID_SEQ.NEXTVAL FROM DUAL' INTO FOO;
EXECUTE IMMEDIATE 'SELECT PDF_DATOS_TITULO_ID_SEQ.NEXTVAL FROM DUAL' INTO FOO;
END;
/
SELECT PDF_DATOS_TITULO_ID_SEQ.CURRVAL FROM DUAL; -- 60 (57+3, correct)
解释是什么？它是
EXECUTE IMMEDIATE 的记录行为吗？在您询问之前，SQL 需要是动态的，因为序列名称是可变的。
最佳答案
当您省略 into 或批量返回 into 子句时，Oracle 将仅解析 sql 而不会从中发出任何提取。文档中没有明确说明会发生这种情况，但文档确实指定当有 1 行返回时应使用 INTO，如果有可能返回多于一行，则应使用 INTO。
例如。:
SQL> create sequence testseq;
Sequence created.
SQL> alter session set events '10046 trace name context forever';
Session altered.
SQL> exec execute immediate 'select testseq.nextval from dual';
PL/SQL procedure successfully completed.
SQL> alter session set events '10046 trace name context off';
Session altered.
SQL> exit
我们在跟踪中看到 Oracle 没有打扰 FETCH 阶段:
=====================
PARSING IN CURSOR #140341213531640 len=32 dep=1 uid=83 oct=3 lid=83 tim=1363260261727946 hv=956010684 ad='7ac66b58' sqlid='56jwk2hwgr45w'
select testseq.nextval from dual
END OF STMT
PARSE #140341213531640:c=4001,e=50473,p=0,cr=2,cu=0,mis=1,r=0,dep=1,og=1,plh=112670795,tim=1363260261727944
EXEC #140341213531640:c=0,e=219,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,plh=112670795,tim=1363260261728249
STAT #140341213531640 id=1 cnt=0 pid=0 pos=1 obj=79530 op='SEQUENCE TESTSEQ (cr=0 pr=0 pw=0 time=181 us)'
STAT #140341213531640 id=2 cnt=0 pid=1 pos=1 obj=0 op='FAST DUAL (cr=0 pr=0 pw=0 time=1 us cost=2 size=0 card=1)'
CLOSE #140341213531640:c=4001,e=24391,dep=1,type=3,tim=1363260261752736
EXEC #140341212444728:c=8002,e=75407,p=0,cr=2,cu=0,mis=0,r=1,dep=0,og=1,plh=0,tim=1363260261752783
*** 2013-03-14 11:24:29.866
CLOSE #140341212444728:c=0,e=37,dep=0,type=0,tim=1363260269866098
=====================
对比:
SQL> alter session set events '10046 trace name context forever';
Session altered.
SQL> var a number
SQL> exec execute immediate 'select testseq.nextval from dual' into :a;
PL/SQL procedure successfully completed.
SQL> alter session set events '10046 trace name context off';
Session altered.
现在:
PARSING IN CURSOR #139830768042232 len=32 dep=1 uid=83 oct=3 lid=83 tim=1363260428931803 hv=956010684 ad='7ac66b58' sqlid='56jwk2hwgr45w'
select testseq.nextval from dual
END OF STMT
PARSE #139830768042232:c=0,e=38,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,plh=112670795,tim=1363260428931802
EXEC #139830768042232:c=0,e=86,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,plh=112670795,tim=1363260428931917
FETCH #139830768042232:c=0,e=22,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=1,plh=112670795,tim=1363260428931980
STAT #139830768042232 id=1 cnt=1 pid=0 pos=1 obj=79530 op='SEQUENCE TESTSEQ (cr=0 pr=0 pw=0 time=39 us)'
STAT #139830768042232 id=2 cnt=1 pid=1 pos=1 obj=0 op='FAST DUAL (cr=0 pr=0 pw=0 time=2 us cost=2 size=0 card=1)'
CLOSE #139830768042232:c=0,e=0,dep=1,type=3,tim=1363260428931980
EXEC #139830768045912:c=0,e=294,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=1,plh=0,tim=1363260428931980
*** 2013-03-14 11:27:13.138
CLOSE #139830768045912:c=0,e=45,dep=0,type=0,tim=1363260433138490
=====================
FETCH 可见。我认为理想情况下，Oracle 应该在用户发出没有 INTO/BULK INTO 定义的选择的情况下抛出错误。
关于oracle - 除非我存储值，否则序列不会增加，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/15407456/