Introduction PL/SQL

Basic PL/SQL

Advance PL/SQL


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.