Lab 11.1 Exercise Answers

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

11.1.1 Answers

a)

What output was printed on the screen?

A1:

Answer: Course 10 has 1 student(s) Course 20 has 6 student(s) Course 25 has 40 student(s) Course 100 has 7 student(s) Course 120 has 19 student(s) Course 122 has 20 student(s) Course 124 has 3 student(s) Course 125 has 6 student(s) Course 130 has 6 student(s) Course 132 has 0 student(s) Course 134 has 2 student(s) Course 135 has 2 student(s) Course 140 has 7 student(s) Course 142 has 3 student(s) Course 144 has 0 student(s) Course 145 has 0 student(s) Course 146 has 1 student(s) Course 147 has 0 student(s) Course 204 has 0 student(s) Course 210 has 0 student(s) Course 220 has 0 student(s) Course 230 has 2 student(s) Course 240 has 1 student(s) Course 310 has 0 student(s) Course 330 has 0 student(s) Course 350 has 9 student(s) Course 420 has 0 student(s) Course 430 has 0 student(s) Done PL/SQL procedure successfully completed.

Notice that each course number is displayed a single time only.

b)

Modify this script so that if a course has more than 20 students enrolled in it, an error message is displayed indicating that this course has too many students enrolled.

A2:

Answer: Your script should look similar to the script shown. All changes are shown in bold letters . -- ch11_1b.sql, version 2.0 SET SERVEROUTPUT ON DECLARE CURSOR course_cur IS SELECT course_no, section_id FROM section ORDER BY course_no, section_id; v_cur_course SECTION.COURSE_NO%TYPE := 0; v_students NUMBER(3) := 0; v_total NUMBER(3) := 0; BEGIN FOR course_rec IN course_cur LOOP IF v_cur_course = 0 THEN v_cur_course := course_rec.course_no; END IF; SELECT COUNT(*) INTO v_students FROM enrollment WHERE section_id = course_rec.section_id; IF v_cur_course = course_rec.course_no THEN v_total := v_total + v_students; IF v_total > 20 THEN RAISE_APPLICATION_ERROR (-20002, 'Course ' v_cur_course' has too many students'); END IF; ELSE DBMS_OUTPUT.PUT_LINE ('Course 'v_cur_course 'has 'v_total' student(s)'); v_cur_course := course_rec.course_no; v_total := 0; END IF; END LOOP; DBMS_OUTPUT.PUT_LINE ('Done...'); END;

Consider the result if you were to add another IF statement to this script, one in which the IF statement checks whether the value of the variable exceeds 20. If the value of the variable does exceed 20, the RAISE_APPLICATION_ERROR statement executes, and the error message is displayed on the screen.

c)	Execute the new version of the script. What output was printed on the screen?
A3:	Answer: Your output should look similar to the following: Course 10 has 1 student(s) Course 20 has 6 student(s) DECLARE * ERROR at line 1: ORA-20002: Course 25 has too many students ORA-06512: at line 21

Course 25 has 40 students enrolled. As a result, the IF statement

  IF v_total > 20 THEN   RAISE_APPLICATION_ERROR (-20002, 'Course '   v_cur_course' has too many students');   END IF;  

evaluates to TRUE, and the unnamed user -defined error is displayed on the screen.

d)	Generally, when an exception is raised and handled inside a loop, the loop does not terminate prematurely. Why do you think the cursor FOR loop terminates as soon as RAISE_APPLICATION_ERROR executes?
A4:	Answer: When the RAISE_APPLICATION_ERROR procedure is used to handle a user-defined exception, control is passed to the host environment as soon as the error is handled. Therefore, the cursor FOR loop terminates prematurely. In this case, it terminates as soon as the course that has more than 20 students registered for it is encountered .

When a user-defined exception is used with the RAISE statement, the exception propagates from the inner block to the outer block. For example:

 -- outer block BEGIN FOR record IN cursor LOOP -- inner block BEGIN RAISE my_exception; EXCEPTION WHEN my_exception THEN DBMS_OUTPUT.PUT_LINE ('An error has occurred'); END; END LOOP; END; 

In this example, the exception my_exception is raised and handled in the inner block. Control of the execution is passed to the outer block once the exception my_exception is raised. As a result, the cursor FOR loop will not terminate prematurely.

When the RAISE_APPLICATION_ERROR procedure is used, control is always passed to the host environment. The exception does not propagate from the inner block to the outer block. Therefore, any loop defined in the outer block will terminate prematurely if an error has been raised in the inner block, with the help of the RAISE_APPLICATION_ERROR procedure.