Lab 11.1 Exercises | Oracle PL[s]SQL by Example

11.1.1 Use RAISE_APPLICATION_ERROR

In this exercise, you calculate how many students are registered for each course. You then display a message on the screen that contains the course number and the number of students registered for it. The original PL/SQL script will not contain any exception handlers, so you will be asked to add the RAISE_APPLICATION_ERROR statement.

Create the following PL/SQL script:

 -- ch11_1a.sql, version 1.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;       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;

Take a closer look this script. As you learned earlier, this script determines the number of students registered for each course. It then displays the course number and the number of students on the screen. In order to achieve these results, the cursor needs to be defined on the SECTION table. This cursor retrieves the course numbers and section IDs. It also now defines three variables : v_cur_course , v_students , and v_total .

The variable v_cur_course holds the number of the current course. There are duplicate course numbers in the SECTION table, because a course can have multiple sections. In order to display the number of students for each course rather than each section, you need to store the number of the current course. For example, course 10 has three sections: 1, 2, and 3. Section 1 has 3 students, section 2 has 5 students, and section 3 has 10 students. Therefore, course 10 has 18 students. Once this number is calculated, the message "10 has 18 student(s)" can be displayed on the screen. As a result, you need to compare the variable v_cur_course to the course number returned by the cursor.

The variable v_students holds the number of students registered for a specific section of a course. As long as the value of the variable v_cur_course equals the value of the course_rec.course_no , the variable v_students is added to the current value of the variable v_total , which holds the total number of students registered for a given course.

Notice that in the body of the cursor FOR loop, there are two IF statements. The first IF statement

  IF v_cur_course = 0 THEN   v_cur_course := course_rec.course_no;   END IF;

is executed only once, for the first iteration of the cursor FOR loop. This IF statement guarantees that the value of course_rec.course_no is assigned to the variable v_cur_course before any further processing.

The second IF statement

  IF v_cur_course = course_rec.course_no THEN   v_total := v_total + v_students;   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;

compares the value of v_cur_course to the value of the course_rec.course_no . For the first iteration of the cursor FOR loop, this condition of the IF statement evaluates to TRUE, and the value of v_students is added to the current value of v_total . For the next iteration of the cursor FOR loop, the IF statement evaluates to TRUE if the course number has not changed. However, if the course number has changed, this IF statement evaluates to FALSE, and the ELSE part of the IF statement is executed. Therefore, the DBMS_OUTPUT.PUT_LINE statement displays the course information on the screen, the value of the course_rec.course_no is assigned to the variable v_cur_course , and the value of the variable v_total is set to 0 again. Why do you think the variable v_total must be set to 0?

Execute the script, and then answer the following questions:

a)	What output was printed on the screen?
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.
c)	Execute the new version of the script. What output was printed 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?