Lab 9.2 Exercises

9.2.1 Use a Cursor FOR Loop

a)

Write a PL/SQL block that will reduce the cost of all courses by 5% for courses having an enrollment of eight students or more. Use a cursor FOR loop that will update the course table.

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 20 “26, it is retrieving students who have the zipcode of the current record for the parent cursor. The parent cursor is processed from lines 13 “31. 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."

a)

Write a PL/SQL block with two cursor FOR loops. The parent cursor will call the student_id, first_name , and last_name from the student table for students with a student_id less than 110 and output one line with this information. For each student, the child cursor will loop through all the courses that the student is enrolled in, outputting the course_no and the description.

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,'9,999'));    END LOOP; END; 

b)	Before you run the preceding code, analyze what it is doing and determine what you think the result would be. Explain what is happening in each phase of the PL/SQL block and what is happening to the variables as control is passing through parent and child cursor.
c)	Run the code and see what the result is. Is it what you expected? Explain the difference.