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

 

 

   
 

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.

  
 

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