Lab 9.2 Using Cursor for Loops and Nesting 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.
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.
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.
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.
_____ True _____ False
| | 2) |
In a cursor FOR loop, it is necessary to declare the rowtype for the cursor.
_____ True _____ False
| | 3) |
Is it necessary to open, fetch, and close a cursor in a cursor FOR loop?
_____ Yes _____ No
| | 4) |
The child loop in a nested cursor is passed through how many times for each cycle of the parent?
_____ Three _____ One or more _____ Two _____ 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?
_____ It is a bind variable and therefore does not need to be declared. _____ In the declarative section. _____ Before the cursor that is using it. _____ It will be self-declared upon initialization.
|
|
No comments:
Post a Comment