无法使用序列

Question

 FOR I IN 1..arrResCode.COUNT
         LOOP
             BEGIN             
                     resid:=res_ID.Currval;                    STRSQL:='INSERT INTO DRUG_PRODUCTRESCODE (CID,RESCODEID,RESCODE,SUBTYPENO,CODEVERSION,CODELEVEL,PKGRATION)
                     VALUES(:CID,:RESCODEID,:RESCODE,:SUBTYPENO,:CODEVERSION,:CODELEVEL,:PKGRATION)';
                     EXECUTE IMMEDIATE STRSQL USING 
                     rCID,res_ID.Nextval,arrResCode(I),rSubTypeNo,arrCodeVersion(I),arrCodeLevel(I),arrPkgRation(I);
                     p_sess := p_sess+1;
                     COMMIT;
             ELSE res_ID  是预先定义的序列.   这里报错当前上下文中不允许作用res_ID.Next.也就是在循中不能使用序列. 求教如何解决

Answer 1

 你把序列直接放到SQL字符串里执行看看

Answer 2

STRSQL:='INSERT INTO DRUG_PRODUCTRESCODE (CID,RESCODEID,RESCODE,SUBTYPENO,CODEVERSION,CODELEVEL,PKGRATION)
                     VALUES(:CID,res_ID.Nextval,:RESCODE,:SUBTYPENO,:CODEVERSION,:CODELEVEL,:PKGRATION)';
                     EXECUTE IMMEDIATE STRSQL USING 
                     rCID,arrResCode(I),rSubTypeNo,arrCodeVersion(I),arrCodeLevel(I),arrPkgRation(I);

Answer 3

或者
select res_ID.nextval into resid from dual;                    STRSQL:='INSERT INTO DRUG_PRODUCTRESCODE (CID,RESCODEID,RESCODE,SUBTYPENO,CODEVERSION,CODELEVEL,PKGRATION)
                     VALUES(:CID,:RESCODEID,:RESCODE,:SUBTYPENO,:CODEVERSION,:CODELEVEL,:PKGRATION)';
                     EXECUTE IMMEDIATE STRSQL USING 
                     rCID,res_id,arrResCode(I),rSubTypeNo,arrCodeVersion(I),arrCodeLevel(I),arrPkgRation(I);
                     p_sess := p_sess+1;

Answer 4

--外面定义一个变量resid,获取序列号
FOR I IN 1..arrResCode.COUNT
         LOOP
             BEGIN  
                select res_ID.nextval into resid from dual;
                STRSQL:='INSERT INTO DRUG_PRODUCTRESCODE (CID,RESCODEID,RESCODE,SUBTYPENO,CODEVERSION,CODELEVEL,PKGRATION)
                     VALUES(:CID,:RESCODEID,:RESCODE,:SUBTYPENO,:CODEVERSION,:CODELEVEL,:PKGRATION)';
                     EXECUTE IMMEDIATE STRSQL USING 
                     rCID,resid,arrResCode(I),rSubTypeNo,arrCodeVersion(I),arrCodeLevel(I),arrPkgRation(I);
                     p_sess := p_sess+1;
                     COMMIT;
                ELSE