Declares one or more variables, cursors, or statements.

Syntax

---

DECLARE <VariableDefinition>|<CursorDefinition>|
        <StatementDefinition>

<VariableDefinition> =

<VariableName> [,<VariableName>] <DataType>
[ARRAY [<MaximumCardinality>]]
[DEFAULT <DefaultExpression>]

<DataType> =

CHARACTER|CHAR [(<Length>]) [<CollationName>]
CHARACTER VARYING|VARCHAR [(<Length>]) [<CollationName>]
GUID
BYTE [(<LengthInBytes>])
BYTE VARYING|VARBYTE [(<LengthInBytes>])
BINARY LARGE OBJECT|BLOB
CHARACTER LARGE OBJECT|CLOB [<CollationName>]
BOOLEAN|BOOL
SMALLINT
INTEGER|INT
BIGINT
FLOAT [(<Precision>,<Scale>)]
DECIMAL|NUMERIC [(<Precision>,<Scale>)]
DATE
TIME
TIMESTAMP
INTERVAL YEAR [TO MONTH]
INTERVAL MONTH
INTERVAL DAY [TO HOUR|MINUTE|SECOND|MSECOND]
INTERVAL HOUR [TO MINUTE|SECOND|MSECOND]
INTERVAL MINUTE [TO SECOND|MSECOND]
INTERVAL SECOND [TO MSECOND]
INTERVAL MSECOND

<CursorDefinition> =

<CursorName> [SENSITIVE|INSENSITIVE|ASENSITIVE] CURSOR
[WITH RETURN|WITHOUT RETURN] FOR <StatementName>

<StatementDefinition> = 

<StatementName> STATEMENT

Usage

---

Use this statement to declare one or more variables, cursors, or statements to be used later in the function or procedure.

Variables

Variables can be declared as any valid data type, and the DEFAULT clause can be used to specify an initial value for the variable. To declare an array, use the ARRAY clause along with the maximum cardinality specifier after the data type. The maximum cardinality sets the limit on the size of the array, and any attempt to reference any index (1-based) greater than the maximum cardinality of the array will result in an exception.


Cursors

Cursors can be declared as sensitive, insensitive, or asensitive.


It is important to recognize that the cursor type in a cursor declaration is simply a request for a certain type of cursor, except in the case of the ASENSITIVE cursor declaration which is equivalent to declaring that the type of cursor is irrelevant. ElevateDB may or may not be able to create a declared cursor type. To determine the actual type of cursor that was created, use the SENSITIVE function on any opened cursor. See the Result Set Cursor Sensitivity topic for more information on what rules determine whether a cursor can be sensitive or not.

Cursor declarations also may specify whether the cursor should be returned to the calling program. Returnability only applies to procedures and does not apply to functions.


The statement name given in a cursor associates a statement (see below) with the cursor for use in the preparation of the SQL SELECT statement used to output the result set on which the cursor will be operating.

Statements

Statements are simply containers for executing dynamic SQL statements in a procedure or function. To actually use a statement you must first bind SQL statement text to the statement using the PREPARE statement. Then the statement may be executed any number of times using the EXECUTE statement.

Examples

---

-- This procedure changes all
-- rows with a State column value of 'FL'
-- to 'NY'and returns a sensitive cursor
-- on the Customers table

CREATE PROCEDURE UpdateState()
BEGIN
   DECLARE CustCursor CURSOR WITH RETURN FOR Stmt;
   DECLARE State CHAR(2) DEFAULT '';

   PREPARE Stmt FROM 'SELECT * FROM Customer';

   OPEN CustCursor;

   FETCH FIRST FROM CustCursor ('State') INTO State;

   WHILE NOT EOF(CustCursor) DO
      IF (State='FL') THEN
         UPDATE CustCursor SET 'State'='NY';
      END IF;
      FETCH NEXT FROM CustCursor ('State') INTO State;
   END WHILE;
END

-- This procedure simply returns an insensitive
-- cursor on the States table

CREATE PROCEDURE States()
BEGIN
   DECLARE Test INSENSITIVE CURSOR WITH RETURN FOR stmt;

   PREPARE stmt FROM 'SELECT * FROM States';

   OPEN Test;
END

-- This procedure uses a statement to
-- execute a CREATE TABLE statement

CREATE PROCEDURE CreateTestTable()
BEGIN
   DECLARE stmt STATEMENT;

   PREPARE stmt FROM 'CREATE TEMPORARY TABLE "TestTable"
                     (
                     "FirstColumn" INTEGER,
                     "SecondColumn" VARCHAR(30),
                     "ThirdColumn" CLOB,
                     PRIMARY KEY ("FirstColumn")
                     )

                     DESCRIPTION ''Test Table''';

   EXECUTE stmt;
END

-- This script loops through the Customer table and
-- populates an array with the CustNo column value
-- for each row

SCRIPT
BEGIN
   DECLARE Done BOOLEAN DEFAULT False;
   DECLARE TotalRows INTEGER DEFAULT 0;
   DECLARE CustCursor CURSOR FOR CustStmt;
   DECLARE CustArray INTEGER ARRAY [56];

   PREPARE CustStmt FROM 'SELECT CustNo,
                          Company
                          FROM Customer';

   OPEN CustCursor;

   WHILE (NOT EOF(CustCursor)) DO
      SET TotalRows=TotalRows+1;
      FETCH NEXT FROM CustCursor INTO CustArray[TotalRows];
      SET PROGRESS TO TRUNC((TotalRows/ROWCOUNT(CustCursor))*100);
   END WHILE;

   CLOSE CustCursor;
END

SQL 2003 Standard Deviations

---

This statement deviates from the SQL 2003 standard in the following ways: