The
CREATE VARIABLE statement defines a session global variable.
Invocation
This statement can be embedded
in an application program or issued interactively. It is an executable
statement that can be dynamically prepared only if DYNAMICRULES run
behavior is in effect for the package (SQLSTATE 42509).
Authorization
The
privileges held by the authorization ID of the statement must include
at least one of the following authorities:
- IMPLICIT_SCHEMA authority on the database, if the implicit or
explicit schema name of the variable does not exist
- CREATEIN privilege on the schema, if the schema name of the variable
refers to an existing schema
- DBADM authority
and any privileges that are necessary to execute the default
expression.
To execute this statement with
a cursor-value-constructor that uses a select-statement,
the privileges held by the authorization ID of the statement must
include the privileges necessary to execute the select-statement.
See the Authorization section in "SQL queries".
Group
privileges are not considered when checking authorization for objects
referenced in the statement
To replace an existing variable, the authorization
ID of the statement must be the owner of the existing variable (SQLSTATE
42501).
Syntax
>>-CREATE--+------------+--VARIABLE--variable-name--| data-type1 |-->
'-OR REPLACE-'
.-DEFAULT NULL-----------------------------------------. (1)
>--+------------------------------------------------------+-----><
+-CONSTANT NULL----------------------------------------+
'-+-DEFAULT--+--+-constant---------------------------+-'
'-CONSTANT-' +-special-register-------------------+
+-global-variable--------------------+
+-(--| cursor-value-constructor |--)-+
'-(--expression--)-------------------'
data-type1
|--+-| built-in-type |---------------+--------------------------|
+-| anchored-variable-data-type |-+
+-array-type-name-----------------+
+-cursor-type-name----------------+
+-distinct-type-name--------------+
+-REF--(type-name)----------------+
'-row-type-name-------------------'
built-in-type
|--+-+-SMALLINT----+----------------------------------------------------------------------+--|
| +-+-INTEGER-+-+ |
| | '-INT-----' | |
| '-BIGINT------' |
| .-(5,0)-------------------. |
+-+-+-DECIMAL-+-+--+-------------------------+-----------------------------------------+
| | '-DEC-----' | | .-,0-------. | |
| '-+-NUMERIC-+-' '-(integer-+----------+-)-' |
| '-NUM-----' '-,integer-' |
| .-(53)------. |
+-+-FLOAT--+-----------+--+------------------------------------------------------------+
| | '-(integer)-' | |
| +-REAL------------------+ |
| | .-PRECISION-. | |
| '-DOUBLE--+-----------+-' |
| .-(34)-. |
+-DECFLOAT--+------+-------------------------------------------------------------------+
| '-(16)-' |
| .-(1)------------------------. |
+-+-+-+-CHARACTER-+--+----------------------------+----------+--+------------------+-+-+
| | | '-CHAR------' '-(integer-+-------------+-)-' | | (2) | | |
| | | +-OCTETS------+ | '-FOR BIT DATA-----' | |
| | | '-CODEUNITS32-' | | |
| | '-+-VARCHAR----------------+--(integer-+-------------+-)-' | |
| | '-+-CHARACTER-+--VARYING-' +-OCTETS------+ | |
| | '-CHAR------' '-CODEUNITS32-' | |
| | .-(1M)-----------------------------. | |
| '-+-CLOB------------------------+--+----------------------------------+------------' |
| '-+-CHARACTER-+--LARGE OBJECT-' '-(integer-+---+-+-------------+-)-' |
| '-CHAR------' +-K-+ +-OCTETS------+ |
| +-M-+ '-CODEUNITS32-' |
| '-G-' |
| .-(1)------------------------. |
+-+-GRAPHIC--+----------------------------+------+-------------------------------------+
| | '-(integer-+-------------+-)-' | |
| | +-CODEUNITS16-+ | |
| | '-CODEUNITS32-' | |
| +-VARGRAPHIC--(integer-+-------------+-)-------+ |
| | +-CODEUNITS16-+ | |
| | '-CODEUNITS32-' | |
| | .-(1M)-----------------------------. | |
| '-DBCLOB--+----------------------------------+-' |
| '-(integer-+---+-+-------------+-)-' |
| +-K-+ +-CODEUNITS16-+ |
| +-M-+ '-CODEUNITS32-' |
| '-G-' |
| .-(1M)-------------. |
+-+-BLOB----------------+--+------------------+----------------------------------------+
| '-BINARY LARGE OBJECT-' '-(integer-+---+-)-' |
| +-K-+ |
| +-M-+ |
| '-G-' |
+-+-DATE-------------------------+-----------------------------------------------------+
| +-TIME-------------------------+ |
| | .-(--6--)-------. | |
| '-TIMESTAMP--+---------------+-' |
| '-(--integer--)-' |
| (3) |
+-XML----------------------------------------------------------------------------------+
+-BOOLEAN------------------------------------------------------------------------------+
'-CURSOR-------------------------------------------------------------------------------'
anchored-variable-data-type
.-DATA TYPE-. .-TO-.
|--ANCHOR--+-----------+--+----+--+-variable-name2------------------------+--|
+-table-name.column-name----------------+
| .-OF-. |
'-ROW--+----+--+-table-name-----------+-'
+-view-name------------+
'-cursor-variable-name-'
cursor-value-constructor
.-ASENSITIVE--.
|--+-------------+--CURSOR--+-------------------------------------+-->
'-INSENSITIVE-' | .-,-------------------------. |
| V | |
'-(----| parameter-declaration |-+--)-'
>--| holdability |--FOR--select-statement-----------------------|
parameter-declaration
|--parameter-name--| data-type2 |-------------------------------|
data-type2
|--+-| built-in-type |----------------+-------------------------|
+-| anchored-parameter-data-type |-+
'-distinct-type-name---------------'
anchored-parameter-data-type
.-DATA TYPE-. .-TO-.
|--ANCHOR--+-----------+--+----+--+-variable-name----------+----|
'-table-name.column-name-'
holdability
.-WITHOUT HOLD-.
|--+--------------+---------------------------------------------|
'-WITH HOLD----'
Notes:
- If data-type1 specifies a CURSOR built-in
type or cursor-type-name, only NULL or cursor-value-constructor can
be specified. Only DEFAULT NULL can be explicitly specified
for array-type-name or row-type-name.
- The
FOR BIT DATA clause can be specified in any order with the other column
constraints that follow. The FOR BIT DATA clause cannot be specified
with string units CODEUNITS32 (SQLSTATE 42613).
- For Version 10.1, you
can use the XML data type only as a parameter data type in a cursor value constructor. For Version 10.1 Fix
Pack 1 or later fix pack releases, you can also use the XML data type to create global
variables.
Description
- OR REPLACE
- Specifies to replace
the definition for the variable if one exists at the current server.
The existing definition is effectively dropped before the new definition
is replaced in the catalog, with the exception that privileges that
were granted on the variable are not affected. This option is ignored
if a definition for the variable does not exist at the current server.
This option can be specified only by the owner of the object.
- variable-name
- Names
the global variable. The name, including an implicit or explicit qualifier,
must not identify a global variable that already exists at the current
server (SQLSTATE 42710). If a qualifier is not specified, the current
schema is implicitly assigned. If the global variable name is explicitly
qualified with a schema name, the schema name must not begin with
the characters 'SYS' (SQLSTATE 42939).
- data-type1
- Specifies the data type of the global variable. A structured type
cannot be specified (SQLSTATE 42611).
- built-in-type
- Specifies a built-in data type. BOOLEAN and CURSOR cannot be specified for a table. For
Version 10.1, an
XML data type cannot be specified (SQLSTATE 42611). The XML data type support starts in Version 10.1 Fix
Pack 1. For a more complete description of each built-in data type, see "CREATE TABLE".
FOR
BIT DATA can be specified as part of character string data types.
- BOOLEAN
- For a Boolean.
- CURSOR
- For a reference to an underlying cursor.
- anchored-variable-data-type
- Identifies another object used to determine the data type of the
global variable. The data type of the anchor object has the same limitations
that apply to specifying the data type directly, or in the case of
a row, to creating a row type.
- ANCHOR DATA TYPE TO
- Indicates an anchored data type is used to specify the data type.
- variable-name2
- Identifies a global variable. The data type of the referenced
variable is used as the data type for the global variable.
- table-name.column-name
- Identifies a column name of an existing table or view. The data
type of the column is used as the data type for the global variable.
- ROW OF table-name or view-name
- Specifies that the global variable is a row of fields with names
and data types that are based on the column names and column data
types of the table identified by table-name or
the view identified by view-name. The data type of the global variable is an unnamed
row type.
- ROW OF cursor-variable-name
- Specifies a row of fields with names
and data types that are based on the field
names and field data types of the cursor variable identified by cursor-variable-name.
The specified cursor variable must be one of the following elements
(SQLSTATE 428HS):
- A global variable with a strongly typed cursor data type
- A global variable with a weakly typed cursor data type that was
created or declared with a CONSTANT clause specifying a select-statement
where all the result columns are named.
If the cursor type of the cursor variable is not strongly-typed
using a named row type, the data type of the global variable is an unnamed row type.
- array-type-name
- Specifies the name of a user-defined array type. If array-type-name is
specified without a schema name, the array type is resolved by searching
the schemas in the SQL path.
- cursor-type-name
- Specifies the name of a cursor type. If cursor-type-name is
specified without a schema name, the cursor type is resolved by searching
the schemas in the SQL path.
- distinct-type-name
- Specifies the name of a distinct type. The length, precision,
and scale of the declared variable are, respectively, the length,
precision, and scale of the source type of the distinct type. If distinct-type-name is
specified without a schema name, the distinct type is resolved by
searching the schemas in the SQL path.
- REF (type-name)
- Specifies a reference type. If a type name is specified without
a schema name, the type-name is resolved
by searching the schemas in the SQL path.
- row-type-name
- Specifies the name of a user-defined row type. The fields of the variable are the fields of the row type. If row-type-name is
specified without a schema name, the row type is resolved by searching
the schemas in the SQL path.
- DEFAULT or CONSTANT
- Specifies a value for the global
variable when it is first referenced. The DEFAULT or CONSTANT clause
value is determined on this first reference. If neither is specified,
the default for the global variable is the null value. Only DEFAULT
NULL can be explicitly specified if array-type-name or row-type-name is
specified.
- DEFAULT
- Defines the default for the global variable. The default value
must be assignment-compatible with the data type of the variable.
- CONSTANT
- Specifies that the global variable has a fixed value that cannot
be changed. A global variable that is defined using CONSTANT cannot
be used as the target of any assignment operation. The fixed value
must be assignment-compatible with the data type of the variable.
- NULL
- Specifies NULL as the default for the global variable. If row-type-name is
specified, the value for the global variable is a row where each field
has the null value.
- constant
- Specifies the value of a constant as the default for the global
variable. If data-type1 specifies a CURSOR
built-in type or cursor-type-name, constant cannot
be specified (SQLSTATE 42601).
- special-register
- Specifies the value of a special register as the default for the
global variable. If data-type1 specifies
a CURSOR built-in type or cursor-type-name, special-register cannot
be specified (SQLSTATE 42601).
- global-variable
- Specifies the value of a global variable as the default for the
global variable. If data-type1 specifies
a CURSOR built-in type or cursor-type-name, global-variable cannot
be specified (SQLSTATE 42601).
- cursor-value-constructor
- A cursor-value-constructor specifies
the select-statement that is associated
with the global variable. The assignment of a cursor-value-constructor to
a cursor variable defines the underlying cursor of that cursor variable.
- ASENSITIVE or INSENSITIVE
- Specifies whether the cursor is asensitive or insensitive to changes.
See "DECLARE CURSOR" for more information. The default is ASENSITIVE.
- ASENSITIVE
- Specifies that the cursor should be as sensitive as possible to
insert, update, or delete operations made to the rows underlying the
result table, depending on how the select-statement is
optimized. This option is the default.
- INSENSITIVE
- Specifies that the cursor does not have sensitivity to insert,
update, or delete operations that are made to the rows underlying
the result table. If INSENSITIVE is specified, the cursor is read-only
and the result table is materialized when the cursor is opened. As
a result, the size of the result table, the order of the rows, and
the values for each row do not change after the cursor is opened.
The SELECT statement cannot contain a FOR UPDATE clause, and the cursor
cannot be used for positioned updates or deletes.
- (parameter-declaration, ...)
- Specifies the input parameters of the cursor, including the name
and the data type of each parameter.
- parameter-name
- Names the parameter for use as an SQL variable within select-statement.
The name cannot be the same as any other parameter name for the cursor.
Names should also be chosen to avoid any column names that could be
used in select-statement, since column names
are resolved before parameter names.
- data-type2
- Specifies the data type of the cursor parameter used
within select-statement.
- built-in-type
- Specifies a built-in data type. For a more complete description
of each built-in data type, see "CREATE TABLE". The BOOLEAN and CURSOR
built-in types cannot be specified (SQLSTATE 429BB).
- anchored-parameter-data-type
- Identifies another object used to determine the data type of the cursor parameter. The data type of the anchor
object is bound by the same limitations that apply when specifying
the data type directly.
- ANCHOR DATA TYPE TO
- Indicates an anchored data type is used to specify the data type.
- variable-name
- Identifies a global variable. The data type of the referenced
variable is used as the data type for the cursor
parameter.
- table-name.column-name
- Identifies a column name of an existing table or view. The data
type of the column is used as the data type for the cursor
parameter.
- distinct-type-name
- Specifies the name of a distinct type. If distinct-type-name is
specified without a schema name, the distinct type is resolved by
searching the schemas in the SQL path.
- holdability
- Specifies whether the cursor is prevented from being closed as
a consequence of a commit operation. See "DECLARE CURSOR" for more
information. The default is WITHOUT HOLD.
- WITHOUT HOLD
- Does not prevent the cursor from being closed as a consequence
of a commit operation.
- WITH HOLD
- Maintains resources across multiple units of work. Prevents the
cursor from being closed as a consequence of a commit operation.
- select-statement
- Specifies the SELECT statement of the cursor. See "select-statement"
for more information.
- statement-name
- Specifies the prepared select-statement of
the cursor. See "PREPARE" for an explanation of prepared statements.
The target cursor variable must not have a data type that is a strongly-typed
user-defined cursor type (SQLSTATE 428HU).
- expression
- Specifies the value of an expression as the default for the global
variable. The expression can be any expression of the type described
in "Expressions". The expression must be assignment-compatible with
the data type of the variable. The maximum size of the expression
is 64K. The default expression must not modify SQL data (SQLSTATE
428FL) or perform external action (SQLSTATE 42845). If data-type1 specifies
a CURSOR built-in type or cursor-type-name, expression cannot
be specified (SQLSTATE 42601).
Rules
- Use of anchored data types: An anchored
data type cannot refer to the following objects (SQLSTATE 428HS):
a nickname, typed table, typed view, statistical view that is associated
with an expression-based index, declared temporary table, row definition
that is associated with a weakly typed cursor, object with a code
page or collation that is different from the database code page or
database collation.
Notes
- Session
global variables have a session scope. This means that, although they
are available to all sessions that are active on the database, their
value is private for each session.
- Contexts for array, Boolean, cursor, and
row global variables: Global variables that are array variables,
Boolean variables, or row variables can only be used in compound SQL
(compiled) statements or SET variable statements.
Global variables that are cursor variables can only be used in compound
SQL (compiled) statements.
- Create with errors: If
an object referenced in the default expression does not exist or is
marked invalid, or the definer temporarily doesn't have privileges
to access the object, and if the database configuration parameter auto_reval is
not set to DISABLED, then the variable will still be created successfully.
The variable will be marked invalid and will be revalidated the next
time it is invoked.
- Scope of global variable values: The
values for session global variables persist until they are updated
in the current session, the global variable is dropped or altered,
or the application session ends. The value is unaffected by COMMIT
or ROLLBACK statements. The default value for a global variable can
be not deterministic and dependent on when the default value is calculated
for the global variable (for example, a reference to the time of day,
or a reference to some data stored in a table).
A technique
commonly used, especially for performance, is for an application or
product to manage a set of connections and route transactions to an
arbitrary connection. In these situations, the non-default value of
a global variable or the not deterministic initial default value for
a global variable should only be relied on until the end of the transaction.
Examples of where this type of situation can occur include applications
that: use XA protocols, use connection pooling, use the connection
concentrator, and use HADR to achieve failover.
- Privileges to use a global variable: An attempt
to read from or to write to a global variable created by this statement
requires that the authorization ID attempting this action hold the
appropriate privilege on the global variable. The definer of the variable
is implicitly granted all privileges on the variable.
- Setting of the default value: A created global variable
is instantiated to its default value when it is first referenced within
its given scope. Note that if a global variable is referenced in a
statement, it is instantiated independently of the control flow for
that statement.
- Using a newly created session global variable: If
a global variable is created within a session, it cannot be used by
other sessions until the unit of work has committed. However, the
new global variable can be used within the session that created the
variable before the unit of work commits.
Examples
- Example 1: Create a session
global variable to indicate what printer to use for the session.
CREATE VARIABLE MYSCHEMA.MYJOB_PRINTER VARCHAR(30)
DEFAULT 'Default printer'
- Example 2: Create a session
global variable to indicate the department where an employee works.
CREATE VARIABLE SCHEMA1.GV_DEPTNO INTEGER
DEFAULT ((SELECT DEPTNO FROM HR.EMPLOYEES
WHERE EMPUSER = SESSION_USER))
- Example 3: Create a session
global variable to indicate the security level of the current user.
CREATE VARIABLE SCHEMA2.GV_SECURITY_LEVEL INTEGER
DEFAULT (GET_SECURITY_LEVEL (SESSION_USER))
- Example 4: Create a session
global variable as a cursor on the STAFF table that returns the names
of each employee for the specified job type. Order the results by
the department number.
CREATE VARIABLE STAFFJOBS CURSOR
CONSTANT (CURSOR (WHICHJOB CHAR(5))
FOR SELECT NAME, DEPT FROM STAFF WHERE JOB = WHICHJOB
ORDER BY DEPT)
- Example 5: Create a global variable
of the XML data type:
CREATE VARIABLE MYSCHEMA.CUSTOMER_HISTORY_VAR XML