DECLARE HANDLER (EXIT Type) | Teradata Vantage - DECLARE HANDLER (EXIT 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™

EXIT handlers deal with conditions that are serious enough to terminate the procedure.

EXIT Handler Actions

When a condition is raised, an EXIT handler does the following:

  1. Executes the handler action.
  2. Implicitly exits the BEGIN … END compound statement in which the handler is declared.
  3. The stored procedure execution continues with the remaining statements outside the compound statement. If the procedure contains no other statement, the procedure terminates and control passes to the caller.
  4. The following table describes the detailed flow of control for an EXIT handler when it is activated by a raised exception or completion condition:
    IF … THEN the next stage in the process is this …
    the handler action completes successfully control transfers to the end of the compound statement or, if at the top level, exits the stored procedure.

    All open cursors declared in the compound statement are implicitly closed.

    the CREATE PROCEDURE statement for this procedure defines INOUT or OUT parameters the value for ACTIVITY_COUNT in the SUCCESS response is set to 1.
    no INOUT or OUT parameters are defined for the procedure then the value for ACTIVITY_COUNT in the SUCCESS response is set to 0.
    the caller is a stored procedure the status variable in the calling stored procedure is set to a value appropriate for the returned condition code.

    For SQLSTATE, the value is set to ‘00000’.

    For SQLCODE, the value is set to 0.

    a control statement raises an exception control exits the compound statement that contains the invoked EXIT handler.
  5. If the handler action raises a condition, it is handled if a handler has been defined within the handler action clause.
  6. End of process.

Examples of an EXIT Handler

The following examples illustrate the behavior of an EXIT handler. The examples are based on the following stored procedure:

CREATE PROCEDURE spSample5()
BEGIN
  DECLARE hNumber INTEGER;
  DECLARE EXIT HANDLER
  FOR SQLSTATE ’42000’
   INSERT INTO Proc_Error_Table
    (:SQLSTATE, CURRENT_TIMESTAMP, ’spSample5’,
     ’Failed to Insert Row’);
    
-- Statement_5_1
UPDATE Employee
  SET Salary_Amount = 10000
  WHERE Employee_Number = 1001;
    
-- Statement_5_2
INSERT INTO EmpNames VALUES (1002, ’Thomas’);
    
-- If the EmpNames table had been dropped, Statement_5_2
-- returns an SQLSTATE code of ’42000’ that is handled.
    
-- Statement_5_3
UPDATE Employee
  SET Salary_Amount = 10000
  WHERE Employee_Number = 1003;
END;

In the cases that follow, control exits the stored procedure and passes to the caller after the handler action is complete because the example stored procedure contains only one compound statement.

In the case of a stored procedure with nested compound statements, the scope of the EXIT handler and its behavior described in these examples apply only to the compound statement in which the handler is defined.

If the handler defined within the compound statement cannot handle the raised condition, then the condition is propagated outwards in search of a suitable handler.

Example: ANSI Session Mode for EXIT Handler

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 spSample5();

If an exception condition that is not a failure condition is reported, the following sequence of events occurs:

  1. The stored procedure statement marked as Statement_5_2 raises an exception with the SQLSTATE code ‘42000’. The request is rolled back.
  2. SQLSTATE code ‘42000’ invokes the EXIT handler defined to handle that specific condition.
  3. Because this handler type is EXIT, the update made by Statement_5_1 is not affected.

    Control passes to the caller after the handler action completes. If the stored procedure has any other statements outside the calling compound statement, control passes to the next statement outside the calling compound statement.

    The implicit transaction initiated by the first interactively invoked SQL statement remains outstanding.

  4. End of process.

If an exception condition is reported (that is, a failure condition), the following occurs:

  1. The stored procedure statement marked as Statement_5_2 raises an exception with the SQLSTATE code that indicates a failure condition.
  2. The effects of Statement_5_1, Statement_5_2, and the first two interactively entered SQL statements are rolled back.
  3. The returned SQLSTATE code invokes the EXIT handler defined for that specific condition.
  4. Control exits the calling compound statement and passes to the next statement, if any, after the handler action completes.
  5. A new transaction remains outstanding if there are SQL statements executed in the EXIT handler that have not been committed.
  6. End of process.

Example: Teradata Session Mode for DECLARE HANDLER (EXIT Type)

This example assumes that the following three SQL statements are invoked interactively from BTEQ in Teradata session mode. Because the statements are invoked in Teradata session mode, each is an implicit transaction.

INSERT INTO Department VALUES (’10’, ’Development’);
    
UPDATE Employee SET Salary_Amount = 10000
 WHERE Employee_Number = 1000;
    
CALL spSample5();

When the preceding three SQL statements are invoked in Teradata session mode, the following sequence of events occurs:

  1. The stored procedure statement marked as Statement_5_2 raises an exception with the SQLSTATE code ‘42000’. The implicit statement is rolled back.
  2. SQLSTATE code ‘42000’ invokes the EXIT handler defined for that specific condition.
  3. Because this handler type is EXIT, and Statement_5_1 was executed in an implicit transaction, the update made by that statement is not affected.
  4. Because the first two BTEQ requests are implicit transactions, their updates are not rolled back.
  5. Control exits the calling compound statement and passes to the next statement, if any, after the handler action completes.
  6. End of process.

Example: Teradata Session Mode for BT Statement

This example assumes that the following three SQL statements are invoked interactively from BTEQ in Teradata session mode. Note that the BT statement at the beginning of the sequence makes the SQL statements into a single explicit transaction.

BT;
INSERT INTO Department VALUES (’10’, ’Development’);
    
UPDATE Employee SET Salary_Amount = 10000
 WHERE Employee_Number = 1000;
  
CALL spSample5();

When the preceding three SQL statements are invoked in Teradata session mode, the following sequence of events occurs:

  1. The stored procedure statement marked as Statement_5_2 raises an exception with the SQLSTATE code ‘42000’, indicating a failure condition.
  2. The updates made by Statement_5_1, Statement_5_2, and the first three BTEQ requests are all rolled back.
  3. The failure condition invokes the EXIT handler defined to handle that specific condition.
  4. Because the handler type is EXIT, control exits the compound statement and passes to the next statement, if any, after the handler action completes.
    The handler action is executed as an implicit transaction because the effect of the initial BT was revoked when it was rolled back in Stage 2.
  5. End of process.

Example of an EXIT Handler That Contains a COMMIT Statement

The following example illustrates the behavior of an EXIT handler that contains a COMMIT transaction control statement. The example assumes the following stored procedure:

CREATE PROCEDURE spSample6()
BEGIN
  DECLARE hNumber INTEGER;
  DECLARE EXIT HANDLER
  FOR SQLSTATE ’42000’
   INSERT INTO Proc_Error_Table
    (:SQLSTATE, CURRENT_TIMESTAMP, ’spSample6’,
    ’Failed to Insert Row’);
  
-- Statement_6_1
UPDATE Employee
  SET Salary_Amount = 10000
  WHERE Employee_Number = 1001;
   
-- Statement_6_2
COMMIT;
  
-- Statement_6_3
UPDATE Employee
  SET Salary_Amount = 10000
WHERE Employee_Number = 1003;
        
-- Statement_6_4
INSERT INTO EmpNames VALUES (1002, ’Thomas’);
    
-- If the EmpNames table had been dropped, Statement_6_2
-- returns an SQLSTATE code of ’42000’ that is handled.
END;

Example: ANSI Session Mode for EXIT Handler That Contains a COMMIT Statement

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 spSample6();

If an exception condition that is not a failure condition is reported, then the following sequence of events occurs:

  1. The first two BTEQ requests and Statement_6_1 and Statement_6_2 from the stored procedure execute and commit normally.
  2. Statement_6_3 initiates a new transaction.
  3. The stored procedure statement marked as Statement_6_4 raises an exception with the SQLSTATE code ‘42000’.
  4. Statement_6_4 is rolled back.
  5. SQLSTATE code ‘42000’ invokes the EXIT handler defined to handle that specific condition.
  6. Because this handler type is EXIT, control exits the compound statement and passes to the next statement outside that compound statement, if any, after the handler action completes. If the stored procedure contains no other statement, the procedure terminates and control passes to the caller.

    The handler action executes within the transaction begun by Statement_6_3.

  7. End of process.

If an exception is reported (that is a failure condition), the following occurs:

  1. The stored procedure statement marked as Statement_6_4 raises an exception with the SQLSTATE code that indicates a failure condition.
  2. The effects of Statement_6_1, Statement_6_2, and the first two interactively entered SQL statements are committed and are not rolled back.

    The failure of Statement_6_4 rolls back its transaction and that of Statement_6_3 (because Statement_6_3 was not committed).

  3. The handler action statements initiate a new transaction.
  4. The failure condition invokes the EXIT handler defined to handle that specific condition.

    Control exits the calling compound statement and passes to the next statement, if any, after the handler action completes.

    If the stored procedure contains no other statement, the procedure terminates and control passes to the caller.

  5. End of process.

Related Topics

For more information about EXIT handler conditions, see Conditions Raised by a Handler Action.