trap or bug when using CONTINUE in Oracle 11g

最新推荐文章于 2025-12-05 12:37:18 发布

最新推荐文章于 2025-12-05 12:37:18 发布 · 80 阅读

文章标签：

本文探讨了Oracle11g中CONTINUE特性的一个陷阱，通过对比隐式游标和显式游标的使用，展示了CONTINUE在隐式游标循环中未能按预期工作的现象。

CONTINUE is a new featurebroughtin 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!!!