Use the CLOSE statement to close a cursor variable - PolarDB

Use the CLOSE statement to release resources held by an open cursor variable when you no longer need its result set.

Cursor variables behave differently from static cursors when closed:

Cursor type	Behavior
Static cursor	Must be closed before it can be reopened.
Cursor variable	Does not need to be closed before reopening. Calling `OPEN ... FOR` on an open cursor variable discards the previous result set and replaces it with the new query.

After you close a cursor variable, its result set is no longer accessible. Close a cursor variable when you no longer need the results, so that the underlying resources can be reused.

Example

The following procedure fetches employee records for a given department, then closes the cursor variable after the loop completes.

CREATE OR REPLACE PROCEDURE emp_by_dept (
    p_deptno        emp.deptno%TYPE
)
IS
    emp_refcur      SYS_REFCURSOR;
    v_empno         emp.empno%TYPE;
    v_ename         emp.ename%TYPE;
BEGIN
    OPEN emp_refcur FOR SELECT empno, ename FROM emp WHERE deptno = p_deptno;
    DBMS_OUTPUT.PUT_LINE('EMPNO    ENAME');
    DBMS_OUTPUT.PUT_LINE('-----    -------');
    LOOP
        FETCH emp_refcur INTO v_empno, v_ename;
        EXIT WHEN emp_refcur%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(v_empno || '     ' || v_ename);
    END LOOP;
    CLOSE emp_refcur;
END;

Run the procedure for department 20:

EXEC emp_by_dept(20)

Expected output:

EMPNO    ENAME
-----    -------
7369     SMITH
7566     JONES
7788     SCOTT
7876     ADAMS
7902     FORD