Chapter 7 Error Handling and Built-In Exceptions

Team-Fly    

Oracle® PL/SQL® Interactive Workbook, Second Edition
By Benjamin Rosenzweig, Elena Silvestrova
Table of Contents
Appendix D.  Answers to Test Your Thinking Sections


1)

Create the following script: Check to see whether there is a record in the STUDENT table for a given student ID. If there is no record for the given student ID, insert a record into the STUDENT table for the given student ID.

A1:

Answer: Your answer should look similar to the following:

 SET SERVEROUTPUT ON  DECLARE     v_student_id NUMBER := &sv_student_id;     v_first_name VARCHAR2(30) := '&sv_first_name';     v_last_name VARCHAR2(30) := '&sv_last_name';     v_zip CHAR(5) := '&sv_zip';     v_name VARCHAR2(50);  BEGIN     SELECT first_name||' '||last_name       INTO v_name       FROM student      WHERE student_id = v_student_id;     DBMS_OUTPUT.PUT_LINE ('Student '||v_name||        ' is a valid student');  EXCEPTION     WHEN NO_DATA_FOUND THEN        DBMS_OUTPUT.PUT_LINE           ('This student does not exist, and will be '||            'added to the STUDENT table');        INSERT INTO student           (student_id, first_name, last_name, zip,            registration_date, created_by, created_date,            modified_by, modified_date)        VALUES           (v_student_id, v_first_name, v_last_name, v_zip,            SYSDATE, USER, SYSDATE, USER, SYSDATE);       COMMIT;  END; 

This script accepts a value for student's ID from a user. For a given student ID, it determines the student's name via the SELECT INTO statement and displays it on the screen. If the value provided by the user is not a valid student ID, the control of execution is passed to the exception-handling section of the block, where the NO_DATA_FOUND exception is raised. As a result, the message 'This student does not exist…' is displayed on the screen, and a new record is inserted in the STUDENT table.

To test this script fully, consider running it for two values of student ID. Only one value should correspond to an existing student ID. It is important to note that a valid zipcode should be provided for both runs. Why do you think this is necessary?

When 319 is provided for the student ID (it is a valid student ID), this exercise produces the following output:

 Enter value for sv_student_id: 319  old   2:    v_student_id NUMBER := &sv_student_id;  new   2:    v_student_id NUMBER := 319;  Enter value for sv_first_name: John  old   3:    v_first_name VARCHAR2(30) := '&sv_first_name';  new   3:    v_first_name VARCHAR2(30) := 'John';  Enter value for sv_last_name: Smith  old   4:    v_last_name VARCHAR2(30) := '&sv_last_name';  new   4:    v_last_name VARCHAR2(30) := 'Smith';  Enter value for sv_zip: 07421  old   5:    v_zip CHAR(5) := '&sv_zip';  new   5:    v_zip CHAR(5) := '07421';  Student George Eakheit is a valid student  PLSQL procedure successfully completed. 

Notice that the name displayed by the script does not correspond to the name entered at runtime. Why do you think this occurs?

When 555 is provided for the student ID (it is not a valid student ID), this exercise produces the following output:

 Enter value for sv_student_id: 555  old   2:    v_student_id NUMBER := &sv_student_id;  new   2:    v_student_id NUMBER := 555;  Enter value for sv_first_name: John  old   3:    v_first_name VARCHAR2(30) := '&sv_first_name';  new   3:    v_first_name VARCHAR2(30) := 'John';  Enter value for sv_last_name: Smith  old   4:    v_last_name VARCHAR2(30) := '&sv_last_name';  new   4:    v_last_name VARCHAR2(30) := 'Smith';  Enter value for sv_zip: 07421  old   5:    v_zip CHAR(5) := '&sv_zip';  new   5:    v_zip CHAR(5) := '07421';  This student does not exist, and will be added to the  STUDENT table  PLSQL procedure successfully completed. 

Next, you can select this new record from the STUDENT table as follows:

 SELECT student_id, first_name, last_name    FROM student   WHERE student_id = 555;  STUDENT_ID FIRST_NAME                LAST_NAME  ---------- ------------------------- ----------------        555 John                      Smith 
2)

Create the following script: For a given instructor ID, check to see whether it is assigned to a valid instructor. Then check the number of sections that are taught by this instructor and display this information on the screen.

A2:

Answer: Your answer should look similar to the following:

 SET SERVEROUTPUT ON  DECLARE     v_instructor_id NUMBER := &sv_instructor_id;     v_name VARCHAR2(50);     v_total NUMBER;  BEGIN     SELECT first_name||' '||last_name       INTO v_name       FROM instructor      WHERE instructor_id = v_instructor_id;     -- check how many sections are taught by this instructor     SELECT COUNT(*)       INTO v_total       FROM section      WHERE instructor_id = v_instructor_id;    DBMS_OUTPUT.PUT_LINE ('Instructor, '||v_name||       ', teaches '||v_total||' section(s)');  EXCEPTION     WHEN NO_DATA_FOUND THEN        DBMS_OUTPUT.PUT_LINE           ('This is not a valid instructor');  END; 

This script accepts a value for instructor's ID from a user. For a given instructor ID, it determines the instructor's name via the SELECT INTO statement. This SELECT INTO statement checks if the ID provided by the user is a valid instructor ID. If this value is not valid, the control of the execution is passed to the exception-handling section of the block, where the NO_DATA_FOUND exception is raised. As a result, the message 'This is not a valid instructor' is displayed on the screen. On the other hand, if the value provided by the user is a valid instructor ID, the second SELECT INTO statement calculates how many sections are taught by this instructor.

To test this script fully, consider running it for two values of instructor ID. When 105 is provided for the instructor ID (it is a valid instructor ID), this exercise produces the following output:

 Enter value for sv_instructor_id: 105  old   2:    v_instructor_id NUMBER := &sv_instructor_id;  new   2:    v_instructor_id NUMBER := 105;  Instructor, Anita Morris, teaches 10 section(s)  PLSQL procedure successfully completed. 

When 123 is provided for the instructor ID (it is not a valid student ID), this exercise produces the following output:

 Enter value for sv_instructor_id: 123  old   2:    v_instructor_id NUMBER := &sv_instructor_id;  new   2:    v_instructor_id NUMBER := 123;  This is not a valid instructor  PLSQL procedure successfully completed. 


    Team-Fly    
    Top
     



    Oracle PL. SQL Interactive Workbook
    Oracle PL/SQL Interactive Workbook (2nd Edition)
    ISBN: 0130473200
    EAN: 2147483647
    Year: 2002
    Pages: 146

    Similar book on Amazon

    flylib.com © 2008-2017.
    If you may any questions please contact us: flylib@qtcs.net