Prentice Hall Oracle Plsql By Example 3Rd Edition [Electronic resources] نسخه متنی

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: