Call (800) 766-1884 for Oracle support & training
Free Oracle Tips


Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance
 

Free Oracle Tips


 

HTML Text

BC Oracle tuning

Oracle training

Oracle support

Remote Oracle

 

 

   
 

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.

††
 

Oracle performance tuning software 
 
 
 
 

Oracle performance tuning book

 

 
 
 
Oracle performance Tuning 10g reference poster
 
 
 
Oracle training in Linux commands
 
Oracle training Excel
 
Oracle training & performance tuning books
 

 

   

Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracleģ is the registered trademark of Oracle Corporation. 

Hit Counter