Lab 7.2 Exercise Answers

This section gives you some suggested answers to the questions in Lab 7.2, 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.

7.2.1 Answers

a)

What output was printed on the screen (for all values of zip)?

A1:

Answer: The first version of the output is produced when the value of zip is 07024. The second version of the output is produced when the value of zip is 00914. The third version of the output is produced when the value of zip is 12345. Your output should look like the following: Enter value for sv_zip: 07024 old 4: v_zip CHAR(5):= '&sv_zip'; new 4: v_zip CHAR(5):= '07024'; There are 9 students PL/SQL procedure successfully completed.

When "07024" is entered for the variable v_zip , the first SELECT INTO statement is executed. This SELECT INTO statement checks whether the value of zip is valid, or, in other words, if a record exists in the ZIPCODE table for a given value of zip. Next, the value of the variable v_exists is evaluated with the help of the IF statement. For this run of the example, the IF statement evaluates to TRUE, and, as a result, the SELECT INTO statement against the STUDENT table is evaluated. Next, the DBMS_OUTPUT.PUT_LINE following the SELECT INTO statement is executed, and the message "There are 9 students" is displayed on the screen.

Your output should look like the following:

  Enter value for sv_zip: 00914   old 4: v_zip CHAR(5):= '&sv_zip';   new 4: v_zip CHAR(5):= '00914';   There are 0 students   PL/SQL procedure successfully completed.  

For the second run, the value 00914 is entered for the variable v_zip . The SELECT INTO statement against the STUDENT table returns one record, and the message "There are 0 students" is displayed on the screen.

Because the SELECT INTO statement against the STUDENT table uses a group function, COUNT, there is no reason to use the exception NO_DATA_FOUND, because the COUNT function will always return data.

Your output should look like the following:

  Enter value for sv_zip: 12345   old 4: v_zip CHAR(5):= '&sv_zip';   new 4: v_zip CHAR(5):= '12345';   12345 is not a valid zip   PL/SQL procedure successfully completed.  

For the third run, the value 12345 is entered for the variable v_zip . The SELECT INTO statement against the ZIPCODE table is executed. Next, the variable v_exists is evaluated with the help of the IF statement. Because the value of v_exists equals 0, the IF statement evaluates to FALSE. As a result, the ELSE part of the IF statement is executed. The message "12345 is not a valid zip" is displayed on the screen.

b)	Explain why no exception has been raised for these values of the variable v_zip .
A2:	Answer: The exceptions VALUE_ERROR or INVALID_NUMBER have not been raised because there was no conversion or type mismatch error. Both variables , v_exists and v_total_students , have been defined as NUMBER(1). The group function COUNT used in the SELECT INTO statement returns a NUMBER datatype. Moreover, on both occasions, a single digit number is returned by the COUNT function. As a result, neither exception has been raised.
c)	Insert a record into the STUDENT table with a zip having the value of "07024." INSERT INTO student (student_id, salutation, first_name, last_name, zip, registration_date, created_by, created_date, modified_by, modified_date) VALUES (STUDENT_ID_SEQ.NEXTVAL, 'Mr.', 'John', 'Smith', '07024', SYSDATE, 'STUDENT', SYSDATE, 'STUDENT', SYSDATE); Run the script again for the same value of zip ("07024"). What output was printed on the screen? Why?
A3:	Answer: After a student has been added, your output should look like the following: Enter value for sv_zip: 07024 old 4: v_zip CHAR(5):= '&sv_zip'; new 4: v_zip CHAR(5):= '07024'; An error has occurred PL/SQL procedure successfully completed. Once the student has been inserted into the STUDENT table with a zip having a value of "07024," the total number of students changes to 10 (remember, previously this number was 9). As a result, the SELECT INTO statement against the STUDENT table causes an error, because the variable v_total_students has been defined as NUMBER(1). This means that only a single-digit number can be stored in this variable. The number 10 is a two-digit number, so the exception INVALID_NUMBER is raised. As a result, the message "An error has occurred" is displayed on the screen.
d)	How would you change the script to display a student's first name and last name instead of displaying the total number of students for any given value of a zip? Remember, only one record can be returned by a SELECT INTO statement.
A4:	Answer: The new version of your program should look similar to this program. All changes are shown in bold letters . -- ch07_2b.sql, version 2.0 SET SERVEROUTPUT ON DECLARE v_exists NUMBER(1); v_student_name VARCHAR2(30); v_zip CHAR(5):= '&sv_zip'; BEGIN SELECT count(*) INTO v_exists FROM zipcode WHERE zip = v_zip; IF v_exists != 0 THEN SELECT first_name' 'last_name INTO v_student_name FROM student WHERE zip = v_zip AND rownum = 1; DBMS_OUTPUT.PUT_LINE ('Student name is ' v_student_name); ELSE DBMS_OUTPUT.PUT_LINE (v_zip' is not a valid zip'); END IF; EXCEPTION WHEN VALUE_ERROR OR INVALID_NUMBER THEN DBMS_OUTPUT.PUT_LINE ('An error has occurred'); WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE ('There are no students for this value of ' 'zip code'); END;

This version of the program contains several changes. The variable v_total_students has been replaced by the variable v_student_name . The SELECT INTO statement against the STUDENT table has been changed as well. Another condition has been added to the WHERE clause:

  rownum = 1  

You have seen from the previous runs of this program that for any given value of zip there could be multiple records in the STUDENT table. Because a SELECT INTO statement returns only a single row, the condition rownum = 1 has been added to it. Another way to deal with multiple rows returned by the SELECT INTO statement is to add the exception TOO_MANY_ROWS.

Finally, another exception has been added to the program. The SELECT INTO statement against the STUDENT table does not contain any group functions. Therefore, for any given value of zip, the SELECT INTO statement may not return any data, and it causes an error. As a result, the exception NO_DATA_FOUND will be raised.