Implicit Cursor Examples

A common use of implicit cursors is to perform a lookup based on a primary key. In the following example, I look up the title of a book based on its ISBN number:

DECLARE l_title book.title%TYPE;BEGIN SELECT title INTO l_title FROM book WHERE isbn = '0-596-00121-5';

Once I have fetched the title into my local variable, l_title, I can manipulate that information—for example, by changing the variable's value, displaying the title, or passing the title on to another PL/SQL program for processing.

Here is an example of an implicit query that retrieves an entire row of information into a record:

DECLARE l_book book%ROWTYPE;BEGIN SELECT * INTO l_book FROM book WHERE isbn = '0-596-00121-5';

You can also retrieve group-level information from a query. The following single-row query calculates and returns the total salary for a department. Once again, PL/SQL creates an implicit cursor for this statement:

SELECT SUM (salary) INTO department_total FROM employee WHERE department_number = 10;

Because PL/SQL is so tightly integrated with the Oracle database, you can also easily retrieve complex datatypes, such as objects and collections, within your implicit cursor.

All of these illustrate the use of implicit queries to retrieve a single row's worth of information. If you want to retrieve more than one row, you must use either an explicit cursor for that query or the BULK COLLECT INTO clause (available only in Oracle8i and above) in your query. Both of these approaches are discussed later in this chapter.

As mentioned earlier, I recommend that you always "hide" single-row queries like those shown above behind a function interface. This concept was explored in detail in Section 14.1.5.