Section 2.2. Using Explicit Cursors Versus Implicit Cursors

2.2. Using Explicit Cursors Versus Implicit Cursors

A topic that has inspired much debate over the years is the choice between explicit and implicit cursorsor put another way, the "OPEN, FETCH, CLOSE" versus the "SELECT INTO" debate. In this section, I will completely set aside the debate about performance because Oracle has done a lot of work in recent releases to render the point moot. Instead, I'll focus on the effects on the database and discuss the differing PL/SQL usage of both cursor types including the fact they don't always match up in the database's shared pool.

2.2.1. What's the Difference?

In PL/SQL, an implicit cursor is one that is defined as it is being executed. Here's an example:

     DECLARE       v_date DATE;     BEGIN       SELECT order_date         INTO v_date         FROM orders        WHERE order_number = 100;     END;

As the code was executing, it created a cursor to select the order_date for order 100. Thus, the cursor was implicitly defined when the code executed.

An explicit cursor is one that is defined before it actually gets executed. Here's a simple example:

     DECLARE       CURSOR curs_get_od IS       SELECT order_date         FROM orders        WHERE order_number = 100;       v_date DATE;     BEGIN       OPEN curs_get_od;       FETCH curs_get_od INTO v_date;       CLOSE curs_get_od;     END;

The implicit version was much easier to write and required a lot less typing, so the initial reaction may be to go with that choice. However, explicit cursors have other benefits that make the extra typing worthwhile within PL/SQL code, as described in the next two sections.

2.2.2. Cursor Attributes

A key benefit of explicit cursors is the attributes they provide to facilitate logical programming. Consider the following example. Here, we want to look for an order and do something if it is found. The first procedure using implicit cursors has to rely on exception handling to determine whether a record was found or not.

     CREATE OR REPLACE PROCEDURE demo AS       v_date      DATE;       v_its_there BOOLEAN := TRUE;     BEGIN       BEGIN         SELECT order_date           INTO v_date           FROM orders          WHERE order_number = 1;       EXCEPTION         WHEN no_data_found THEN           v_its_there := FALSE;         WHEN OTHERS THEN           RAISE;       END;       IF NOT v_its_there THEN         do_something;       END IF;     END;

The following code, now using explicit cursors, is easier to follow because the availability of the cursor 's %NOTFOUND attribute makes it obvious what is being checked. There is also no need to embed extra PL/SQL blocks (BEGIN-END) just to handle logic.

     CREATE OR REPLACE PROCEDURE demo AS       CURSOR curs_get_date IS       SELECT order_date         FROM orders        WHERE order_number = 1;       v_date DATE;     BEGIN       OPEN curs_get_date;       FETCH curs_get_date INTO v_date;       IF curs_get_date%NOTFOUND THEN         do_something;       END IF;       CLOSE curs_get_date;     END;

Oracle supports the following cursor attributes:

Attribute	Description
%BULK_ROWCOUNT	Number of records returned by a bulk fetch (BULK COLLECT INTO) operation.
%FOUND	TRUE if the last FETCH was successful, FALSE if not.
%NOTFOUND	TRUE if the last FETCH was not successful, FALSE if it was.
%ISOPEN	TRUE if the cursor is open, FALSE if not.
%ROWCOUNT	Number of the record currently fetched from the cursor.

You probably are aware that some of these attributes are available for implicit cursors, as well. However, they lend themselves better to programming logic with explicit cursors, especially when you are using multiple cursors as shown in this brief example:

     IF curs_get_order%ROWCOUNT = 1 THEN       IF curs_get_details%FOUND THEN         process_order_detail;

2.2.3. Cursor Parameters

As I mentioned earlier in this chapter, you can further promote cursor reuse in PL/SQL by parameterizing your cursors. Here's my simple order_date procedure with a parameterized cursor:

     DECLARE       CURSOR curs_get_od ( cp_on NUMBER ) IS        SELECT order_date          FROM orders         WHERE order_number = cp_on;        v_date DATE;      BEGIN        OPEN curs_get_od(100);       FETCH curs_get_od INTO v_date;       CLOSE curs_get_od;     END;

If later on in the program I wanted to get order 200, 300, and 500, I could simply reopen the cursor. Doing so promotes cursor reuse within the PL/SQL program itself as well as in the shared pool.

2.2.4. Mixing but not Matching

Explicit and implicit cursors do not match up in the shared pool. What do I mean by that? I'll explain with an example.

     DECLARE       CURSOR get_region IS       SELECT region_id FROM orders WHERE region_id = 2;       v_region NUMBER;     BEGIN       OPEN get_region;       FETCH get_region INTO v_region;       CLOSE get_region;       SELECT region_id INTO v_region FROM orders WHERE region_id = 2;     END;

How many shared pool cursors does this produce? The answer is two.

     SQL_TEXT                       PARSE_CALLS EXECUTIONS     ------------------------------ ----------- ----------     SELECT REGION_ID FROM ORDERS W           1          1     HERE REGION_ID = 2     SELECT REGION_ID FROM ORDERS W           1          1     HERE REGION_ID = 2

Even though these cursors look identical and share the same address in the shared pool, they differ just enough (perhaps the INTO clause?) for Oracle to store two separate ones. The moral is that you can't assume that explicit and implicit cursors will match up in the shared pool. It's best to stick with one way or the other.