PL/SQL Explicit Cursors

PL SQL ref cursors.   PL SQL 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;

DECLARE

my_emp_id NUMBER (6); -- variable for employee_id

my_job_id VARCHAR2 (10); -- variable for job_id

my_sal NUMBER (8,2); -- variable for salary

CURSOR c1 IS

SELECT employee_id, job_id, salary FROM employees

WHERE salary > 2000;

my_dept departments%ROWTYPE; -- variable for departments row

CURSOR c2 RETURN departments%ROWTYPE IS

SELECT * FROM departments

WHERE department_id = 110;

BEGIN

NULL;

END;

/

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;

see also: PL SQL ref cursors. PL SQL cursors.