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

Table of Contents

Designing The Function

To design the function, we first need to examine how each business rule can be satisfied by the function.

1. We can query a count of the classes taught by a particular instructor from the SCHEDULED_CLASSES table using the following code:

 SELECT count (*)   FROM   SCHEDULED_CLASSES   WHERE  instructor_number = <the professor's ID number>;

If the result is four or more, the professor can t teach any more classes.
Because there are several hundred professors and several hundred graduate students who will be teaching courses, we can save ourselves some work if a professor is marked off our list once he or she is teaching the maximum number of courses. After some discussions with our DBA, the maximum_classes column is added to the INSTRUCTORS table. Our function will set this flag to Y if the professor can t teach any more classes.

2. If the previous rule is sufficiently handled, then this one is easy. The only difference is that graduate students are only allowed to teach two courses, not four.

3. We can compare the approved_class_level from the INSTRUCTORS table against the field_level from the SCHEDULED_CLASSES table. If field_level is greater than approved_class_level , the instructor can t teach the class.

4. We can compare the min_class_level from the INSTRUCTORS table against the field_level from the SCHEDULED_CLASSES table. If field_level is less than approved_class_level , the instructor can t teach the class.

5. We can get a count of the number of classes to which the instructor is assigned that have the same class_time as the course being scheduled. If the count isn t zero, the instructor can t be scheduled for the class.

Pseudocode

After determining how we can meet each individual requirement, we can write the pseudocode in Listing 5.22 for the function.

Listing 5.22 Pseudocode for the Assign_Instructor() function.

 get the information about the specified course; open a cursor of all instructors still able to teach courses; for each instructor in the list loop    determine how many classes the instructor is teaching;    determine how many classes the instructor can teach;    if the instructor can't teach any more classes then       update the maximum_classes field in the INSTRUCTORS table;       goto the next professor;    end if;    if the instructor's approved level is too low for the course then       goto the next professor;    end if;    if the instructor's min level > the course level then       goto the next professor;    end if;    open a cursor of other classes taught by the instructor;    for each course taught by the instructor loop       compare the course time and day against the specified course;       if there is a conflict then          goto the next professor;       end if;       return the professor's ID to the calling procedure;    end loop; end loop; if the function has come this far then    raise exception NO_INSTRUCTORS_AVAILABLE; end if;

Once pseudocode is written it s very easy to write the code for the function, because the essential part of the work, deciding the flow of the function s logic, has already been accomplished. Figure 5.1 illustrates the logic for the Assign_Instructor() function.

Figure 5.1 The logic flow of the Assign_Instructor() function.

Code

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.

Table of Contents