Different types of cursors in pl/sql

by dinesh
(chennai)

Can anyone say me the difference b/w cursors?

Oracle supports 3 different types of cursors in PL/SQL
  • implicit cursors
  • explicit cursors
  • and
  • ref cursors
. Let's look at each of these in turn but first, let's define a cursor so we know what we're talking about.

A cursor can be regarded as a pointer (handle) to a memory area that stores information about the SQL statement being used in PL/SQL. All types of cursor are used by Oracle to control the interaction of the SQL statement with the database.

Now let's look at the 3 types of cursor in more detail.

Implicit cursors


These are cursors without a name and are created by Oracle for all the dml (data manipulation language) statements insert, update, delete that are embedded in your PL/SQL code as well as for any queries that aren't declared explicitly. For example a SELECT statement in a cursor FOR loop is an implicit cursor.

FOR emp in (SELECT first_name,last_name, salary FROM employees) LOOP
DBMS_OUTPUT.PUT_LINE(emp.last_name);
END LOOP;


In the above snippet of code already creates an implicit cursor for the SELECT statement.

Implicit cursors are also created for SELECT INTO statements in PL/SQL, for example:

DECLARE
max_sal employees.salary%TYPE;
BEGIN
SELECT MAX(salary) INTO max_sal FROM employees;
END;


And as we've already mentioned they are created for DML statements. For example:

BEGIN
UPDATE employees SET salary=salary*1.1;-- give everyone 10% more
DELETE employees;; -- fire everyone
INSERT INTO employees( employee_id,first_name,last_name,email,hire_date,job_id)
VALUES (1,'Top','Dog','topdog', sysdate, 'AD_PRES');
-- start from scratch
END;


Oracle also provides a number of attributes with implicit cursors: %FOUND, %NOTFOUND, %ROWCOUNT and %ISOPEN. These are accessed by prefixing them with the name of the cursor - SQL.

Explicit cursors


Explicit cursors are PL/SQL named database queries. Giving the queries names provides developers with more control over the cursors, enabling you to OPEN, FETCH from and CLOSE them unlike with implicit cursors where you have no control over them. Also unlike implicit cursors, explicit cursors can't be used for DML statements.

Using an explicit cursor, our first example would look like this:

DECLARE
CURSOR emp_cur IS
SELECT first_name,last_name, salary FROM employees ;
BEGIN
FOR emp in emp_cur LOOP
DBMS_OUTPUT.PUT_LINE(emp.last_name);
END LOOP;
END;


This next example demonstrates that we have full control in PL/SQL over the explicit cursor to open, fetch from and close it.

DECLARE
CURSOR emp_cur IS SELECT first_name,last_name, salary FROM employees;
emp emp_cur%ROWTYPE;
BEGIN
OPEN emp_cur;
FETCH emp_cur INTO emp;
FETCH emp_cur INTO emp;
CLOSE emp_cur;
DBMS_OUTPUT.PUT_LINE(emp.last_name);
END;


Having full control of the cursor enables us to fetch just twice from it even though more rows may be available.

NB. Oracle also allows us to continue fetching from the cursor after all the rows (if any) have been returned without raising an exception. You have to explicitly check for data using cursor%FOUND or cursor%ROWCOUNT.

We can use the standard PL/SQL cursor attributes provided by Oracle to check the status of the cursor as in the following example, however %FOUND can only be checked when the cursor is open.

DECLARE
CURSOR emp_cur IS
SELECT first_name,last_name, salary FROM employees;
emp emp_cur%ROWTYPE;
BEGIN
IF NOT emp_cur%ISOPEN THEN OPEN emp_cur; END IF;
FETCH emp_cur INTO emp;
DBMS_OUTPUT.PUT_LINE('count = '||emp_cur%ROWCOUNT);
IF emp_cur%FOUND THEN DBMS_OUTPUT.PUT_LINE(emp.last_name); END IF;
IF emp_cur%ISOPEN THEN CLOSE emp_cur; END IF;
END;


Ref cursors


PL/SQL ref cursors are much like explicit cursors however unlike explicit cursors they can be assigned to different result sets and they can be passed from the client to the server (or vice versa) or between different subroutines.

PL/SQL has 2 flavours of ref cursors - strongly typed and weakly typed. Weakly-typed cursors provide more flexibility as they can be re-opened for a completely different query. Strongly-typed ref cursors on the other hand provide more protection against run-time errors as the PL/SQL compiler checks for incompatibilities.

The other drawback of weakly-typed ref cursors is that you can't declare a variable of the cursor%ROWTYPE;

This example shows the use of a PL/SQL weak ref cursor type.

DECLARE
TYPE cur_typ IS REF CURSOR; -- weak ref cursor type
emp_cur cur_typ;
fnam employees.first_name%TYPE;
lnam employees.last_name%TYPE;
sal employees.salary%TYPE;
email_addr employees.email%TYPE;
BEGIN
OPEN emp_cur FOR SELECT first_name,last_name, salary FROM employees;
FETCH emp_cur INTO fnam,lnam,sal;
CLOSE emp_cur;

/* now attempt to reuse cursor for new query returning different row structure, however this generates a run-time error ORA-06504 */
OPEN emp_cur FOR SELECT email,job_id FROM employees;
FETCH emp_cur INTO email_addr;
CLOSE emp_cur;
DBMS_OUTPUT.PUT_LINE(email_addr);
END;


This example shows the use of a PL/SQL strong ref cursor type.

DECLARE
TYPE cur_typ IS REF CURSOR RETURN departments%ROWTYPE; -- strong ref cursor type
dept_cur cur_typ;
dept dept_cur%ROWTYPE;
BEGIN
OPEN dept_cur FOR SELECT * FROM departments;
FETCH dept_cur INTO dept;
CLOSE dept_cur;
DBMS_OUTPUT.PUT_LINE(dept.department_name);

OPEN dept_cur FOR SELECT * FROM departments WHERE department_id=1; --reopen for new query returning same row structure
FETCH dept_cur INTO dept;
CLOSE dept_cur;
DBMS_OUTPUT.PUT_LINE(dept.department_name);
END;


See also

Click here to post comments

Join in and write your own page! It's easy to do. How? Simply click here to return to Oracle Questions.