Chapter 13 Functions


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


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.


Answer: Your answer should look similar to the following:

 CREATE OR REPLACE FUNCTION new_student_id     RETURN student.student_id%TYPE  AS     v_student_id student.student_id%TYPE;  BEGIN     SELECT student_id_seq.NEXTVAL       INTO v_student_id       FROM dual;     RETURN(v_student_id);  END; 

Write a stored function called zip_does_not_exist that takes in a 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; 

Answer: Your answer should look similar to the following:

 CREATE OR REPLACE FUNCTION zipcode_does_not_exist     (i_zipcode IN     RETURN BOOLEAN  AS     v_dummy char(1);  BEGIN     SELECT NULL       INTO v_dummy       FROM zipcode      WHERE zip = i_zipcode;     -- meaning the zipcode does exits     RETURN FALSE;  EXCEPTION     WHEN OTHERS THEN        -- the select statement above will cause an exception        -- to be raised if the zipcode is not in the database.        RETURN TRUE;  END zipcode_does_not_exist; 

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.


Answer: Your answer should look similar to the following:

 CREATE OR REPLACE FUNCTION instructor_status     (i_first_name IN instructor.first_name%TYPE,      i_last_name IN instructor.last_name%TYPE)     RETURN VARCHAR2  AS     v_instructor_id instructor.instructor_id%TYPE;     v_section_count NUMBER;     v_status VARCHAR2(100);  BEGIN     SELECT instructor_id       INTO v_instructor_id       FROM instructor      WHERE first_name = i_first_name        AND last_name = i_last_name;     SELECT COUNT(*)       INTO v_section_count       FROM section      WHERE instructor_id = v_instructor_id;     IF v_section_count >= 3 THEN        v_status :=           'The instructor '||i_first_name||'  '||            i_last_name||' is teaching '||v_section_count||           ' and needs a vaction.';     ELSE        v_status :=           'The instructor '||i_first_name||'  '||           i_last_name||' is teaching '||v_section_count||           ' courses.';     END IF;     RETURN v_status;  EXCEPTION     WHEN NO_DATA_FOUND THEN        -- note that either of the SELECT statements can raise        -- this exception        v_status :=           'The instructor '||i_first_name||'  '||            i_last_name||' is not shown to be teaching'||           ' any courses.';        RETURN v_status;     WHEN OTHERS THEN        v_status :=           'There has been in an error in the function.';        RETURN v_status;  END; 

Test the function as follows:

 SELECT instructor_status(first_name, last_name)    FROM instructor;  \ 


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

    Similar book on Amazon © 2008-2017.
    If you may any questions please contact us: