After a cursor has been opened, rows can be retrieved from the result set of the cursor 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 such as 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...
The following code 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. 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 by 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;