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

initial-value :
special-value
| string
| [ - ] number
| ( constant-expression )
| built-in-function ( constant-expression )
| NULL

special-value :
CURRENT { 
DATABASE
   | DATE 
   | PUBLISHER 
   | TIME 
   | TIMESTAMP
   | USER 
   | UTC TIMESTAMP }
| USER

The CREATE VARIABLE statement creates a new variable of the specified data type. If you specify initial-value, the variable is set to that value. If you do not specify an initial-value, the variable contains the NULL value until a different value is assigned by the SET statement.

Specifying the OR REPLACE clause drops the named variable if it already exists and replaces its definition. You can use the OR REPLACE clause as an alternative to the VAREXISTS function in SQL scripts. See VAREXISTS function [Miscellaneous].

A variable can be used in a SQL expression anywhere a column name is allowed. Name resolution is performed as follows:

Variables belong to the current connection, and persist until you disconnect from the database or when you use the DROP VARIABLE statement. Variables are not visible to other connections. Variables are not affected by COMMIT or ROLLBACK statements.

Variables are useful for creating large text or binary objects for INSERT or UPDATE statements from embedded SQL programs.

Local variables in procedures and triggers are declared within a compound statement. See Using compound statements.

If you specify initial-value, the data type must match the type defined by data-type.

None.

None.

• BEGIN statement
• SQL data types
• DROP VARIABLE statement
• SET statement
• VAREXISTS function [Miscellaneous]
• Special values

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

CREATE VARIABLE first_name VARCHAR(50);

This example creates a variable named 'birthday', of data type DATE.

CREATE VARIABLE birthday DATE;

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

CREATE VARIABLE v1 INT = 5;

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

CREATE OR REPLACE VARIABLE v1 INT = 10;