RSS

9.2.2 Process Nested Cursors

15 May

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 previous chapters. If you have one parent cursor and two child cursors, each time the parent cursor makes a single loop, it loops through each child cursor once and then begins a second round. The following two examples show a nested cursor with a single child cursor.

 

DECLARE

v_zip zipcode.zip%TYPE;

v_student_flag CHAR;

CURSOR c_zip IS

SELECT zip, city, state

FROM zipcode

WHERE state = ‘CT’;

CURSOR c_student IS

SELECT first_name, last_name

FROM student

WHERE zip = v_zip;

BEGIN

FOR r_zip IN c_zip LOOP

v_student_flag := ‘N’;

v_zip := r_zip.zip;

DBMS_OUTPUT.PUT_LINE(CHR(10));

DBMS_OUTPUT.PUT_LINE(‘STUDENT LIVING IN  || r_zip.city);

FOR r_student IN c_student LOOP

DBMS_OUTPUT.PUT_LINE(r_student.first_name ||   || r_student.last_name);

v_student_flag := ‘Y’;

END LOOP;

IF v_student_flag = ‘N’ THEN

DBMS_OUTPUT.PUT_LINE(‘No Students for this zipcode’);

END IF;

END LOOP;   

END;

 

 

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;

 

 

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;

Advertisements
 
Leave a comment

Posted by on 05/15/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: