 |
|
Designing the Function
Oracle Tips by
Burleson
|
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.
This is an excerpt from the book "High Performance Oracle
Database Automation" by Jonathan Ingram and Donald K.
Burleson, Series Editor. |
