6.3.1 Implicit Cursors

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.

6.3.2 Drawbacks of Implicit Cursors

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.

6.3.3 Explicit Cursors

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:

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.