DECLARE HANDLER (NOT FOUND Type) | Teradata Vantage - DECLARE HANDLER (NOT FOUND Type) - Advanced SQL Engine - Teradata Database

SQL Stored Procedures and Embedded SQL

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
17.00
Published
June 2020
Language
English (United States)
Last Update
2021-01-23
dita:mapPath
xqq1557098602407.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1148
lifecycle
previous
Product Category
Teradata Vantage™

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:

  1. 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'.
  2. Because the condition handler is an EXIT handler, control passes to the caller after the handler action finishes.
  3. The following items remain active and uncommitted:
    • The first two interactive SQL statements
    • Statement_11_1
    • The INSERT statement inside the handler
  4. 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:

  1. Handler_2 executes its handler action. Because Handler_2 is an EXIT handler, control passes to the caller after the handler action completes.
  2. 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.
  3. The following items remain active, but are not committed:
    • The first two interactive SQL statements
    • Statement_12_1
    • Action statement for Handler_2
  4. End of process.