BEGIN … END | Teradata Vantage - BEGIN … END - Advanced SQL Engine - Teradata Database

Purpose

Delimits a compound statement in a stored procedure.

Invocation

Executable.

Stored procedures only.

Syntax

[ label_name : ] BEGIN
  [ local_declaration ] [...]
  [ cursor_declaration ] [...]
  [ condition_handler ] [...]
  [ statement ] [...]
END [ label_name ] ;

label_name

An optional label for the BEGIN … END compound statement.

The beginning label must be terminated by a colon character (:). An ending label is not mandatory. However, if an ending label is specified, you must specify an equivalent beginning label.

The label of a BEGIN … END statement cannot be reused for any statement within it.

Using label names for each BEGIN … END statement is recommended if you specify nested compound statements in a stored procedure.

local_declaration

A local variable declared using the DECLARE statement, or a condition declared using the DECLARE CONDITION statement.

In the case of nested compound statements, variables and conditions declared in an outer compound statement can be reused in any inner compound statement.

Local variables can be qualified with the label of the compound statement in which the variable is declared. This helps to avoid conflicts that can be caused by the reuse of local variables in nested compound statements.

cursor_declaration

A cursor declared using the DECLARE CURSOR statement.

In the case of nested compound statements, a cursor declared in an outer compound statement can be reused in any inner compound statement.

condition_handler

A condition handler declared using the DECLARE HANDLER statement.

You can use BEGIN … END compound statements inside a condition_handler to enclose condition handler action statements.

statement

Any one of the following:

• DML, DDL or DCL statements supported by stored procedures. These include dynamic SQL statements.
• Control statements, including BEGIN … END.

ANSI Compliance

BEGIN … END is ANSI/ISO SQL:2011-compliant.

Authorization

None.

Labels Referenced in LEAVE and ITERATE

If a label associated with a LEAVE or ITERATE statement inside a labeled BEGIN … END statement refers to the BEGIN … END block label, the following applies:

Order of Declarations

Declarations of each type should be specified together. They cannot be interspersed with other types of declarations or other statements in the same block.

If compound statements are nested, you can specify the declarations in some, or all, or none of the BEGIN-END blocks.

Rules

• Stored procedure definitions normally contain one BEGIN … END statement, although this is not mandatory. All other statements of the stored procedure must be specified within this compound statement.
• You can also use a BEGIN … END statement in condition_handler declarations to enclose a list of handler action statements.
• You can nest BEGIN … END compound statements. There is no limit on the nesting level.
• Every BEGIN statement must end with the keyword END.
• You can label the BEGIN … END statement. The scope of the label associated with the BEGIN … END statement is the entire statement.

  This includes all nested compound statements and excludes any handlers declared in the compound statement or nested compound statements.

• You can execute stored procedures from within a BEGIN … END statement.
• The scope of the local variables, conditions, parameters, and cursors declared in a compound statement is the entire compound statement, including all nested compound statements.
• If a local variable, condition, parameter or cursor name in an inner compound statement is the same as one in an outer compound statement, the local variable,condition, parameter, or cursor name in the inner compound statement takes precedence during execution over the name in the outer compound statement.
• Exception, completion, and user-defined conditions raised in a compound statement by any statement other than handler action statements are handled within the compound statement.

  If no appropriate handler is available for a condition in an inner compound statement, then that condition is propagated to the outer compound statement in search of a suitable handler.

• Exception, completion, and user-defined conditions raised in the action clause can be handled by a handler defined within the action clause.

  If a condition raised by a handler action is not handled within the action clause, then that condition is not propagated outwards to search for suitable handlers. It remains unhandled. The only exception is the RESIGNAL statement, whose condition is propagated outside the compound statement action clause in a handler.

  The following table compares unhandled exception, completion and user-defined conditions.

Example: A Valid Stored Procedure with Nested Compound Statements

The following example illustrates a valid stored procedure with nested compound statements.

