Opening Explicit Cursors

The first step in using a cursor is to define it in the declaration section. The next step is to open that cursor. The syntax for the OPEN statement is simplicity itself:

where cursor_name is the name of the cursor you declared and the arguments are the values to be passed if the cursor was declared with a parameter list.

Oracle also offers the OPEN cursor FOR syntax, which is utilized in both cursor variables (see Section 14.6) and native dynamic SQL (see Chapter 15).

When you open a cursor, PL/SQL executes the query for that cursor. It also identifies the active set of data—that is, the rows from all involved tables that meet the criteria in the WHERE clause and join conditions. The OPEN does not actually retrieve any of these rows—that action is performed by the FETCH statement.

Regardless of when you perform the first fetch, however, the read consistency model in the Oracle RDBMS guarantees that all fetches will reflect the data as it existed when the cursor was opened. In other words, from the moment you open your cursor until the moment that cursor is closed, all data fetched through the cursor will ignore any inserts, updates, and deletes performed after the cursor was opened.

Furthermore, if the SELECT statement in your cursor uses a FOR UPDATE clause, all the rows identified by the query are locked when the cursor is opened. (This feature is covered in Section 14.5 later in this chapter.)

If you try to open a cursor that is already open you will get the following error:

You can be sure of a cursor's status by checking the %ISOPEN cursor attribute before you try to open the cursor:

The later section Section 14.3.6 explains the different cursor attributes and how to best make use of them in your programs.

If you are using a cursor FOR loop, you do not need to open the cursor explicitly. Instead, the PL/SQL engine does that for you.

• ⇐ Назад
• 149
• 150
• 151
• 152
• 153
• 154
• 155156157
• 158
• 159
• 160
• 161
• 162
• 163
• 164
• Далее ⇒