|
Chapter 6 Database Interaction and Cursors |
|
Let's take a closer look at implicit and explicit cursors and the ways you can put them in your programs.
PL/SQL issues an implicit cursor whenever you execute a SQL statement directly in your code, as long as that code does not employ an explicit cursor. It is called an "implicit" cursor because you, the developer, do not explicitly declare a cursor for the SQL statement.
If you use an implicit cursor, Oracle performs the open, fetches, and close for you automatically; these actions are outside of 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.
PL/SQL employs an implicit cursor for each UPDATE, DELETE, or INSERT statement you execute in a program. You cannot, in other words, execute these statements within an explicit cursor, even if you want to. You have a choice between using an implicit or explicit cursor only when you execute a single-row SELECT statement (a SELECT that returns only one row).
In the following UPDATE statement, which gives everyone in the company a 10% raise, PL/SQL creates an implicit cursor to identify the set of rows in the table which would be affected by the update:
UPDATE employee SET salary = salary * 1.1;
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;
If you have a SELECT statement that returns more than one row, you must use an explicit cursor for that query and then process the rows returned one at a time. PL/SQL does not yet support any kind of array interface between a database table and a composite PL/SQL datatype such as a PL/SQL table.
Even if your query returns only a single row, you might still decide to use an explicit cursor. The implicit cursor has the following drawbacks:
It is less efficient than an explicit cursor (in PL/SQL Release 2.2 and earlier)
It is more vulnerable to data errors
It gives you less programmatic control
The following sections explore each of these limitations to the implicit cursor.
An explicit cursor is, at least theoretically, more efficient than an implicit cursor (in PL/SQL Release 2.2 and earlier). An implicit cursor executes as a SQL statement and Oracle's SQL is ANSI-standard. ANSI dictates that a single-row query must not only fetch the first record, but must also perform a second fetch to determine if too many rows will be returned by that query (such a situation will RAISE the TOO_MANY_ROWS PL/SQL exception). Thus, an implicit query always performs a minimum of two fetches, while an explicit cursor only needs to perform a single fetch.
This additional fetch is usually not noticeable, and you shouldn't be neurotic about using an implicit cursor for a single-row query (it takes less coding, so the temptation is always there). Look out for indiscriminate use of the implicit cursor in the parts of your application where that cursor will be executed repeatedly. A good example is the Post-Query trigger in the Oracle Forms.
Post-Query fires once for each record retrieved by the query (created from the base table block and the criteria entered by the user). If a query retrieves ten rows, then an additional ten fetches are needed with an implicit query. If you have 25 users on your system all performing a similar query, your server must process 250 additional (unnecessary) fetches against the database. So, while it might be easier to write an implicit query, there are some places in your code where you will want to make that extra effort and go with the explicit cursor.
NOTE: In PL/SQL Release 2.3 and above, the implicit cursor has been optimized so that it may, in isolation, run faster than the corresponding explicit cursor. Generally, the differences between these two approaches from a performance standpoint are negligible. On the other hand, if you use an explicit cursor, you are more likely (or at least able ) to reuse that cursor, which increases the chance that it will be pre-parsed in shared memory when needed -- thereby improving the performance of your application as a whole.
If an implicit SELECT statement returns more than one row, it raises the TOO_MANY_ROWS exception. When this happens, execution in the current block terminates and control is passed to the exception section. Unless you deliberately plan to handle this scenario, use of the implicit cursor is a declaration of faith. You are saying, "I trust that query to always return a single row!"
It may well be that today, with the current data, the query will only return a single row. If the nature of the data ever changes, however, you may find that the SELECT statement which formerly identified a single row now returns several. Your program will raise an exception. Perhaps this is what you will want. On the other hand, perhaps the presence of additional records is inconsequential and should be ignored.
With the implicit query, you cannot easily handle these different possibilities. With an explicit query, your program will be protected against changes in data and will continue to fetch rows without raising exceptions.
The implicit cursor version of a SELECT statement is a black box. You pass the SQL statement to the SQL layer in the database and it returns (you hope) a single row. You can't get inside the separate operations of the cursor, such as the open and close stages. You can't examine the attributes of the cursor -- to see whether a row was found, for example, or if the cursor has already been opened. You can't easily apply traditional programming control constructs, such as an IF statement, to your data access.
Sometimes you don't need this level of control. Sometimes you just think you don't need this level of control. I have found that if I am going to build programs in PL/SQL, I want as much control as I can possibly get.
An explicit cursor is a SELECT statement that is explicitly defined in the declaration section of your code and, in the process, assigned a name. There is no such thing as an explicit cursor for UPDATE, DELETE, and INSERT statements.
With explicit cursors, you have complete control over how to access information in the database. You decide when to OPEN the cursor, when to FETCH records from the cursor (and therefore from the table or tables in the SELECT statement of the cursor) how many records to fetch, and when to CLOSE the cursor. Information about the current state of your cursor is available through examination of the cursor attributes. This granularity of control makes the explicit cursor an invaluable tool for your development effort.
Let's look at an example. The following anonymous block looks up the employee type description for an employee type code:
1 DECLARE 2 /* Explicit declaration of a cursor */ 3 CURSOR emptyp_cur IS 4 SELECT emptyp.type_desc 5 FROM employees emp, employee_type emptyp 6 WHERE emp.type_code = emptyp.type_code; 7 BEGIN 8 /* Check to see if cursor is already open. If not, open it. */ 9 IF NOT emptyp_cur%ISOPEN 10 THEN 11 OPEN emptyp_cur; 12 END IF; 13 14 /* Fetch row from cursor directly into an Oracle Forms item */ 15 FETCH emptyp_cur INTO :emp.type_desc; 16 17 /* Close the cursor */ 18 CLOSE emptyp_cur; 19 END;
This PL/SQL block performs the following cursor actions:
|
Action |
Line(s) |
|---|---|
|
Declare the cursor |
3 |
|
Open the cursor (if not already open) |
9, 11 |
|
Fetch one or more rows from the cursor |
15 |
|
Close the cursor |
18 |
The next few sections examine each of these steps in more detail. For the remainder of this chapter, unless noted otherwise, the word "cursor" refers to the explicit cursor.
|
|
|
|
| 6.2 Cursors in PL/SQL |
|
6.4 Declaring Cursors |
Copyright (c) 2000 O'Reilly & Associates. All rights reserved.