在常规sql中,每次调用时,我的序列都会递增.NEXTVAL：
@H_403_11@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)
@H_403_11@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
…除非我将值存储到变量中：
@H_403_11@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的记录行为吗？
解决方法
当你省略或批量返回子句时,Oracle只会解析sql但不会从中发出任何提取.在文档中没有明确说明会发生这种情况,但是文档确实指定当你有1行返回时你应该使用INTO,如果你有可能有多行返回,那么你应该使用INTO.
例如.：
@H_403_11@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阶段感到困扰：
@H_403_11@===================== 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,cr=0,mis=0,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,type=3,tim=1363260261752736 EXEC #140341212444728:c=8002,e=75407,r=1,dep=0,plh=0,tim=1363260261752783 *** 2013-03-14 11:24:29.866 CLOSE #140341212444728:c=0,e=37,type=0,tim=1363260269866098 =====================VS：
@H_403_11@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.现在：
@H_403_11@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,tim=1363260428931802 EXEC #139830768042232:c=0,e=86,tim=1363260428931917 FETCH #139830768042232:c=0,e=22,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,tim=1363260428931980 EXEC #139830768045912:c=0,e=294,tim=1363260428931980 *** 2013-03-14 11:27:13.138 CLOSE #139830768045912:c=0,e=45,tim=1363260433138490 =====================可以看到FETCH.我认为理想情况下,Oracle应该在用户发出没有定义INTO / BULK INTO的选择的情况下抛出错误.
