Sample Chapter: Efficient PL/SQL, Page 15

The CEO wants to balance the distribution of salaries within each department so that there is less of a discrepancy. After a detailed analysis, a module specification has been produced as follows:

Please code a module report_sal_adjustment that runs in less than three seconds and fetches each employee record from the emp tables and performs the following:

1. Determine the average salary for this employees department.
2. If the employee's salary differs by more than 20 percent of the average salary, add a row to the emp_sal_log table with the employee number, name, department name, hiring date, and the amount that the current salary differs from the average salary.
3. If this employee's salary is the lowest in the department, flag this employee by setting the min_sal column in emp_sal_log to "Y," otherwise set this column to "N."

Based on the module specifications you have probably seen over the years, this would rate as a fairly good one, and strangely enough, herein lies the fundamental problem. Procedural specifications yield procedural solutions.

In an attempt to make the module specification easy and straightforward to follow, it has been written in a way that lends itself to a procedural solution. The specification outlines a number of discrete steps to be taken for each employee in the organization. Let's look at a solution that represents a direct mapping of the specification to our database. First we will need a table called emp_sal_log in which to record the results of our report.

SQL> create table EMP_SAL_LOG (
  2   ENAME       VARCHAR2(20),
  3   HIREDATE    DATE,
  4   SAL         NUMBER(7,2),
  5   DNAME       VARCHAR2(20),
  6   MIN_SAL     VARCHAR2(1));

Table created.

Now let's look at a PL/SQL program that satisfies the requirements of the specification. We'll break it into sections that align with the module specification that was used to build the program.

SQL> create or replace
  2  procedure report_sal_adjustment is

We will need some variables to store the average and minimum salaries for each department, as well as the department name for an employee.

  3  v_avg_dept_sal emp.sal%type;
  4  v_min_dept_sal emp.sal%type;
  5  v_dname        dept.dname%type;

We will have a cursor to allow us to loop through each employee in the EMP table.

  6  cursor c_emp_list is
  7    select empno,ename,deptno,sal,hiredate
  8    from emp;
  9  begin

Here is where the real work begins. We start fetching through our cursor, picking up each employee row in turn. Using this employee's department number, we can determine the average salary for this department (module specification component #1).

 10  for each_emp in c_emp_list loop
 11    select avg(sal)
 12    into   v_avg_dept_sal
 13    from   emp
 14    where  deptno =each_emp.deptno;

Now we compare the employee's salary with the average salary for his or her department. If it's more than 20 percent away from the average, we want to log a row in the emp_sal_log table (module specification component #2). Before we can do that, however, we need to look up the name of the employee's department, at which point we may as well pick up the lowest salary for the department as well because we will need that shortly to satisfy module specification component #3.

 15  if abs(each_emp.sal -v_avg_dept_sal )/v_avg_dept_sal >0.20 then
 16    select dept.dname,min(emp.sal)
 17    into   v_dname,v_min_dept_sal
 18    from   dept,emp
 19    where  dept.deptno =each_emp.deptno
 20    and    emp.deptno =dept.deptno
 21  group by dname;

Before we insert a record into the emp_sal_log table, we need to know if this employee has the lowest salary for this department. Comparing the minimum department salary we just picked up with the employee's salary allows us to set the min_sal flag in emp_sal_log.

 22        if v_min_dept_sal =each_emp.sal then
 23          insert into emp_sal_log
 24          values (each_emp.ename,each_emp.hiredate,
 25                  each_emp.sal,v_dname,'Y');
 26        else
 27          insert into emp_sal_log
 28          values (each_emp.ename,each_emp.hiredate,
 29                  each_emp.sal,v_dname,'Y');
 30        end if;
 31      end if;
 32    end loop;
 33  end;
 34  /

Procedure created.

A couple of executions (not shown) confirm that the procedure satisfies the functional requirements, so let's look at how efficient it is. When run against the sample data that was generated earlier (that is, 500 employees and 10 departments), all appears well.

SQL> exec report_sal_adjustment

PL/SQL procedure successfully completed.

Elapsed:00:00:00.03

Magic! It ran in less then three seconds (one of the requirements of our module specification) so our code is ready for deployment into production! And depending on the size of the company, this might be a perfectly adequate solution. But what happens when we scale this up to a larger number of employees. This company (or a company that we might be hoping to sell this solution to) might have thousands of employees. To test the efficiency of the solution under various scenarios (after all, we want to demonstrate that our code is efficient), a simple SQL*Plus script can be used to test the performance with varying employee and departmental populations. Follow the REM lines in the script to see how it works.

rem REPTEST.SQL
rem ......
rem Takes as input two parameters,the first being
rem the number of employees,the second the number
rem of departments.Employees are assigned at random
rem to departments,and salaries are randomised between
rem 7500 and 10000
rem
set termout off

rem Number of employees will be passed as the first parameter,
rem number of departments as the second.We want to assign these
rem values to two SQL Plus substitution variables called NUM_EMPS
rem and NUM_DEPTS respectively

col x new_value num_emps
col y new_value num_depts

rem Select the values from DUAL to complete the assigment

select &1 x,&2 y from dual;

rem Now we erase the contents of the EMP table and DEPT table
rem in preparation for population with sample data

truncate table EMP reuse storage;
truncate table DEPT reuse storage;

rem Now we load up the EMP table just like we did in the
rem previous example,using the SRC table to generate as
rem many rows as we require

insert into EMP
select rownum,
       'Name'||rownum,
       sysdate+rownum/100,
       dbms_random.value(7500,10000),
       dbms_random.value(1,&num_depts)
from SRC
where rownum <=&num_emps;
rem We do a similar exercise with the DEPT table to load
rem the required number of department rows

insert into DEPT
select rownum,'Dept'||rownum
from SRC
where rownum <=&num_depts;

rem And for good measure,we will calculate optimizer
rem statistics on the two tables,because no-one should
rem be using the rule based optimizer anymore

analyze table emp compute statistics;
analyze table dept compute statistics;

rem We truncate our results table in preparation for the test
truncate table EMP_SAL_LOG;


rem And now,here is the real testing portion of the script
rem We will run the procedure 3 times,and report an average
rem execution time across the 3 executions.To do this,we
rem use the DBMS_UTILITY.GET_TIME function to give start and
rem end timings for the test . the delta of these two times
rem is the total time elapsed (in centiseconds).Dividing
rem by 100 and then by 3,gives us the average execution
rem time in seconds.
set serverout on
set termout on
declare
  x number :=dbms_utility.get_time;
begin
  for i in 1 ..3 loop
    report_sal_adjustment;
  end loop;
  dbms_output.put_line('Average run time:'||
                       round((dbms_utility.get_time-x)/3/100,2));
end;
/