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:
OPEN cursor_name [ ( argument [, argument ...]) ];where cursor_name is the name of the cursor you declared and the argument s are the values to be passed if the cursor was declared with a parameter list.
|
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:
ORA-06511: PL/SQL: cursor already openYou can be sure of a cursor's status by checking the %ISOPEN cursor attribute before you try to open the cursor:
IF NOT company_cur%ISOPENTHEN OPEN company_cur;END IF;The later section Section 14.3.6 explains the different cursor attributes and how to best make use of them in your programs.
|
