|
|
 Blogs
Toad World blogs are a mix of insightful how-tos from Quest experts as well as their commentary on experiences with new database technologies. Have some views of your own to share? Post your comments! Note: Comments are restricted to registered Toad World users.
 Do you have a topic that you'd like discussed? We'd love to hear from you. Send us your idea for a blog topic.
Dec
19
Written by:
StevenFeuersteinTW
Monday, December 19, 2011 8:33 AM
Oracle added the CONTINUE statement in Oracle Database 11g to simplify the logic you would have to write when you need to terminate execution of code within the body of a loop.
CONTINUE is especially helpful when you are executing nested loops and need to skip the remainder of the inner loop's body and continue on to the next iteration of the outer loop. Sure, you can achieve the same effect with IF statements, but you will need to declare variables (Boolean flags to indicate "execute" or "skip") and the resulting code will be convoluted, hard to read and maintain.
CONTINUE offers a much simpler, understandable solution.
The CONTINUE statement can be used only inside a loop; you cannot use CONTINUE to force termination of a block (and continue in the enclosing block). An attempt to use CONTINUE outside of a loop will raise one of this errors:
PLS-00376: illegal EXIT/CONTINUE statement; it must appear inside a loop
PLS-00373: EXIT/CONTINUE label 'label_name' must label a LOOP statement
If you specify a named loop to which you want to continue, that loop must have been named with a valid label, as in:
BEGIN
<<for_loop>>
FOR indx IN 1 .. 5
LOOP
DBMS_OUTPUT.put_line ('Outer: ' || indx);
CONTINUE for_loop;
FOR indx2 IN 1 .. 5
LOOP
DBMS_OUTPUT.put_line ('Inner: ' || indx2);
END LOOP;
END LOOP;
END;
/
You can also use CONTINUE without a label, and include a WHEN clause (just like with EXIT):
BEGIN
FOR indx IN 1 .. 5
LOOP
DBMS_OUTPUT.put_line ('Outer: ' || indx);
CONTINUE WHEN indx > 3;
FOR indx2 IN 1 .. 5
LOOP
DBMS_OUTPUT.put_line ('Inner: ' || indx2);
END LOOP;
END LOOP;
END;
/
If you include a label after CONTINUE, then the remaining code in the current and all enclosing loops up to the loop named by that label will be skipped over. The next iteration of the named loop will be executed.
If you use a CONTINUE statement to exit a cursor FOR loop prematurely (for example, to exit an inner loop and transfer control to the next iteration of an outer loop), the cursor is closed automatically (in this way, CONTINUE works like GOTO).
1 comment(s) so far...
Re: Using CONTINUE in a PL/SQL Block
I would have thought you would discourage use of continue as it violates the 'one way in, one way out' principal that you discussed in the July/August 2011 issue of Oracle Magazine. I realize we are talking about a loop rather than a function/procedure, but the principal is the same.
Actually, I am just stirring the pot. :-) I try to abide by 'one way in, one way out', but I do violate the principal when the nesting it sometimes forces on you makes the code more difficult to read, understand, or maintain. Continue is a useful statement, as long as it is used judiciiously.
Cheers,
-Brian
By BrianLeach99519 on
Monday, December 19, 2011 6:06 PM
|
|
|