183-187 | High Performance Oracle Database Automation: Creating Oracle Applications with SQL and PL/SQL

Table of Contents

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.

Table of Contents