Entire Site
Free Trial
Safari Books Online is a digital library providing on-demand subscription access to thousands of learning resources.
- Create BookmarkCreate Bookmark
- Create Note or TagCreate Note or Tag
- DownloadDownload
- PrintPrint
Chapter 12. Advanced Cursors > Use Complex Nested Cursors
12.1.2. Use Complex Nested Cursors
Nesting cursors allows for looping through data at various stages. For example, one cursor could loop through zip codes. When it hits a zip code, a second, nested cursor would loop through students who live in that zip code. Working through a specific example will help you understand this in more detail.
| A) | The following PL/SQL code is complex. It involves all the topics covered so far in this chapter. It has 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 its levels of nesting. When you describe each level of the code, explain what parameters are passed into the cursor and why. What do you think the result of running this statement will be?
Code View:
Scroll
/
Show All -- ch12_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;
|
| ANSWER: | The grandparent cursor, c_student, is declared in lines 2 through 5. It takes no parameters and is a collection of students with a last name beginning with J. The parent cursor, c_course, is declared in lines 6 through 13. It takes in the parameter of student_ID to generate a list of courses that student is taking. The child cursor, c_grade, is declared in lines 14 through 27. It takes in two parameters, section_id and student_id. In this way it can generate an average of the different grade types (quizzes, homework, final, etc.) 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 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 section_id from the parent cursor and student_id from the grandparent cursor. The grades are then displayed. The grandparent cursor loop ends on line 45, the parent cursor loop on line 44, and the child cursor loop on line 43. |
| B) | Now run the code. Analyze it line by line, and explain what is processed and then displayed for each line. |
| ANSWER: | The output is 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 guess the correct answer, try commenting out different sections of the block and see what happens. This helps you understand what is happening in each step. |
