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



BC Oracle tuning

Oracle training

Oracle support

Remote Oracle




Step-By-Step: Design a Procedure
Oracle Tips by Burleson

Now, it’s time dive into building a procedure from scratch. We’ll start with a problem and discuss the relevant data structures, then we’ll design and write the procedure.

Procedure Requirements

Your assignment is to automate the year-end raise calculations for employees. After some quick thoughts of the wonderful raise you could give yourself, you start taking a look at the following rules that determine eligibility and amounts for the raises:

  • If an employee has been on time more than 98 percent of the time, the employee earns a .5 percent raise.
  • If an employee is always on time, an extra .1 percent.
  • The amount of an employee’s raise is the sum of the percentages earned from individual qualifying factors.
  • If an employee has received four or more warnings, no raise can be given to the employee.
  • A performance rating higher than 8 earns an employee a .5 percent raise.

All the information needed for the procedure is stored in the EMPLOYEES table, as follows:

employee_num           NOT NULL   number (6)
first_name             NOT NULL   varchar2 (12)
last_name              NOT NULL   varchar2 (12)
ssn                    NOT NULL   number (9)
home_phone             NOT NULL   number (10)
eff_hire_date          NOT NULL   date
base_salary            NOT NULL   number (8,2)
eff_termination_date              date
middle_name                       varchar2 (12)
late_days                         number
warnings                          number
overtime_hours                    number (5,2)
performance_rating                number (2)

The procedure won’t take any parameters because it has to run for all employees. We’ll call the procedure Annual_Review(), because it’s going to be run once a year and gives raises based on some gauges of employee performance.


The first step to designing the procedure is to determine how each individual requirement can be met.

  • We can determine the percentage of the employee’s on-time days by subtracting the number of late_days from the total working days for a year and then calculating a percentage value. If the percentage value is greater than 98 percent, add to the raise amount.
  • It is pretty straightforward if an employee is always on time. No late days, an extra something in the raise. If the total number of late_days for the employee equals zero, the employee earns an extra .1 percent on the raise.
  • The procedure has to keep a running total of the percentages added and then update the salary once.
  • If four or more warnings are received, no raise. This can be determined by checking the warnings column in the EMPLOYEES table.
  • The EMPLOYEES table contains a performance_rating column, which accepts integer values from 1 through 10. If this value is higher than 8, add .5 percent to the total raise.

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