DECLARE VARIABLE

---

Declares a variable.

where row-specification is:

Usage

Procedural.

Description

The value for data-type can be any data type supported by Mimer SQL, see Data Types in SQL Statements.

More than one variable of the same type can be declared in a single declaration.

It is possible to declare a variable as a record, by using the row specification.

The fields in a record may themselves be records, to an unlimited depth. To reference a field in a record the notation recordVariable.fieldName is used.

A record can be declared as being the same as a table or a part of table with the AS clause. This means that the fields in the record will have the same name and type as the columns in the table. If the column list is omitted, all columns are used.

The optional DEFAULT clause may be used to specify an initial value for the variable(s). A value of NULL is permitted as the value for the DEFAULT clause.

If a ROW data type definition has been specified for data-type, a row value expression can be specified for expression in the DEFAULT clause.

If the DEFAULT clause is not specified, the variable(s) will be set to NULL initially.

In the case of a variable declared with the ROW data type, each field in the variable is set to NULL initially if a DEFAULT clause is not specified.

Restrictions

A domain cannot be used to specify the data type in a variable declaration.

The name of a variable cannot be the same as any of the routine parameter names.

A function with MODIFIES SQL DATA specified for its access clause cannot be used as expression in the DEFAULT clause.

Notes

It is possible to declare a variable with the same name as that of a column in a table. In such a situation, an unqualified name will always resolve to the table column name and not the variable. We recommend that a suitable naming convention be adhered to that distinguishes between the two.

Examples

 DECLARE orderNumber INTEGER DEFAULT 0;
 
 DECLARE firstName,lastName VARCHAR(30);
 
 DECLARE purchase row(customerId integer, orderNumber integer,
                       purchaseDate date, productId integer, quantity integer)
    DEFAULT (0,0,current_date,0,0);
 
 DECLARE book ROW AS (mimer_store_book.details);
 
 DECLARE bookTitle ROW AS(mimer_store_book.details(isbn,title));
 

For more information, see Mimer SQL Programmer's Manual, Declaring Variables.

Standard Compliance

Standard	Compliance	Comments
SQL-2011	Features outside core	Feature P002, "Computational completeness".
	Mimer SQL extension	The use of AS-clause is a Mimer SQL extension.

Mimer Information Technology AB Voice: +46 18 780 92 00 Fax: +46 18 780 92 40 info@mimer.se