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.
| | 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);
| 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.
|
لطفا منتظر باشید ...
