本文共 2435 字，大约阅读时间需要 8 分钟。

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 ProductionPL/SQL Release 11.1.0.7.0 - ProductionCORE 11.1.0.7.0 ProductionTNS for Solaris: Version 11.1.0.7.0 - ProductionNLSRTL Version 11.1.0.7.0 - Production==========dept_loop0=========10, ACCOUNTING, NEW YORK20, RESEARCH, DALLAS30, SALES, CHICAGO40, OPERATIONS, BOSTON50, name1, loc151, name1, name152, loc1, loc1==========dept_loop1=========10, ACCOUNTING, NEW YORK20, RESEARCH, DALLAS==========dept_loop2=========10, ACCOUNTING, NEW YORK20, RESEARCH, DALLAS40, OPERATIONS, BOSTON50, name1, loc151, name1, name152, loc1, loc1

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

转载地址：http://yytai.baihongyu.com/