Prentice Hall Oracle Plsql By Example 3Rd Edition [Electronic resources] نسخه متنی

Lab 8.1 Exercise Answers

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

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.

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.

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.

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.

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

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.

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.