Noncursor Variables
Noncursor variables in SQL Script are expressions or other elements that resolve to single values. You can define a noncursor variable by specifying its name and data type.
Syntax
DECLARE <varName>[,…] <dataType>
[DEFAULT <valueExpression>]
Remarks
• The DEFAULT syntax is optional. It is used to initialize a variable.
• Any variable that is not initialized with a DEFAULT clause has the value NULL.
• Variables can be used in SQL Script expressions anywhere a literal value is valid. For example, both 1 + 1 and x + y are valid expressions (assuming x and y are declared variables).
• Variables in SQL Scripts are subject to scoping rules.
• A variable can be declared within a block that has the same name as a variable in a parent block. Parameters are treated as if they were defined in the main block of the procedure.
• String-type variables are delimited by single quotes ('string'). To specify an apostrophe within a string, use two apostrophes in a row ('').
• You can declare variables, parameters, and column definitions that are of type BLOB or CLOB.
• You can declare multiple variables at one time, provided all the variables are of the same data type and each has a unique name.
• The <valueExpression> can use IN parameters, previously declared variables in this block, and any variables in parent blocks. In the current block, the value expression cannot use variables that are defined later. If the value expression’s type does not match the variable’s type, an implicit cast is performed (if possible). For information about IN parameters, see
SQL Script Procedure Header.
• If the evaluation of the value expression causes an exception, any other declared variables that have not yet been initialized are set to NULL before entering the exception handler.
Examples
PROCEDURE p ( )
BEGIN
DECLARE a INTEGER;
DECLARE b DATE;
DECLARE c TIME;
DECLARE d TIMESTAMP;
DECLARE e DECIMAL;
DECLARE f FLOAT;
DECLARE g VARCHAR;
DECLARE h CHAR;
END
PROCEDURE p ( )
BEGIN
DECLARE x INTEGER;
SET x = 1;
DECLARE x INTEGER; --illegal
END