CREATE VARIABLE statement

Creates a connection- or database-scope variable.

Syntax

Create a connection-scope variable

CREATE [ OR REPLACE ] VARIABLE identifier data-type [ { = | DEFAULT } initial-value ]

initial-value : expression

Create a database-scope variable

CREATE [ OR REPLACE ] DATABASE VARIABLE [ IF NOT EXISTS ] [ owner.]identifier data-type [ { = | DEFAULT } initial-value ]

initial-value : expression

Parameters

OR REPLACE clause
Specifying the OR REPLACE clause drops the named variable if it already exists and recreates it with the new definition. OR REPLACE only replaces the value of the variable if the data type of the current and new value are the same.

Do not use this clause with the IF NOT EXISTS clause.
owner
This parameter applies only to database-scope variables. Specify a valid user ID or role, or PUBLIC to set ownership of the variable. If set to a user, only that user can use the database variable. If set to a role, users who have that role are able to use the database variable. If set to PUBLIC, all users are able to use the variable.

If owner is not specified, it is set to the user executing the CREATE VARIABLE statement.
identifier
A valid identifier for the variable.
data-type
The data type for the variable. Set the data type explicitly, or use the %TYPE or %ROWTYPE attribute to set the data type to the data type of another object in the database. Use %TYPE to set it to the data type of a variable or a column in a table or view. Use %ROWTYPE to set the data type to a composite data type derived from a row in a cursor, table, or view.

%ROWTYPE and TABLE REF is not supported as data types for database-scope variables.
IF NOT EXISTS clause
Specify this clause to allow the statement to complete without returning an error if a database-scope variable with the same name already exists. This parameter is only for use when creating owned database-scope variables.

Do not use this clause with the OR REPLACE clause.
{ = | DEFAULT } clause
The default value for the variable. For database-scope variables, this is also the initial value after the database is restarted.

initial-value must match the data type defined by data-type. If you do not specify an initial-value, then the variable contains the NULL value until a different value is assigned, for example by using a SET statement, a SELECT ... INTO statement, or in an UPDATE statement. If initial-value is set by using an expression, then the expression is evaluated at creation time and the resulting constant is stored (not the expression).

Remarks

Variables are useful for sharing values between procedures. They are also useful for creating large text or binary objects for INSERT or UPDATE statements from Embedded SQL programs. Database-scope variables are useful for sharing values across connections and database restarts.

Use the CREATE VARIABLE syntax to create a connection-scope variable that is available in the context of the connection.

Use the CREATE DATABASE VARIABLE syntax to create a database-scope variable that can be used by other users and other connections.

Use the OR REPLACE clause as an alternative to the VAREXISTS function in SQL scripts.

If you specify a variable name for initial-value, then the variable must already be initialized in the database.

Privileges

Connection-scope variables: No privileges are required to create or replace a connection-scope variable.

Database-scope variables: To create or replace a self-owned database-scope variable, you must have the CREATE DATABASE VARIABLE or MANAGE ANY DATABASE VARIABLE system privilege. To create or replace a database-scope variable owned by another user or by PUBLIC, you must have the MANAGE ANY DATABASE VARIABLE system privilege.

Side effects

Connection-scope variables: There are no side effects associated with creating a connection-scope variable.

Database-scope variables: Creating and replacing a database-scope variable causes an automatic commit.

Standards

ANSI/ISO SQL Standard
Not in the standard.

Example

This example creates (or updates) a database-scope variable called site_name of type VARCHAR(50).

CREATE OR REPLACE DATABASE VARIABLE @site_name VARCHAR(50);

This example creates (or updates) a database-scope variable owned by PUBLIC called database_name of type CHAR(66) and sets it to the special value CURRENT DATABASE.

CREATE OR REPLACE DATABASE VARIABLE PUBLIC.@database_name CHAR(66) DEFAULT CURRENT DATABASE;

This example creates a connection-scope variable named first_name, of data type VARCHAR(50).

CREATE VARIABLE first_name VARCHAR(50);

This example creates a connection-scope variable named birthday, of data type DATE.

CREATE VARIABLE birthday DATE;

This example creates a connection-scope variable named v1 as an INT with the initial setting of 5.

CREATE VARIABLE v1 INT = 5;

This example creates a connection-scope variable named v1 and sets its value to 10, regardless of whether the v1 variable already exists.

CREATE OR REPLACE VARIABLE v1 INT = 10;

This example creates a connection-scope variable, ProductID, and uses the %TYPE attribute to set its data type to the data type of the ID column in the Products table:

CREATE VARIABLE ProductID Products.ID%TYPE;

This example creates a connection-scope variable, ItemsForSale, and uses the %ROWTYPE attribute to set its data type to a composite data type comprised of the columns defined for the Products table. It then creates another variable, ItemID, and declares its type to be the data type of the ID column in the ItemsForSale variable:

CREATE VARIABLE ItemsForSale Products%ROWTYPE;
CREATE VARIABLE ItemID ItemsForSale.ID%TYPE;