DECLARE | Teradata Vantage - DECLARE - Advanced SQL Engine - Teradata Database

Declares one or more local variables.

ANSI Compliance

DECLARE is ANSI/ISO SQL:2011-compliant.

Required Privileges

None.

Invocation

Nonexecutable control declaration.

Stored procedures only.

Syntax

DECLARE variable_name [,...]
  { predefined_data_type | UDT_name }
  [ attribute ]
  [ DEFAULT
    { literal |
      NEW [SYSUDTLIB.] constructor_name |
      NULL
    }
  ] ;

Syntax Elements

variable_name

The name of an SQL local variable to be declared.

This must be a valid Teradata SQL name. Reserved words and words reserved as status variable names are not permitted.

At runtime, you can qualify the variable with the label of the BEGIN END statement in which the variable is being declared, provided that the statement has a label, as follows:

    label.variable_name

predefined_data_type

The data type of the declared local variable.

This can be either a predefined data type or a UDT, except for the VARIANT_TYPE UDT data type.

UDT_name

You can specify CHARACTER SET with parameters of character data type after the data_type for each parameter.

If CHARACTER SET is not specified, the character set defaults to the character set of the user creating/compiling the stored procedure.

You can also specify CASESPECIFIC or NOT CASESPECIFIC with data_type.

DEFAULT literal

The default value for the local variables.

If specified, this must be a literal and compatible with the specified data type. However, Vantage performs an implicit conversion if a default DateTime value differs from the specified DateTime data type. For details, see Teradata Vantage™ - Data Types and Literals, B035-1143.

If a default value is specified for a local variable declaration, that default is assigned to all variables in the list.

A DEFAULT clause cannot contain an expression.

The variable is initialized to NULL if you do not specify a default for it.

Usage Notes

You can only declare local variables within a BEGIN END compound statement.

You can specify any number of local variable declarations in each BEGIN END compound statement. Each declaration must end with a semicolon character.

Within each declaration, you can specify any number of local variables, separated by commas in a list.

All local variable and condition declarations in a compound statement must be specified before any cursor declarations, condition handlers and other statements.

The scope of a local variable is the BEGIN END compound statement in which it is declared and all nested compound statements.

No two variables declared in a compound statement can have the same name.

A variable name can, however, be reused in any nested compound statement.

Example: Specifying Declaration

The declaration is completely specified:

DECLARE hErrorMsg CHARACTER(30) DEFAULT ’NO ERROR’;

Example: Specifying Multiple Local Variables of the Same Data Type

Multiple local variables of the same data type can be specified in one declaration statement.

The following declaration declares both hAccountNo and tempAccountNo to be INTEGER. No default is specified for either variable; therefore NULL is assigned as the default for both.

DECLARE hAccountNo, tempAccountNo INTEGER;

The following statement declares the data types of hLastName and hFirstNameto be CHARACTER(30).

DECLARE hFirstName, hLastName CHARACTER(30);

Example: Assigning a Default Value For Each Local Variable

A default value can be assigned for each local variable specified in a declaration.

In the following example, a default value of ‘NO ERROR’ is explicitly assigned to hNoErrorMsg and hErrorMsg:

DECLARE hNoErrorMsg, hErrorMsg CHARACTER(30) DEFAULT ’NO ERROR’;

Example: Declaring the Variable MyCircle

The following DECLARE statement declares the variable MyCircle, which has the structured UDT type CircleUdt, to have a default value determined by the constructor external routine named circle with input parameters of 1, 1, and 9:

DECLARE MyCircle CircleUdt DEFAULT NEW circle(1,1,9);

Example: Declaring hBirthdate as DATE Data Type

The following statement declares hBirthdate to be of DATE data type with a default value of '1998-01-06'.

DECLARE hBirthdate DATE DEFAULT '1998-01-06';