Section 15.2.  Working with Implicit Cursors

15.2. Working with Implicit Cursors PL/SQL declares and manages an implicit 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 allocating a cursor, opening the cursor, fetching, and so on. The implicit DML statements are covered in Chapter 14. This chapter is concerned only with the implicit SQL query. An implicit cursor is a SELECT statement that has these 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. 15.2.1. 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 informationfor 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 Database and above) in your query. Both 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 the section, "Choosing Between Explicit and Implicit Cursors." 15.2.2. Error Handling with Implicit Cursors The implicit cursor version of the SELECT statement is kind of a black box. You pass the SQL statement to the SQL engine in the database, and it returns a single row of information. You can't get inside the separate operations of the cursor, such as the open, fetch, and close stages. You are also stuck with the fact that Oracle automatically raises exceptions from within the implicit SELECT for two common outcomes: The query does not find any rows matching your criteria. In this case, Oracle raises the NO_DATA_FOUND exception. The SELECT statement returns more than one row. In this case, Oracle raises the TOO_MANY_ROWS exception. When either of these scenarios occurs (as well as any other exceptions raised when executing a SQL statement), execution of the current block terminates and control is passed to the exception section. You have no control over this process flow; you cannot tell Oracle that with this implicit cursor you actually expect to not find any rows, and it is not an error. Instead, whenever you code an implicit cursor, you should include an exception section that traps and handles these two exceptions (and perhaps others, depending on your application logic). In the following block of code, I query the title of a book based on its ISBN number, but I also anticipate the possible problems that arise: DECLARE l_isbn book.isbn%TYPE := '0-596-00121-5'; l_title book.title%TYPE; BEGIN SELECT title INTO l_title FROM book WHERE isbn = l_isbn; EXCEPTION WHEN NO_DATA_FOUND THEN display.line ('Unknown book: ' || l_isbn); WHEN TOO_MANY_ROWS THEN errpkg.record_and_stop ('Data integrity error for: ' || l_isbn); RAISE; END; One of the problems with using implicit queries is that there is an awesome temptation to make assumptions about the data being retrieved, such as: "There can never possibly be more than one row in the book table for a given ISBN; we have constraints in place to guarantee that." "There will always be an entry in the book table for Steven and Bill's Oracle PL/SQL Programming. I don't have to worry about NO_DATA_FOUND." The consequence of such assumptions is often that we neglect to include exception handlers for our implicit queries. Now, it may well be true that today, with the current set of data, a query will return only a single row. If the nature of the data ever changes, however, you may find that the SELECT statement that formerly identified a single row now returns several. Your program will raise an exception, the exception will not be properly handled, and this could cause problems in your code. You should, as a rule, always include handlers for NO_DATA_FOUND and TOO_MANY_ROWS whenever you write an implicit query. More generally, you should include error handlers for any errors that you can reasonably anticipate will occur in your program. The action you take when an error does arise will vary. Consider the code that retrieves a book title for an ISBN. In the function below, notice that my two error handlers act very differently: NO_DATA_FOUND returns a value, while TOO_MANY_ROWS logs the error and reraises the exception, causing the function to actually fail. (See Chapter 6 for more information about the errpkg.pkg package.) CREATE OR REPLACE FUNCTION book_title ( isbn_in IN book.isbn%TYPE ) RETURN book.title%TYPE IS return_value book.title%TYPE; BEGIN SELECT title INTO return_value FROM book WHERE isbn = l_isbn; RETURN return_value; EXCEPTION WHEN NO_DATA_FOUND THEN RETURN NULL; WHEN TOO_MANY_ROWS THEN errpkg.record_and_stop ( 'Data integrity error for: ' || l_isbn); RAISE; END; Here is the reasoning behind these varied treatments: the point of my function is to return the name of a book, which can never be NULL. The function can also be used to validate an ISBN (e.g., "does a book exist for this ISBN?"). For this reason, I really don't want my function to raise an exception when no book is found for an ISBN; that may actually constitute a successful condition, depending on how the function is being used. The logic may be, "If a book does not exist with this ISBN, then it can be used for a new book," which might be coded as: IF book_title ('0-596-00121-7') IS NULL THEN ... In other words, the fact that no book exists for that ISBN is not an error and should not be treated as one within my general lookup function. On the other hand, if the query raises the TOO_MANY_ROWS exception, I have a real problem: there should never be two different books with the same ISBN number. So in this case, I need to log the error and then stop the application. 15.2.3. Implicit SQL Cursor Attributes Oracle allows you to access information about the most recently executed implicit cursor by referencing the special implicit cursor attributes shown in Table 15-2. The table describes the significance of the values returned by these attributes for an implicit SQL query (SELECT INTO). Because the cursors are implicit, they have no name, and therefore, the keyword "SQL" is used to denote the implicit cursor. Table 15-2. Implicit SQL cursor attributes for queries Name Description SQL%FOUND Returns TRUE if one row (or more in the case of BULK COLLECT INTO) was fetched successfully, FALSE otherwise (in which case Oracle will also raise the NO_DATA_FOUND exception). SQL%NOTFOUND Returns TRUE if a row was not fetched successfully (in which case Oracle will also raise the NO_DATA_FOUND exception), FALSE otherwise. SQL%ROWCOUNT Returns the number of rows fetched from the specified cursor. For a SELECT INTO, this will be 1 if a row was found and 0 if Oracle raises the NO_DATA_FOUND exception. SQL%ISOPEN Always returns FALSE for implicit cursors because Oracle opens and closes implicit cursors atomically. All the implicit cursor attributes return NULL if no implicit cursors have yet been executed in the session. Otherwise, the values of the attributes always refer to the most recently executed SQL statement, regardless of the block or program from which the SQL statement was executed. For more information about this behavior, see the "Cursor Attributes for DML Operations" section in Chapter 14. You can also run the query_implicit_attributes.sql script on the book's web site to test out these values yourself. Let's make sure we understand the implications of this last point. Consider the following two programs: CREATE OR REPLACE PROCEDURE remove_from_circulation (isbn_in in book.isbn%TYPE) IS BEGIN DELETE FROM book WHERE isbn = isbn_in; END; CREATE OR REPLACE PROCEDURE show_book_count IS l_count INTEGER; BEGIN SELECT COUNT (*) INTO l_count FROM book; -- No such book! remove_from_circulation ('0-000-00000-0'); DBMS_OUTPUT.put_line (SQL%ROWCOUNT); END; No matter how many rows of data are in the book table, we will always see "0" displayed in the output window. Because I call remove_from_circulation after my SELECT INTO statement, the SQL%ROWCOUNT reflects the outcome of my silly, impossible DELETE statement, and not the query. If you want to make certain that you are checking the values for the right SQL statement, you should save attribute values to local variables immediately after execution of the SQL statement. I demonstrate this technique in the following example: CREATE OR REPLACE PROCEDURE show_book_count IS l_count INTEGER; l_numfound PLS_INTEGER; BEGIN SELECT COUNT (*) INTO l_count FROM book; -- Take snapshot of attribute value: l_numfound := SQL%ROWCOUNT; -- No such book! remove_from_circulation ('0-000-00000-0'); -- Now I can go back to the previous attribute value. DBMS_OUTPUT.put_line (l_foundsome); END;