Thursday, October 22, 2009

Chapter 13 Functions



[ Team LiB ]





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.

A1:

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 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;
A2:

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.

A3:

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






    [ Team LiB ]



    Posted by at

    No comments:

    Post a Comment

    Subscribe to: Post Comments (Atom)