Lab 8.4 Exercise Answers

This section gives you some suggested answers to the questions in Lab 8.4, with discussion related to how those answers resulted. The most important thing to realize is whether your answer works. You should figure out the implications of the answers here and what the effects are from any different answers you may come up with.

8.4.1 Answers

a)	What output was printed on the screen?
A1:	Answer: Your output should look like the following: Outer Loop i = 1 v_test = 0 Inner Loop j = 1 i = 1 v_test = 1 Inner Loop j = 2 i = 1 v_test = 1 Outer Loop i = 2 v_test = 1 Inner Loop j = 1 i = 2 v_test = 2 Inner Loop j = 2 i = 2 v_test = 2 Outer Loop i = 3 v_test = 2 Inner Loop j = 1 i = 3 v_test = 3 Inner Loop j = 2 i = 3 v_test = 3 PL/SQL procedure successfully completed.

Every time the outer loop is run, the value of the loop counter is incremented by 1 implicitly and displayed on the screen. In addition, the value of v_test is displayed on the screen and is incremented by 1, as well. Next, control is passed to the inner loop.

Every time the inner loop is run, the value of the inner loop counter is incremented by 1 and displayed on the screen, along with the value of the outer loop counter and the variable v_test .

b)	How many times was the outer loop executed?
A2:	Answer: The outer loop was executed three times, according to the range specified by the lower limit and the upper limit of the loop. In this example, the lower limit is equal to 1, and the upper limit is equal to 3.
c)	How many times was the inner loop executed?
A3:	Answer: The inner loop was executed six times.

For each iteration of the outer loop, the inner loop was executed twice. However, the outer loop was executed three times. Overall, the inner loop was executed six times.

d)	What are the values of the loop counters, i and j, after both loops terminate?
A4:	Answer: After both loops terminate, both loop counters are undefined again and can hold no values. As mentioned earlier, the loop counter ceases to exist once the numeric FOR loop is terminated .
e)	Rewrite this script using the REVERSE option for both loops. How many times will each loop be executed in this case?
A5:	Answer: Your script should be similar to the script below. Changes are shown in bold letters . The outer loop will execute three times, and the inner loop will execute six times. -- ch08_6b.sql, version 2.0 SET SERVEROUTPUT ON DECLARE v_test NUMBER := 0; BEGIN <<outer_loop>> FOR i IN REVERSE 1..3 LOOP DBMS_OUTPUT.PUT_LINE('Outer Loop'); DBMS_OUTPUT.PUT_LINE('i = 'i); DBMS_OUTPUT.PUT_LINE('v_test = 'v_test); v_test := v_test + 1; <<inner_loop>> FOR j IN REVERSE 1..2 LOOP DBMS_OUTPUT.PUT_LINE('Inner Loop'); DBMS_OUTPUT.PUT_LINE('j = 'j); DBMS_OUTPUT.PUT_LINE('i = 'i); DBMS_OUTPUT.PUT_LINE('v_test = 'v_test); END LOOP inner_loop; END LOOP outer_loop; END;

This script produces the following output:

  Outer Loop   i = 3   v_test = 0   Inner Loop   j = 2   i = 3   v_test = 1   Inner Loop   j = 1   i = 3   v_test = 1   Outer Loop   i = 2   v_test = 1   Inner Loop   j = 2   i = 2   v_test = 2   Inner Loop   j = 1   i = 2   v_test = 2   Outer Loop   i = 1   v_test = 2   Inner Loop   j = 2   i = 1   v_test = 3   Inner Loop   j = 1   i = 1   v_test = 3   PL/SQL procedure successfully completed.  

Notice that the output produced by this example has changed significantly from the output in the previous example. The values of the loop counters are decremented because the REVERSE option is used. However, the value of the variable v_test was not affected by using the REVERSE option.