Procedure gr_than_avg
The gr_than_avg stored procedure given below returns the rows from the acctmgrs table that have a commission value that is greater than the average commission computed from those acctmgr rows with a hire_date that is inclusively between the values specified in the lo_date and hi_date argument values.
1 create procedure gr_than_avg( 2 in lo_date date, 3 in hi_date date) 4 begin 5 declare comm_sum double default 0.0; 6 declare comm_count integer default 0; 7 declare comm_avg double default 0.0; 8 declare l_id char(7); 9 declare l_name char(30); 10 declare l_date date; 11 declare l_comm double; 12 declare gr_than_avg cursor for select * from acctmgr; 13 14 if lo_date is null or hi_date is null or lo_date > hi_date then 15 signal sqlcode eINVARG set message_text = "invalid lo_date/hi_date value(s)"; 16 end if; 17 18 open gr_than_avg; 19 get_next1: loop 20 fetch gr_than_avg into l_id, l_name, l_date, l_comm; 21 if not found then 22 leave get_next1; 23 end if; 24 if l_date >= lo_date and l_date <= hi_date then 25 set comm_sum = comm_sum + l_comm; 26 set comm_count = comm_count + 1; 27 end if; 28 end loop get_next1; 29 close gr_than_avg; 30 31 if comm_count > 0 then 32 set comm_avg = comm_sum/comm_count; 33 end if; 34 35 open gr_than_avg; 36 get_next2: loop 37 fetch gr_than_avg into l_id, l_name, l_date, l_comm; 38 if not found then 39 leave get_next2; 40 else if l_comm > comm_avg then 41 select l_id, l_name, l_date, l_comm, comm_avg; 42 end if; 43 end loop get_next2; 44 close gr_than_avg; 45 end;
A single cursor named, gr_than_avg, is used to retrieve each row of the acctmgr table. Note that it is okay to have a local variable with the same name as the procedure - although this is probably not a good programming practice!
The first action is to make sure that the argument values are valid. This is done by the code in lines 14-16. Note that the SIGNAL statement can be used to raise the appropriate SQL error which, since there is no handler declared in this procedures, is passed out to the procedure caller.
Lines 18 through 29 open the cursor, loop through and retrieve each acctmgr row and then update the count and sum of the commission values for those rows that have a hire_date between the specified argument values.
Lines 31-33 computes the average commission rate making sure that a divide-by-zero error does not occur!
And finally, lines 35 through 44, open the cursor again, looping through the acctmgr rows and returning each row that has a commission value that it greater than the computed average.
Note that since each of the two loops exit only after the NOT FOUND condition has been raised, the CLOSE statements at lines 29 and 44 are not technically needed. But it is still good practice to include them for clarity as well as to ensure that should the code functionality later change so that the first loop terminates before the NOT FOUND status has been raised (which automatically closes the cursor) the cursor will still be closed when the subsequent OPEN at line 35 is executed.
This script below calls this procedure to report on the two account managers whose commission is greater than the average commission for employees hired from 1972 through 1994.
rdm-sql: open database bookshop;
rdm-sql: .r gr_than_avg.sql
rdm-sql: call gr_than_avg("1972-01-01", "1995-01-01");
l_id l_name l_date l_comm comm_avg
------ ----------------------------- ---------- ------------------ ----------------
BARNEY Noble, Barney 1972-05-08 0.035 0.02875
l_id l_name l_date l_comm comm_avg
------ ----------------------------- ----------- ----------------- ----------------
FRANK Doel, Frank 1987-02-13 0.030 0.02875