Lab 15.1 Using Parameters with Cursors and for Update Cursors

Team-Fly    

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


Lab Objectives

After this Lab, you will be able to:

  • Use Parameters in a Cursor

  • Use a FOR UPDATE Cursor

  • Use the WHERE CURRENT OF Clause

Cursors with Parameters

A cursor can be declared with parameters. This enables a cursor to generate a specific result set, which is, on the one hand, narrow, but, on the other hand, reusable. A cursor of all the data from the ZIPCODE table may be very useful, but it would be more useful for certain data processing if it held information for only one state. At this point, you know how to create such a cursor. But wouldn't it be more useful if you could create a cursor that could accept a parameter of a state and then run through only the city and zip for that state?

graphics/intfig03.gif FOR EXAMPLE

 CURSOR c_zip (p_state IN zipcode.state%TYPE) IS     SELECT zip, city, state       FROM zipcode      WHERE state = p_state; 

The main points to keep in mind for parameters in cursors are as follows:

  • Cursor parameters make the cursor more reusable.

  • Cursor parameters can be assigned default values.

  • The scope of the cursor parameters is local to the cursor.

  • The mode of the parameters can only be IN.

When a cursor has been declared as taking a parameter, it must be called with a value for that parameter. The c_zip cursor that was just declared is called as follows:

 OPEN c_zip (parameter_value) 

The same cursor could be opened with a FOR CURSOR loop as follows:

 FOR r_zip IN c_zip('NY')  LOOP  ... 

Lab 15.1 Exercises

15.1.1 Use Parameters in a Cursor

a)

Complete the code for the parameter cursor that was begun in the preceding example. Include a DBMS_OUTPUT line that displays the zipcode, city, and state. This is identical to the process you have already used in a FOR CURSOR loop, only now, when you open the cursor, you pass a parameter.

b)

The following PL/SQL code is complex. It involves all of the topics covered so far in this chapter. There is a nested cursor with three levels, meaning a grandparent cursor, a parent cursor, and a child cursor. Before running this script, review the code and identify the levels of nesting in the code. When you describe each level of the code, explain what parameters are being passed into the cursor and why. What do you think the result will be from running this statement?

 -- ch15_1a.sql  SET SERVEROUTPUT ON    1    DECLARE    2       CURSOR c_student IS    3          SELECT first_name, last_name, student_id    4            FROM student    5           WHERE last_name LIKE 'J%';    6       CURSOR c_course    7             (i_student_id IN                              student.student_id%TYPE)   8       IS   9          SELECT c.description, s.section_id sec_id  10            FROM course c, section s, enrollment e  11           WHERE e.student_id = i_student_id  12             AND c.course_no = s.course_no  13             AND s.section_id = e.section_id;  14       CURSOR c_grade(i_section_id IN                             section.section_id%TYPE,  15                      i_student_id IN                             student.student_id%TYPE)  16            IS  17            SELECT gt.description grd_desc,  18               TO_CHAR  19                  (AVG(g.numeric_grade), '999.99')                                       num_grd  20              FROM enrollment e,  21                   grade g, grade_type gt  22             WHERE e.section_id = i_section_id  23               AND e.student_id = g.student_id  24               AND e.student_id = i_student_id  25               AND e.section_id = g.section_id  26               AND g.grade_type_code =                                 gt.grade_type_code  27             GROUP BY gt.description ;  28    BEGIN  29       FOR r_student IN c_student  30       LOOP  31         DBMS_OUTPUT.PUT_LINE(CHR(10));  32         DBMS_OUTPUT.PUT_LINE(r_student.first_name||  33            '  '||r_student.last_name);  34         FOR r_course IN                    c_course(r_student.student_id)  35         LOOP  36            DBMS_OUTPUT.PUT_LINE                   ('Grades for course :'||  37               r_course.description);  38            FOR r_grade IN c_grade(r_course.sec_id,  39                              r_student.student_id)  40            LOOP  41               DBMS_OUTPUT.PUT_LINE(r_grade.num_grd||  42                  '  '||r_grade.grd_desc);  43            END LOOP;  44         END LOOP;  45       END LOOP;  46   END; 
c)

Now run the code and see if you were correct. Analyze the code line by line and explain what is being processed and then displayed for each line.

