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

Lab 11.1 Exercises

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: