Choosing Between Explicit and Implicit Cursors

In years past, it was common for "Oracle gurus" (including yours truly) to solemnly declare that you should never use implicit cursors for single-row fetches, and then explain that implicit cursors follow the ANSI standard and always perform two fetches, making them less efficient than explicit cursors (for which you can just fetch a single time).

The first two editions of this book repeated that "wisdom," but it is time to break from tradition. The bottom line is that from Oracle8 onwards, as a result of Oracle optimization, it is very likely that your implicit cursor will run more—not less—efficiently than the equivalent explicit cursor.

So does that mean that you should now always use implicit cursors? Not at all. There are still good reasons to useexplicit cursors, including the following:

· In some cases, explicit cursors will be more efficient. You should test your critical, often-executed queries in both formats to see which will be better in that particular situation.

· Explicit cursors offer much tighter programmatic control. If a row is not found, for example, Oracle will not raise an exception, instead forcing the execution block to shut down.

I suggest that the question to answer is not "implicit or explicit?," but rather, "encapsulate or expose?" And the answer is (new wisdom revealed):

You should always encapsulate your single-row query, hiding the query behind a function interface, and passing back the data through the RETURN clause.

Don't worry about explicit versus implicit. Worry about how you can tune and maintain your code if single-row queries are duplicated throughout your code. Take the time to encapsulate them behind functions, preferably package-based functions.

14.2 Working with Implicit Cursors

PL/SQL declares and manages animplicit cursor every time you execute a SQL DML statement (INSERT, UPDATE, or DELETE) or a SELECT INTO that returns a single row from the database directly into a PL/SQL data structure. This kind of cursor is called implicit because Oracle implicitly or automatically handles many of the cursor-related operations for you, such as allocation of a cursor, opening the cursor, fetching, and so on.

The implicit DML statements are covered in Chapter 13. This chapter is concerned only with the implicit SQL query.

An implicit cursor is a SELECT statement that has the following special characteristics:

· The SELECT statement appears in the executable section of your block; it is not defined in the declaration section, as explicit cursors are.

· The query contains an INTO clause (or BULK COLLECT INTO for bulk processing). The INTO clause is a part of the PL/SQL (not the SQL) language and is the mechanism used to transfer data from the database into local PL/SQL data structures.

· You do not open, fetch, or close the SELECT statement; all of these operations are done for you.

The general structure of an implicit query is as follows:

SELECT column_list [BULK COLLECT] INTO PL/SQL variable list ...rest of SELECT statement...

If you use an implicit cursor, Oracle performs the open, fetches, and close for you automatically; these actions are outside your programmatic control. You can, however, obtain information about the most recently executed SQL statement by examining the values in the implicit SQL cursor attributes, as explained later in this chapter.

In the following sections, the term implicit cursor means a SELECT INTO statement that retrieves (or attempts to retrieve) a single row of data. Later we'll discuss the SELECT BULK COLLECT INTO variation that allows you to retrieve multiple rows of data with a single implicit query.