Lab 8.1 Exercise Answers | Oracle PL[s]SQL by Example

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

What output was printed on the screen?

A1:

Answer: Your output should look like the following:

  v_counter = 1   v_counter = 2   v_counter = 3   v_counter = 4   v_counter = 5   Done...   PL/SQL procedure successfully completed.

Every time the loop is run, the statements in the body of the loop are executed. In this script, the value of v_counter is incremented by 1 and displayed on the screen. The EXIT condition is evaluated for each value of v_counter . Once the value of v_counter increases to 5, the loop is terminated . For the first iteration of the loop, the value of v_counter is equal to 1, and it is displayed on the screen, and so forth. After the loop has terminated, "Done..." is displayed on the screen.

b)	How many times was the loop executed?
A2:	Answer: The loop was executed five times.

Once the value of v_counter increases to 5, the IF statement

  IF v_counter = 5 THEN   EXIT;   END IF;

evaluates to TRUE, and the loop is terminated.

The loop counter tracks the number of times the loop is executed. You will notice that in this exercise, the maximum value of v_counter is equal to the number of times the loop is iterated.

c)	What is the EXIT condition for this loop?
A3:	Answer: The EXIT condition for this loop is `v_counter = 5` .

The EXIT condition is used as a part of an IF statement. The IF statement evaluates the EXIT condition to TRUE or FALSE, based on the current value of v_counter .

How many times will the value of the variable v_counter be displayed if the DBMS_OUTPUT.PUT_LINE statement is used after the END IF statement?

A4:

Answer: The value of v_counter will be displayed four times.

  LOOP   v_counter := v_counter + 1;   IF v_counter = 5 THEN   EXIT;   END IF;   DBMS_OUTPUT.PUT_LINE ('v_counter = 'v_counter);   END LOOP;

Assume that the loop has iterated four times already. Then the value of v_counter is incremented by 1, so v_counter is equal to 5. Next, the IF statement evaluates the EXIT condition. The EXIT condition yields TRUE, and the loop is terminated. The DBMS_OUTPUT.PUT_LINE statement is not executed for the fifth iteration of the loop because control is passed to the next executable statement after the END LOOP statement. Thus, only four values of v_counter are displayed on the screen.

e)	Why does the number of times the loop counter value is displayed on the screen differ when the DBMS_OUTPUT.PUT_ LINE statement is placed after the END IF statement?
A5:	Answer: When the DBMS_OUTPUT.PUT_LINE statement is placed before the IF statement, the value of `v_counter` is displayed on the screen first. Then it is evaluated by the IF statement. The fifth iteration of the loop "v_counter = 5" is displayed first, then the EXIT condition yields TRUE and the loop is terminated. When the DBMS_OUTPUT.PUT_LINE statement is placed after the END IF statement, the EXIT condition is evaluated prior to the execution of the DBMS_OUTPUT.PUT_ LINE statement. Thus, for the fifth iteration of the loop, the EXIT condition evaluates to TRUE before the value of `v_counter` is displayed on the screen by the DBMS_OUTPUT.PUT_LINE statement.
f)	Rewrite this script using the EXIT WHEN condition instead of the EXIT condition, so that it produces the same result.
A6:	Answer: Your script should look similar to the following script. Changes are shown in bold letters . -- ch08_1b.sql, version 2.0 SET SERVEROUTPUT ON DECLARE v_counter BINARY_INTEGER := 0; BEGIN LOOP -- increment loop counter by one v_counter := v_counter + 1; DBMS_OUTPUT.PUT_LINE ('v_counter = 'v_counter); -- if EXIT WHEN condition yields TRUE exit the loop EXIT WHEN v_counter = 5; END LOOP; -- control resumes here DBMS_OUTPUT.PUT_LINE ('Done...'); END;

Notice that the IF statement has been replaced by the EXIT WHEN statement. The rest of the statements in the body of the loop do not need to be changed.

8.1.2 Answers

a)	How many sections will be added for the specified course number?
A1:	Answer: Four sections were added for the given course number.
b)	How many times will the loop be executed if the course number is not valid?
A2:	Answer: The loop will be executed one time.

If the course number is not valid, the INSERT statement

  INSERT INTO section   (section_id, course_no, section_no, instructor_id,   created_date, created_by, modified_date, modified_by)   VALUES   (section_id_seq.nextval, v_course, v_sec_num,   v_instructor_id, SYSDATE, USER, SYSDATE, USER);

will cause an exception to be raised. As soon as an exception is raised, control is passed out of the loop to the exception handler. Therefore, if the course number is not valid, the loop will be executed only once.

How would you change this script to add 10 sections for the specified course number?

A3:

Answer: Your script should look similar to the following script. Changes are shown in bold letters.

 -- ch08_2b.sql, version 2.0 DECLARE v_course course.course_no%type := 430; v_instructor_id instructor.instructor_id%type := 102; v_sec_num section.section_no%type := 0; BEGIN LOOP -- increment section number by one v_sec_num := v_sec_num + 1; INSERT INTO section (section_id, course_no, section_no, instructor_id, created_date, created_by, modified_date, modified_by) VALUES (section_id_seq.nextval, v_course, v_sec_num, v_instructor_id, SYSDATE, USER, SYSDATE, USER); -- if number of sections added is ten exit the loop  EXIT WHEN v_sec_num = 10;  END LOOP; -- control resumes here COMMIT; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE ('An error has occurred'); END;

In order to add 10 sections for the given course number, the test value of v_sec_num in the EXIT condition is changed to 10.

Note that before you execute this version of the script you need to delete records from the SECTION table that were added when you executed the original example. If you did not run the original script, you do not need to delete records from the SECTION table.

The SECTION table has a unique constraint defined on the COURSE_NO and SECTION_NO columns . In other words, the combination of course and section numbers allows you to uniquely identify each row of the table. When the original script is executed, it creates four records in the SECTION table for course number 430, section numbers 1, 2, 3, and 4. When the new version of this script is executed, the unique constraint defined on the SECTION table is violated because there already are records corresponding to course number 430 and section numbers 1, 2, 3, and 4. Therefore, these rows must be deleted from the SECTION table as follows :

  DELETE FROM section   WHERE course_no = 430   AND section_no <= 4;

Once these records are deleted from the SECTION table, you can execute the new version of the script.

d)	How would you change the script to add only even-numbered sections (maximum section number is 10) for the specified course number?
A4:	Answer: Your script should look similar to the following script. Changes are shown in bold letters. In order to run this script, you will need to delete records from the SECTION table that were added by the previous version. With each iteration of the loop, the value of `v_sec_num` should be incremented by two, as shown: -- ch08_2c.sql, version 3.0 SET SERVEROUTPUT ON DECLARE v_course course.course_no%type := 430; v_instructor_id instructor.instructor_id%type := 102; v_sec_num section.section_no%type := 0; BEGIN LOOP -- increment section number by two v_sec_num := v_sec_num + 2; INSERT INTO section (section_id, course_no, section_no, instructor_id, created_date, created_by, modified_date, modified_by) VALUES (section_id_seq.nextval, v_course, v_sec_num, v_instructor_id, SYSDATE, USER, SYSDATE, USER); -- if number of sections added is ten exit the loop EXIT WHEN v_sec_num = 10; END LOOP; -- control resumes here COMMIT; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE ('An error has occurred'); END;
e)	How many times will the loop be executed in this case?
A5:	Answer: The loop is executed five times when even-numbered sections are added for the given course number.