10.1.1 Understand the Scope of an Exception
In this exercise, you will display the number of students in each zipcode (you still use the first 50 zipcodes only). You will use nested PL/SQL blocks to achieve the desired results. The original PL/SQL script will not contain any exception handlers. Therefore, you will be asked to identify possible errors that may occur and define exception handlers for them.
Create the following PL/SQL script:
-- ch10_1a.sql, version 1.0 SET SERVEROUTPUT ON DECLARE CURSOR zip_cur IS SELECT zip FROM zipcode WHERE rownum <= 50 ORDER BY zip; v_total NUMBER(1); -- outer block BEGIN FOR zip_rec IN zip_cur LOOP -- inner block BEGIN SELECT count(*) INTO v_total FROM student WHERE zip = zip_rec.zip; IF v_total != 0 THEN DBMS_OUTPUT.PUT_LINE ('There is(are) ' v_total' student(s) for zipcode ' zip_rec.zip); END IF; END; END LOOP; DBMS_OUTPUT.PUT_LINE ('Done...'); END;
Execute the script, and then answer the following questions: