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> 

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?

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.

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.

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?

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