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

Benjamin Rosenzweig

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

Lab 10.1 Exercise Answers

This section gives you some suggested answers to the questions in Lab 10.1, with discussion related to how those answers resulted. The most important thing to realize is whether your answer works. You should figure out the implications of the answers here and what the effects are from any different answers you may come up with.

10.1.1 Answers

A1:

Answer: Your output should look like the following:

There is(are) 1 student(s) for zipcode 01247

There is(are) 1 student(s) for zipcode 02124

There is(are) 1 student(s) for zipcode 02155

There is(are) 1 student(s) for zipcode 02189

There is(are) 1 student(s) for zipcode 02563

There is(are) 1 student(s) for zipcode 06483

There is(are) 1 student(s) for zipcode 06605

There is(are) 1 student(s) for zipcode 06798

There is(are) 3 student(s) for zipcode 06820

There is(are) 3 student(s) for zipcode 06830

There is(are) 1 student(s) for zipcode 06850

There is(are) 1 student(s) for zipcode 06851

There is(are) 1 student(s) for zipcode 06853

There is(are) 1 student(s) for zipcode 06870

There is(are) 1 student(s) for zipcode 06877

There is(are) 2 student(s) for zipcode 06880

There is(are) 1 student(s) for zipcode 06902

There is(are) 2 student(s) for zipcode 06903

There is(are) 1 student(s) for zipcode 06905

There is(are) 1 student(s) for zipcode 06907

There is(are) 2 student(s) for zipcode 07003

There is(are) 1 student(s) for zipcode 07008

There is(are) 6 student(s) for zipcode 07010

There is(are) 2 student(s) for zipcode 07011

There is(are) 2 student(s) for zipcode 07012

There is(are) 2 student(s) for zipcode 07016

There is(are) 1 student(s) for zipcode 07023

There is(are) 9 student(s) for zipcode 07024

There is(are) 1 student(s) for zipcode 07029

There is(are) 2 student(s) for zipcode 07036

There is(are) 1 student(s) for zipcode 07040

There is(are) 5 student(s) for zipcode 07042

There is(are) 1 student(s) for zipcode 07044

There is(are) 5 student(s) for zipcode 07047

Done…

PL/SQL procedure successfully completed.

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);

A2:

Answer: The example will produce a partial output only. When the total number of students is calculated for zipcode 07024, the error occurs.

The SELECT INTO statement returns a value of 10. However, the variable v_total has been defined so that it is able to hold only single digit numbers. Because 10 is a two-digit number, the error occurs during the execution of the SELECT INTO statement. As a result, an error message is displayed on the screen.

The following output contains only a portion of the output produced by the example:

There is(are) 1 student(s) for zipcode 01247

…

There is(are) 1 student(s) for zipcode 07023

DECLARE

ERROR at line 1:

ORA-06502: PL/SQL: numeric or value error: number

precision too large

ORA-06512: at line 13

Notice that as soon as the error occurs, the example terminates because there is no exception handler for this error.

A3:

Answer: The error message produced by the example in the previous question referred to a numeric or value error. Therefore, an exception VALUE_ERROR or INVALID_NUMBER must be added to the script.

Your script should look similar to the following script. Changes are shown in bold letters.

-- ch10_1b.sql, version 2.0 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...');

EXCEPTION

WHEN VALUE_ERROR OR INVALID_NUMBER THEN

DBMS_OUTPUT.PUT_LINE ('An error has occurred'); END;

When run, this version of the example produces the following output (only a portion of the output is shown):

There is(are) 1 student(s) for zipcode 01247

…

There is(are) 1 student(s) for zipcode 07023

An error has occurred

PL/SQL procedure successfully completed.

Notice that because an exception handler has been added to the script, it was able to terminate successfully.

A4:

Answer: Your script should look similar to the script shown. All changes are shown in bold letters.

-- ch10_1c.sql, version 3.0 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;

EXCEPTION

WHEN VALUE_ERROR OR INVALID_NUMBER THEN

DBMS_OUTPUT.PUT_LINE

('An error has occurred'); END; END LOOP; DBMS_OUTPUT.PUT_LINE ('Done...'); END;

In order for the cursor loop to be able to execute after an exception has occurred, the exception handler must be moved inside the loop in the inner block. In this case, once an exception has occurred, control is transferred to the exception handler of the block. Once the exception is raised, control is passed to the next executable statement of the outer block. That statement is END LOOP. If the end of the loop has not been reached and there are more records to process, control is passed to the top of the loop, and the inner block is executed again. As a result, this version of the script produces the following output (again, only a portion of the output is shown):

There is(are) 1 student(s) for zipcode 01247

…

There is(are) 1 student(s) for zipcode 07023

An error has occurred

There is(are) 1 student(s) for zipcode 07029

There is(are) 2 student(s) for zipcode 07036

There is(are) 1 student(s) for zipcode 07040

There is(are) 5 student(s) for zipcode 07042

There is(are) 1 student(s) for zipcode 07044

There is(are) 5 student(s) for zipcode 07047

Done...

PL/SQL procedure successfully completed.