CREATE PROCEDURE spAccount(OUT p1 CHARACTER(30))
L1: BEGIN
      DECLARE i INTEGER;
     DECLARE DeptCursor CURSOR FOR
      SELECT DeptName from Department;
     DECLARE CONTINUE HANDLER FOR SQLSTATE VALUE '23505'
          L2: BEGIN
       SET p1='Failed To Insert Row';
            END L2;
  L3: BEGIN
INSERT INTO table_1 VALUES(1,10);
IF SQLCODE <> 0 THEN LEAVE L1;
END L3;
 INSERT INTO table_2 VALUES(2,20);
END L1;

The procedure body in this example contains a labeled block L1 enclosing a local variable declaration, cursor declaration, condition handler declaration, the nested block labeled L3, and other statements.

The first INSERT statement and the IF statement are part of the inner compound statement labeled L3 and the second is part of the outer block labeled L1.

The BEGIN … END block labeled L2 is a part of the handler declaration.

Example: Using An Outer Compound Statement's Variable In the Inner Compound Statement

The following example shows the use of an outer compound statement's variable in the inner compound statement by qualifying the variable with the compound statement label.

CREATE PROCEDURE spSample1(INOUT IOParam1 INTEGER,
                              OUT OParam2 INTEGER)
L1: BEGIN
   DECLARE K INTEGER DEFAULT 10;
   L2: BEGIN
     DECLARE K INTEGER DEFAULT 20;
     SET OParam2 = K;
     SET IOParam1 = L1.K;
   END L2;
  ...
END L1;

K is the local variable declared in the outer compound statement L1 and reused in the inner compound statement L2.

After stored procedure execution, the parameter OParam2 takes the default value of K defined in L2, that is 20, because the local declaration of the variable in the inner block takes precedence over the declaration of the same variable in an outer block.

On the other hand, IOParam1 takes the default value of K defined in L1, that is 10, because K is qualified in the second SET statement with the label L1 of the outer compound statement.

Example: Creating a Valid Stored Procedure with Local Variable and Condition Handler Declarations

The following example creates a valid stored procedure with local variable and condition handler declarations. Assume that table1 is dropped before executing this stored procedure.

The INSERT statement in the stored procedure body raises ‘42000’ exception condition, invoking the EXIT handler. The DROP TABLE statement inside the handler action clause raises another ‘42000’ exception, which is handled by the CONTINUE handler.

CREATE PROCEDURE spSample3(OUT p1 CHARACTER(80))
BEGIN
   DECLARE i INTEGER DEFAULT 20;
  
   DECLARE EXIT HANDLER
      FOR SQLSTATE '42000'
      BEGIN
         DECLARE i INTEGER DEFAULT 10;
         DECLARE CONTINUE HANDLER
            FOR SQLEXCEPTION
               SET p1 = 'Table does not exist';
         DROP TABLE table1;
         CREATE TABLE table1 (c1 INTEGER);
         INSERT INTO table1 (i);
      END;
    
   INSERT INTO table1 VALUES(1000,'aaa');
   /* table1 does not exist */
    
END;

Example: Reusing Local Variables and Condition Handlers for the Same SQLSTATE Code

The following example shows the valid reuse of local variables and condition handlers for the same SQLSTATE code in non-nested compound statements.

CREATE PROCEDURE spSample (OUT po1 VARCHAR(50),
                     OUT po2 VARCHAR(50))
BEGIN
     DECLARE i INTEGER DEFAULT 0;
     L1: BEGIN
         DECLARE var1 VARCHAR(25) DEFAULT 'ABCD';
         DECLARE CONTINUE HANDLER FOR SQLSTATE '42000'
            SET po1 = "Table does not exist in L1';
         INSERT INTO tDummy (10, var1);
         -- Table Does not exist
     END L1;
     L2: BEGIN
         DECLARE var1 VARCHAR(25) DEFAULT 'XYZ';
         DECLARE CONTINUE HANDLER FOR SQLSTATE '42000'
            SET po2 = "Table does not exist in L2';
         INSERT INTO tDummy (i, var1);
         -- Table Does not exist
     END L2;
END;

Related Topics