Nested Loops in oracle server

Question

I'm not software programmer. I want to learn PL/SQL for my job. I examined an anonymous procedure including nested basic loop like below. As result of this anonymous block was found "166". I can't understant how to iteration for loop in following statement. Can someone help me this issue? I will be appreciated for your help me to grasp this problem.

DECLARE
s PLS_INTEGER := 0;
i PLS_INTEGER := 0;
j PLS_INTEGER;
BEGIN
<<outer_loop>>
LOOP
i := i + 1;
j := 0;
<<inner_loop>>
LOOP
j := j + 1;
s := s + i * j; -- Sum several products
EXIT inner_loop WHEN (j > 5);
EXIT outer_loop WHEN ((i * j) > 15);
END LOOP inner_loop;
END LOOP outer_loop;
DBMS_OUTPUT.PUT_LINE
('The sum of products equals: ' || TO_CHAR(s));
END;
/

Statement processed. The sum of products equals: 166

Answer 1

You will get the idea by just using one DBMS_OUTPUT in INNER_LOOP as follows:

SQL> SET SERVEROUT ON
SQL>
SQL> DECLARE
  2  S   PLS_INTEGER := 0;
  3  I   PLS_INTEGER := 0;
  4  J   PLS_INTEGER;
  5  BEGIN
  6  <<OUTER_LOOP>> LOOP
  7  I   := I + 1;
  8  J   := 0;
  9  <<INNER_LOOP>> LOOP
 10  J   := J + 1;
 11  S   := S + I * J; -- Sum several products
 12  DBMS_OUTPUT.PUT_LINE('I: ' || I || ' , J: ' || J || ' , S: ' || S);
 13  EXIT INNER_LOOP WHEN ( J > 5 );
 14  EXIT OUTER_LOOP WHEN ( ( I * J ) > 15 );
 15  END LOOP INNER_LOOP;
 16  END LOOP OUTER_LOOP;
 17  DBMS_OUTPUT.PUT_LINE('The sum of products equals: ' || TO_CHAR(S));
 18  END;
 19  /
I: 1 , J: 1 , S: 1
I: 1 , J: 2 , S: 3
I: 1 , J: 3 , S: 6
I: 1 , J: 4 , S: 10
I: 1 , J: 5 , S: 15
I: 1 , J: 6 , S: 21
I: 2 , J: 1 , S: 23
I: 2 , J: 2 , S: 27
I: 2 , J: 3 , S: 33
I: 2 , J: 4 , S: 41
I: 2 , J: 5 , S: 51
I: 2 , J: 6 , S: 63
I: 3 , J: 1 , S: 66
I: 3 , J: 2 , S: 72
I: 3 , J: 3 , S: 81
I: 3 , J: 4 , S: 93
I: 3 , J: 5 , S: 108
I: 3 , J: 6 , S: 126
I: 4 , J: 1 , S: 130
I: 4 , J: 2 , S: 138
I: 4 , J: 3 , S: 150
I: 4 , J: 4 , S: 166
The sum of products equals: 166

PL/SQL procedure successfully completed.

SQL>

Answer 2

For you to better understand how the loops ends, you need to add some lines to your process that will help you to understand why you get 166

SQL> SET SERVEROUTPUT ON SIZE UNLIMITED
 SQL> DECLARE
  2  s PLS_INTEGER := 0;
  3  i PLS_INTEGER := 0;
  4  j PLS_INTEGER;
  5  BEGIN
  6  <<outer_loop>>
  7  LOOP
  8  i := i + 1;
  9  j := 0;
 10  <<inner_loop>>
 11  LOOP
 12  j := j + 1;
 13  s := s + i * j; -- Sum several products
 14  dbms_output.put_line( ' I is : '||to_char(i)||' ');
 15  dbms_output.put_line( ' J is : '||to_char(j)||' ');
 16  dbms_output.put_line( ' S is : '||to_char(s)||' ');
 17  EXIT inner_loop WHEN (j > 5);
 18  EXIT outer_loop WHEN ((i * j) > 15);
 19  END LOOP inner_loop;
 20  END LOOP outer_loop;
 21  DBMS_OUTPUT.PUT_LINE
 22  ('The sum of products equals: ' || TO_CHAR(s));
 23* END;
SQL> /
I is : 1
J is : 1
S is : 1
I is : 1
J is : 2
S is : 3
I is : 1
J is : 3
S is : 6
I is : 1
J is : 4
S is : 10
I is : 1
J is : 5
S is : 15
I is : 1
J is : 6
S is : 21
I is : 2
J is : 1
S is : 23
I is : 2
J is : 2
S is : 27
I is : 2
J is : 3
S is : 33
I is : 2
J is : 4
S is : 41
I is : 2
J is : 5
S is : 51
I is : 2
J is : 6
S is : 63
I is : 3
J is : 1
S is : 66
I is : 3
J is : 2
S is : 72
I is : 3
J is : 3
S is : 81
I is : 3
J is : 4
S is : 93
I is : 3
J is : 5
S is : 108
I is : 3
J is : 6
S is : 126
I is : 4
J is : 1
S is : 130
I is : 4
J is : 2
S is : 138
I is : 4
J is : 3
S is : 150
I is : 4
J is : 4
S is : 166
The sum of products equals: 166

PL/SQL procedure successfully completed.

SQL>

The loop applies the formulas you got in your code, and it is the EXIT WHEN which applies to both loops which defines when the loops end. In your case the first loop should have ended when J is > 5, but you have a secondary loop that needs to end before and it only ends when ((i * j) > 15 , which only occurs when i = 4 and j = 4 , so 16.

Hope it clarifies a bit