DECLARE VARIABLE

Each scripting code block or subroutine can have a variable declaration clause.

Syntax

<declare_variable> ::=

  DECLARE [VARIABLE] varname datatype [NOT NULL] [{DEFAULT | =} <initvalue>];

<initvalue> ::= <literal> | <context_var>

varname is the name of the variable and initvalue its intialization value, is no such vaue is supplied, the variable is initialize with NULL.

You can use a datatype from the list available here including length or precision and scale specifications if required.

An optional NOT NULL constaint can be added, so that whenever NULL is assigned to the variable, an exception is raised. If you define a variable as being NOT NULL, make sure you initialize it with a value.

The DECLARE CURSOR is a special case of this clause.

Example

declare myint integer;

declare variable user_name varchar(200) = '';

Assigning Values

You can assign a value to variable in multiple ways.

Syntax

<var_assignment> ::=

  varname = <value_expression>

Write the variable name, followed by the assignment operator " = " followed by a value expression, either a literal value or a function call.

You can also assign values to variables using SELECT ... INTO, or assign output parameters from a local procedure.