Cursors

OPEN FOR query

OPEN unbound_cursorvar [ [ NO ] SCROLL ] FOR query;

Opens an unbound cursor variable against a specific query. The cursor must not already be open, and the query must be a SELECT statement or another row-returning statement such as EXPLAIN. PL/SQL variable values in the query are resolved at open time — subsequent changes to those variables do not affect the cursor. The SCROLL and NO SCROLL options have the same meaning for a bound cursor.

Example:

OPEN curs1 FOR SELECT * FROM foo WHERE key = mykey;

OPEN FOR EXECUTE

OPEN unbound_cursorvar [ [ NO ] SCROLL ] FOR EXECUTE query_string
                                         [ USING expression [, ... ] ];

Opens an unbound cursor against a dynamically constructed query string, in the same way as the EXECUTE statement. Because the query is compiled at run time, the query plan can vary between executions. Variable names in the query string are not substituted directly; pass values through format() or the USING clause instead.

Example:

OPEN curs1 FOR EXECUTE format('SELECT * FROM %I WHERE col1 = $1', tabname) USING keyvalue;

The table name is inserted via format(). The comparison value for col1 is passed through USING, so it does not need to be quoted or escaped.

Open a bound cursor

OPEN bound_cursorvar [ ( [ argument_name := ] argument_value [, ...] ) ];

Opens a cursor that was bound to a query at declaration time. The cursor must not already be open. Provide argument values only if the cursor was declared with parameters. The query plan is cached and cannot be changed with EXECUTE. SCROLL and NO SCROLL cannot be specified here — the scrolling behavior was fixed at declaration time.

Pass argument values using positional notation, named notation (with :=), or a mix of both.

Examples:

OPEN curs2;
OPEN curs3(42);
OPEN curs3(key := 42);

You can pass values into the cursor by specifying an explicit parameter in the OPEN statement, or by implicitly referencing a PL/SQL variable in the query. However, only the names of variables that are declared before the cursor is bound to the query can be replaced by the parameter values of the cursor. In both cases, the values to be passed are specified when the cursor is opened.

Alternatively, declare a variable before the cursor and reference it implicitly:

DECLARE
    key integer;
    curs4 CURSOR FOR SELECT * FROM tenk1 WHERE unique1 = key;
BEGIN
    key := 42;
    OPEN curs4;

FETCH

FETCH [ direction { FROM | IN } ] cursor INTO target;

Retrieves the next row from the cursor into a row variable, record variable, or a comma-separated list of simple variables. If no row is available, the target is set to NULL. The special variable FOUND can be checked afterward to determine whether a row was retrieved.

Omitting direction is the same as specifying NEXT. Supported directions (single-row variants only):

NEXT, PRIOR, FIRST, LAST, ABSOLUTE count, RELATIVE count, FORWARD, BACKWARD

The count clause accepts an integer expression, unlike the SQL FETCH statement, which requires an integer constant. Backward-moving directions fail unless the cursor was declared or opened with SCROLL.

Examples:

FETCH curs1 INTO rowvar;
FETCH curs2 INTO foo, bar, baz;
FETCH LAST FROM curs3 INTO x, y;
FETCH RELATIVE -2 FROM curs4 INTO x;

MOVE

MOVE [ direction { FROM | IN } ] cursor;

Repositions a cursor without returning the row. Supports the same direction options as FETCH. The special variable FOUND can be checked to determine whether the target row exists.

Examples:

MOVE curs1;
MOVE LAST FROM curs3;
MOVE RELATIVE -2 FROM curs4;
MOVE FORWARD 2 FROM curs4;

UPDATE/DELETE WHERE CURRENT OF

UPDATE table SET ... WHERE CURRENT OF cursor;
DELETE FROM table WHERE CURRENT OF cursor;

Updates or deletes the row at the cursor's current position. Cursor queries used with these statements have restrictions — grouping is not allowed. Use FOR UPDATE in the cursor query to lock rows for modification.

Example:

UPDATE foo SET dataval = myval WHERE CURRENT OF curs1;

CLOSE

CLOSE cursor;

Closes the underlying portal of an open cursor. Use CLOSE to release resources before the transaction ends, or to reopen the same cursor variable with a different query.

Example:

CLOSE curs1;

Return cursors

A function can return an open cursor to the caller, which is useful for returning large result sets. The caller then fetches rows from the cursor and closes it, or lets the transaction close it automatically.

Portal naming:

• Bound cursor variables are initialized with their own variable name as the portal name. If you assign a string to the variable before opening, that string becomes the portal name instead.

• Unbound cursor variables default to NULL. If still NULL at OPEN time, a unique portal name is generated automatically and assigned back to the variable.

Example — caller provides the portal name:

CREATE TABLE test (col text);
INSERT INTO test VALUES ('123');

CREATE FUNCTION reffunc(refcursor) RETURN refcursor IS
BEGIN
    OPEN $1 FOR SELECT col FROM test;
    RETURN $1;
END;

BEGIN;
SELECT reffunc('funccursor');
FETCH ALL IN funccursor;
COMMIT;

Example — portal name is auto-generated:

CREATE FUNCTION reffunc2() RETURN refcursor IS
DECLARE
    ref refcursor;
BEGIN
    OPEN ref FOR SELECT col FROM test;
    RETURN ref;
END;

-- Cursors require an active transaction.
BEGIN;
SELECT reffunc2();

      reffunc2
--------------------
 <unnamed cursor 1>
(1 row)

FETCH ALL IN "<unnamed cursor 1>";
COMMIT;

Example — return multiple cursors from one function:

CREATE FUNCTION myfunc(refcursor, refcursor) RETURN SETOF refcursor IS
BEGIN
    OPEN $1 FOR SELECT * FROM table_1;
    RETURN NEXT $1;
    OPEN $2 FOR SELECT * FROM table_2;
    RETURN NEXT $2;
END;

-- Cursors require an active transaction.
BEGIN;

SELECT * FROM myfunc('a', 'b');

FETCH ALL FROM a;
FETCH ALL FROM b;
COMMIT;