141-145

Previous

Table of Contents

Next

Identifiers

Take a look at this block of code and see if you can recognize the purpose of the variables :

 IF (x < 100) THEN   Raise_Application_Error (-20343, 'The balance is too low.');; END IF; 

You might recognize this code as the same code described just a few lines ago, sans comments and with different variable names . Keeping track of x in three lines of code is easy, but keeping track of x in a 200-line procedure is another story entirely.

Using meaningful identifier names is the best way to document code, as well as one of the easiest . Six months down the road, you might have to debug your 200-line procedure that uses x, y, and z for variable names.

Step-By-Step: Design A Procedure

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.

Pseudocode

After determining how each individual requirement can be met, we can write some pseudocode that puts the logic for the procedure together. This allows us to clarify our thoughts about the procedure and to put those same thoughts into a form that can be looked over by someone who is more familiar with the business rules.

Listing 4.27 shows the pseudocode for the Annual_Review() procedure.

Listing 4.27 Pseudocode for the Annual_Review() procedure.

 open a cursor of all employees; for each employee loop   determine how often the employee has been on time;   if the employee is on time more than 98% then      grant a .5% raise;      if the employee has no late days then        grant another .1% raise;      end if;   end if;   if the employee has four or more warnings then      skip this employee -- no raise;   end if;   if performance rating is higher than 8 then      grant a .5% raise;   end if;   calculate the new salary;   update the base_salary column in the EMPLOYEES table; end loop; commit changes; if any errors occur then   rollback; end if; 

This pseudocode seems like it will do the trick, but looking at it a little more closely, we notice that we re checking the warnings after doing some other things. Because warnings can disqualify the employee from getting a raise, the procedure will work a bit more quickly if no work is done until after the warnings are checked. Figure 4.2 illustrates the logical execution of the procedure.

Figure 4.2    The logical execution of the Annual_Review() procedure.

Code

Now that the pseudocode for the procedure has been written and looked over for logic errors, the code can be written using the pseudocode as a base. The final draft of the Annual_Review() procedure is shown in Listing 4.28.

Previous

Table of Contents

Next