Variables

NuoDB SQL supports declaring and defining SQL variables for use in SQL stored procedures, user defined functions (UDFs) or triggers.

Syntax

VAR { var_name var_type [ = { sql_expression | ( sql_query_statement ) | ( dynamic_sql_statement ) } ] }
      [ ,var_name var_type [ = { sql_expression | ( sql_query_statement ) | ( dynamic_sql_statement ) } ] ]... ;

var_name =
   { sql_expression | ( sql_query_statement ) | ( dynamic_sql_statement ) };

var_name [ ,var_name ]... =  { ( sql_query_statement ) | ( dynamic_sql_statement ) };

Description

Variables can be declared and optionally defined with the VAR keyword. After being declared, assignment to variables is done with the “var_name =” syntax.

Multiple variables can be defined at once with the syntax “var_name [ ,var_name ]… =” and the return value from sql_query_statement or dynamic_sql_statement. In the case where we are assigning the dynamic_sql_statement return value to multiple variables, the dynamic_sql_statement must be a query. The SELECT list in sql_query_statement or dynamic_sql_statement will be assigned to each var_name in the order listed in the SELECT list. The number of var_name s to the left of the equation must match the number of items in the SELECT list.

An entire stored procedure is considered a code block, or scope. Variables, having the same name, cannot be redefined within a code block or scope. What this means is that a variable, having the same name, cannot be redefined inside a stored procedure. However, a stored procedure can contain nested scopes. These nested scopes are defined by any of the following:

IF and ELSE code block
WHILE loop
FOR loop
TRY and CATCH code block

Variables defined inside a nested scope are valid only within that scope. These variables, inside a nested scope, can be defined with the same name as an already existing variable that was defined outside the nested scope. This nested scope variable is initialized and assigned values based on processing inside the nested scope. Once processing exits the nested scope, this variable’s value reverts back to the value it was initially assigned prior to processing inside the nested scope (see Example 2).

One or more SQL variables can also be initialized by a CASE statement. See SQL Procedural Conditional Statements.

Parameters

var_name

A name to be assigned to a SQL procedural variable.

var_type

A SQL type for a SQL procedural variable.

sql_expression

A valid expression to be assigned to a SQL procedural variable.

sql_query_statement

A string representing a valid SQL query statement to be assigned to a SQL procedure variable. See SELECT for syntax of a valid SELECT statement.

The SELECT statement select list will be assigned to each var_name in the order listed in the select list. The number of var_name s to the left of the equation must match the number of items in the select list. This sql_query_statement must be enclosed by parentheses.

dynamic_sql_statement

An EXECUTE IMMEDIATE statement. See SQL Procedural Dynamic SQL. The EXECUTE IMMEDIATE statement must be a query returning a result set and the result set will be assigned to each var_name in the order listed in the SELECT list of the query. The number of var_name s to the left of the equation must match the number of items in the query SELECT list. If EXECUTE IMMEDIATE is used in a variable assignment, an error will incur if it also specifies INTO target_var. This dynamic_sql_statement must be enclosed by parentheses.

Using EXECUTE IMMEDIATE, it is possible to call a stored procedure with a variable passed as an OUT or INOUT argument in order to initialize the variable using the body of the stored procedure. You would do this with:

EXECUTE IMMEDIATE 'CALL procedure(? [,?]...)
    INTO target_var [ ,target_var ]...
    USING VALUES input_var [ ,input_var ]...

Stored procedures do not return values so any command EXECUTE IMMEDIATE 'CALL …' can not be assigned to a variable. A function may be called within a SELECT statement using EXECUTE IMMEDIATE, as in:

EXECUTE IMMEDIATE 'SELECT function() FROM dual';

Example:

SELECT f1() FROM dual;

 F1
 ---

  1

SET DELIMITER @
CREATE PROCEDURE p1
AS
    VAR x INTEGER;
    x = (EXECUTE IMMEDIATE 'SELECT f1() FROM dual');
    THROW x;
END_PROCEDURE;
@
SET DELIMITER ;

CALL p1;
1

Examples

Example 1: Simple examples

Declare variable name as type STRING and assign to it:

VAR name='Elizabeth';

Declare variable name as type STRING and in a separate statement assign to it:

VAR name;

name = 'Elizabeth';

Declare a variable x to be INTEGER and assign it the result of a simple query:

VAR x INTEGER = (SELECT 1 FROM dual);

Declare the same variable x and assign to it using an EXECUTE IMMEDIATE statement:

VAR x INTEGER = (EXECUTE IMMEDIATE 'SELECT 1 FROM dual');

Example 2: Redefining variables within a stored procedure

A variable cannot be redefined, using the same name, within the same scope or code block.

USE TEST;
DROP PROCEDURE IF EXISTS prc_variable_example;
SET DELIMITER @
CREATE PROCEDURE prc_variable_example
AS
    VAR l_local_var STRING = 'string1';
    VAR l_local_var STRING = 'string2';
    THROW l_local_var;
END_PROCEDURE
@
Redeclaration of variable L_LOCAL_VAR not allowed.

SET DELIMITER ;

A variable with the same name can be defined inside a nested scope. Once processing inside the nested scope exits, the variable value returns to its original value.

USE TEST;
DROP PROCEDURE IF EXISTS prc_variable_example;
SET DELIMITER @
CREATE PROCEDURE prc_variable_example
AS
    VAR l_local_var STRING = 'string1';
    VAR l_if_var STRING;
    IF (true)
        VAR l_local_var STRING = 'string2';
        l_if_var=l_local_var;
    END_IF;
    THROW 'local_var='||l_local_var||' if_var='||l_if_var;
END_PROCEDURE;
@
SET DELIMITER ;

CALL prc_variable_example;
Procedure TEST.PRC_VARIABLE_EXAMPLE, local_var=string1 if_var=string2

A variable defined inside a nested scope is valid only from within that scope.

USE TEST;
DROP PROCEDURE IF EXISTS prc_variable_example;
SET DELIMITER @
CREATE PROCEDURE prc_variable_example
AS
    IF (true)
        VAR l_local_var STRING = 'string1';
    END_IF;
    THROW 'local_var='||l_local_var;
END_PROCEDURE;
@
can't resolve field "L_LOCAL_VAR"

SET DELIMITER ;

Example 3: Assigning values to multiple variables using a single SELECT statement

The assignment of a variable to a value is done through the ‘=’ operator. The user can also specify multiple variables on the left side of the operator, provided that the right side evaluates to a single row having the same number of columns.

SET DELIMITER @
CREATE PROCEDURE prc_player_info(IN inNumber INTEGER)
AS
   VAR outName STRING, outPos STRING, outTeam STRING;
   outName, outPos, outTeam = (SELECT name, position ,
                                      CASE team
                                           WHEN 'Bruins' THEN 'Boston Bruins'
                                           ELSE 'Other Team'
                                       END AS team
                         FROM hockey.hockey
                        WHERE number = inNumber);
   VAR outParm STRING = outName || ' plays position '|| outPos ||' for '||outTeam;
   THROW outParm;
END_PROCEDURE
@
SET DELIMITER ;
EXECUTE prc_player_info(37);
Procedure TEST.PRC_PLAYER_INFO, PATRICE BERGERON plays position Forward for Boston Bruins