Pl/Sql - Cursor

A cursor is a temporary work area created in the system memory when a SQL statement is executed.A cursor contains information on a select statement and the rows of data accessed by it.  This temporary work area is used to store the data retrieved from the database, and manipulate this data. A cursor can hold more than one row, but can process only one row at a time.

Defining a Cursor

A cursor is defined in the declarative part of the PL/SQL block by naming it and associating it with a query
CURSOR <cursorname> IS
 SELECT statement;

Example

CURSOR emp_cur IS
SELECT empno, ename, job, sal
FROM emp;

A Cursor can be manipulated using
– OPEN
– FETCH
– CLOSE

Cursor must be declared before it can be referenced using the OPEN, CLOSE or FETCH statements

The OPEN Statement
    Initializes or opens a cursor
    Cursor must be opened before any rows are returned by the query
OPEN <cursorname>
Example --
           OPEN emp_cur;

The FETCH Statement
    Can be executed repeatedly until all rows have been retrieved
FETCH <cursorname> INTO var1, …, varN;
OR
FETCH <cursorname> INTO record_variable;
    Example
FETCH emp_cur INTO mrec;

The CLOSE Statement
    Closes the cursor and makes the active set undefined
CLOSE <cursorname>;
    Example
CLOSE emp_cur;

Example -

DECLARE
  2     
  3      v_id emp.empno%TYPE;
  4     
  5      CURSOR v_cursor IS SELECT empno FROM emp ORDER BY empno;
  6  BEGIN
  7     
  8      OPEN v_cursor;
  9      LOOP
 10         
 11          FETCH v_cursor INTO v_id;
 12         
 13          EXIT WHEN v_cursor%NOTFOUND;
 14          DBMS_OUTPUT.PUT_LINE('v_id = ' || v_id );
 15      END LOOP;
 16      CLOSE v_cursor;
 17  END;
 18  /