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

CASE#5

DECLARE
current_term_desc term.term_desc%type;
current_call_id course.call_id%type;
current_course_name course.course_name%type;
current_c_sec_day course_section.c_sec_day%type;
current_c_sec_time course_section.c_sec_time%type;
current_bldg_code location.bldg_code%type;
current_room location.room%type;
current_f_first faculty.f_first%type;
current_f_last faculty.f_last%type;
messTerm	VARCHAR2(10):= 'Term: ';
messCourse	VARCHAR2(10):= 'Course: ';
messTime	VARCHAR2(10):= 'Time: ';
messLoc		VARCHAR2(10):= 'Location: ';
messInstruc	VARCHAR2(13):= 'Instructor: ';
blank		VARCHAR2(1):= ' ';
BEGIN
SELECT	term_desc,call_id,course_name,c_sec_day,c_sec_time,bldg_code,room,f_first,f_last
INTO	current_term_desc,current_call_id,current_course_name,current_c_sec_day,current_c_sec_time,
		current_bldg_code,current_room,current_f_first,current_f_last
FROM	term, course, course_section, location, faculty
WHERE	course_section.term_id = term.term_id AND
		course_section.course_id = course.course_id AND
		course_section.loc_id = location.loc_id AND
		course_section.f_id = faculty.f_id AND
		c_sec_id = 1;
DBMS_OUTPUT.PUT_LINE(messTerm || current_term_desc );
DBMS_OUTPUT.PUT_LINE(messCourse || current_call_id || blank || current_course_name );
DBMS_OUTPUT.PUT_LINE(messTime || current_c_sec_day || blank || current_c_sec_time );
DBMS_OUTPUT.PUT_LINE(messLoc || current_bldg_code || current_room );
DBMS_OUTPUT.PUT_LINE(messInstruc || current_f_first ||blank || current_f_last );

	EXCEPTION
		WHEN NO_DATA_FOUND THEN
			DBMS_OUTPUT.PUT_LINE('No rows where found that match creiteria' );
			DBMS_OUTPUT.PUT_LINE('Enter new search conditions');
		WHEN TOO_MANY_ROWS THEN
			DBMS_OUTPUT.PUT_LINE('search condition fteched more than one row');

END;