CONTINUE WHEN example explanation - Oracle SQL-CodePudding

Hi there I'm trying to interpret the result of the following examples but I could not figure out why. Hope someone could shed a light on it for me. Thank you

Code Result

DECLARE 
 v_total NUMBER := 0;
BEGIN
 <<BeforeTopLoop>>
 FOR i IN 1..10 LOOP
   v_total := v_total   1;
   dbms_output.put_line('Total is: ' || v_total);
   FOR j IN 1..10 LOOP
     CONTINUE BeforeTopLoop WHEN i   j > 5;
     v_total := v_total   1;
   END LOOP;
 END LOOP;
END two_loop;

CodePudding user response：

<<BeforeTopLoop>> is a label that can be the target of certain flow control operations, like CONTINUE.

In the example below, it just shortcuts the inner loop conditionally and transfers control to the top of the outer loop, while keeping the current state of variables.

To expose the state changes of total, see the following suggestion.

Try adjusting the procedure in the following way, then rerun.

You'll notice how the total is incremented by the inner loop without hitting the outer put_line statement.

DECLARE 
 v_total NUMBER := 0;
BEGIN
 <<BeforeTopLoop>>
 FOR i IN 1..10 LOOP
   v_total := v_total   1;
   dbms_output.put_line('Total is: ' || v_total);
   FOR j IN 1..10 LOOP
     CONTINUE BeforeTopLoop WHEN i   j > 5;
     v_total := v_total   1;
     dbms_output.put_line('Intermediate is: ' || v_total);   -- Try adding this line and run again
   END LOOP;
 END LOOP;
END two_loop;

The result:

Total is: 1
Intermediate is: 2
Intermediate is: 3
Intermediate is: 4
Intermediate is: 5
Total is: 6
Intermediate is: 7
Intermediate is: 8
Intermediate is: 9
Total is: 10
Intermediate is: 11
Intermediate is: 12
Total is: 13
Intermediate is: 14
Total is: 15
Total is: 16
Total is: 17
Total is: 18
Total is: 19
Total is: 20