Valid in: DBProc
The Declare statement describes a list of locl variables for use in a database procedure.
This statement is used only in a database procedure definition to declare a list of local variables for use in the procedure. If this statement is to be used, place it before the begin clause of the database procedure definition.
Nullable variables are initialized to null. Non-nullable variables are initialized to the default value according to data type: character data types are initialized to blank, and numeric data types are initialized to zero. Any non-nullable variables declared without an explicit default value are initialized to the default value.
The following table lists the effects of the null and default specifications on the default value of a column.
Nullability Option |
Default Option |
Results |
---|---|---|
with null |
(none specified) |
The variable can be null; default value is null. |
not null |
(none specified) |
The default is 0 or blank (according to data type). |
(none specified) |
with default |
Not valid without a null clause. |
(none specified) |
not default |
Not valid without a null clause. |
with null |
with default |
Not valid. |
with null |
not default |
Not valid. |
not null |
with default |
The variable defaults to 0 or blank, according to its data type. |
not null |
not default |
The variable defaults to 0 or blank, according to its data type. |
The Declare statement has the following format:
DECLARE var_name {, var_name} [=] var_type
[NOTE NULL [WITH | NOT DEFAULT] | WITH NULL];
{var_name {, var_name} [=] var_type
[NOT NULL [WITH | NOT DEFAULT] | WITH NULL];}
Specifies the name of the local variable. A variable name must be unique within the procedure; it cannot match the name of any other procedure variable or parameter.
Is the data type of the variable. A local variable can be any data type except a system_maintained table_key or object_key.
All users are permitted to use this statement.
The following example demonstrates some declarations and uses of local variables:
create procedure variables (vmny money not null) as
declare
vi4 integer not null;
vf8 float;
vc11 char(11) not null;
vdt date;
begin
vi4 = 1234;
vf8 = null;
vc11 = '26-jun-1957';
vdt = date(:vc11);
vc11 = :vmny;--data type conversion error
vmny = :vf8;--null to non-null conversion
error
return :vi4;
end;