SQL> start a:9 SQL> set echo on SQL> set serveroutput on SQL> -- SQL> -- joseph speigle SQL> -- july 17, 2002 SQL> -- summer PL-SQL lab 9 SQL> -- SQL> -- SQL> -- question 1 SQL> DECLARE 2 CURSOR c_cnt IS 3 select section_id, count(*) counted from enrollment 4 GROUP BY section_id; 5 BEGIN 6 FOR r_cnt IN c_cnt 7 LOOP 8 IF r_cnt.counted > 4 9 THEN 10 DBMS_OUTPUT.PUT_LINE('Section' || 11 r_cnt.section_id || ' is over the limit ' || 12 r_cnt.counted); 13 ELSE 14 DBMS_OUTPUT.PUT_LINE('Section' || 15 r_cnt.section_id || ' total ' || 16 r_cnt.counted); 17 END IF; 18 END LOOP; 19 END; 20 . SQL> / Section80 total 1 Section81 total 3 Section82 total 2 Section83 total 2 Section84 total 2 Section85 is over the limit 5 Section86 is over the limit 6 Section87 is over the limit 7 Section88 is over the limit 5 Section89 is over the limit 12 Section90 total 4 Section91 total 2 Section92 total 4 Section94 total 2 Section95 is over the limit 5 Section96 total 1 Section99 is over the limit 12 Section100 total 2 Section101 is over the limit 12 Section102 total 1 Section103 total 4 Section104 total 3 Section105 is over the limit 5 Section106 total 4 Section107 total 2 Section108 total 2 Section109 total 1 Section111 total 1 Section112 total 2 Section113 total 1 Section115 total 1 Section116 is over the limit 8 Section117 is over the limit 7 Section119 total 4 Section120 total 3 Section123 total 2 Section125 total 1 Section126 is over the limit 5 Section127 total 1 Section128 total 2 Section130 total 2 Section131 total 1 Section132 total 2 Section133 total 3 Section135 total 4 Section137 total 2 Section138 total 2 Section140 total 1 Section141 is over the limit 6 Section142 total 2 Section143 total 2 Section144 total 2 Section145 total 1 Section146 total 4 Section147 is over the limit 8 Section148 is over the limit 5 Section149 total 1 Section150 total 3 Section151 total 2 Section152 total 4 Section153 total 3 Section154 total 4 Section155 is over the limit 5 Section156 is over the limit 8 PL/SQL procedure successfully completed. SQL> -- SQL> -- question 2 SQL> -- SQL> DECLARE 2 e_too_big EXCEPTION; 3 CURSOR c_cnt IS 4 select section_id, count(*) counted from enrollment 5 GROUP BY section_id; 6 v_sid ENROLLMENT.SECTION_ID%TYPE; 7 v_counted NUMBER; 8 BEGIN 9 FOR r_cnt IN c_cnt LOOP 10 BEGIN 11 v_sid := r_cnt.section_id; 12 v_counted := r_cnt.counted; 13 -- select section_id, count(*) counted 14 -- INTO v_sid, v_counted FROM enrollment 15 -- WHERE r_cnt.section_id = section_id; 16 IF v_counted > 4 17 THEN 18 RAISE e_too_big; 19 ELSE 20 DBMS_OUTPUT.PUT_LINE('Section' || 21 v_sid || ' total ' || 22 v_counted); 23 END IF; 24 25 EXCEPTION 26 WHEN e_too_big THEN RAISE; 27 END; 28 END LOOP; 29 EXCEPTION 30 WHEN e_too_big THEN 31 DBMS_OUTPUT.PUT_LINE('Section' || 32 v_sid || ' is over the limit ' || 33 v_counted); 34 END; 35 . SQL> / Section80 total 1 Section81 total 3 Section82 total 2 Section83 total 2 Section84 total 2 Section85 is over the limit 5 PL/SQL procedure successfully completed. SQL> -- SQL> -- question 3 SQL> -- SQL> DECLARE 2 v_iid2 INSTRUCTOR.INSTRUCTOR_ID%TYPE; 3 v_iid INSTRUCTOR.INSTRUCTOR_ID%TYPE := &sv_iid; 4 v_name INSTRUCTOR.LAST_NAME%TYPE; 5 v_counted NUMBER; 6 e_invalid EXCEPTION; 7 BEGIN 8 BEGIN 9 select i.last_name 10 INTO v_name 11 from instructor i 12 where i.instructor_id = v_iid; 13 14 END; 15 16 BEGIN 17 SELECT i.instructor_id, count(s.section_id) counted 18 INTO v_iid2, v_counted 19 FROM instructor i, section s 20 WHERE i.instructor_id = v_iid and 21 s.instructor_id = v_iid 22 GROUP BY i.instructor_id; 23 24 DBMS_OUTPUT.PUT_LINE('Instructor ' || 25 v_iid || ' teaches ' || 26 v_counted || ' sections'); 27 28 EXCEPTION WHEN NO_DATA_FOUND THEN 29 DBMS_OUTPUT.PUT_LINE('No error has been encountered'); 30 END; 31 32 EXCEPTION 33 WHEN NO_DATA_FOUND THEN 34 DBMS_OUTPUT.PUT_LINE('Instructor ' || 35 v_iid || ' is invalid.'); 36 END; 37 . SQL> / Enter value for sv_iid: 109 old 3: v_iid INSTRUCTOR.INSTRUCTOR_ID%TYPE := &sv_iid; new 3: v_iid INSTRUCTOR.INSTRUCTOR_ID%TYPE := 109; No error has been encountered PL/SQL procedure successfully completed. SQL> DECLARE 2 v_iid2 INSTRUCTOR.INSTRUCTOR_ID%TYPE; 3 v_iid INSTRUCTOR.INSTRUCTOR_ID%TYPE := &sv_iid; 4 v_name INSTRUCTOR.LAST_NAME%TYPE; 5 v_counted NUMBER; 6 e_invalid EXCEPTION; 7 BEGIN 8 BEGIN 9 select i.last_name 10 INTO v_name 11 from instructor i 12 where i.instructor_id = v_iid; 13 14 END; 15 16 BEGIN 17 SELECT i.instructor_id, count(s.section_id) counted 18 INTO v_iid2, v_counted 19 FROM instructor i, section s 20 WHERE i.instructor_id = v_iid and 21 s.instructor_id = v_iid 22 GROUP BY i.instructor_id; 23 24 DBMS_OUTPUT.PUT_LINE('Instructor ' || 25 v_iid || ' teaches ' || 26 v_counted || ' sections'); 27 28 EXCEPTION WHEN NO_DATA_FOUND THEN 29 DBMS_OUTPUT.PUT_LINE('No error has been encountered'); 30 END; 31 32 EXCEPTION 33 WHEN NO_DATA_FOUND THEN 34 DBMS_OUTPUT.PUT_LINE('Instructor ' || 35 v_iid || ' is invalid.'); 36 END; 37 . SQL> / Enter value for sv_iid: 108 old 3: v_iid INSTRUCTOR.INSTRUCTOR_ID%TYPE := &sv_iid; new 3: v_iid INSTRUCTOR.INSTRUCTOR_ID%TYPE := 108; Instructor 108 teaches 9 sections PL/SQL procedure successfully completed. SQL> DECLARE 2 v_iid2 INSTRUCTOR.INSTRUCTOR_ID%TYPE; 3 v_iid INSTRUCTOR.INSTRUCTOR_ID%TYPE := &sv_iid; 4 v_name INSTRUCTOR.LAST_NAME%TYPE; 5 v_counted NUMBER; 6 e_invalid EXCEPTION; 7 BEGIN 8 BEGIN 9 select i.last_name 10 INTO v_name 11 from instructor i 12 where i.instructor_id = v_iid; 13 14 END; 15 16 BEGIN 17 SELECT i.instructor_id, count(s.section_id) counted 18 INTO v_iid2, v_counted 19 FROM instructor i, section s 20 WHERE i.instructor_id = v_iid and 21 s.instructor_id = v_iid 22 GROUP BY i.instructor_id; 23 24 DBMS_OUTPUT.PUT_LINE('Instructor ' || 25 v_iid || ' teaches ' || 26 v_counted || ' sections'); 27 28 EXCEPTION WHEN NO_DATA_FOUND THEN 29 DBMS_OUTPUT.PUT_LINE('No error has been encountered'); 30 END; 31 32 EXCEPTION 33 WHEN NO_DATA_FOUND THEN 34 DBMS_OUTPUT.PUT_LINE('Instructor ' || 35 v_iid || ' is invalid.'); 36 END; 37 . SQL> Input truncated to 1 characters / Enter value for sv_iid: 999 old 3: v_iid INSTRUCTOR.INSTRUCTOR_ID%TYPE := &sv_iid; new 3: v_iid INSTRUCTOR.INSTRUCTOR_ID%TYPE := 999; Instructor 999 is invalid. PL/SQL procedure successfully completed. SQL> spool off