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

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;


SELECT * FROM emp_information; -- repeating return type
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 )];

Exaple code

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;