Variables

A variable in an SQL statement specifies a value that can be changed when the SQL statement is executed. There are several types of variables used in SQL statements.

host variable: Host variables are defined by statements of a host language. For more information about how to refer to host variables, see Host variables.
transition variable: Transition variables are defined in a trigger and refer to either the old or new values of columns of the subject table or view of a trigger. For more information about how to refer to transition variables, see CREATE TRIGGER.
SQL variable: SQL variables are defined by an SQL compound statement in an SQL function or SQL procedure. For more information about SQL variables, see References to SQL parameters and SQL variables.
SQL parameter: SQL parameters are defined in an CREATE FUNCTION (SQL Scalar), CREATE FUNCTION (SQL table), or CREATE PROCEDURE (SQL) statement. For more information about SQL parameters, see References to SQL parameters and SQL variables.
parameter marker: Parameter markers are specified in an SQL statement that is dynamically prepared, instead of variables, other than global variables. For more information about parameter markers, see Parameter markers in the PREPARE statement.

Unless otherwise noted, the term variable in syntax diagrams is used to describe where a host variable, transition variable, SQL variable, SQL parameter, or parameter marker can be used.

Host variables
Host variables are defined directly by statements of the host language or indirectly by SQL extensions. A host-variable in an SQL statement must identify a host variable that is described in the program according to the rules for declaring host variables. Host variables cannot be referenced in dynamic SQL statements; parameter markers must be used instead.
Variables in dynamic SQL
In dynamic SQL statements, parameter markers are used instead of variables.
LOB variables
Regular LOB variables (CLOB, DBCLOB, and BLOB), LOB locator variables and LOB file reference variables can be defined in all host languages with a few exceptions.
LOB locator variables
A LOB locator variable is a host variable that contains the locator representing a LOB value on the database server.
XML variables
An XML variable can be defined as a string or file reference variable. Regular XML variables and XML file reference variables can be defined in all host languages with a few exceptions.
LOB or XML file reference variables
File reference variables are used for direct file input and output for LOB and XML values (when the XML value is declared using XML AS variable-type), and can be defined in all host languages.
Result set locator variables
A result set locator variable is a variable that contains the locator that identifies a stored procedure result set. A result set locator variable in an SQL statement must identify a result set locator variable described in the program according to the rules for declaring result set locator variables. This is always indirectly through an SQL statement.
Host structures in PL/I, C, and COBOL
A host structure is a PL/I structure, C structure, or COBOL group that is referred to in an SQL statement.
Host-variable arrays in PL/I, C, C++, and COBOL
A host-variable array is an array in which each element of the array contains a value for the same column. The first element in the array corresponds to the first value, the second element in the array corresponds to the second value, and so on.

Parent topic: Language elements