Oracle 11g new Loops added and updated

PL/SQL has provided very strong conditional (if..then..else, when..case) and loop constructs. Loops are the sequence of multiple statements which are executed repeatedly. There is an EXIT command available to exit the loop when a specific condition is satisfied but there was no construct or command to end the current iteration of loop and start next iteration.

In Oracle 11g, a new construct ‘CONTINUE’ is available. When ‘CONTINUE’ is encountered in the loop, control goes to the end of the loop to end the current iteration and starts the next iteration. Any statements between the CONTINUE and END LOOP syntax are not executed.

It has two flavors.

IF THEN
CONTINUE;
END IF;

OR

CONTINUE WHEN ;

Here is the small example.

DECLARE

v_counter NUMBER := 0;

BEGIN

WHILE (v_counter < 100)
LOOP
v_counter := v_counter + 1;

IF MOD(v_counter,25) <> 0 THEN
CONTINUE;
END IF;

Dbms_output.put_line(’Counter Value = ‘ || v_counter);

END LOOP;

END;
/

Here is the example using CONTINUE-WHEN construct.

DECLARE

v_counter NUMBER := 0;

BEGIN

WHILE (v_counter < 100)
LOOP
v_counter := v_counter + 1;

CONTINUE WHEN MOD(v_counter,25) <> 0;

Dbms_output.put_line(’Counter Value = ‘ || v_counter);

END LOOP;

END;
/

For both of the above PL/SQL blocks, output will be as shown below.

Counter Value = 25
Counter Value = 50
Counter Value = 75
Counter Value = 100

For each iteration, IF or CONTINUE WHEN statement is evaluated. Current iteration ends, if condition evaluates to true and control goes back to starting of the loop otherwise this statement is ignored and current iteration is not terminated.