The Oracle FETCH statement is one of the stages of using an Oracle cursor. The FETCH statement retrieves rows of data from the result set of a multi-row query and places them into an area in Oracle memory. Rows can be FETCHED one at a time, several at a time, or all at once.
The FETCH statement performs the following operations:
- Reads the data for the current row in the result set into the output PL/SQL variables.
- Moves the pointer to the next row in the result set.
NOTE: If you FETCH past the last valid row in the result set, the values of the variable values are indeterminate and the %NOTFOUND attribute returns TRUE.
The PL/SQL block below declares an explicit cursor 'C'. It is opened in the execution section, the result is iterated and fetched into local memory variables, and the action tehn completed. The cursor is closed once the complete result set is retrieved.
CURSOR C IS
SELECT ENAME, DEPTNO, SAL
WHERE DEPTNO = 20;
FETCH C INTO L_ENAME, L_DEPTNO, L_SAL;
EXIT WHEN C%NOTFOUND;