FirebirdSQL logo
 Data Manipulation (DML) StatementsBuilt-in Scalar Functions 

Examples of various ways to declare local variables

CREATE OR ALTER PROCEDURE SOME_PROC
AS
  -- Declaring a variable of the INT type
  DECLARE I INT;
  -- Declaring a variable of the INT type that does not allow NULL
  DECLARE VARIABLE J INT NOT NULL;
  -- Declaring a variable of the INT type with the default value of 0
  DECLARE VARIABLE K INT DEFAULT 0;
  -- Declaring a variable of the INT type with the default value of 1
  DECLARE VARIABLE L INT = 1;
  -- Declaring a variable based on the COUNTRYNAME domain
  DECLARE FARM_COUNTRY COUNTRYNAME;
  -- Declaring a variable of the type equal to the COUNTRYNAME domain
  DECLARE FROM_COUNTRY TYPE OF COUNTRYNAME;
  -- Declaring a variable with the type of the CAPITAL column in the COUNTRY table
  DECLARE CAPITAL TYPE OF COLUMN COUNTRY.CAPITAL;
BEGIN
  /* PSQL statements */
END
See also

Data Types and Subtypes, Custom Data Types — Domains, CREATE DOMAIN

DECLARE .. CURSOR

Declares a named cursor

Syntax
DECLARE [VARIABLE] cursor_name
  [[NO] SCROLL] CURSOR
  FOR (<select>);
Table 1. DECLARE …​ CURSOR Statement Parameters
Argument Description

cursor_name

Cursor name

select

SELECT statement

The DECLARE …​ CURSOR …​ FOR statement binds a named cursor to the result set obtained by the SELECT statement specified in the FOR clause.In the body code, the cursor can be opened, used to iterate row-by-row through the result set, and closed.While the cursor is open, the code can perform positioned updates and deletes using the WHERE CURRENT OF in the UPDATE or DELETE statement.

Note

Syntactically, the DECLARE …​ CURSOR statement is a special case of [fblangref50-psql-declare-variable].

Forward-Only and Scrollable Cursors

The cursor can be forward-only (unidirectional) or scrollable.The optional clause SCROLL makes the cursor scrollable, the NO SCROLL clause, forward-only.By default, cursors are forward-only.

Forward-only cursors can — as the name implies — only move forward in the dataset.Forward-only cursors only support the FETCH [NEXT FROM] statement, other fetch options raise an error.Scrollable cursors allow you to move not only forward in the dataset, but also back, as well as N positions relative to the current position.

Warning

Scrollable cursors are materialized as a temporary dataset, as such, they consume additional memory or disk space, so use them only when you really need them.