 |
|
Step-By-Step: Creating a
Function
Oracle Tips by
Burleson
|
Let’s get our feet wet by creating a function from scratch. We’ll
start with a problem and discuss the relevant data, and then we’ll
design, write, and test our new function.
Function Requirements
Your assignment is to create a function that will find a professor
who can teach a class. The ID number of the class will be provided as
a parameter:. The business rules that the function must enforce are:
-
A full professor is not allowed to teach more
than four classes.
-
A graduate student may not teach more than two
classes.
-
An instructor may not teach a class with a
number above his or her approved level.
-
An instructor with a minimum course level may
not be assigned to teach a course below that level.
-
Obviously, no one can teach two courses at the
same time.
The next step is to establish which tables and views hold data that
is relevant to the problem.
A record of information about professors is kept in the
INSTRUCTORS table, which has this structure:
instructor_number NOT NULL number (5)
last_name varchar2 (15)
first_name varchar2 (15)
faculty_member_flag char (1)
approved_class_level number (3)
min_class_level number (3)
approved_field varchar2 (3)
maximum_classes char (1)
Information about classes is kept in the SCHEDULED_CLASSES
table, which has this structure:
course_number NOT NULL number (5)
course_field NOT NULL char (3)
instructor_number number (5)
semester_id NOT NULL number (3)
class_time number (2)
field_level NOT NULL number (3)
The class_time column is a lookup code that points to a more
detailed description of the class’s day and time in the CLASS_TIMES
table, which has this structure:
class_time NOT NULL varchar2 (2)
description NOT NULL varchar2 (9)
Now that you understand the relationships between the various
pieces of data that the function must consider, it’s time to start
designing the function.
This is an excerpt from the book "High Performance Oracle
Database Automation" by Jonathan Ingram and Donald K.
Burleson, Series Editor. |