15.1.2 Use a FOR UPDATE Cursor

The cursor FOR UPDATE clause is only used with a cursor when you want to update tables in the database. Generally, when you execute a SELECT statement, you are not locking any rows. The purpose of using the FOR UPDATE clause is to lock the rows of the tables that you want to update, so that another user cannot perform an update until you perform your update and release the lock. The next COMMIT or ROLLBACK statement releases the lock. The FOR UPDATE clause will change the manner in which the cursor operates in only a few respects. When you open a cursor, all rows that meet the restriction criteria are identified as part of the active set. Using the FOR UPDATE clause will lock these rows that have been identified in the active set. If the FOR UPDATE clause is used, then rows may not be fetched from the cursor until a COMMIT has been issued. It is important for you to consider where to place the COMMIT. Be careful to consider issues covered in the transaction management topic in Chapter 4.

The syntax is simply to add FOR UPDATE to the end of the cursor definition. If there are multiple items being selected, but you only want to lock one of them, then end the cursor definition with the following syntax:

 FOR UPDATE OF <item_name> 

graphics/intfig03.gif FOR EXAMPLE

 -- ch15_2a.sql  DECLARE    CURSOR c_course IS          SELECT course_no, cost         FROM course FOR UPDATE;  BEGIN     FOR r_course IN c_course     LOOP        IF r_course.cost < 2500        THEN           UPDATE course              SET cost = r_course.cost + 10            WHERE course_no = r_course.course_no;        END IF;     END LOOP;  END; 

This example shows how to update the cost of all courses with a cost under $2500. It will increment them by 10.

a)

In the example just given, where should the COMMIT be placed? What are the issues involved in deciding where to place a COMMIT in this example?

graphics/intfig03.gif FOR EXAMPLE

 -- ch15_3a.sql  DECLARE     CURSOR c_grade(        i_student_id IN enrollment.student_id%TYPE,        i_section_id IN enrollment.section_id%TYPE)     IS        SELECT final_grade          FROM enrollment         WHERE student_id = i_student_id           AND section_id = i_section_id         FOR UPDATE;     CURSOR c_enrollment IS        SELECT e.student_id, e.section_id          FROM enrollment e, section s         WHERE s.course_no = 135           AND e.section_id = s.section_id;  BEGIN     FOR r_enroll IN c_enrollment     LOOP        FOR r_grade IN c_grade(r_enroll.student_id,                               r_enroll.section_id)        LOOP           UPDATE enrollment              SET final_grade  = 90            WHERE student_id = r_enroll.student_id              AND section_id = r_enroll.section_id;        END LOOP;     END LOOP;  END; 
b)

What do you think will happen if you run the code in this example? After making your analysis, run the code, and then perform a SELECT statement to determine if your guess is correct.

c)

Where should the COMMIT go in the preceding example? Explain the considerations.

FOR UPDATE OF can be used when creating a cursor for update that is based on multiple tables. FOR UPDATE OF locks the rows of a stable that both contain one of the specified columns and are members of the active set. In other words, it is the means of specifying which table you want to lock. If the FOR UPDATE OF clause is used, then rows may not be fetched from the cursor until a COMMIT has been issued.

graphics/intfig03.gif FOR EXAMPLE

 -- ch15_4a.sql  DECLARE     CURSOR c_stud_zip IS        SELECT s.student_id, z.city          FROM student s, zipcode z         WHERE z.city = 'Brooklyn'           AND s.zip = z.zip         FOR UPDATE OF phone;  BEGIN    FOR r_stud_zip IN c_stud_zip    LOOP       UPDATE student          SET phone = '718'||SUBSTR(phone,4)        WHERE student_id = r_stud_zip.student_id;    END LOOP;  END; 

d)

What changes to the database will take place if the preceding example is run? Explain specifically what is being locked as well as when it is locked and when it is released.

15.1.3 Use the WHERE CURRENT OF Clause

Use WHERE CURRENT OF when you want to update the most recently fetched row. WHERE CURRENT OF can only be used with a FOR UPDATE OF cursor. The advantage of the WHERE CURRENT OF clause is that it enables you to eliminate the WHERE clause in the UPDATE statement.

