Chapter 13 Test Your Thinking

Team-Fly    

Oracle® PL/SQL® Interactive Workbook, Second Edition
By Benjamin Rosenzweig, Elena Silvestrova
Table of Contents
Chapter 13.  Functions


In this chapter, you have learned about functions. Here are some projects that will help you test the depth of your understanding.

1)

Write a stored function called new_student_id that takes in no parameters and returns a student.student_id%TYPE. The value returned will be used when inserting a new student into the CTA application. It will be derived by using the formula: student_id_seq.NEXTVAL.

2)

Write a stored function called zip_does_not_exist that takes in a zipcode.zip%TYPE and returns a Boolean.The function will return TRUE if the zipcode passed into it does not exist. It will return a FALSE if the zipcode exists. Hint: An example of how it might be used is as follows:

 DECLARE     cons_zip CONSTANT zipcode.zip%TYPE := '&sv_zipcode';     e_zipcode_is_not_valid EXCEPTION;  BEGIN     IF zipcode_does_not_exist(cons_zip);     THEN        RAISE e_zipcode_is_not_valid;     ELSE      -- An insert of an instructor's record which      -- makes use of the checked zipcode might go here.        NULL;     END IF;  EXCEPTION     WHEN e_zipcode_is_not_valid     THEN        RAISE_APPLICATION_ERROR           (-20003, 'Could not find zipcode '||            cons_zip||'.'           );  END; 
3)

Create a new function. For a given instructor, determine how many sections he or she is teaching. If the number is greater or equal to 3, return a message saying the instructor needs a vacation. Otherwise, return a message saying how many sections this instructor is teaching.

The answers to Test Your Thinking can be found in Appendix D and on the Web site.


    Team-Fly    
    Top
     



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

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