DECLARE CURSOR
NAME
DECLARE CURSOR: declare a cursor (in a program)
SYNOPSIS
<declare cursor> ::=
DECLARE <cursor name> [ INSENSITIVE ] [ SCROLL ] CURSOR
FOR <cursor specification>
<cursor specification> ::=
<query expression> [ <updatability clause> ]
<updatability clause> ::= FOR { READ ONLY | UPDATE [ OF <column name list> ] }
<dynamic declare cursor> ::=
DECLARE <cursor name> [ INSENSITIVE ] [ SCROLL ] CURSOR
FOR <statement name>
The <query expression>, with its ORDER BY, OFFSET and FETCH FIRST, is
that of SELECT.
DESCRIPTION
A cursor reads a query's rows one at a time, into a program's parameters: OPEN runs its query, FETCH moves it to a row and reads the row, CLOSE ends it. Through a cursor on a row, an UPDATE or DELETE ... WHERE CURRENT OF changes that row.
Cursors run only in a program that embeds turborel. A cursor belongs to
the program's module, not to a statement it runs, so it is declared
through the Rust API, Session::declare_cursor, not by
Session::execute; the statements that use it run by
Session::execute, with the program's parameters. Direct SQL, and so
the client, declares no cursor: DECLARE CURSOR given there is refused
(SQLCODE -102), and OPEN, FETCH, CLOSE and a positioned UPDATE or
DELETE find no cursor of the name (SQLCODE -103). A query given there
is read by a cursor of its own all the same, unnamed: its rows are sent
a batch at a time as they are fetched, never held whole first
(The Embedded Library,
The Client Library).
The cursor specification is kept as written, and bound when the cursor is opened: a name in it that is a parameter's is the parameter, as of any statement the program runs, so parameters are best named apart from columns.
A dynamic cursor. Declared FOR a statement name, the cursor's specification is the query PREPARE prepares by that name, read as the cursor is opened: OPEN gives its dynamic parameters their values, USING parameters or an SQL descriptor area, and FETCH puts its rows into targets or into a descriptor area. The name may be prepared before the cursor is opened, and again, of a query, once it is closed. ALLOCATE CURSOR allocates a cursor of a query prepared by an extended name, as a statement.
SCROLL lets FETCH move the cursor any way; without it, FETCH moves to the next row alone. INSENSITIVE is taken: a cursor sees the rows as its transaction's snapshot has them, whatever the cursor is.
Updatability. FOR UPDATE makes the cursor updatable, on the columns named or every column; FOR READ ONLY, read-only. With neither, a cursor is read-only if it is INSENSITIVE or SCROLL, has ORDER BY, OFFSET or FETCH FIRST, reads a read-only table, or has a column that is no column of its table, as an SQL parameter's; else updatable on every column. A query of OFFSET or FETCH FIRST is not updatable, so FOR UPDATE of one is refused; one of ORDER BY alone is.
Positioned UPDATE and DELETE.
<update statement: positioned> ::=
UPDATE <table name> SET <set clause> [ { , <set clause> }... ]
WHERE CURRENT OF <cursor name>
<delete statement: positioned> ::=
DELETE FROM <table name> WHERE CURRENT OF <cursor name>
They change the row the cursor is on, of the table it reads. After a
DELETE, the cursor is on no row until the next FETCH. Prepared, either
may name no table, DELETE WHERE CURRENT OF U: it is of the table the
cursor reads, a cursor declared or allocated, which is found as the
statement is prepared.
RULES
- No cursor of the module has the name.
- An INSENSITIVE cursor is not FOR UPDATE.
- As it is opened, the specification follows the rules of a query; FOR UPDATE is of an updatable query; the columns of FOR UPDATE OF are the table's.
- A positioned UPDATE or DELETE names the cursor's table, and the cursor is updatable, open, and on a row. An UPDATE sets only columns of its FOR UPDATE, and none a sort key of its ORDER BY reads.
PRIVILEGES
As it is opened, SELECT on the tables the query reads. A positioned UPDATE or DELETE needs UPDATE on the columns set, or DELETE.
ERRORS
| SQLSTATE | SQLCODE | When |
|---|---|---|
| 2A000 | -102 | Declared by Session::execute, as a statement |
| 2A000 | -103 | A rule above is broken |
| 24000 | -501 | A positioned UPDATE or DELETE of a cursor not open, or on no row |
| 0A000 | -900 | An updatable cursor ordered by a window function is opened |
EXAMPLES
In a program, of a table STAFF (ID INTEGER PRIMARY KEY, NAME VARCHAR(30), SALARY DECIMAL(9,2)) of rows 1, Ada, 7200; 2, Grace, 8100; and 3, NULL, 6900:
let s = db.session("ANN");
let mut p = Params::new()
.declare("MY_ID", DataType::Exact { precision: 9, scale: 0 }, Value::Null)
.declare("MY_NAME", DataType::varying(30), Value::Null)
.indicator("NAME_IND", 0)
.declare("FLOOR", DataType::Exact { precision: 9, scale: 0 }, Value::Exact(Decimal::new(7000, 0)));
s.declare_cursor("DECLARE U CURSOR FOR SELECT ID, NAME FROM STAFF WHERE SALARY > FLOOR FOR UPDATE OF SALARY")?;
s.execute("OPEN U", &mut p)?;
s.execute("FETCH U INTO MY_ID, MY_NAME INDICATOR NAME_IND", &mut p)?; // 1, Ada
s.execute("UPDATE STAFF SET SALARY = SALARY + 100 WHERE CURRENT OF U", &mut p)?;
s.execute("UPDATE STAFF SET NAME = 'x' WHERE CURRENT OF U", &mut p); // refused
s.execute("FETCH U INTO MY_ID, MY_NAME INDICATOR NAME_IND", &mut p)?; // 2, Grace
s.execute("DELETE FROM STAFF WHERE CURRENT OF U", &mut p)?;
s.execute("DELETE FROM STAFF WHERE CURRENT OF U", &mut p); // refused
s.execute("COMMIT", &mut p)?;
The refusals, and declaring a name twice:
UPDATE STAFF SET NAME = 'x' WHERE CURRENT OF U -103 (2A000): cursor U is not FOR UPDATE of NAME [ISO/IEC 9075:1992 §13.9 SR8]
DELETE FROM STAFF WHERE CURRENT OF U -501 (24000): cursor U is not positioned on a row [X3.135-1986 §8.4 GR1]
DECLARE U CURSOR FOR SELECT ID FROM STAFF -103 (2A000): cursor U is already declared [X3.135-1986 §8.3 SR1]
STAFF is left of 1, 7300.00 and 3, 6900.00.
A dynamic cursor, of the parameters of PREPARE, FLOOR holding 7000, and of a descriptor area ROW and N, exact:
s.declare_cursor("DECLARE D SCROLL CURSOR FOR NAME_LIST")?;
s.execute("OPEN D USING FLOOR", &mut p); // refused: not prepared
p.set("TEXT", Value::Char("SELECT ID, NAME FROM STAFF WHERE SALARY < ? ORDER BY ID".into()));
s.execute("PREPARE NAME_LIST FROM TEXT", &mut p)?;
s.execute("OPEN D USING FLOOR", &mut p)?;
s.execute("FETCH LAST FROM D INTO MY_ID, MY_NAME INDICATOR NAME_IND", &mut p)?; // 3, NAME_IND = -1
s.execute("ALLOCATE DESCRIPTOR 'ROW'", &mut p)?;
s.execute("DESCRIBE NAME_LIST USING SQL DESCRIPTOR 'ROW'", &mut p)?;
s.execute("SET DESCRIPTOR 'ROW' VALUE 1 TYPE = 2, PRECISION = 9", &mut p)?;
s.execute("FETCH FIRST FROM D INTO SQL DESCRIPTOR 'ROW'", &mut p)?;
s.execute("GET DESCRIPTOR 'ROW' VALUE 1 N = DATA", &mut p)?; // 3
s.execute("CLOSE D", &mut p)?;
A positioned UPDATE prepared, of no table, RAISE holding 100:
s.declare_cursor("DECLARE U CURSOR FOR SELECT ID, SALARY FROM STAFF FOR UPDATE OF SALARY")?;
p.set("TEXT", Value::Char("UPDATE SET SALARY = SALARY + ? WHERE CURRENT OF U".into()));
s.execute("PREPARE PAY_RISE FROM TEXT", &mut p)?;
s.execute("OPEN U", &mut p)?;
s.execute("FETCH U INTO MY_ID, N", &mut p)?;
s.execute("EXECUTE PAY_RISE USING RAISE", &mut p)?; // Ada's SALARY 7300.00
STANDARD
ISO/IEC 9075:1992 §13.1, §13.6 and §13.9; of a dynamic cursor, §17.12,
§17.19 and §17.20; X3.135-1986 §8.3, §8.4 and
§8.11, whose cursors are neither SCROLL nor INSENSITIVE. Neither
standard makes a cursor's statements <directly executable statement>s
[ISO/IEC 9075:1992 §20.1].
SEE ALSO
OPEN, FETCH, CLOSE, SELECT, UPDATE, DELETE, PREPARE, ALLOCATE CURSOR, Data Change Delta Tables