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

1. Declare cursor
   Declare explicit cursor has this syntax,

   CURSOR cursor_name [ parameter ] RETURN return_type;

   CURSOR cursor_name [ parameter ] [ RETURN return_type ]
      IS SELECT STATEMENT;

   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 type 

2. 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 )];

3. Loop
   Loop iterate until ROW not found. Once found loop exit control goes next statement (outside loop).
4. Fetching data from cursor
   Using FETCH statement you can fetch CURSOR data into explicit variable.

   FETCH cursor_name INTO variable;

5. Exit loop
6. 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,

EMP_NO	EMP_NAME	EMP_DEPT	EMP_SALARY
1	Forbs ross	Web Developer	45k
2	marks jems	Program Developer	38k
3	Saulin	Program Developer	34k
4	Zenia Sroll	Web Developer	42k

Now above employee information table update the employee name 'Saulin' department 'Program Developer' update to 'Web Developer'.

Example Code

explicit_cursor.sql

SQL>set serveroutput on
SQL>edit explicit_cursor
DECLARE
 cursor c is select * from emp_information
 where emp_name='bhavesh';
 tmp emp_information%rowtype;
BEGIN 
 OPEN c;
 Loop exit when c%NOTFOUND;
  FETCH c into tmp;
  update emp_information set tmp.emp_dept='Web Developer'
  where tmp.emp_name='Saulin';
 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; 
/

Example Result

SQL>@explicit_cursor
1 Rows Updated

PL/SQL procedure successfully completed.