RSS

9.3.2 Use Complex Nested Cursors

18 May

DECLARE

 CURSOR c_student IS

SELECT first_name, last_name, student_id

FROM student

WHERE last_name LIKE ‘J%’;

 CURSOR c_course(i_student_id IN student.student_id%TYPE) IS

SELECT c.description, s.section_id sec_id

FROM course c, section s, enrollment e

WHERE e.student_id = i_student_id

AND c.course_no = s.course_no

AND s.section_id = e.section_id;

 CURSOR c_grade(i_section_id IN section.section_id%TYPE,

 i_student_id IN student.student_id%TYPE) IS

SELECT gt.description grd_desc,

   TO_CHAR(AVG(g.numeric_grade), ‘999.99’) num_grd

FROM enrollment e, grade g, grade_type gt

WHERE e.section_id = i_section_id

AND e.student_id = g.student_id

AND e.student_id = i_student_id

AND e.section_id = g.section_id

AND g.grade_type_code = gt.grade_type_code

GROUP BY gt.description;

BEGIN

 FOR r_student IN c_student LOOP

DBMS_OUTPUT.PUT_LINE(CHR(10));

DBMS_OUTPUT.PUT_LINE(r_student.first_name ||   ||

 r_student.last_name);

FOR r_course IN c_course(r_student.student_id) LOOP

 DBMS_OUTPUT.PUT_LINE(‘Grades for course :’ || r_course.description);

 FOR r_grade IN c_grade(r_course.sec_id, r_student.student_id) LOOP

DBMS_OUTPUT.PUT_LINE(r_grade.num_grd ||   || r_grade.grd_desc);

 END LOOP;

END LOOP;

 END LOOP;

END;

Advertisements
 
Leave a comment

Posted by on 05/18/2010 in ORACLE

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

 
%d bloggers like this: