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

Lab 9.2 Exercises

9.2.1 Use a Cursor FOR Loop

9.2.2 Process Nested Cursors

Cursors can be nested inside each other. Although this may sound complex, it is really just a loop inside a loop, much like nested loops, which were covered in the previous chapter. If you had one parent cursor and two child cursors, then each time the parent cursor makes a single loop, it will loop through each child cursor once and then begin a second round. In the following two examples, you will encounter a nested cursor with a single child cursor.

FOR EXAMPLE

SET SERVEROUTPUT ON
-- ch09_8a.sql
1 DECLARE
2 v_zip zipcode.zip%TYPE;
3 v_student_flag CHAR;
4 CURSOR c_zip IS
5 SELECT zip, city, state
6 FROM zipcode
7 WHERE state = 'CT';
8 CURSOR c_student IS
9 SELECT first_name, last_name
10 FROM student
11 WHERE zip = v_zip;
12 BEGIN
13 FOR r_zip IN c_zip
14 LOOP
15 v_student_flag := 'N';
16 v_zip := r_zip.zip;
17 DBMS_OUTPUT.PUT_LINE(CHR(10));
18 DBMS_OUTPUT.PUT_LINE('Students living in '||
19 r_zip.city);
20 FOR r_student in c_student
21 LOOP
22 DBMS_OUTPUT.PUT_LINE(
23 r_student.first_name||
24 ' '||r_student.last_name);
25 v_student_flag := 'Y';
26 END LOOP;
27 IF v_student_flag = 'N'
28 THEN
29 DBMS_OUTPUT.PUT_LINE
('No Students for this zipcode');
30 END IF;
31 END LOOP;
32 END;

There are two cursors in this example. The first is a cursor of the zipcodes, and the second cursor is a list of students. The variable v_zip is initialized in line 16 to be the zipcode of the current record of the c_zip cursor. The c_ student cursor ties in the c_zip cursor by means of this variable. Thus, when the cursor is processed in lines 2026, it is retrieving students who have the zipcode of the current record for the parent cursor. The parent cursor is processed from lines 1331. Each iteration of the parent cursor will only execute the DBMS_OUTPUT in lines 16 and 17 once. The DBMS_OUTPUT in line 22 will be executed once for each iteration of the child loop, producing a line of output for each student. The DBMS statement in line 29 will only execute if the inner loop did not execute. This was accomplished by setting a variable v_student_flag. The variable is set to N in the beginning of the parent loop. If the child loop executes at least once, the variable will be set to Y. After the child loop has closed, a check is made with an IF statement to determine the value of the variable. If it is still N, then it can be safely concluded that the inner loop did not process. This will then allow the last DBMS statement to execute. Nested cursors are more often parameterized. You will see parameters in cursors explained in depth in Lab 8.3, "Using Parameters in Cursors."

The following is an example of a nested cursor. Review the code.

FOR EXAMPLE

SET SERVEROUTPUT ON
-- ch09_9a.sql
DECLARE
v_amount course.cost%TYPE;
v_instructor_id instructor.instructor_id%TYPE;
CURSOR c_inst IS
SELECT first_name, last_name, instructor_id
FROM instructor;
CURSOR c_cost IS
SELECT c.cost
FROM course c, section s, enrollment e
WHERE s.instructor_id = v_instructor_id
AND c.course_no = s.course_no
AND s.section_id = e.section_id;
BEGIN
FOR r_inst IN c_inst
LOOP
v_instructor_id := r_inst.instructor_id;
v_amount := 0;
DBMS_OUTPUT.PUT_LINE(
'Amount generated by instructor '||
r_inst.first_name||' '||r_inst.last_name
||' is');
FOR r_cost IN c_cost
LOOP
v_amount := v_amount + NVL(r_cost.cost, 0);
END LOOP;
DBMS_OUTPUT.PUT_LINE
(' '||TO_CHAR(v_amount,'$999,999'));
END LOOP;
END;