Procedure new_acctmgr
The new_authors stored procedure given below uses all three of the above statements as well as the GET DIAGNOSTICS statement described in the previous chapter. Three arguments passed in are to be inserted into the author table: newId is the new mgr_id value, newName is the new name value, and comm_rate is the new commission value.
1 create procedure new_acctmgr( 2 newId char, 3 newName char, 4 comm_rate decimal(4,3)) 5 modifies sql data 6 begin 7 declare stat smallint default 0; 8 declare stmtstr char; 9 declare c_id char(7); 10 declare c_name char(24); 11 declare c_hired date; 12 declare c_commission float; 13 declare commstr char(7); 14 declare dyn_name, cmd_name char(25); 15 declare dyn_code, cmd_code smallint; 16 declare errcode char(20); 17 declare errmsg char(45); 18 declare continue handler for sqlexception 19 begin 20 get stacked diagnostics cmd_name = COMMAND_FUNCTION, cmd_code = COMMAND_FUNCTION_CODE, 21 dyn_name = DYNAMIC_FUNCTION, dyn_code = DYNAMIC_FUNCTION_CODE; 22 get stacked diagnostics condition 1 errcode = SQLCODE_NAME, errmsg = MESSAGE_TEXT; 23 set stat = 2; 24 end; 25 declare continue handler for sqlcode eDUPLICATE 26 begin 27 get diagnostics dyn_name = DYNAMIC_FUNCTION, dyn_code = DYNAMIC_FUNCTION_CODE; 28 set stat = 1; 29 end; 30 if comm_rate < 0.001 or comm_rate > 0.10 then 31 signal sqlcode eINVARG set message_text = "commission must be >= 0.001 and <= 0.100"; 32 else 33 set commstr = convert(comm_rate, char); 34 set stmtstr = "INSERT INTO ACCTMGR VALUES '" || newId || "', '" || newName || "', 35 current_date(), " || commstr; 36 prepare s1 from stmtstr; 37 end if; 38 if stat = 2 then 39 select "**** error", cmd_name, dyn_name, errcode, errmsg; 40 else 41 execute s1; 42 if stat = 1 then 43 select "Duplicate mgrid value entered", dyn_name, dyn_code, newId; 44 else if stat = 2 then 45 select "execute error", cmd_name, dyn_name, errcode, errmsg; 46 else 47 set stmtstr = "SELECT * FROM ACCTMGR WHERE mgrid = '" || newId || "'"; 48 prepare s2 from stmtstr; 49 execute s2 into c_id, c_name, c_hired, c_commission; 50 select stat, c_id, c_name, c_hired, c_commission; 51 deallocate prepare s2; 52 end if; 53 end if; 54 deallocate prepare s1; 55 end;
Two continue exception handlers are declared in this procedure. The one at line 25 handles the eDUPLICATE SQLCODE that is raised by the SQL system when a duplicate mgr_id value is inserted when the prepared INSERT statement is executed at line 41. These are declared as continue handlers so that after the handler has been invoked execution will continue at the statement that follows the one which raised the exception. The stat variable is used to indicate whether or not an exception occurred and what kind of exception occurred: stat = 0 means no exception occurred, stat = 1 indicates that a duplicate row was inserted, and stat = 2 indicates that some other kind of error occurred which would either be an invalid argument error raised by the SIGNAL statement at line 30, a compilation error that was raised by the PREPARE statement at line 36 or line 48 or an execution error (unlikely in this example other than the eDUPLICATE) raised by the EXECUTE statement at either line 41 or line 49.
This procedure is intended to illustrate the use of the PREPARE, EXECUTE, and DEALLOCATE PREPARE statements that lets a stored procedure dynamically construct, compile, and execute an SQL statement. Note that this would not be how one would write an efficient procedure to simply insert a new row into the acctmgr table!
The SET statement at line 33 calls the CONVERT function to convert the decimal argument comm_rate into a CHAR string so that its value can be concatenated into the INSERT statement that is constructed and assigned to the stmtstr variable at lines 34 and 35. The prepare at line 36 will compile that string and assign it statement name s1. Any error found during compilation will raise an exception that will be handled by the exception handler at line 18. If successful, stat will still equal 0 and the EXECUTE statement at line 41 will execute the INSERT. If an acctmgr row already exists that has a mgrid equal to the value of newId, an eDUPLICATE error will be raised invoking the handler at line 25.
If the EXECUTE of the INSERT succeeds then lines 47 to 49 will construct, PREPARE and EXECUTE a SELECT statement that will retrieve the row just inserted and store the column values in the variables listed with the INTO clause of the EXECUTE statement at line 49.
The DEALLOCATE PREPARE statements at lines 51 and 54 release all dynamic memory allocated for those named statements. Note that it is not necessary to explicitly execute these statements as that will be done automatically by the system when the procedure returns.