PolarDB:Cursors

Declare cursor variables

Cursors in PL/SQL are accessed over cursor variables, which are of the special data type refcursor. You can create a cursor variable by declaring it as a variable of the refcursor type. Alternatively, you can use the following cursor declaration syntax to create a cursor variable:

    name [ [ NO ] SCROLL ] CURSOR [ ( arguments ) ] FOR query;

FOR can be replaced by IS for compatibility with Oracle. If SCROLL is specified, the cursor can be scrolled backward. If NO SCROLL is specified, backward fetches are rejected. If both SCROLL and NO SCROLL are not specified, queries determine whether backward fetches are allowed. arguments indicates a list of name`` ``datatype pairs that are separated by commas (,). arguments defines the names of variables to be replaced by parameter values in the specified query. The actual values that are used to replace the variable names are specified when the cursor is opened.

Example:

    DECLARE
        curs1 refcursor;
        curs2 CURSOR FOR SELECT * FROM tenk1;
        curs3 CURSOR (key integer) FOR SELECT * FROM tenk1 WHERE unique1 = key;

The three variables are of the refcursor type. curs1 can be used for all queries. curs2 is bound to a specific query. curs3 is bound to a parameterized query. key is replaced by a parameter value of the INTEGER type when the cursor is opened. curs1 is considered as an unbound cursor variable because it is not bound to any specific query.

Open cursors

Before a cursor can be used to retrieve rows, the cursor must be opened. This operation is equivalent to the execution of the DECLARE CURSOR statement. PL/SQL has three forms of the OPEN statement. Two forms of the OPEN statement are used for unbound cursor variables, and the third one is used for bound cursor variables.

OPEN FOR query

    OPEN unbound_cursorvar [ [ NO ] SCROLL ] FOR query;

The cursor variable is opened and the query to be executed is specified. The cursor cannot be already opened, and the cursor must have been declared as an unbound cursor variable, which is a simple refcursor variable. The query must be a SELECT statement or a statement such as EXPLAIN that returns rows. The query is processed in the same way as other SQL statements in PL/SQL: PL/SQL variable names are replaced, and the query plan is cached for possible reuse. When a PL/SQL variable name is to be replaced by a value in the cursor query, the value is the one that the cursor has when the cursor is opened. Subsequent changes to the variable do not affect the cursor behavior. 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 [, ... ] ];

The cursor variable is opened and the specified query is executed. The cursor cannot be already opened, and the cursor must have been declared as an unbound cursor variable, which is a simple refcursor variable. The query is specified as a string expression in the same way as in the EXECUTE statement. This provides flexibility so that the query plan can vary from one execution to the next. This also indicates that the variable names in the statement string are not replaced. As with the EXECUTE statement, parameter values can be inserted into the dynamic statement by using the format() method and the USING parameter. The SCROLL and NO SCROLL options have the same meaning for a bound cursor.

Example:

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

In this example, the table name is inserted into the query by using the format() method. The comparison value for col1 is inserted by using the USING parameter. Therefore, the comparison value does not need to be referenced.

Open a bound cursor

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

This form of the OPEN statement is used to open a cursor variable that is bound to a query when it is declared. The cursor cannot be already opened. A list of actual parameter value expressions must be displayed only if the cursor is declared to receive parameters. These values are used to replace the variable names in the query.

The query plan to which a cursor is bound is considered cacheable. In this case, no equivalent of the EXECUTE statement exists. SCROLL and NO SCROLL cannot be specified in the OPEN statement because the scrolling behavior of the cursor is already determined.

Parameter values can be passed by using positional or named notation. In positional notation, all parameters are specified in order. In named notation, each parameter is specified by using := to separate the parameter name from the parameter expression. Positional and named notations can be used together.

Examples:

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

In each example, the preceding sample cursor declaration is used. Variable names are replaced in the query to which the cursor is bound. Therefore, you can use one of the following methods to pass values into the cursor: specify an explicit parameter in the OPEN statement, or implicitly reference 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. The following sample code shows a different way to open a bound cursor. This has the same effect as the preceding example of curs3.

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

