PolarDB:Static cursors

Working with a static cursor follows four steps: declare the cursor, open it, fetch rows, then close it. Cursors are most often used inside a LOOP, FOR, or WHILE construct. Use a conditional test (such as %NOTFOUND) to detect the end of the result set and exit the loop.

Declare a cursor

Declare a cursor in the declaration section of your SPL program:

CURSOR <name> IS <query>;

• name — the identifier used to reference the cursor and its result set throughout the program.

• query — a SELECT statement that defines the result set the cursor retrieves.

The following example declares three cursors against the emp table:

CREATE OR REPLACE PROCEDURE cursor_example
IS
    CURSOR emp_cur_1 IS SELECT * FROM emp;
    CURSOR emp_cur_2 IS SELECT empno, ename FROM emp;
    CURSOR emp_cur_3 IS SELECT empno, ename FROM emp
        WHERE deptno = 10
        ORDER BY empno;
BEGIN
    ...
END;

Open a cursor

Before fetching rows, open the cursor with the OPEN statement. Opening the cursor runs the associated SELECT query and builds the result set.

OPEN <name>;

name is the identifier of a cursor declared in the same SPL program. Do not call OPEN on a cursor that is already open.

BEGIN
    OPEN emp_cur_3;
    ...
END;

Fetch rows

Use FETCH to retrieve one row at a time from the open cursor into a record or a set of variables:

FETCH <name> INTO { <record> | <variable> [, <variable_2>]... };

• name — the identifier of an open cursor.

• record — a record variable (for example, declared with table%ROWTYPE) that receives all fields in the fetched row.

• variable, variable_2... — individual SPL variables that receive the field values. The number, order, and data types must match the SELECT list in the cursor declaration.

The following example fetches all rows from emp_cur_1 in a LOOP and exits when no more rows are available:

CREATE OR REPLACE PROCEDURE cursor_example
IS
    v_emp_rec   emp%ROWTYPE;
    CURSOR emp_cur_1 IS SELECT * FROM emp;
BEGIN
    OPEN emp_cur_1;
    LOOP
        FETCH emp_cur_1 INTO v_emp_rec;
        EXIT WHEN emp_cur_1%NOTFOUND;  -- exit when the result set is exhausted
        DBMS_OUTPUT.PUT_LINE('Employee: ' || v_emp_rec.empno
            || '  ' || v_emp_rec.ename);
    END LOOP;
    CLOSE emp_cur_1;
END;

Close a cursor

After processing, close the cursor to release its resources. Once closed, you can no longer access the result set through that cursor.

CLOSE <name>;

name is the identifier of a currently open cursor. Do not call CLOSE on a cursor that is already closed. You can reopen a closed cursor with OPEN to rebuild the result set and fetch from it again.

Use a cursor FOR loop

The cursor FOR loop combines OPEN, repeated FETCH, end-of-data detection, and CLOSE into a single construct:

FOR <record> IN <cursor>
LOOP
    <statements>
END LOOP;

• record — an implicitly declared record with the same type as cursor%ROWTYPE.

• cursor — the name of a previously declared cursor.

The FOR loop opens the cursor, fetches each row into record, executes statements for each row, and closes the cursor automatically when the result set is exhausted. The cursor opened by a FOR loop is a holdable cursor — it survives any COMMIT or ROLLBACK that occurs inside the loop body.

The following example rewrites the LOOP-based example above using a cursor FOR loop:

CREATE OR REPLACE PROCEDURE cursor_example
IS
    CURSOR emp_cur_1 IS SELECT * FROM emp;
BEGIN
    FOR emp_rec IN emp_cur_1
    LOOP
        DBMS_OUTPUT.PUT_LINE('Employee: ' || emp_rec.empno
            || '  ' || emp_rec.ename);
    END LOOP;
    -- cursor is closed automatically after the loop
END;

Cursor attributes

SPL provides the following attributes to inspect cursor state after a FETCH:

Use cursor_name%ATTRIBUTE to reference an attribute. For example:

FETCH emp_cur_1 INTO v_emp_rec;
EXIT WHEN emp_cur_1%NOTFOUND;

Parameterized cursors

A parameterized cursor accepts input values when opened, letting you reuse the same cursor declaration with different filter criteria:

CURSOR <name> (<param> <datatype> [, <param_2> <datatype_2>]...) IS <query>;

Pass parameter values when you open the cursor:

OPEN <name> (<value> [, <value_2>]...);

The following example declares a cursor that filters employees by department:

CREATE OR REPLACE PROCEDURE dept_employees(p_deptno NUMBER)
IS
    CURSOR dept_cur (p_dept NUMBER) IS
        SELECT empno, ename FROM emp WHERE deptno = p_dept ORDER BY empno;
BEGIN
    FOR emp_rec IN dept_cur(p_deptno)
    LOOP
        DBMS_OUTPUT.PUT_LINE(emp_rec.empno || '  ' || emp_rec.ename);
    END LOOP;
END;

Call the procedure with a department number to list employees in that department:

EXEC dept_employees(10);