Prentice Hall Oracle Plsql By Example 3Rd Edition [Electronic resources]

Benjamin Rosenzweig

نسخه متنی -صفحه : 289/ 122

Lab 10.1 Exercises

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:

a)	What output was printed on the screen?
b)	The first run of this example was successful. The output produced by the example shows that there are 9 students for zipcode 07024. What will happen if there are 10 students with a zip code 07024? What output will be produced? Note that in order to answer this question you will need to add a record to the STUDENT table as follows: INSERT INTO student (student_id, salutation, first_name, last_name, street_address, zip, phone, employer, registration_date, created_by, created_date, modified_by, modified_date) VALUES (STUDENT_ID_SEQ.NEXTVAL, 'Mr.', 'John', 'Smith', '100 Main St.', '07024', '718-555-5555', 'ABC Co.', SYSDATE, USER, SYSDATE, USER, SYSDATE);
c)	Based on the error message produced by the example in the previous question, what exception handler must be added to the script?
d)	How would you change this script so that when an error occurs, the cursor loop does not terminate prematurely?