Oracle PL[s]SQL by Example

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.