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