PL/SQL Explicit Cursor

PL/SQL Explicit Cursor
Explicit Cursor which are construct/manage by user itself call explicit cursor.User itself to declare the cursor, open cursor to reserve the memory and populate data, fetch the records from the active data set one at a time, apply logic and last close the cursor.

You can not directly assign value to an explicit cursor variable you have to use expression or create subprogram for assign value to explicit cursor variable.
Step for Using Explicit Cursor


Step for Using Explicit Cursor

    1.Declare cursor

    Declare explicit cursor has this syntax,

CURSOR c RETURN EMP_DEPT%ROWTYPE;          -- Declare c

CURSOR c IS -- Define c, SELECT * FROM emp_information;     -- all row return type
 

CURSOR c RETURN EMP_DEPT%ROWTYPE IS       -- Define c,
    SELECT * FROM emp_information;          -- repeating return type 

Declaring explicit cursor example:

CURSOR c RETURN EMP_DEPT%ROWTYPE;       -- Declare c
 CURSOR c IS                           --Define c,
 SELECT * FROM emp_information;       -- all row return type
 CURSOR c RETURN EMP_DEPT%ROWTYPE IS    -- Define c,
 SELECT * FROM emp_information;        -- repeating return t 


 Opening Explicit Cursor

 DECLARE block you are already declare CURSOR now you can OPEN CURSOR by using following way, and allocate some reserve area for process database query.

 

     OPEN cursor_name [( cursor_parameter )];

Loop

Loop iterate until ROW not found. Once found loop exit control goes next statement (outside loop).



    Fetching data from cursor
Using FETCH statement you can fetch CURSOR data into explicit variable.

FETCH cursor_name INTO variable;

 Exit loop

Closing Explicit Cursor
This way you can close opened CURSOR.

CLOSE cursor_name [( cursor_parameter )]; 

Following emp_information table having employee information, now we update information using Explicit Cursor.