graphics/intfig03.gif FOR EXAMPLE

 -- ch15_5a.sql  DECLARE     CURSOR c_stud_zip IS        SELECT s.student_id, z.city          FROM student s, zipcode z         WHERE z.city = 'Brooklyn'           AND s.zip = z.zip         FOR UPDATE OF phone;  BEGIN     FOR r_stud_zip IN c_stud_zip     LOOP        DBMS_OUTPUT.PUT_LINE(r_stud_zip.student_id);        UPDATE student           SET phone = '718'||SUBSTR(phone,4)         WHERE CURRENT OF c_stud_zip;     END LOOP;  END; 

a)

Compare the last two examples. Explain their similarities and differences. What has been altered by using the WHERE CURRENT OF clause? What is the advantage of doing this?

graphics/intfig07.gif

The FOR UPDATE and WHERE CURRENT OF syntax can be used with cursors that are performing a delete as well as an update.


Lab 15.1 Exercise Answers

15.1.1 Answers

a)

Complete the code for the parameter cursor that was begun in the preceding example. Include a DBMS_OUTPUT line that displays the zipcode, city, and state. This is identical to the process you have already used in a FOR CURSOR loop, only now, when you open the cursor, you pass a parameter.

A1:

Answer: Your block should look like this:

 -- ch15_17a.sql  DECLARE     CURSOR c_zip (p_state IN zipcode.state%TYPE) IS        SELECT zip, city, state          FROM zipcode         WHERE state = p_state  BEGIN     FOR r_zip IN c_zip('NJ')     LOOP  ...       DBMS_OUTPUT.PUT_LINE(r_zip.city||          ' '||r_zip.zip');     END LOOP;  END; 

To complete the block, the cursor declaration must be surrounded by DECLARE and BEGIN. The cursor is opened by passing the parameter "NJ," and then, for each iteration of the cursor loop, the zipcode and the city are displayed by using the built-in package DBMS_OUTPUT.

b)

The following PL/SQL code is complex. It involves all of the topics covered so far in this chapter. There is a nested cursor with three levels, meaning a grandparent cursor, a parent cursor, and a child cursor. Before running this script, review the code and identify the levels of nesting in the code. When you describe each level of the code, explain what parameters are being passed into the cursor and why. What do you think the result will be from running this statement?

 -- ch15_1a.sql  SET SERVEROUTPUT ON    1    DECLARE    2       CURSOR c_student IS    3          SELECT first_name, last_name, student_id    4            FROM student    5           WHERE last_name LIKE 'J%';    6       CURSOR c_course    7             (i_student_id IN                              student.student_id%TYPE)   8       IS   9          SELECT c.description, s.section_id sec_id  10            FROM course c, section s, enrollment e  11           WHERE e.student_id = i_student_id  12             AND c.course_no = s.course_no  13             AND s.section_id = e.section_id;  14       CURSOR c_grade(i_section_id IN                             section.section_id%TYPE,  15                      i_student_id IN                             student.student_id%TYPE)  16            IS  17            SELECT gt.description grd_desc,  18               TO_CHAR  19                  (AVG(g.numeric_grade), '999.99')                                       num_grd  20              FROM enrollment e,  21                   grade g, grade_type gt  22             WHERE e.section_id = i_section_id  23               AND e.student_id = g.student_id  24               AND e.student_id = i_student_id  25               AND e.section_id = g.section_id  26               AND g.grade_type_code =                                 gt.grade_type_code  27             GROUP BY gt.description ;  28    BEGIN  29       FOR r_student IN c_student  30       LOOP  31         DBMS_OUTPUT.PUT_LINE(CHR(10));  32         DBMS_OUTPUT.PUT_LINE(r_student.first_name||  33            '  '||r_student.last_name);  34         FOR r_course IN                    c_course(r_student.student_id)  35         LOOP  36            DBMS_OUTPUT.PUT_LINE                   ('Grades for course :'||  37               r_course.description);  38            FOR r_grade IN c_grade(r_course.sec_id,  39                              r_student.student_id)  40            LOOP  41               DBMS_OUTPUT.PUT_LINE(r_grade.num_grd||  42                  '  '||r_grade.grd_desc);  43            END LOOP;  44         END LOOP;  45       END LOOP;  46   END; 
