Declares variables that you can use in interactive and dynamic
SQL for invoking stored procedures and for testing procedures in
modules or embedded SQL programs. For information on declaring
variables in compound statements, see the Compound_Statement HELP
topic.
1 – Environment
You can use the DECLARE statement:
o In interactive SQL
o In dynamic SQL as a statement to be dynamically executed
2 – Format
(B)0[m[1;4mDECLARE[m[1m qwq> :<variable-name> wqqwqqqqqqqqqqqqqqqwqk[m
[1m mqqqqqqqq , <qqqqqqqqj tqq> CONSTANT qqu[m [1mx[m
[1mmqq> UPDATABLE[m [1mqj[m [1mx[m
[1mlqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj[m
[1mmqwq> data-type qqqqwqwqqqqqqqqqqqqqqqqqqqqwqqqqqqq>[m
[1m mq> <domain-name> j mq> default-clause qj [m
[1m [m
(B)0[m[1mdefault-clause = [m
[1m [m
[1mqqwq> [1;4mDEFAULT[m[1m qqwqqwqq> date-time-literal qwqqqqq>[m
[1m mq> = qqqqqqj tqq> interval-literal qu [m
[1m tqq> numeric-literal qqu [m
[1m [m [1mtqq>[m [1mstring-literal[m [1mqqqu[m
[1m [m [1mmqq>[m [1m: <variable-name>[m [1mqj[m
3 – Arguments
3.1 – CONSTANT
Syntax options:
CONSTANT | UPDATABLE
CONSTANT changes the variable into a declared constant that
cannot be updated. If you specify CONSTANT, you must also have
specified the DEFAULT clause to ensure the variable has a value.
CONSTANT also indicates that the variable cannot be used as
the target of an assignment or be passed as an expression to a
procedure's INOUT or OUT parameter.
UPDATABLE is the default and allows the variable to be modified.
An update of a variable can occur due to a SET assignment, an
INTO assignment (as part of an INSERT ... RETURNING, UPDATE ...
RETURNING, or SELECT statement), or as a procedure's OUT or INOUT
parameter on a CALL statement.
3.2 – data-type
Specifies the data type assigned to the variable. See the Data_
Types HELP topic for more information on data types.
3.3 – default-clause
You can only use references to simple literal values and other
declared variables as a default.
3.4 – domain-name
Specifies the domain name assigned to the variable. The domain
supplies the data type and, for interactive SQL, the edit string
of the variable.
See the User_Supplied_Names HELP topic for more information on
domain names.
3.5 – variable-name
Specifies the local variable.
4 – Example
Example 1: Declaring variables in interactive SQL
SQL> DECLARE :X INTEGER;
SQL> DECLARE :Y CHAR(10);
SQL>
SQL> BEGIN
cont> SET :X = 100;
cont> SET :Y = 'Active';
cont> END;
SQL> PRINT :X, :Y ;
X Y
100 Active
SQL> SHOW VARIABLES;
X INTEGER
Y CHAR(10)
Example 2: Using the values of SQLSTATE in an interactive SQL
script
The following simple script uses the named SQLSTATE variable with
the SIGNAL statement to make the script easier to read.
@SYS$LIBRARY:SQLSTATE
set verify;
begin
signal :SQLSTATE_DATA_ASSIGN ('Error in assignment');
end;
When executed the output appears as shown below.
SQL> begin
cont> signal :SQLSTATE_DATA_ASSIGN ('Error in assignment');
cont> end;
%RDB-E-SIGNAL_SQLSTATE, routine "(unnamed)" signaled SQLSTATE "22005"
-RDB-I-TEXT, Error in assignment
SQL>