Cursors in PL/SQL
Implicit and Explicit Cursors
Fetching from Cursors
Column Aliases in Cursors
SELECT FOR UPDATE in Cursors
Working with Cursors
PL/SQL is tightly integrated with the Oracle database via the SQL language. From within PL/SQL, you can execute any DML (data manipulation language) statements, including INSERTs, UPDATEs, DELETEs, and, of course, queries. You can also join multiple SQL statements together logically as a transaction, so that they are either saved ("committed" in SQL parlance) together or rejected in their entirety (rolled back). This chapter examines the SQL statements available inside PL/SQL to manage transactions. It then moves on to cursors, which give you a way to fetch and process database information in your PL/SQL program.
The Oracle RDBMS provides a very robust transaction model, as you might expect for a relational database. You (or more precisely, your application code) determine what constitutes a transaction, the logical unit of work that must be either saved together with a COMMIT statement or rolled back together with a ROLLBACK statement. A transaction begins implicitly with the first SQL statement issued since the last COMMIT or ROLLBACK (or with the start of a session).
PL/SQL provides the following statements for transaction management:
Saves all outstanding changes since the last COMMIT or ROLLBACK and releases all locks.
Erases all outstanding changes since the last COMMIT or ROLLBACK and releases all locks.
Erases all changes made since the specified savepoint was established.
Establishes a savepoint, which then allows you to perform partial ROLLBACKs.
Allows you to begin a read-only or read-write session, establish an isolation level, or assign the current transaction to a specified rollback segment.
Allows you to lock an entire database table in the specified mode. This overrides the default row-level locking usually applied to a table.
These statements are explained in more detail in the following sections.
When you COMMIT, you make permanent any changes made by your session to the database in the current transaction. Once you commit, your changes will be visible to other Oracle sessions or users. The syntax for the COMMIT statement is:
COMMIT [WORK] [COMMENT text];
The COMMENT keyword specifies a comment which is then associated with the current transaction. The text must be a quoted literal and can be no more than 50 characters in length. The COMMENT text is usually employed with distributed transactions. This text can be handy for examining and resolving in-doubt transactions within a two-phase commit framework. It is stored in the data dictionary along with the transaction ID.
Once you COMMIT your changes, you cannot roll them back with a ROLLBACK statement.
The following statements are all valid uses of the COMMIT:
COMMIT; COMMIT WORK; COMMIT COMMENT 'maintaining account balance'.
When you ROLLBACK, you undo some or all changes made by your session to the database in the current transaction. Why would you want to erase changes? From an ad hoc SQL standpoint, the ROLLBACK gives you a way to erase mistakes you might have made, as in:
DELETE FROM orders;
"No, no! I meant to delete only the orders before May 1995!" No problem, just issue ROLLBACK. From an application coding standpoint, ROLLBACK is important because it allows you to clean up or restart from a "clean state" when a problem occurs.
The syntax for the ROLLBACK statement is:
ROLLBACK [WORK] [TO [SAVEPOINT] savepoint_name];
There are two basic ways to use ROLLBACK: without parameters or with the TO clause to indicate a savepoint at which the ROLLBACK should stop.
The parameterless ROLLBACK undoes all outstanding changes in your transaction.
The ROLLBACK TO version allows you to undo all changes and release all acquired locks which were issued since the savepoint identified by savepoint_name was marked (see the next section on the SAVEPOINT statement for more information on how to mark a savepoint in your application).
The savepoint_name is an undeclared Oracle identifier. It cannot be a literal (enclosed in quotes) or variable name.
All of the following uses of ROLLBACK are valid:
ROLLBACK; ROLLBACK WORK; ROLLBACK TO begin_cleanup;
All of the following uses of ROLLBACK are invalid:
ROLLBACK SAVEPOINT; -- ORA-02181: invalid option to ROLLBACK WORK -- Must use TO keyword before SAVEPOINT. ROLLBACK WORK TO; -- ORA-02182: save point name expected -- Must specify savepoint name. ROLLBACK TO SAVEPOINT 'favorite_movies'; -- ORA-03001: Unimplemented feature -- Savepoint cannot be in quotes.
When you roll back to a specific savepoint, all savepoints issued after the specified savepoint_name are erased. The savepoint to which you roll back is not, however, erased. This means that you can restart your transaction from that point and, if necessary, roll back to that same savepoint if another error occurs.
Immediately before you execute an INSERT, UPDATE, or DELETE, PL/SQL implicitly generates a savepoint. If your DML statement then fails, a rollback is automatically performed to that implicit savepoint. In this way, only that last DML statement is undone.
SAVEPOINT gives a name to and marks a point in the processing of your transaction. This marker allows you to ROLLBACK TO that point, erasing any changes and releasing any locks issued after that savepoint, but preserving any changes and locks which occurred before you marked the savepoint.
The syntax for the SAVEPOINT statement is:
where savepoint_name is an undeclared identifier. This means that it must conform to the rules for an Oracle identifier (up to 30 characters in length, starting with a letter, containing letters, numbers and #, $, or _ ), but that you do not need to (nor can you) declare that identifier.
Savepoints are not scoped to PL/SQL blocks. If you reuse a savepoint name within the current transaction, that savepoint is "moved" from its original position to the current point in the transaction, regardless of the procedure, function, or anonymous block in which the SAVEPOINT statements are executed. As a corollary, if you issue a SAVEPOINT inside a recursive program, a new SAVEPOINT is executed at each level of recursion, but you can only roll back to the most recently marked savepoint.
The SET TRANSACTION statement allows you to begin a read-only or read-write session, establish an isolation level, or assign the current transaction to a specified rollback segment. This statement must be the first SQL statement processed in a transaction and it can appear only once. This statement comes in the following four flavors:
SET TRANSACTION READ ONLY;
This version defines the current transaction as read-only. In a read-only transaction, all subsequent queries only see those changes which were committed before the transaction began (providing a read-consistent view across tables and queries). This statement is useful when you are executing long-running, multiple query reports and you want to make sure that the data used in the report is consistent:
SET TRANSACTION READ WRITE;
This version defines the current transaction as read-write:
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE|READ COMMITTED;
This version defines how transactions that modify the database should be handled. You can specify a serializable or read-committed isolation level. When you specify SERIALIZABLE, a data manipulation statement (update, insert, delete) which attempts to modify a table already modified in an uncommitted transaction will fail. To execute this command, you must set the database initialization parameter COMPATIBLE to 7.3.0 or higher.
If you specify READ COMMITTED, a DML which requires row-level locks held by another transaction will wait until those row locks are released:
SET TRANSACTION USE ROLLBACK SEGMENT rollback_segname;
This version assigns the current transaction to the specified rollback segment and establishes the transaction as read-write. This statement cannot be used in conjunction with SET TRANSACTION READ ONLY.
This statement allows you to lock an entire database table with the specified lock mode. By doing this, you can share or deny access to that table while you perform operations against it. The syntax for this statement is:
LOCK TABLE table_reference_list IN lock_mode MODE [NOWAIT];
where table_reference_list is a list of one or more table references (identifying either a local table/view or a remote entity through a database link), and lock_mode is the mode of the lock, which can be one of the following:
|SHARE ROW EXCLUSIVE|
If you specify the NOWAIT keyword, Oracle will not wait for the lock if the table has already been locked by another user. If you leave out the NOWAIT keyword, Oracle waits until the table is available (and there is no set limit on how long Oracle will wait). Locking a table never stops other users from querying or reading the table.
The following LOCK TABLE statements show valid variations:
LOCK TABLE emp IN ROW EXCLUSIVE MODE; LOCK TABLE emp, dept IN SHARE MODE NOWAIT; LOCK TABLE scott.emp@new_york IN SHARE UPDATE MODE;
Now that you know the "macro" commands for managing transactions from within a PL/SQL application, let's move on to cursors; you will use cursors (in one form or another) to create transactions (i.e., specify the SQL statements which make up the transaction).
Copyright (c) 2000 O'Reilly & Associates. All rights reserved.