An important aspect of Case 3 and Case 4 is how conditions raised by a handler action associated with the completion of an SQL statement are reported to the calling stored procedure or application.
- If a raised condition is handled without exceptions, then the status variables are set to reflect the successful completion condition. No information about the raised condition is sent to the caller. Thus, if a failure occurs in a stored procedure and it is handled, the caller is not aware of the occurrence of failure or the transaction rollback.
An appropriate mechanism like application rules must be defined to get information about the occurrence of the condition.
- If a statement within the handler action associated with a completion condition handler raises a completion condition other than successful completion, and if there is no suitable handler for that condition, the execution continues with the next statement inside the handler action clause. The status variables contain the values reflecting the original completion condition.
On completion of the handler action, the status variables are set to reflect the successful completion of the handler action.
- If the possibility of a handler action clause raising an exception condition is known, a suitable handler can be placed inside the handler action clause, while creating the stored procedure, to handle such exceptions. The handlers can be nested as deep as necessary.
Example: An Exception Raised By a Handler
In this example, an exception raised by a handler remains unhandled, and the original condition that invoked the handler is propagated outwards.
CREATE PROCEDURE spSample2(IN pName CHARACTER(30), IN pAmt INTEGER) BEGIN DECLARE EXIT HANDLER FOR SQLSTATE '23505' INSERT INTO Error_Tbl VALUES (:SQLSTATE,CURRENT_TIMESTAMP, 'spSample2', 'Failed to insert record'); ... L1:BEGIN DECLARE CONTINUE HANDLER FOR SQLSTATE '23505' BEGIN INSERT INTO Proc_Error_Tbl VALUES (:SQLSTATE, CURRENT_TIMESTAMP, spSample2', 'Failed to Insert record'); END; INSERT INTO tab1 VALUES (pName, pAmt); INSERT INTO tab1 VALUES (pName, pAmt); -- Duplicate row error ... END L1; ... END;
Assume that the table tab1 is created as follows:
CREATE SET TABLE tab1(c1 CHARACTER(30), c2 INTEGER);
Drop the table Proc_Error_Tbl:
DROP TABLE Proc_Error_Tbl;
Now execute the procedure spSample2:
CALL spSample2('Richard', 100);
During stored procedure execution, the last INSERT statement of the compound statement L1 raises a duplicate row exception. The CONTINUE handler declared for SQLSTATE '23505' is invoked. The handler action statement (INSERT) results in another exception '42000'.
Because there is no handler within this handler to handle SQLSTATE '42000', the original condition that invoked the handler, SQLSTATE '23505', is propagated outwards. The outer compound statement has an EXIT handler defined for SQLSTATE '23505'. This handler handles the exception and control exits the compound statement. Because the procedure contains no other statement, the procedure terminates.
Example: Ignoring a Completion Condition Raised Within a Handler
In this example, a completion condition raised within a handler is ignored.
CREATE PROCEDURE spSample1(IN pName CHARACTER(30), IN pAmt INTEGER) BEGIN DECLARE CONTINUE HANDLER FOR SQLSTATE '23505' BEGIN DELETE FROM temp_table; INSERT INTO Proc_Error_Tbl VALUES (:SQLSTATE, CURRENT_TIMESTAMP, 'spSample1', 'Failed to Insert record'); END; INSERT INTO tab1 VALUES (pName, pAmt); INSERT INTO tab1 VALUES (pName, pAmt); -- duplicate row error ... END;
Assume that the tables temp_table and tab1 are defined as follows:
CREATE TABLE temp_table(c1 INTEGER, c2 CHARACTER(30)); CREATE SET TABLE tab1(c1 CHARACTER(30), c2 DECIMAL(18,2));
Now execute the procedure:
CALL spSample1('Richard', 10000);
The last INSERT statement raises a duplicate row exception and the CONTINUE handler declared for this error is invoked. The DELETE statement in the handler action clause results in a “no data found” completion condition.
Since there is no handler defined within the handler to handle this condition, the condition is ignored and the stored procedure execution continues from the next statement (INSERT) in the handler action clause.
Example: Combining Conditions Raised by Statements Within and Outside a Handler Action Clause
This example combines conditions raised by statements within and outside a handler action clause, and shows how an exception raised by a handler action remains unhandled.
REPLACE PROCEDURE han1(INOUT IOParam1 INTEGER, INOUT IOParam2 CHARACTER(20)) Loutermost: BEGIN DECLARE Var1 INTEGER DEFAULT 10; L1: BEGIN DECLARE EXIT HANDLER FOR SQLSTATE '42000' -- Statement 3_1a SET IOParam2 = 'Table does not exist in the outer block'; DECLARE EXIT HANDLER FOR SQLSTATE '23505' L2: BEGIN DECLARE EXIT HANDLER FOR SQLSTATE '23505' -- Statement 3_2a SET IOParam2 = ' Duplicate row error '; DECLARE EXIT HANDLER FOR SQLSTATE '42000' BEGIN -- Statement 3_3a SET IOParam2 = 'Nonexistent table in inner block '; -- Statement 3_3b INSERT INTO tab1 VALUES (IOParam1); -- duplicate row error END; -- Statement 3_3c INSERT INTO notable VALUES (IOParam1, IOParam2); -- 42000 END L2; /* End Label L2 */ -- Statement 3_4a DELETE tab1 ALL; -- Statement 3_4b SET IOParam1 = Var1; -- Statement 3_4c INSERT INTO tab1 VALUES (IOParam1); -- Statement 3_4d INSERT INTO tab1 VALUES (IOParam1); -- duplicate row error END L1; /* End Label L1 */ END Loutermost;
During stored procedure execution, the INSERT statement (Statement 3_4d) raises a duplicate row exception. The first EXIT handler declared for SQLSTATE ‘23505’ is invoked because the handler is in the same compound statement labeled L1.
Then the Statement 3_3c in L2 raises an exception with SQLSTATE '42000'. The EXIT handler defined for ‘42000’ is invoked to handle this exception. The INSERT statement (Statement 3_3b within the handler) raises a duplicate row exception. Since there is no handler within the handler to handle this new condition, the handler exits.
The original condition corresponding to SQLSTATE '23505', which invoked the outermost handler, is propagated outwards. Since there is no suitable handler for that in the outermost compound statement Loutermost, the stored procedure terminates with the error corresponding to '23505'.
Example: Condition Handlers in Nested Stored Procedures
The example in this section is based on the following stored procedure:
CREATE PROCEDURE spSample7a() BEGIN DECLARE hNumber INTEGER; -- Statement_7a_1 UPDATE Employee SET Salary_Amount = 10000 WHERE Employee_Number = 1001; -- Statement_7a_2 INSERT INTO EmpNames VALUES (1002, ’Thomas’); -- Statement_7a_3 UPDATE Employee SET Salary_Amount = 10000 WHERE Employee_Number = 1003; END;
If the EmpNames table had been dropped, Statement_7a_2 in the preceding stored procedure returns an error with SQLSTATE code ‘42000’ that is not handled because no condition handler is defined for it.
Note that the second procedure calls the first procedure at Statement_7b_2.
Consider a second stored procedure definition:
CREATE PROCEDURE spSample7b() BEGIN DECLARE hNumber INTEGER; DECLARE EXIT HANDLER FOR SQLSTATE '42000' INSERT INTO Proc_Error_Table (:SQLSTATE, CURRENT_TIMESTAMP, 'spSample7b', 'Failed to Insert Row'); -- Statement_7b_1 SELECT nextEmpNum INTO hNumber FROM EmpNext; UPDATE Employee SET nextEmpNum = hNumber+1; -- Statement_7b_2 CALL spSample7a(); -- Statement_7b_3 UPDATE Employee SET Salary_Amount = 10000 WHERE Employee_Number = 1003; END;
Example: ANSI Session Mode for Reporting Handler Action-Raised Conditions
This example assumes that the following three SQL statements are invoked interactively from BTEQ in ANSI session mode:
INSERT INTO Department VALUES (’10’, ’Development’); UPDATE Employee SET Salary_Amount = 10000 WHERE Employee_Number = 1000; CALL spSample7b();
When the preceding three SQL statements are invoked in ANSI session mode, the following sequence of events occurs:
- The stored procedure statement marked as Statement_7b_2 calls stored procedure spSample7a.
- Statement_7a_2 in stored procedure spSample7a raises an exception with SQLSTATE code '42000'.
- Control returns to the calling procedure, spSample7b, along with the exception because there is no condition handler defined in spSample7a.
- The exception is handled in spSample7b and the handler action is executed.
- Control exits the calling compound statement because the handler type is EXIT.
- The following items are left uncommitted:
- The first two interactive SQL statements
- Statement_7a_1 from spSample7a
- Statement_7b_1 from spSample7b
- The INSERT statement from the condition handler in spSample7b.
- The following items are not executed:
- Statement_7a_3 from spSample7a
- Statement_7b_3 from spSample7b
- End of process.