Procedure add_wayne

The last procedure example includes a simple error handler that is used to detect when an INSERT statement fails due to a duplicate primary key value.

 1 create procedure add_wayne(inout stat int)
 2 modifies sql data
 3 begin
 4     declare continue handler for sqlcode eDUPLICATE
 5     begin
 6         set stat = stat + 1;
 7     end;
 8     if stat < 0 or stat > 10 then
 9         set stat = 0;
10     end if;
11     insert into acctmgr values "WAYNE", "Warren, Wayne", current_date(), 0.025;
12     signal sqlcode eDUPLICATE set message_text = "hard-coded error";
13     select stat, * from acctmgr where mgrid = "WAYNE";
14 end; 

This example illustrates a few more features of SQL PL. First of all notice in line 1 that PROCEDURE can be abbreviated PROC. Also notice that the procedure argument named stat is declared as a input/output (INOUT) argument. Line 2 contains a procedure clause that indicates that it modifies the content of the database. This is required because of the INSERT statement at line 11. The most interesting feature, however, is the error handler declared at line 4. Here a CONTINUE HANDLER is declared to handle the situation where the error code eDUPLICATE is raised. When the continue handler gets control, the statements between the BEGIN and END are executed and then control is passed to (i.e., continues from) the statement which follows the statement which raised the condition. This particular handler simply increments the value of stat.

The INSERT at line 11 inserts a new acctmgr row whose mgrid is "WAYNE". If there is already an acctmgr row with that mgrid, the INSERT will raise the eDUPLICATE error and the system will invoke the error handler which will increment stat and then continue execution at line 12. But, line 12 contains a SIGNAL statement which also raises the eDUPLICATE error also invoking the error handler and incrementing the value of stat and then returning control to the SELECT at line 13 which returns the value of stat along with the row in acctmgr with mgrid = "WAYNE" (which must exist!). The following script compiles and calls add_wayne showing how the value of stat changes. Note that it increased by 1 when the INSERT succeeds and by 2 when it fails.

rdm-sql: open database bookshop;
rdm-sql: declare stat int;
rdm-sql: :compile add_wayne.sql
rdm-sql: set @stat = 0;
rdm-sql: call add_wayne(stat);
       stat mgrid   name                     hire_date                commission
          1 WAYNE   Warren, Wayne            2015-09-11                    0.025
rdm-sql: call add_wayne(stat);
       stat mgrid   name                     hire_date                commission
          3 WAYNE   Warren, Wayne            2015-09-11                    0.025
rdm-sql: set @stat = 0;
rdm-sql: call add_wayne(stat);
       stat mgrid   name                     hire_date                commission
          2 WAYNE   Warren, Wayne            2015-09-11                    0.025