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.
CURSOR cursor_name IS SELECT column-name(s) FROM table_name(s) [WHERE condition];
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;
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.
|Attribute||Return Data Type||Working|
|cursorname%ISOPEN||BOOLEAN||ISOPEN returns TRUE if the cursor is already open else FALSE|
|cursorname%FOUND||BOOLEAN||FOUND attribute returns TRUE if the FETCH operation successfully fetches row or fields in the PL/SQL variables. If the row can’t be fetched, this attribute returns FALSE|
|cursorname%NOTFOUND||BOOLEAN||NOTFOUND attribute return TRUE if the FETCH operation fails to fetch a row or fields in the PL/SQL variables. If a row is fetched successfully, this attribute returns FALSE|
|cursorname%ROWCOUNT||NUMBER||ROWCOUNT attribute returns the count of rows fetched into the cursor.|
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.
FOR variable name IN cursor_name LOOP
PL/SQL and SQL Statements
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;