SQLWARNING is a generic condition that represents the SQLSTATE codes for all completion conditions (other than successful completion and “no data found” conditions).
The handler associated with SQLWARNING is invoked when a completion condition is raised during statement execution, and a handler to handle the specific completion condition does not exist.
An SQLWARNING handler can be of EXIT type or CONTINUE type.
SQLWARNING Handler Actions
The flow of control for an SQLWARNING generic condition handler is similar to the flow of control for the SQLEXCEPTION handler. The difference is that an SQLWARNING handler is activated by a raised completion condition.
SQLWARNING cannot handle a “no data found” condition. An SQLWARNING handler, if declared in a stored procedure either along with a NOT FOUND handler or separately, is not activated by a “no data found” completion condition.
Example: Generic Condition Handler for DECLARE HANDLER (SQLWARNING Type)
The following example illustrates the behavior of an SQLWARNING handler. The example assumes the following stored procedure:
CREATE PROCEDURE spSample11() BEGIN DECLARE EmpCount INTEGER; DECLARE EXIT HANDLER FOR SQLWARNING INSERT INTO Proc_Error_Table (:SQLSTATE, CURRENT_TIMESTAMP, 'spSample11', 'Generic handler performed'); -- Statement_11_1 UPDATE Employee SET Salary_Amount = 10000 WHERE Employee_Number = 1001; -- Statement_11_2 SELECT COUNT(*) INTO EmpCount FROM Employee SAMPLE 5; -- Suppose table Employee has only three rows. -- Statement_11_2 returns SQLSTATE 'T7473' that is -- handled by the SQLWARNING handler. END;
Example: ANSI Session Mode for DECLARE HANDLER (SQLWARNING Type)
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 spSample11();
When the preceding three SQL statements are invoked in ANSI session mode, the following sequence of events occurs:
- Statement_11_2 in the called stored procedure raises a completion condition with SQLSTATE 'T7473' that is handled by the SQLWARNING handler because no specific handler exists for SQLSTATE code 'T7473'.
- Because the condition handler is of EXIT type, control passes to the caller after the handler action finishes.
- The following items remain active and uncommitted:
- The first two interactive SQL statements
- Statement_11_1
- The INSERT statement inside the handler
- End of process.
Example: Specific and Generic Condition Handlers
The following example illustrates the behavior of an SQLWARNING handler and a specific condition handler when both DECLARE HANDLER forms are combined in a stored procedure. The example assumes the following stored procedure:
CREATE PROCEDURE spSample12() BEGIN DECLARE EmpCount INTEGER DEFAULT 0; -- Handler_1 DECLARE CONTINUE HANDLER FOR SQLWARNING BEGIN UPDATE warning_table SET warning_count = warning_count + 1; INSERT INTO Proc_Error_Table (:SQLSTATE, CURRENT_TIMESTAMP, 'spSample12', 'Generic warning handler'); END; -- Handler_2 DECLARE EXIT HANDLER FOR SQLSTATE 'T7473' INSERT INTO Proc_Error_Table (:SQLSTATE, CURRENT_TIMESTAMP, 'spSample12', 'Requested sample is larger than table rows'); -- Statement_12_1 UPDATE Employee SET Salary_Amount = 10000 WHERE Employee_Number = 1001; -- Statement_12_2 SELECT COUNT(*) INTO EmpCount FROM Employee SAMPLE 5; -- Suppose the table Employee has only three rows. -- Statement_12_2 returns SQLSTATE 'T7473' that is -- handled by specific handler. END;
Example: ANSI Session Mode Calling spSample12();
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 spSample12();
When the preceding three SQL statements are invoked in ANSI session mode, the following sequence of events occurs:
- Statement_12_2 in the called stored procedure raises a completion condition with SQLSTATE code 'T7473' that is handled by the specific handler Handler_2.
- Handler_2 executes its handler action. Because Handler_2 is an EXIT handler, and the procedure has only one compound statement, the procedure terminates after the handler action completes.
- The following items remain active, but are not committed:
- The first two interactive SQL statements
- Statement_12_1
- Action statement for Handler_2
- End of process.