Use cursors

After a cursor is opened, the cursor can be operated by using the statements that are described in this topic.

These operations do not need to be specified in the same function that opens the cursor. You can return a refcursor value from a function and allow the caller to perform operations on the cursor. Internally, a refcursor value is the string name of the portal that contains the query for the cursor. This name can be passed or assigned to other refcursor variables without disturbing the portal.

All portals are implicitly closed after the transaction ends. Therefore, a refcursor value can be used to reference an opened cursor only before the transaction ends.

FETCH

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

The FETCH statement retrieves the next row from the cursor into the destination in the same way as the SELECT INTO statement. The destination can be a row variable, a record variable, or a comma-separated list of simple variables. If no next row is available, the destination is set to NULL. The special variable FOUND can be checked to determine whether a row is obtained.

The direction clause can be one of the variants that are allowed in the FETCH statement except the ones that can fetch more than one row. The variants can be NEXT, PRIOR, FIRST, LAST, ABSOLUTE count, RELATIVE count, FORWARD, or BACKWARD. Omitting the direction clause is the same as specifying the NEXT clause. If you use a count clause, the count clause can be an integer expression. This is different from the FETCH statement, which allows only an integer constant. The values of the direction clause that require backward moving may fail unless the cursor is declared or opened with the SCROLL option.

cursor must be the name of a refcursor variable that references an open cursor portal.

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;

The MOVE statement repositions a cursor without retrieving data. The MOVE statement works in the same way as the FETCH statement, except the MOVE statement only repositions the cursor and does not return the row to move to. The special variable FOUND can be checked to determine whether the row to move to 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;

When a cursor is positioned in a table row, the row can be updated or deleted by using the cursor to identify the row. Cursor queries are limited. Especially, grouping is disallowed. We recommend that you use the FOR UPDATE statement in the cursor.

Example:

    UPDATE foo SET dataval = myval WHERE CURRENT OF curs1;

CLOSE

    CLOSE cursor;

The CLOSE statement closes the underlying portal of an opened cursor. This statement can be used to release resources before the transaction ends, or to free up the cursor variable to be opened again.

Example:

    CLOSE curs1;

Return cursors

PL/SQL functions can return cursors to the caller. This helps return multiple rows or columns, especially with very large result sets. To do this, the function opens the cursor and returns the cursor name to the caller. Alternatively, the function opens the cursor by using a portal name that is specified by or known to the caller. Then, the caller can fetch rows from the cursor. The cursor can be closed by the caller, or automatically closed when the transaction ends.

The portal name that is used for a cursor can be specified by the programmer or automatically generated. To specify a portal name, assign a string to the refcursor variable before the variable is opened. The string value of the refcursor variable is used by the OPEN statement as the name of the underlying portal. However, if the value of the refcursor variable is null, the OPEN statement automatically generates a name that does not conflict with existing portal names, and assigns the name to the refcursor variable.

The following sample code provides an example on how a cursor name is provided by the caller:

   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;

The following sample code provides an example on how a cursor name is automatically generated:

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

    -- A cursor is required in a transaction. 
    BEGIN;
    SELECT reffunc2();

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

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

The following sample code provides an example on how to return multiple cursors from a single 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;
    

    -- A cursor is required in a transaction. 
    BEGIN;

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

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

Loop through the results of a cursor

A variant of the FOR statement allows iterating through the rows returned by a cursor. Syntax:

    [ <<label>> ]
    FOR recordvar IN bound_cursorvar [ ( [ argument_name := ] argument_value [, ...] ) ] LOOP
        statements
    END LOOP [ label ];

The cursor variable must have been bound to a query when it is declared, and it cannot be already opened. The FOR statement automatically opens the cursor, and it closes the cursor again when the loop exits. A list of actual parameter value expressions must be specified only if the cursor is declared to receive parameters. These values replace the variable names in the query in the same way as the OPEN statement

The variable recordvar is automatically defined as the record type and exists only inside the loop. Existing definition of the variable name is ignored within the loop. Each row that is returned by the cursor is successively assigned to this record variable, and the loop body is executed.