 |
|
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.
Design
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. |
