trap or bug when using CONTINUE in Oracle 11g-优快云博客

本文链接：https://blog.youkuaiyun.com/t0nsha/article/details/6427388

本文探讨了Oracle11g中引入的新特性CONTINUE的一个潜在陷阱。通过具体的PL/SQL示例代码，展示了当CONTINUE用于隐式游标循环时可能出现的问题，并对比了显式游标的情况。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

CONTINUE is a new feature brought in Oracle 11g, but there is a trap or bug need to pay attention, see the following code scrap:

DECLARE CURSOR dept_cur IS SELECT deptno ,dname ,loc FROM scott.dept; TYPE dept_tab IS TABLE OF dept_cur%ROWTYPE INDEX BY BINARY_INTEGER; l_dept_tab dept_tab; BEGIN --EXECUTE IMMEDIATE 'ALTER SESSION SET PLSQL_OPTIMIZE_LEVEL = 1'; --http://pbarut.blogspot.com/2009/04/caution-for-loop-and-continue-in-oracle.html dbms_output.put_line('==========OracleVersion========='); FOR ver_rec IN (SELECT banner FROM v$version) LOOP dbms_output.put_line(ver_rec.banner); END LOOP; --review test data dbms_output.put_line('==========dept_loop0========='); <<dept_loop0>> FOR dept_rec IN dept_cur LOOP dbms_output.put_line(dept_rec.deptno || ', ' || dept_rec.dname || ', ' || dept_rec.loc); END LOOP dept_loop0; --CONTINUE in implicit cursor dbms_output.put_line('==========dept_loop1========='); <<dept_loop1>> FOR dept_rec IN dept_cur LOOP IF (dept_rec.deptno = '30') THEN continue dept_loop1; END IF; dbms_output.put_line(dept_rec.deptno || ', ' || dept_rec.dname || ', ' || dept_rec.loc); END LOOP dept_loop1; --CONTINUE in explicit cursor dbms_output.put_line('==========dept_loop2========='); OPEN dept_cur; FETCH dept_cur BULK COLLECT INTO l_dept_tab; CLOSE dept_cur; <<dept_loop2>> FOR i IN 1 .. l_dept_tab.COUNT LOOP IF (l_dept_tab(i).deptno = '30') THEN continue dept_loop2; END IF; dbms_output.put_line(l_dept_tab(i).deptno || ', ' || l_dept_tab(i) .dname || ', ' || l_dept_tab(i).loc); END LOOP dept_loop2; END;

Output:

==========OracleVersion========= Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production PL/SQL Release 11.1.0.7.0 - Production CORE 11.1.0.7.0 Production TNS for Solaris: Version 11.1.0.7.0 - Production NLSRTL Version 11.1.0.7.0 - Production ==========dept_loop0========= 10, ACCOUNTING, NEW YORK 20, RESEARCH, DALLAS 30, SALES, CHICAGO 40, OPERATIONS, BOSTON 50, name1, loc1 51, name1, name1 52, loc1, loc1 ==========dept_loop1========= 10, ACCOUNTING, NEW YORK 20, RESEARCH, DALLAS ==========dept_loop2========= 10, ACCOUNTING, NEW YORK 20, RESEARCH, DALLAS 40, OPERATIONS, BOSTON 50, name1, loc1 51, name1, name1 52, loc1, loc1

As you see, CONTINUE didn't work correctly in implicit cursor loop!!!