Cursors Again

Reading through other programmers’ code I often discover interesting things that sometimes I find hard to explain. For example, consider the following simple PL/SQL block:

DECLARE
   CURSOR emp_cur
   IS
      SELECT first_name
        FROM employees
       WHERE employee_id = 100;

   l_emp_name employees.first_name%TYPE;
BEGIN
   IF emp_cur%ISOPEN
   THEN
      CLOSE emp_cur;
   END IF;

   OPEN emp_cur;

   FETCH emp_cur
    INTO l_emp_name;

   CLOSE emp_cur;

END;

The PL/SQL package I was reviewing had the same exact pattern for every query. Basically, every query was declared as an explicit cursor and code similar to the above was used to open, fetch and close every cursor in the program.

I do not understand two things here: the need for an explicit cursor, and the need for this “IF cur%ISOPEN THEN CLOSE cur; END IF;” before “OPEN cur …. CLOSE cur“. Do you?

I would have coded the above like this:

DECLARE
   l_emp_name   employees.first_name%TYPE;
BEGIN
   SELECT first_name
     INTO l_emp_name
     FROM employees
    WHERE employee_id = 100;

EXCEPTION
   WHEN NO_DATA_FOUND
   THEN
      DBMS_OUTPUT.put_line (‘Not found’);
END;

In this example, since the employee_id is the primary key of the employees table, there is no need to code for the TOO_MANY_ROWS exception.

Andrew Clarke has a good post about implicit and explicit cursors in response to Misbah Jalil’s post about the same subject. And here is a virtual book about cursors in Oracle DB 10gR2.

Possibly related:

Tagged | Post a Comment