Term: FETCH

Definition:
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.

Example Usage:

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.

DECLARE



CURSOR C IS

 SELECT ENAME, DEPTNO, SAL

 FROM EMP

 WHERE DEPTNO = 20;

BEGIN

  OPEN C;

  LOOP

  FETCH C INTO L_ENAME, L_DEPTNO, L_SAL;

  EXIT WHEN C%NOTFOUND;

  ...

  ...

  END LOOP;

  CLOSE C;

END;