Lab 8.3 Exercise Answers

This section gives you some suggested answers to the questions in Lab 8.3, 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.3.1 Answers

a)	What output was printed on the screen?
A1:	Answer: Your output should look like the following: Factorial of ten is: 3628800 Done PL/SQL procedure successfully completed.

Every time the loop is run, the value of v_counter is incremented by 1 implicitly, and the current value of the factorial is calculated. Once the value of v_counter increases to 10, the loop is run for the last time. At this point, the final value of the factorial is calculated, and the loop is terminated. After the loop has terminated , control is passed to the first statement outside of the loop ”in this case, DBMS_OUTPUT.PUT_LINE.

b)	How many times was the loop executed?
A2:	Answer: The loop was executed ten 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 upper limit is equal to 10.
c)	What is the value of the loop counter before the loop?
A3:	Answer: The loop counter is defined implicitly by the loop. Therefore, before the loop, the loop counter is undefined and has no value.
d)	What is the value of the loop counter after the loop?
A4:	Answer: Similarly, after the loop has completed, the loop counter is undefined again and can hold no value.
e)	How many times will the loop be executed if the value of v_counter is incremented by 5 inside the body of the loop?
A5:	Answer: If the value of v_counter is incremented by 5 inside the body of the loop, the PL/SQL block will not compile successfully. As a result, it will not execute at all.

In this example, variable v_counter is a loop counter. Therefore, its value can be incremented only implicitly by the loop. Any executable statement that causes v_counter to change its current value leads to compilation errors.

f)

Rewrite this script using the REVERSE option. What will the value of v_factorial be after the loop is completed?

A6:

Answer: Your script should look similar to the following script. Changes are shown in bold letters . The value of v_factorial will be equal to 3628800 after the loop is completed. -- ch08_4b.sql, version 2.0 SET SERVEROUTPUT ON DECLARE v_factorial NUMBER := 1; BEGIN FOR v_counter IN REVERSE 1..10 LOOP v_factorial := v_factorial * v_counter; END LOOP; -- control resumes here DBMS_OUTPUT.PUT_LINE ('Factorial of ten is: ' v_factorial); END;

The preceding script produces the following output:

  Factorial of ten is: 3628800   Done   PL/SQL procedure successfully completed.  

The value of v_factorial computed by this loop is equal to the value of v_factorial computed by the original loop. You will notice that in some cases it does not matter which option, IN or REVERSE, you are using to obtain the final result. You will also notice that in other cases, the result produced by the loop can differ significantly.

8.3.2 Answers

a)	What output will be printed on the screen?
A1:	Answer: Your output should look like the following: v_counter = 10 v_counter = 8 v_counter = 6 v_counter = 4 v_counter = 2 v_counter = 0 Done PL/SQL procedure successfully completed.

Notice that the values of v_counter are displayed in decreasing order from 10 to 0 because the REVERSE option is used. Remember that regardless of the option used, the lower limit is referenced first.

b)	How many times will the body of the loop be executed?
A2:	Answer: The body of the loop will be executed eleven times, since the range of the integer numbers specified varies from 0 to 10.
c)	How many times will the value of v_counter be displayed on the screen?
A3:	Answer: The value of v_counter will be displayed on the screen six times, since the IF statement will evaluate to TRUE only for even integers.
d)	How would you change this script to start the list from 0 and go up to 10?
A4:	Answer: Your script should look similar to the script shown below. Changes are shown in bold letters. To start the list of integers from 0 and go up to 10, the IN option needs to be used in the loop. -- ch08_5b.sql, version 1.0 SET SERVEROUTPUT ON BEGIN FOR v_counter IN 0..10 LOOP -- if v_counter is even, display its value on the -- screen IF MOD(v_counter, 2) = 0 THEN DBMS_OUTPUT.PUT_LINE ('v_counter = 'v_counter); END IF; END LOOP; -- control resumes here DBMS_OUTPUT.PUT_LINE ('Done...'); END;

This example produces the following output:

  v_counter = 0   v_counter = 2   v_counter = 4   v_counter = 6   v_counter = 8   v_counter = 10   Done   PL/SQL procedure successfully completed.  

Notice that when the IN option is used, the value of v_counter is initialized to 0, and, with each iteration of the loop, it is incremented by 1. When the REVERSE option is used, v_counter is initialized to 10, and its value is decremented by 1 with each iteration of the loop.

e)

How would you change the script to display only odd numbers on the screen?

A5:

Answer: Your script should look similar to the following script. Changes are shown in bold letters. -- ch08_5c.sql, version 3.0 SET SERVEROUTPUT ON BEGIN FOR v_counter IN REVERSE 0..10 LOOP -- if v_counter is even, display its value on the -- screen IF MOD(v_counter, 2) != 0 THEN DBMS_OUTPUT.PUT_LINE ('v_counter = 'v_counter); END IF; END LOOP; -- control resumes here DBMS_OUTPUT.PUT_LINE ('Done...'); END;

Notice that only the test condition of the IF statement is changed in order to display the list of odd integers, and the following output is produced:

  v_counter = 9   v_counter = 7   v_counter = 5   v_counter = 3   v_counter = 1   Done   PL/SQL procedure successfully completed.  

f)	How many times will the loop be executed in this case?
A6:	Answer: In this case the loop will be executed eleven times.

Based on the test condition used in the IF statement, even or odd integers are displayed on the screen. Depending on the test condition, the number of times v_counter is displayed on the screen varies. However, the loop is executed eleven times as long as the number range specified is 0 to 10.