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;

Testing

Now, it’s time to write a unit test script for the function. By breaking the requirements down, we find that there are nine tests that must be performed.

•  Attempt to assign five classes to a faculty member instructor. This can be accomplished by creating four classes with the professor already assigned to teach them, then creating another class appropriate to the professor. The function should not return a value.

•  Attempt to assign three classes to a graduate student instructor. This test is similar to the previous test. Set up two classes with the graduate student as the assigned instructor, then create a third class appropriate to the graduate student. The function should not return a value.

•  Attempt to assign four classes to a faculty member instructor. This is similar to the first test, but instead of creating four classes, create three classes that the instructor is already teaching. The function should return the ID number of the instructor.

•  Attempt to assign two classes to a graduate student instructor. This test is similar to the previous test: Set up one existing class that the graduate student is teaching and set up the data so that the graduate student should be assigned to teach one more course. The function should return the ID number of the instructor.

•  Attempt to assign a class with a course_level greater than the instructor’s approved_class_level by creating a class appropriate to the instructor with the exception of the class level and running the function. The function should return without value.

•  Attempt to assign a class with a course_level less than the instructor’s approved_class_level by creating a class appropriate to the instructor. The function should return the instructor’s ID number.

•  Attempt to assign a course with a course_level less than the instructor’s min_class_level by creating a class appropriate to the instructor and setting the instructor’s min_class_level to the level above the course. The function should return without a value.

•  Attempt to assign two classes at different times to one instructor by creating one class assigned to the instructor and another class at a different class period. The function should return the instructor’s ID number.

•  Attempt to assign two classes at the same time to one instructor by creating one class assigned to the instructor and another class in the same class period. The function should return without a value.