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

Explicit Cursor Example

Following emp_information table having employee information, We'll update information using Explicit Cursor,

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

Example

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;  
/

Result

• « Previous
• Next »