The CONTINUE statement is very handy feature when programming with loops in Oracle PL/SQL. Basically, it lets you skip to the next iteration of your loop (i.e., next record in the cursor loop). Unfortunately, it was only added in 11g Release 1 (11.1). This is stated in the Language Reference of that version.

Below is the example.

SQL> DECLARE
2 x NUMBER := 0;
3 BEGIN
4 LOOP — After CONTINUE statement, control resumes here
5 DBMS_OUTPUT.PUT_LINE (‘Inside loop: x = ‘ || TO_CHAR(x));
6 x := x + 1;
7
8 IF x < 3 THEN 9 CONTINUE; 10 END IF; 11 12 DBMS_OUTPUT.PUT_LINE 13 ('Inside loop, after CONTINUE: x = ' || TO_CHAR(x)); 14 15 EXIT WHEN x = 5; 16 END LOOP; 17 18 DBMS_OUTPUT.PUT_LINE (' After loop: x = ' || TO_CHAR(x)); 19 END; 20 /


As our Siebel CRM here in the office still uses 10g, I have no choice but to use some other ways(i.e., logic change, big if-endif, etc.) There’s a lot of critics of this function with some saying it’s like using GOTO statement in BASIC. I find it handy, though. And I don’t think those critics ever encountered such kind of situations or have programmed a large pl/sql program in the real world.

The statement allows me to make my codes more readable and easily understandable. On top of that, it allows shorter program and easily traceable code. And so I’m happy that Oracle has added it their RDBMS starting from 11g.

One Thought on “Oracle PL/SQL Continue Statement in Loops

  1. i’m actually in need of assistance in writing a plsql function that will generate automatic numbers that will be assigned to fotos and then to generate also a barcode for these number in Oracle application express. Any assistance will be appreciated.

    Thanks

Post Navigation