SQL supports user defined variables within bodies of procedures, functions and triggers. They are distinguishable from table and column names and can be used anywhere a literal constant is allowed.
Identifier resolution follows a string inner-outward scheme. A variable name in a statement block is interpreted in the context of a table, the block, the schema.
declare_statement:
DECLARE variable_list
| DECLARE table_def
variable_list:
ident [ ',' ... ] data_type
| variable_list ',' ident [ ',' ... ] data_type
set_statement:
SET ident '=' simple_atom
Variables can not be declared as global variables.
DECLARE ts1 timestamp;
DECLARE high integer;
DECLARE n varchar(256);
SET ts1 = now();
SET high = 4000;
SET n = 'monetdb';
SELECT ts1;
SELECT count(*) count_ from tables where id > high;
+--------+
| count_ |
+========+
| 2 |
+--------+
Declared variables are not persistent. They will be lost after closing the connection or session.
The list of available session variables can be queried from table returning function sys.var().
sql>select * from sys.var() order by name;
+--------+--------------------+--------------+--------------+
| schema | name | type | value |
+========+====================+==============+==============+
| sys | current_role | varchar | monetdb |
| sys | current_schema | varchar | sys |
| sys | current_timezone | sec_interval | 7200000 |
| sys | current_user | varchar | monetdb |
| sys | debug | int | 0 |
| sys | division_min_scale | int | 3 |
| sys | last_id | bigint | 0 |
| sys | optimizer | varchar | default_pipe |
| sys | rowcnt | bigint | 10 |
| sys | sql_optimizer | int | 2147483647 |
+--------+--------------------+--------------+--------------+
They can be changed in a session via Set statement.