Oracle PL/SQL Interactive Workbook, 2/e

Test Your Thinking Solutions Chapter 13 Functions

  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.

    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;
    
  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 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 that
          -- uses of the checked value of 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 zipcode.zip%TYPE)
       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;
    
  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.

    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;
    

Select a Chapter for Test Your Thinking Solutions

  1. Programming Concepts
  2. PLSQL Concepts
  3. General Programming Language Fundamentals
  4. SQL in PLSQL
  5. Conditional Control: IF Statements
  6. Conditional Control: CASE Statements
  7. Error Handling and Built-In Exceptions
  8. Iterative Control
  9. Introduction to Cursors
  10. Exceptions
  11. Exceptions: Advanced Concepts
  12. Procedures
  13. Functions
  14. Packages
  15. Advanced Cursors
  16. Stored Code
  17. Triggers
  18. Collections
  19. Records