Andre Long
CISP 350 Oracle
May 18, 2006
Chapter 4B PSC 

CASE#7

DECLARE
blank		VARCHAR2(1):= ' ';
current_section_id	number;

CURSOR	red	IS
SELECT	call_id, course_name, sec_num, c_sec_id
FROM	course, course_section
WHERE	course_section.course_id = course.course_id AND
		course_section.term_id = 6;
		
class	red%ROWTYPE;

CURSOR	blue IS
SELECT	s_last, s_first
FROM	student,  enrollment
WHERE	enrollment.s_id = student.s_id AND
		enrollment.c_sec_id = current_section_id;
		
pupil	blue%ROWTYPE;

BEGIN
OPEN	red;
		LOOP
			FETCH  red INTO class;
			EXIT WHEN red%NOTFOUND;
			current_section_id:=class.c_sec_id;
			DBMS_OUTPUT.PUT_LINE('==========================================');
			DBMS_OUTPUT.PUT_LINE(class.call_id || blank || class.course_name || blank ||'Sec. ' || blank || class.sec_num);
			DBMS_OUTPUT.PUT_LINE('==========================================');
			OPEN	blue;
			LOOP
				FETCH blue INTO pupil;
				EXIT WHEN blue%NOTFOUND;
				DBMS_OUTPUT.PUT_LINE(pupil.s_first || blank || pupil.s_last);
			END LOOP;
			CLOSE	blue;
		END LOOP;

CLOSE	red;
END;