Database Reference
In-Depth Information
Figure 24.6
Using an Explicit
Cursor.
Following are three example PL/SQL anonymous blocks: the first con-
tains INSERT and UPDATE statements, the second a SELECT … INTO
statement, and the third a cursor FOR loop.
24.4.2.1
The Internal SQL Implicit Cursor
The results of the most recently executed implicit cursor are stored in an
internal Oracle cursor called SQL. Note how the first example uses
SQL%NOTFOUND to decide on executing the INSERT statement. The
block begins by updating all rows in the ARTIST table where the artist's
name is the band called Chicago. We know that there are no artists by that
name in our MUSIC database. The update does not succeed, and the
implicit cursor used for the update has a status of NOTFOUND. The
block checks the implicit cursor status using SQL%NOTFOUND and
inserts a row into the artist table with a name of Chicago.
In Figure 24.7, you can see output at the end displaying one occurrence
of “Inserted Chicago” and two occurrences of “Updated Chicago.” The
anonymous block is executed three times. The IF statement is used in this
script, the syntax of which is explained later in this chapter.
