PolarDB:Cursor attributes

%ISOPEN

The %ISOPEN attribute is used to test whether a cursor is open.

cursor_name%ISOPEN

cursor_name is the name of the cursor. If the cursor is open, TRUE of the BOOLEAN data type is returned. Otherwise, FALSE is returned.

%ISOPEN is used in the following example:

CREATE OR REPLACE PROCEDURE cursor_example
IS
        ...
    CURSOR emp_cur_1 IS SELECT * FROM emp;
        ...
BEGIN
        ...
    IF emp_cur_1%ISOPEN THEN
        NULL;
    ELSE
        OPEN emp_cur_1;
    END IF;
    FETCH emp_cur_1 INTO ...
        ...
END;

%FOUND

The %FOUND attribute is used to test whether a row is retrieved from the result set of the specified cursor after a FETCH operation is performed on the cursor.

cursor_name%FOUND

cursor_name is the name of the cursor. If a row is retrieved from the result set of the cursor after a FETCH operation is performed, TRUE of the BOOLEAN data type is returned.

After the last row of the result set is fetched in a FETCH operation, %FOUND returns FALSE due to the next FETCH operation. FALSE is also returned after the first FETCH operation if the result set has no rows.

If %FOUND is referenced on a cursor before the cursor is opened or after the cursor is closed, an INVALID_CURSOR exception occurs.

If the cursor is open and %FOUND is referenced before the first FETCH operation, %FOUND returns null.

%FOUND is used in the following example:

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;
    DBMS_OUTPUT.PUT_LINE('EMPNO    ENAME');
    DBMS_OUTPUT.PUT_LINE('-----    -------');
    FETCH emp_cur_1 INTO v_emp_rec;
    WHILE emp_cur_1%FOUND LOOP
        DBMS_OUTPUT.PUT_LINE(v_emp_rec.empno || '     ' || v_emp_rec.ename);
        FETCH emp_cur_1 INTO v_emp_rec;
    END LOOP;
    CLOSE emp_cur_1;
END;

When the previous stored procedure is invoked, the following output appears:

EXEC cursor_example;

EMPNO    ENAME
-----    ------
7369     SMITH
7499     ALLEN
7521     WARD
7566     JONES
7654     MARTIN
7698     BLAKE
7782     CLARK
7788     SCOTT
7839     KING
7844     TURNER
7876     ADAMS
7900     JAMES
7902     FORD
7934     MILLER

%NOTFOUND

The %NOTFOUND attribute is the logical opposite of %FOUND.

cursor_name%NOTFOUND

cursor_name is the name of the cursor. If a row is retrieved from the result set of the cursor after a FETCH operation, FALSE of the BOOLEAN data type is returned.

After the last row of the result set is fetched in a FETCH operation, %NOTFOUND returns TRUE due to the next FETCH operation. TRUE is also returned after the first FETCH operation if the result set has no rows.

If %NOTFOUND is referenced on a cursor before the cursor is opened or after the cursor is closed, an INVALID_CURSOR exception occurs.

If the cursor is open and %NOTFOUND is referenced before the first FETCH operation, %NOTFOUND returns null.

%NOTFOUND is used in the following example:

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;
    DBMS_OUTPUT.PUT_LINE('EMPNO    ENAME');
    DBMS_OUTPUT.PUT_LINE('-----    -------');
    LOOP
        FETCH emp_cur_1 INTO v_emp_rec;
        EXIT WHEN emp_cur_1%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(v_emp_rec.empno || '     ' || v_emp_rec.ename);
    END LOOP;
    CLOSE emp_cur_1;
END;

This stored procedure generates the same output as the preceding example when the stored procedure is invoked.

EXEC cursor_example;

EMPNO    ENAME
-----    ------
7369     SMITH
7499     ALLEN
7521     WARD
7566     JONES
7654     MARTIN
7698     BLAKE
7782     CLARK
7788     SCOTT
7839     KING
7844     TURNER
7876     ADAMS
7900     JAMES
7902     FORD
7934     MILLER

%ROWCOUNT

The %ROWCOUNT attribute returns an integer that indicates the number of rows that have been fetched from the specified cursor in a FETCH operation by now.

cursor_name%ROWCOUNT

cursor_name is the name of the cursor. For this cursor, %ROWCOUNT returns the number of rows that have been retrieved by now. After the last row is retrieved, %ROWCOUNT is still specified as the total number of rows that are returned before the cursor is closed. In this case, if %ROWCOUNT is referenced, an INVALID_CURSOR exception occurs.

If %ROWCOUNT is referenced on a cursor before the cursor is opened or after the cursor is closed, an INVALID_CURSOR exception occurs.

If the cursor has been opened and %ROWCOUNT is referenced before the first FETCH operation, %ROWCOUNT returns 0. %ROWCOUNT also returns 0 after the first FETCH operation when the result set has no rows.

%ROWCOUNT is used in the following example:

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;
    DBMS_OUTPUT.PUT_LINE('EMPNO    ENAME');
    DBMS_OUTPUT.PUT_LINE('-----    -------');
    LOOP
        FETCH emp_cur_1 INTO v_emp_rec;
        EXIT WHEN emp_cur_1%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(v_emp_rec.empno || '     ' || v_emp_rec.ename);
    END LOOP;
    DBMS_OUTPUT.PUT_LINE('**********************');
    DBMS_OUTPUT.PUT_LINE(emp_cur_1%ROWCOUNT || ' rows were retrieved');
    CLOSE emp_cur_1;
END;

This stored procedure generates the total number of rows that are retrieved at the end of the employee list, as shown in the following example:

EXEC cursor_example;

EMPNO    ENAME
-----    -------
7369     SMITH
7499     ALLEN
7521     WARD
7566     JONES
7654     MARTIN
7698     BLAKE
7782     CLARK
7788     SCOTT
7839     KING
7844     TURNER
7876     ADAMS
7900     JAMES
7902     FORD
7934     MILLER
**********************
14 rows were retrieved

Summary of cursor states and attributes

The following table summarizes the possible cursor states and the values that are returned by the cursor attributes.