Fetching Rows From a Cursor v11
Once a cursor has been opened, rows can be retrieved from the cursor’s result set by using the FETCH
statement.
FETCH <name> INTO { <record> | <variable> [, <variable_2> ]... };
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 will 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...
Note
There is a variation of FETCH INTO
using the BULK COLLECT
clause that can return multiple rows at a time into a collection. See Section Using the BULK COLLECT Clause for more information on using the BULK COLLECT
clause with the FETCH INTO
statement.
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, %TYPE
can be used instead. In this way, if the data type of the database column is changed, the target variable declaration in the SPL program does not have to be changed. %TYPE
will automatically pick 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, %ROWTYPE
can be used to define a record into which the FETCH
statement will place the retrieved data. Each field within the record can then be accessed 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;