NOT FOUND is a generic condition that represents the SQLSTATE codes for all “no data found” completion conditions.
The handler associated with NOT FOUND is invoked when a “no data found” completion condition is raised during statement execution and a handler to handle the specific condition does not exist.
A NOT FOUND handler can be of EXIT type or CONTINUE type.
NOT FOUND Handler Actions
The flow of control for a NOT FOUND generic condition handler is similar to the flow of control for an SQLEXCEPTION or SQLWARNING handler. The difference is that a NOT FOUND handler is activated when a “no data found” completion condition is raised.
Example: Behavior of a NOT FOUND handler
The following example illustrates the behavior of a NOT FOUND handler. The example assumes the following stored procedure:
CREATE PROCEDURE spSample11() BEGIN DECLARE EmpCount INTEGER; DECLARE EXIT HANDLER FOR NOT FOUND INSERT INTO Proc_Error_Table (:SQLSTATE, CURRENT_TIMESTAMP, 'spSample11', 'Generic no data found handler performed'); -- Statement_11_1 UPDATE Employee SET Salary_Amount = 10000 WHERE Employee_Number = 1001; -- Statement_11_2 DELETE Employee WHERE Employee_Number = 1; -- Suppose table Employee does not have a row for -- Employee_Number 1. Statement_11_2 returns SQLSTATE -- '02000' that is handled by NOT FOUND handler. END;
Example: ANSI Session Mode for DECLARE HANDLER (NOT FOUND 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 '02000' that is handled by the NOT FOUND handler because no specific handler exists for SQLSTATE code '02000'.
- Because the condition handler is an EXIT handler, 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 When Both DECLARE HANDLER Forms Are Combined
The following example illustrates the behavior of a NOT FOUND 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 CONTINUE HANDLER FOR NOT FOUND -- Handler_1 BEGIN UPDATE warning_table SET warning_count = warning_count + 1; INSERT INTO Proc_Error_Table (:SQLSTATE, CURRENT_TIMESTAMP, 'spSample12', 'Generic no data found handler'); END; DECLARE EXIT HANDLER FOR SQLSTATE '02000' -- Handler_2 INSERT INTO Proc_Error_Table (:SQLSTATE, CURRENT_TIMESTAMP, 'spSample12', 'No data found'); -- Statement_12_1 UPDATE Employee SET Salary_Amount = 10000 WHERE Employee_Number = 1001; -- Statement_12_2 DELETE Employee WHERE Employee_Number = 1; -- Suppose table Employee does not have a row for -- Employee_Number 1. Statement_12_2 returns SQLSTATE -- '02000' that is handled by NOT FOUND handler. END;
Example: ANSI Session Mode Where Handler_2 Executes Its Handler Action
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:
- Handler_2 executes its handler action. Because Handler_2 is an EXIT handler, control passes to the caller after the handler action completes.
- Statement_12_2 in the called stored procedure raises a completion condition with SQLSTATE code '02000' that is handled by the specific handler Handler_2.
- 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.