Oracle PL/SQL for DBAs

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.