Free Oracle Tips   HTML Text

BC Oracle tuning

Oracle training

Oracle support

Remote Oracle

Oracle PL/SQL Code Oracle Tips by Burleson

Now that we have the logic of the function outlined, we’re ready to write the code. Our largest obstacle is the lack of a continue statement in PL/SQL. While our pseudocode can say “skip to the next instructor” or “goto the next instructor,” PL/SQL doesn’t provide us with an easy way to do this. We could use the GOTO statement to handle this situation, but most people consider that to be bad coding style.

To resolve this dilemma, we’ll create a boolean variable called bInstructorValid that we’ll use to keep track of the status. If a condition occurs that forces us to discard an instructor, all subsequent tests inside the loop will be skipped, because the first condition of each test will be that bInstructorValid returns TRUE. Listing 5.23 is the code for our new function.

Listing 5.23 The code for the new Assign_Instructor() function.

FUNCTION Assign_Instructor
   (nClassNumber IN     SCHEDULED_CLASSES.course_number%TYPE)

   RETURN INSTRUCTORS.instructor_number%TYPE

IS

   --
   -- Full professors can teach 4 courses. Graduate students
   -- can teach two.
   --
   FULL_COURSES  CONSTANT integer := 4;
   GRAD_COURSES  CONSTANT integer := 2;

   bInstructorValid Boolean := TRUE;
   iCoursesCarried  integer;
   nDummyVariable   number;
   nTeacherID       INSTRUCTORS.instructor_number%TYPE;
   nTeachLevel      INSTRUCTORS.approved_class_level%TYPE;
   nTeachMinLev     INSTRUCTORS.min_class_level%TYPE;
   nTime            SCHEDULED_CLASSES.class_time%TYPE;
   nClassLevel      SCHEDULED_CLASSES.field_level%TYPE;
   rClassROWID      ROWID;
   vCourseField     SCHEDULED_CLASSES.course_field%TYPE;

   xNO_INSTRUCTOR_AVAILABLE   EXCEPTION;

   CURSOR Instructors_cur
      (vCourseField   SCHEDULED_COURSES.course_field%TYPE)
   IS
   SELECT instructor_number,
          --
          -- Course levels are stored as 100, 200, 300,...800. In
          -- order to properly check the course level for an
          -- instructor, the approved number is raised by 100 for the
          -- comparison against the course's level.
          --
          approved_class_level + 100 approved_class_level,
          min_class_level
   FROM   INSTRUCTORS
   WHERE  maximum_courses = 'N'
   AND    approved_field = vCourseField;

BEGIN
   SELECT class_time, field_level,  course_field, ROWID
   INTO   nTime,      nClass_Level, vCourseField, rClassROWID
   FROM   SCHEDULED_CLASSES
   WHERE  course_number  = nClassNumber;

   FOR Instructors_rec IN Instructors_cur (vCourseField) LOOP
      nTeacherID     := Instructors_rec.instructor_number;
      nTeacherLevel  := Instructors_rec.approved_class_level;
      nTeacherMinLev := Instructors_rec.min_class_level;

      SELECT count (*)
      INTO   iCoursesCarried
      FROM   SCHEDULED_COURSES
      WHERE  instructor_number = nTeacherID;

      bInstructorValid := TRUE;

      --
      -- If the instructor is already teaching the maximum number
      -- of courses allowed, make sure that future executions of
      -- this function don't retrieve the professor.
      --
      IF (iCoursesCarried = decode (vFullProf,
                                    'Y', FULL_COURSES,
                                    'N', GRAD_COURSES)) THEN
         UPDATE INSTRUCTORS
         SET    maximum_courses = 'Y'
         WHERE CURRENT OF Instructors_cur;

         bInstructorValid := FALSE;
      END IF;

      IF bInstructorValid THEN
         --
         -- The course level cannot exceed the instructor's approved
         -- level.
         --
         IF (nClassLevel < nTeacherLevel) THEN
            bInstructorValid := FALSE;
         END IF;
      END IF;

      IF bInstructorValid THEN
         --
         -- If the instructor has a minimum class level defined, the
         -- course must meet or exceed that minimum level.
         --
         IF (nClassLevel < nTeacherMinLev) THEN
            bInstructorValid := FALSE;
         END IF;
      END IF;

      IF bInstructorValid THEN
         --
         -- If this query returns a row, the instructor already has
         -- a class scheduled for this time.
         --
         BEGIN
            SELECT 1
            INTO   nDummyVariable
            FROM   SCHEDULED_CLASSES
            WHERE  instructor_number = nTeacherID
            AND    class_time        = nTime;

            bInstructorValid := FALSE;

         EXCEPTION
            WHEN NO_DATA_FOUND THEN
                 NULL;
         END;        
      END IF;

      --
      -- Everything is OK. Use this instructor.
      --
      IF bInstructorValid THEN
         RETURN nTeacherID;
      END IF;
   END LOOP;

   RAISE xNO_INSTRUCTOR_AVAILABLE;
END Assign_Instructor;

            
This is an excerpt from the book "High Performance Oracle Database Automation" by Jonathan Ingram and Donald K. Burleson, Series Editor.