Lab 9.2 Using Cursor for Loops and Nesting Cursors

Team-Fly    

Oracle® PL/SQL® Interactive Workbook, Second Edition
By Benjamin Rosenzweig, Elena Silvestrova
Table of Contents
Chapter 9.  Introduction to Cursors


Lab Objectives

After this Lab, you will be able to:

  • Use a Cursor FOR Loop

  • Process Nested Cursors

There is an alternative method of handling cursors. It is called the cursor FOR loop because of the simplified syntax that is used. When using the cursor FOR loop, the process of opening, fetching, and closing is handled implicitly. This makes the blocks much simpler to code and easier to maintain.

The cursor FOR loop specifies a sequence of statements to be repeated once for each row returned by the cursor. Use the cursor FOR loop if you need to FETCH and PROCESS each and every record from a cursor.

graphics/intfig03.gif FOR EXAMPLE

Assume the existence of a table called log with one column.

 create table table_log         (description VARCHAR2(250));  -- ch09_7a.sql  DECLARE     CURSOR c_student IS        SELECT student_id, last_name, first_name          FROM student         WHERE student_id < 110;  BEGIN     FOR r_student IN c_student     LOOP        INSERT INTO table_log           VALUES(r_student.last_name);     END LOOP;  END; 

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.

graphics/intfig03.gif 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."

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.

graphics/intfig03.gif 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; 

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.

Lab 9.2 Exercise Answers

9.2.1 Answers

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.

A1:

Answer: Your block should look like this:

 -- ch09_10a.sql  DECLARE     CURSOR c_group_discount IS        SELECT DISTINCT s.course_no          FROM section s, enrollment e         WHERE s.section_id = e.section_id          GROUP BY s.course_no, e.section_id, s.section_id         HAVING COUNT(*)>=8;  BEGIN     FOR r_group_discount IN c_group_discount   LOOP        UPDATE course           SET cost = cost * .95         WHERE course_no = r_group_discount.course_no;     END LOOP;     COMMIT;  END; 

The cursor c_group_discount is declared in the declarative section. The proper SQL is used to generate the select statement to answer the question given. The cursor is processed in a FOR loopin each iteration of the loop the SQL update statement will be executed. This means it does not have to be opened, fetched, and closed. Also, it means that a cursor attribute does not have to be used to create an exit condition for the loop that is processing the cursor.

9.2.2 Answers

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.

A1:

Answer: Your block should look be similar to this:

 -- ch09_11a.sql  DECLARE     v_sid student.student_id%TYPE;     CURSOR c_student IS        SELECT student_id, first_name, last_name          FROM student         WHERE student_id < 110;     CURSOR c_course IS        SELECT c.course_no, c.description          FROM course c, section s, enrollment e         WHERE c.course_no = s.course_no           AND s.section_id = e.section_id           AND e.student_id = v_sid;  BEGIN     FOR r_student IN c_student     LOOP        v_sid := r_student.student_id;        DBMS_OUTPUT.PUT_LINE(chr(10));        DBMS_OUTPUT.PUT_LINE(' The Student '||           r_student.student_id||' '||           r_student.first_name||' '||           r_student.last_name);        DBMS_OUTPUT.PUT_LINE(' is enrolled in the '||           'following courses: ');        FOR r_course IN c_course        LOOP           DBMS_OUTPUT.PUT_LINE(r_course.course_no||              '   '||r_course.description);        END LOOP;     END LOOP;  END; 

The select statements for the two cursors are defined in the declarative section of the PL/SQL block. A variable to store the student_id from the parent cursor is also declared. The course cursor is the child cursor, and, since it makes use of the variable v_sid, the variable must be declared first. Both cursors are processed with a FOR loop, which eliminates the need for OPEN, FETCH, and CLOSE. When the parent student loop is processed, the first step is to initialize the variable v_sid, and the value is then used when the child loop is processed. DBMS_OUTPUT is used so that display is generated for each cursor loop. The parent cursor will display the student name once, and the child cursor will display the name of each course in which the student is enrolled.

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.

A1:

Answer: The declaration section contains a declaration for two variables. The first is v_amount of the datatype matching that of the cost in the course table; the second is the v_instructor_id of the datatype matching the instructor_id in the instructor table. There are also two declarations for two cursors. The first is for c_inst, which is comprised of the first_name, last_name, and instructor_id for an instructor from the instructor table. The second cursor, c_cost, will produce a result set of the cost of the course taken for each student enrolled in a course by the instructor that matches the variable v_instructor_id. These two cursors will be run in nested fashion. First, the cursor c_inst is opened in a FOR loop. The value of the variable v_instructor_id is initialized to match the instructor_id of the current row of the c_inst cursor. The variable v_amount is initialized to 0. The second cursor is open within the loop for the first cursor. This means that for each iteration of the cursor c_inst, the second cursor will be opened, fetched, and closed. The second cursor will loop through all the cost generated by each student enrolled in a course for the instructor, which is current of the c_inst cursor. Each time the nest loop iterates, it will increase the variable v_amount by adding the current cost in the c_cost loop. Prior to opening the c_cost loop, there is a DBMS_OUTPUT to display the instructor name. After the c_cost cursor loop is closed, it will display the total amount generated by all the enrollments of the current instructor.

c)

Run the code and see what the result is. Is it what you expected? Explain the difference.

A2:

Answer: The result set would be as follows:

 Generated by instructor Fernand  Hanks  $16,915  Generated by instructor Tom  Wojick  $18,504  Generated by instructor Nina  Schorin  $30,137  Generated by instructor Gary  Pertez  $24,044  Generated by instructor Anita  Morris  $13,389  Generated by instructor Todd  Smythe  $14,940  Generated by instructor Rick  Chow  $0  Generated by instructor Charles  Lowry  $12,175  Generated by instructor Marilyn  Frantzen  $13,224  PL/SQL procedure successfully completed. 

In this example, the nested cursor is tied to the current row of the outer cursor by means of the variable v_instructor_id. A more common way of doing this is to pass a parameter to a cursor. You will learn more about how to achieve this in Chapter 15, "Advanced Cursors."

Lab 9.2 Self-Review Questions

In order to test your progress, you should be able to answer the following questions.

Answers appear in Appendix A, Section 9.2.

1)

In a cursor FOR loop, cursor and loop handling is carried out implicitly.

  1. _____ True

  2. _____ False

2)

In a cursor FOR loop, it is necessary to declare the rowtype for the cursor.

  1. _____ True

  2. _____ False

3)

Is it necessary to open, fetch, and close a cursor in a cursor FOR loop?

  1. _____ Yes

  2. _____ No

4)

The child loop in a nested cursor is passed through how many times for each cycle of the parent?

  1. _____ Three

  2. _____ One or more

  3. _____ Two

  4. _____ It depends on the individual code.

5)

If the SELECT statement of the cursor makes use of a variable, when should the variable be declared?

  1. _____ It is a bind variable and therefore does not need to be declared.

  2. _____ In the declarative section.

  3. _____ Before the cursor that is using it.

  4. _____ It will be self-declared upon initialization.


    Team-Fly    
    Top
     



    Oracle PL. SQL Interactive Workbook
    Oracle PL/SQL Interactive Workbook (2nd Edition)
    ISBN: 0130473200
    EAN: 2147483647
    Year: 2002
    Pages: 146

    flylib.com © 2008-2017.
    If you may any questions please contact us: flylib@qtcs.net