A2:

Answer: The grandparent cursor, c_student, is declared in lines 25. It takes no parameters and is a collection of students with a last name beginning with J. The parent cursor is declared in lines 613. The parent cursor, c_course, takes in the parameter of the student_ID to generate a list of courses taken by that student. The child cursor, c_grade, is declared in lines 1427. It takes in two parameters, both the section_id and the student_id. In this way it can generate an average of the different grade types for that student for that course. The grandparent cursor loop begins on line 29, and only the student name is displayed with DBMS_OUTPUT. The parent cursor loop begins on line 35. It takes the parameter of the student_id from the grandparent cursor. Only the description of the course is displayed. The child cursor loop begins on line 40. It takes in the parameter of the section_id from the parent cursor and the student_id from the grandparent cursor. The grades are then displayed. The grandparent cursor loop ends on line 45, the parent cursor on line 44, and, finally, the child on line 43.

c)

Now run the code and see if you were correct. Analyze the code line by line and explain what is being processed and then displayed for each line.

A3:

Answer: The output will be a student name, followed by the courses he or she is taking and the average grade he or she has earned for each grade type. If you did not get the correct answer, try commenting out different sections of the block and see what happens. This will help you to understand what is happening in each step.

15.1.2 Answers

a)

In the example just given, where should the COMMIT be placed? What are the issues involved in deciding where to place a COMMIT in this example?

graphics/intfig03.gif FOR EXAMPLE

 -- ch15_3a.sql  DECLARE     CURSOR c_grade(        i_student_id IN enrollment.student_id%TYPE,        i_section_id IN enrollment.section_id%TYPE)     IS        SELECT final_grade          FROM enrollment         WHERE student_id = i_student_id           AND section_id = i_section_id         FOR UPDATE;     CURSOR c_enrollment IS        SELECT e.student_id, e.section_id          FROM enrollment e, section s         WHERE s.course_no = 135           AND e.section_id = s.section_id;  BEGIN     FOR r_enroll IN c_enrollment     LOOP        FOR r_grade IN c_grade(r_enroll.student_id,                               r_enroll.section_id)        LOOP           UPDATE enrollment              SET final_grade  = 90            WHERE student_id = r_enroll.student_id              AND section_id = r_enroll.section_id;        END LOOP;     END LOOP;  END; 
A1:

Answer: Placing a COMMIT after each update can be costly. But if there are a lot of updates and the COMMIT comes after the block loop, then there is a risk of a rollback segment not being large enough. Normally, the COMMIT would go after the loop, except when the transaction count is high, and then you might want to code something that does a COMMIT for each 10,000 records. If this were part of a large procedure, you may want to put a SAVEPOINT after the loop. Then, if you need to rollback this update at a later point, it would be an easy task.

b)

What do you think will happen if you run the code in this example? After making your analysis, run the code, and then perform a SELECT statement to determine if your guess is correct.

A2:

Answer: The final_grade for all students enrolled in course 135 will be updated to 90. There are two cursors here. One cursor captures the students who are enrolled in course 135 into the active set. The other cursor takes the student_id and the section_id from this active set and selects the corresponding final_grade from the enrollment table and locks the entire enrollment table. The enrollment cursor loop is begun first, and then it passes the student_id and the section_id as an IN parameters for the second cursor loop of the c_grade cursor, which performs the update.

c)

Where should the COMMIT go in the preceding example? Explain the considerations.

A3:

Answer: The COMMIT should go immediately after the update to ensure that each update is committed into the database.

Answers

d)

What changes to the database will take place if the preceding example is run? Explain specifically what is being locked as well as when it is locked and when it is released.

A1:

Answer: The phone numbers of students living in Brooklyn are being updated to change the area code to 718. The cursor declaration is only locking the phone column of the student table. The lock is never released because there is no COMMIT or ROLLB ACK statement.

15.1.3 Answers

a)

Compare the last two examples. Explain their similarities and differences. What has been altered by using the WHERE CURRENT OF clause? What is the advantage of doing this?

A1:

Answer: These two statements perform the same update. The WHERE CURRENT OF clause allows you to eliminate a match in the UPDATE statement, because the update is being performed for the current record of the cursor only.


    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