Debian Codegraph @

doc/sql.extensions/README.cursors

raw
1 ------------
2 PSQL cursors
3 ------------
4
5 Function:
6 Allows explicit cursor operations.
7
8 Author:
9 Dmitry Yemanov <[email protected]>
10
11 Syntax rules:
12 DECLARE [VARIABLE] <cursor_name> [SCROLL | NO SCROLL] CURSOR FOR ( <select_statement> );
13 OPEN <cursor_name>;
14 FETCH <cursor_name> [INTO <var_name> [, <var_name> ...]];
15 FETCH {NEXT | PRIOR | FIRST | LAST | ABSOLUTE <n> | RELATIVE <n>} FROM <cursor_name> [INTO <var_name> [, <var_name> ...]];
16 CLOSE <cursor_name>;
17
18 Example(s):
19 1. DECLARE RNAME CHAR(31);
20 DECLARE C CURSOR FOR ( SELECT RDB$RELATION_NAME
21 FROM RDB$RELATIONS );
22 BEGIN
23 OPEN C;
24 WHILE (1 = 1) DO
25 BEGIN
26 FETCH C INTO :RNAME;
27 IF (ROW_COUNT = 0) THEN
28 LEAVE;
29 SUSPEND;
30 END
31 CLOSE C;
32 END
33
34 2. DECLARE RNAME CHAR(31);
35 DECLARE FNAME CHAR(31);
36 DECLARE C CURSOR FOR ( SELECT RDB$FIELD_NAME
37 FROM RDB$RELATION_FIELDS
38 WHERE RDB$RELATION_NAME = :RNAME
39 ORDER BY RDB$FIELD_POSITION );
40 BEGIN
41 FOR
42 SELECT RDB$RELATION_NAME
43 FROM RDB$RELATIONS
44 INTO :RNAME
45 DO
46 BEGIN
47 OPEN C;
48 FETCH C INTO :FNAME;
49 CLOSE C;
50 SUSPEND;
51 END
52 END
53
54 Note(s):
55 1. Cursor declaration is allowed only in the beginning of a PSQL block/procedure/trigger
56 (like a regular local variable declaration).
57 2. Cursor names are required to be unique in the given context. They cannot interfere
58 with FOR SELECT cursor (declared via the AS CURSOR clause) names. But a cursor can
59 share its name with any variable in this context, as possible operations are different.
60 3. Positioned updates and deletes with cursors using the WHERE CURRENT OF clause are allowed.
61 4. Attempts to fetch from or close a FOR SELECT cursor are prohibited.
62 5. Attempts to open cursor which is already open, as well as to fetch from or close cursor
63 which is already closed, will fail.
64 6. All cursors which were not explicitly closed will be closed automatically on exit of
65 the current PSQL block/procedure/trigger.
66 7. ROW_COUNT system variable can be used to check whether the last FETCH statement returned
67 any row.
68
69 See also:
70 README.context_variables
71 README.cursor_variables.txt