NexusDB Guide > SQL Reference > SQL Statements > Data Statements

NexusDB V3 Manual

FETCH statement

NexusDB Manual V3 > SQL Reference > SQL Statements > Data Statements

Syntax

<fetch statement> ::=

FETCH [ [ <fetch orientation> ] FROM ] cursor-name INTO <fetch target list>

<fetch orientation> ::= NEXT | PRIOR | FIRST | LAST | { ABSOLUTE | RELATIVE} <offset>

<fetch target list> ::= variable-name [ { , variable-name }... ]

Usage

The named cursor is positioned on the specified row in the result set defined by the cursor declaration. This row becomes the current row for the cursor.

Notes

§	SQL cursor names shall conform to the rules for identifiers in NexusDB SQL

§	A cursor must be opened with a OPEN statement before using FETCH.

§	The orientation can be specified in one of the following ways:

NEXT - Position the cursor on the row next to the current row

PRIOR - Position the cursor on the row prior to the current row

FIRST - Position the cursor on the first row in the result set

LAST - Position the cursor on the last row in the result set

ABSOLUTE - Position the cursor on the row with a specified absolute row number in the result set. Row zero does not exist and will return NOT FOUND. Negative numbers count from the end of the result set (i.e. -1 = LAST)

RELATIVE - Position the cursor on the row specified with a row number relative to the current row in the result set. Zero is the current row, positive numbers count toward the end of the result set and negative numbers toward the beginning.

§	If there is no next row in the set of rows, the cursor is placed `after the last row', no new values are assigned to the variables and the system variable @@FETCH_STATUS is returned set to a value <> 0

§	If there is a next row values from the current row are assigned to target variables as listed in the INTO clause and the system variable @@FETCH_STATUS is returned set to a value = 0

§	If the cursor that is used by the FETCH statement is not declared with an ORDER BY clause, the sort order for the result set is undefined. To assure a correct sort order, ORDER BY must be used.

Examples

1) The following example shows a fetch loop:

DECLARE acursor CURSOR FOR SELECT f1, f2 from table1;

OPEN acursor;

FETCH NEXT FROM acursor INTO myvar1, myvar2;

WHILE @@FETCH_STATUS = 0 DO

INSERT INTO table2 values (myvar1, myvar2);

FETCH NEXT FROM acursor INTO myvar1, myvar2;

END WHILE;

CLOSE acursor;

Conformance

SQL:2003 standard

Feature F431, "Read-only scrollable cursors"

nexus_logo