Chapter 13 Test Your Thinking

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 := '&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.

Oracle PL[s]SQL by Example
Oracle PL[s]SQL by Example
ISBN: 3642256902
Year: 2003
Pages: 289 © 2008-2017.
If you may any questions please contact us: