PL/SQL Explicit Cursor

A cursor named and declared in a PL/SQL block’s declare section is called an Explicit Cursor. The explicit cursors must be declared, opened, fetched and closed. The management of PL/SQL Explicit Cursor is not automatic. It has to be done programmatically!!

Declaration of  PL/SQL Explicit Cursors

An Explicit Cursor must be declared in the DECLARE section. The declaration statement includes SELECT statement that provides the data in the PL/SQL Explicit cursor. Unlike Implicit Cursors, the explicit cursors are identified with unique names. You can declare multiple PL/SQL explicit cursors in a PL/SQL code block.

Syntax

CURSOR cursor_name IS SELECT column-name(s) FROM table_name(s) [WHERE condition];

To open

OPEN cursor_name;              

To fetch  

The number and type of variables while fetching must match the columns in the cursor SELECT statement in cursor declaration.

FETCH cursor_name INTO variable list or Record type variable;

To close

CLOSE cursor_name;

The rules for variable declaration are followed while declaring explicit cursors. Their scope and visibility is the same as that of variables.  All the cursor attributes can be accessed by writing them after the cursor name in format cursor_name%attribute_name

Attributes for PL/SQL Explicit Cursors.

Handling PL/SQL Explicit Cursor

Handing a cursor begins with its declaration in DECLARE section. A declared PL/SQL explicit cursor is always fetched in the coding (BEGIN) section. Using a loop, the rows are repeatedly fetched into PL/SQL variables and are processed. When a FETCH statement fails to fetch next row, it updates %FOUND with FALSE or %NOTFOUND with TRUE and the loop is terminated.  After processing of all cursor rows, the cursor can be closed with CLOSE statement.

If you try to fetch from a closed cursor, you will get an exception. Here are the steps that define the processing of a cursor

Cursor FOR Loop

The PL/SQL cursor can be handled using looping statements. PL/SQL provides a special FOR LOOP-END LOOP that can be exclusively used to handle movement through cursor rows. It uses record type  %ROWTYPE as the loop index. OPEN, FETCH and CLOSE statements are not required with this FOR loop. This loop opens a declared cursor, fetches the rows into declared fields or record and closes when all the rows are processed. You can add the EXIT statement with condition to terminate the loop prematurely.

Syntax

FOR variable name IN cursor_name LOOP

                PL/SQL and SQL Statements

END LOOP

Example of PL/SQL explicit cursor

Problem: Update salary of employees of department_id= 30. If salary> 2500 then salary=salary+salary*0.50 otherwise If salary<= 2500 then salary= salary* 2.

Before PL/SQL code execution

SELECT employee_id, salary from employees where
department_id=30;

PL/SQL Block with WHILE Loop and OPEN-FETCH-CLOSE

DECLARE
 --Declaring Variables
v_empid  employees.employee_id%TYPE;
v_sal  employees.salary%TYPE;
--Declaring Cursor
CURSOR c_emp IS SELECT employee_id, salary from employees where department_id=30;
BEGIN
 --Opening Cursor 
OPEN c_emp;
 --Fetching First row from Cursor  into two variables declared in DECLARE Section
FETCH c_emp INTO v_empid, v_sal;
 --Loop to manage Cursor condition using %FOUND attribute
WHILE c_emp%FOUND LOOP
      --Checking value fetched from cursor into variable 
    IF v_sal>2500 THEN
         --Updating Table when condition results in TRUE
        update employees set salary= salary*1.50 where employee_id=v_empid;
    ELSE 
         --Updating Table when condition results in FALSE
        update employees set salary= salary*2 where employee_id=v_empid;
    END IF ;  
     --Fetching next row from cursor 
    FETCH c_emp INTO v_empid, v_sal;
END LOOP;
 --Printing the number of rows affected using  %ROWCOUNT 
dbms_output.put_line('rows affected= ' ||c_emp%ROWCOUNT);
CLOSE c_emp;
END;

PL/SQL Block with Cursor FOR Loop without OPEN-FETCH-CLOSE

/* this block of PL/SQL updates salaries in employee table for department_ID =30 by checking salary values fetched in explicit cursor 
DECLARE
--declaring variable of record type of employee table 
vc_emp employees%ROWTYPE;
 --declaring cursor from employee table 
CURSOR c_emp IS SELECT * from employees where department_id=30;
BEGIN
 --Beginning FOR loop for cursor 
FOR vc_emp IN c_emp LOOP
     --checking condition by using salary field from vc_emp record fetched from cursor
    IF vc_emp.salary>2500 THEN
         --updating table with TRUE condition
        update employees set salary= salary*1.50 where employee_id=vc_emp.employee_id;
    ELSE 
         --updating table with False condition 
        update employees set salary= salary*2 where employee_id=vc_emp.employee_id;
    END IF ;   
END LOOP;
END;

Data after Execution of PL/SQL block

admin