Purpose
Declares one or more local variables.
Invocation
Nonexecutable control declaration.
Stored procedures only.
Syntax
DECLARE variable_name [,...] { predefined_data_type | UDT_name } [ attribute ] [ DEFAULT { literal | NEW [SYSUDTLIB.] constructor_name | NULL } ] ;
- variable_name
- The name of an SQL local variable to be declared.
- predefined_data_type
- The data type of the declared local variable.
- UDT_name
- You can specify CHARACTER SET with parameters of character data type after the data_type for each parameter.
- DEFAULT literal
- The default value for the local variables.
ANSI Compliance
DECLARE is ANSI/ISO SQL:2011-compliant.
Authorization
None.
Variable Declaration Rules
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.
- Local variable name (mandatory)
- Variable data type (mandatory)
- Default value for the local variable (optional).
The default value must be compatible with the data type declared. However, Teradata Database performs an implicit conversion if a default DateTime value differs from the specified DateTime data type. For details, see “Data Type Conversions” in Teradata Vantage™ - Data Types and Literals, B035-1143.
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';