Fetching rows from a cursor v16

Once a cursor is open, you can retrieve rows from the cursor’s result set by using the FETCH statement.

Syntax

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

Where:

• name is the identifier of a previously opened cursor.
• record is the identifier of a previously defined record (for example, using table%ROWTYPE).

variable, variable_2... are SPL variables that receive the field data from the fetched row. The fields in record or variable, variable_2... must match in number and order the fields returned in the SELECT list of the query given in the cursor declaration. The data types of the fields in the SELECT list must match or be implicitly convertible to the data types of the fields in record or the data types of variable, variable_2...

Example

The following shows the FETCH statement:

CREATE OR REPLACE PROCEDURE cursor_example
IS
    v_empno         NUMBER(4);
    v_ename         VARCHAR2(10);
    CURSOR emp_cur_3 IS SELECT empno, ename FROM emp WHERE deptno = 10
        ORDER BY empno;
BEGIN
    OPEN emp_cur_3;
    FETCH emp_cur_3 INTO v_empno, v_ename;
        ...
END;

Instead of explicitly declaring the data type of a target variable, you can use %TYPE instead. In this way, if the data type of the database column changes, the target variable declaration in the SPL program doesn't have to change. %TYPE picks up the new data type of the specified column.

CREATE OR REPLACE PROCEDURE cursor_example
IS
    v_empno         emp.empno%TYPE;
    v_ename         emp.ename%TYPE;
    CURSOR emp_cur_3 IS SELECT empno, ename FROM emp WHERE deptno = 10
        ORDER BY empno;
BEGIN
    OPEN emp_cur_3;
    FETCH emp_cur_3 INTO v_empno, v_ename;
        ...
END;

If all the columns in a table are retrieved in the order defined in the table, you can use %ROWTYPE to define a record into which the FETCH statement places the retrieved data. You can then access each field in the record using dot notation:

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;
    FETCH emp_cur_1 INTO v_emp_rec;
    DBMS_OUTPUT.PUT_LINE('Employee Number: ' || v_emp_rec.empno);
    DBMS_OUTPUT.PUT_LINE('Employee Name  : ' || v_emp_rec.ename);
        ...
END;

• On this page
• Syntax
• Example