Explicit Cursor

Explicit Cursor which declared by user are called Explicit Cursor. User is declare and open the cursor to reserve 

the memory and fetch the populated data from the active data set one at a time, and last close the cursor.

Step for Using Explicit Cursor

Declare Cursor

Open Cursor

Loop

Fetch Record into Cursor

Exit Loop

Close Cursor

Explicit Cursor variabl

Cursor Attribute	Cursor Variable	Description
%ISOPEN	c%ISOPEN	Oracle engine automatically open the cursor If cursor open return true otherwise return false.
%FOUND	c%FOUND	If selected return one or more than one row INSERT, UPDATE, DELETE operation affect If affect return true otherwise return false.
%NOTFOUND	c%NOTFOUND	If selected return one or more than one row INSERT, UPDATE, DELETE operation not affect If not affect the row return true otherwise return false.
%ROWCOUNT	c%ROWCOUNT	Return the number of rows affected by an insert, update, delete or select statement.

Step for Using Explicit Cursor:

Explicit Cursor Example

MP_NO	EMP_NAME	EMP_DEPT	EMP_SALARY
1	Hema	Java  Developer	20000
2	Meena	Program Developer	30000
3	Girish	Program Developer	30000
4	Nasim	Web Developer	45000

We update the employee 'Girish' department is update to 'web developer'.

SQL>set serveroutput on

SQL>edit emp_cursor

DECLARE

         cursor c is select * from emp_information

         where emp_name='Girish';

         emp_information%rowtype;

BEGIN 

         OPEN c;

         Loop exit when c%NOTFOUND;

         FETCH c into temp;

         update emp_information set emp_dept='Web Developer'

         where tmp.emp_name='Girish';

         END Loop;

IF c%ROWCOUNT>0 THEN

         dbms_output.put_line(SQL%ROWCOUNT||' Rows Updated');

ELSE

         dbms_output.put_line('NO Rows Updated Found');

END IF;

CLOSE c;

END;     

/

SAVE: emp_cursor.sql