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

CONTINUE handlers are useful for handling completion conditions and exception conditions not severe enough to affect the flow of control.

CONTINUE Handler Actions

When a condition is raised, a CONTINUE handler does the following:

1. Executes the handler action.
2. Passes control to the next statement following the statement that invoked it.
3. Executes all remaining SQL statements following the statement that raised the condition.
4. The following table describes the detailed flow of control for a CONTINUE handler when it is activated by a raised exception.

   IF …	THEN in the next stage, control …
   the handler action completes successfully	returns to the statement following the statement that raised the condition.
   the exception was raised by a statement embedded within a control statement such as FOR, IF, LOOP, or WHILE
   a control statement raises an exception (for example, while evaluating a conditional expression)	passes to the statement following the control statement that raised the condition.

5. If a handler action raises an exception or completion condition, and if a suitable handler exists within that handler action, the newly raised condition is handled.

   Control returns to the handler action clause.

6. End of process.

Examples of a CONTINUE Handler

The following examples illustrate the behavior of a CONTINUE handler. The examples are based on the following stored procedure:

CREATE PROCEDURE spSample4()
BEGIN
  DECLARE hNumber INTEGER;
  DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
   INSERT INTO Proc_Error_Table
    (:SQLSTATE, CURRENT_TIMESTAMP, 'spSample4',
     'Failed to Insert Row');
       
-- Statement_4_1
UPDATE Employee
  SET Salary_Amount = 10000
  WHERE Employee_Number = 1001;
     
-- Statement_4_2
INSERT INTO EmpNames VALUES (1002, 'Thomas');
     
-- If the EmpNames table had been dropped, Statement_4_2
-- returns SQLEXCEPTION that is handled.
      
-- Statement_4_3
UPDATE Employee
  SET Salary_Amount = 10000
  WHERE Employee_Number = 1003;
END;

Example: ANSI Session Mode for DECLARE HANDLER

The following 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 spSample4();

If an SQL statement reports either an error condition or a failure condition such as deadlock in ANSI session mode, the condition is handled using a condition handler.

When the preceding three SQL statements are invoked in ANSI session mode, the following sequence of events describes the impact of an error condition with respect to them:

1. The stored procedure statement marked as Statement_4_2 raises an exception with the SQLSTATE code '42000'. The request is rolled back.
2. The handler is invoked for the '42000' condition.
3. Because this handler type is CONTINUE, control passes to Statement_4_3 after the handler action completes.
4. The following items are left uncommitted:
   • The first two interactive SQL statements
   • Statement_4_1
   • Statement_4_3
   • The INSERT statement from the condition handler in spSample4.
5. End of process.

When the preceding three SQL statements are invoked in ANSI session mode, the following sequence of events describes the impact of a failure condition with respect to them:

1. The stored procedure statement marked as Statement_4_2, which is invoked by the CALL spSample4() statement, returns an SQLSTATE code that indicates a failure condition.
2. The effects of Statement_4_1 and of the first two interactively entered SQL statements are rolled back and the transaction is rolled back.
3. The returned SQLSTATE code invoked the CONTINUE handler defined for the block, which is written to handle that specific condition (failure in ANSI session mode).
4. Because the handler type is CONTINUE, the stored procedure submits the handler action statements and Statement_4_3 in a new transaction, and the stored procedure execution continues with the next statement after the handler action completes.
5. End of process.

Example: Teradata Session Mode

The following 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 spSample4();

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_4_2 raises an exception with the SQLSTATE code '42000'. The implicit statement is rolled back.
2. SQLSTATE code '42000' invokes the CONTINUE handler defined to handle that specific condition.
3. Since this handler type is CONTINUE, the changes made by Statement_4_1 is not affected.
4. Because the first two BTEQ requests are implicit transactions, their updates are not rolled back.
5. Control passes to Statement_4_3 after the handler action completes.
6. End of process.

Example: Teradata Session Mode Using a BT Statement

This example assumes that the following three SQL statements are invoked interactively from BTEQ in Teradata session mode. 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 spSample4();

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

1. The updates made by Statement_4_1, Statement_4_2, and the first three BTEQ requests are all rolled back.
2. The stored procedure statement marked as Statement_4_2 raises an exception with the SQLSTATE indicating a failure condition.
3. The failure condition invokes the CONTINUE handler defined to handle that specific condition.
4. Because the handler type is CONTINUE, Statement_4_3 is executed after the handler action completes.
   Both the handler action and Statement_4_3 are executed as implicit transactions because the effect of the initial BT was revoked when it was rolled back in Stage 2.
5. End of process.