Oracle PLSQL Explicit Cursors

Explicit Cursors


Programmers create explicit cursors, and with these you can do operations on a set of rows, which can be processed one by one. You use explicit cursors when you are sure that the SQL statement will return more than one row. You have to declare an explicit cursor in the declare section at the beginning of the PL/SQL block.

Use explicit cursors to individually process each row returned by a multiple-row SELECT statement.

Explicit cursor functions:

• Can process beyond the first row returned by the query, row by row
  
• Keep track of which row is currently being processed
  
• Allow the programmer to manually control explicit cursors in the PL/SQL block

Once you declare your cursor, the explicit cursor will go through these steps:

Declare: This clause initializes the cursor into memory.
Open: The previously declared cursor is now open and memory is allotted.
Fetch: The previously declared and opened cursor can now access data;
Close: The previously declared, opened, and fetched cursor is closed, which also releases memory allocation.

Below is a small example of an Explicit cursor:

SQL> set serveroutput on;
SQL> Declare
2 Cursor empcursor(empn in number)
3 Is select * from emp where empno=empn;
4
5 empvar emp%rowtype;
6 Begin
7 Dbms_output.put_line('Getting records for employee no. 7521');
8 Open empcursor(7521);
9 Loop
10 Fetch empcursor into empvar;
11 Exit when empcursor%notfound;
12 Dbms_output.put_line('emp name : ' || empvar.ename);
13 Dbms_output.put_line('emp salary : ' || empvar.sal);
14 End loop;
15 Close empcursor;
16 End;
17 /
Getting records for employee no. 7521
emp name : WARD
emp salary : 1250

PL/SQL procedure successfully completed.


Explicit Cursor Attributes

Here are the main cursor attributes:

%ISOPEN	It returns TRUE if cursor is open, and FALSE if it is not.
%FOUND	It returns TRUE if the previous FETCH returned a row and FALSE if it did not.
%NOTFOUND	It returns TRUE if the previous FETCH did not return a row and FALSE if it did.
%ROWCOUNT	It gives you the number of rows the cursor fetched so far.

Few more examples of Explicit Cursors:

Example 1 of an Explicit Cursor:

An example to retrieve the first 10 employees one by one.

SET SERVEROUTPUT ON
DECLARE
v_empno employees.employee_id%TYPE;
v_ename employees.last_name%TYPE;
CURSOR emp_cursor IS
SELECT employee_id, last_name
FROM employees;
BEGIN
OPEN emp_cursor;
FOR i IN 1..10 LOOP
FETCH emp_cursor INTO v_empno, v_ename;
DBMS_OUTPUT.PUT_LINE (TO_CHAR(v_empno)
||' '|| v_ename);
END LOOP;
Close emp_cursor
END ;

Example 2 of an Explicit Cursor:

DECLARE

CURSOR csr_ac (p_name VARCHAR2) IS
SELECT empno, name, sal
FROM employee
WHERE name LIKE '%p_name%';

BEGIN

FOR rec_ac IN csr_ac ('LE')
LOOP
DBMS_OUTPUT.PUT_LINE(rec_ac.empno || ' ' ||rec_ac.name || ' '||v_sal);
END LOOP ;

CLOSE csr_ac;

END;

Example 3 of an Explicit Cursor:

Another way of writing the above code, is to use the basic loop and the SQL%NOTFOUND cursor, as shown in the following.

DECLARE
CURSOR csr_ac (p_name VARCHAR2) IS
SELECT empno, ename, sal
FROM emp

WHERE ename LIKE '%SMITH%';

v_a emp.empno%TYPE;
v_b emp.ename%TYPE;
v_c emp.sal%TYPE;

BEGIN
OPEN csr_ac('');
LOOP
FETCH csr_ac INTO v_a, v_b, v_c;
EXIT WHEN csr_ac%NOTFOUND;

DBMS_OUTPUT.PUT_LINE(v_a || ' ' || v_b || ' '||v_c);

END LOOP;
CLOSE csr_ac;
END;