3-8. Bypassing the Current Loop Iteration
Problem
If a specified conditional statement evaluates to TRUE, you want to terminate the current loop iteration of the loop early and start the next iteration immediately.
Solution
Use a CONTINUE statement along with a condition to end the current iteration.
In the following example, a loop is used to iterate through the records in the employees table. The primary reason for the loop is to print out a list of employees who receive a salary greater than 15,000. If an employee does not receive more than 15,000, then nothing is printed out, and the loop continues to the next iteration.
DECLARE
CURSOR emp_cur is
SELECT *
FROM employees;
emp_rec emp_cur%ROWTYPE;
BEGIN
DBMS_OUTPUT.PUT_LINE('Employees with salary > 15000: '),
OPEN emp_cur;
LOOP
FETCH emp_cur INTO emp_rec;
EXIT WHEN emp_cur%NOTFOUND;
IF emp_rec.salary < 15000 THEN
CONTINUE;
ELSE
DBMS_OUTPUT.PUT_LINE('Employee: ' || emp_rec.first_name || ' ' ||
emp_rec.last_name);
END IF;
END LOOP;
CLOSE emp_cur;
END;
Here are some sample results:
Employees with salary > 15000:
Employee: Steven King
Employee: Neena Kochhar
Employee: Lex De Haan
PL/SQL procedure successfully completed.
How It Works
You can use the CONTINUE statement in any loop to unconditionally halt execution of the current iteration of the loop and move to the next. As shown in the solution, the CONTINUE statement is usually encompassed within some conditional statement so that it is invoked only when that certain condition is met.
You can use the CONTINUE statement along with a label in order to jump to a specified point in the program. Rather than merely using CONTINUE to bypass the current loop iteration, specifying a label will allow you to resume programming in an outer loop. For more information regarding the use of the CONTINUE statement along with labels in nested loops, please see Recipe 3-13.
As an alternative to specifying CONTINUE from within an IF statement, you can choose to write a CONTINUE WHEN statement. For example, the following two approaches yield identical results:
IF team_rec.total_points < 10 THEN
CONTINUE;
or
CONTINUE WHEN rec.total_points < 10;
Using the CONTINUE WHEN format, the loop will stop its current iteration if the condition in the WHEN clause is met. Otherwise, the iteration will ignore the statement altogether.