Sample Chapter: Efficient PL/SQL
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:
- Determine the average salary for this employees department.
- 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.
- 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; /
Page 15 of 19