|
Chapter 1 Oracle PL/SQL Language Pocket Reference |
|
PL/SQL is tightly integrated with the underlying SQL layer of the Oracle database. You can execute SQL statements (UPDATE, INSERT, DELETE, and SELECT) directly in PL/SQL programs. You can also execute Data Definition Language (DDL) statements through the use of dynamic SQL (DBMS_SQL in Oracle7 and Oracle8, native dynamic SQL in Oracle8 i ). In addition, you can manage transactions with COMMIT, ROLLBACK, and other Data Control Language (DCL) statements.
The Oracle RDBMS provides a transaction model based on a unit of work. The PL/SQL language supports most, but not all, of the database model for transactions (you cannot, for example, ROLLBACK FORCE). Transactions begin with the first change to data and end with either a COMMIT or ROLLBACK. Transactions are independent of PL/SQL blocks. Transactions can span multiple PL/SQL blocks, or there can be multiple transactions in a single PL/SQL block. The PL/SQL supported transaction statements are: COMMIT, ROLLBACK, SAVEPOINT, SET TRANSACTION, and LOCK TABLE. Each is detailed here:
COMMIT [WORK] [COMMENT text];
COMMIT makes the database changes permanent and visible to other database sessions. The WORK keyword is optional and only aids readability -- it is rarely used. The COMMENT text is optional and can be up to 50 characters in length. It is only germane to in-doubt distributed (two-phase commit) transactions. The database statement COMMIT FORCE for distributed transactions is not supported in PL/SQL.
ROLLBACK [WORK] [TO [SAVEPOINT] savepoint_name];
ROLLBACK undoes the changes made in the current transaction either to the beginning of the transaction or to a savepoint . A savepoint is a named processing point in a transaction, created with the SAVEPOINT statement. Rolling back to a savepoint is a partial rollback of a transaction, wiping out all changes (and savepoints) that occurred later than the named savepoint.
SAVEPOINT savepoint_name;
SAVEPOINT establishes a savepoint in the current transaction. savepoint_name is an undeclared identifier -- you do not declare it. More than one savepoint can be established within a transaction. If you reuse a savepoint name, that savepoint is moved to the later position and you will not be able to rollback to the initial savepoint position.
SET TRANSACTION READ ONLY; SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; SET TRANSACTION USE ROLLBACK SEGMENT rbseg_name;
SET TRANSACTION has three transaction control functions:
Marks the beginning of a read-only transaction. This indicates to the RDBMS that a read-consistent view of the database is to be enforced for the transaction (the default is for the statement). This read-consistent view means that only changes committed before the transaction begins are visible for the duration of the transaction. The transaction is ended with either a COMMIT or ROLLBACK. Only LOCK TABLE, SELECT, SELECT INTO, OPEN, FETCH, CLOSE, COMMIT, or ROLLBACK statements are permitted during a read-only transaction. Issuing other statements, such as INSERT or UPDATE, in a read-only transaction results in an ORA-1456 error.
Similar to a READ ONLY transaction in that transaction-level read consistency is enforced instead of the default statement-level read consistency. Serializable transactions do allow changes to data, however.
Tells the RDBMS to use the specifically named rollback segment rbseg_name . This statement is useful when only one rollback segment is large and a program knows that it needs to use the large rollback segment, such as during a month-end close operation. For example, if we know our large rollback segment is named rbs_large , we can tell the database to use it by issuing the following statement before our first change to data:
SET TRANSACTION USE ROLLBACK SEGMENT rbs_large;
LOCK TABLE table_list IN lock_mode MODE [NOWAIT];
This statement bypasses the implicit database row-level locks by explicitly locking one or more tables in the specified mode. The table_list is a comma-delimited list of tables. The lock_mode is one of ROW SHARE, ROW EXCLUSIVE, SHARE UPDATE, SHARE, SHARE ROW EXCLUSIVE, or EXCLUSIVE. The NOWAIT keyword specifies that the RDBMS should not wait for a lock to be released. If there is a lock when NOWAIT is specified, the RDBMS raises the exception "ORA-00054: resource busy and acquire with NOWAIT specified". The default RDBMS locking behavior is to wait indefinitely.
Native dynamic SQL introduces a new PL/SQL statement, EXECUTE IMMEDIATE, and new semantics for the OPEN FOR, FETCH, and CLOSE statement family. The former applies to single-row queries and DDL, while the latter supports dynamic multi-row queries. The syntax for these statements is:
EXECUTE IMMEDIATE SQL_statement_string [INTO { define_variable_list | record | object_variable }] [USING [IN | OUT | IN OUT] bind_argument_list]; OPEN cursor_variable FOR SELECT_statement_string; FETCH cursor_variable INTO {define_variable_list | record | object_variable}; CLOSE cursor_variable;
The EXECUTE IMMEDIATE statement parses and executes the SQL statement in a single step. It can be used for any SQL statement except a multi-row query. define_variable_list is a comma-delimited list of variable names; the bind_argument_list is a comma-delimited list of bind arguments. The parameter mode is optional and defaults to IN. Do not place a trailing semicolon in the SQL_statement_string .
This is the statement that can be used to execute DDL without the DBMS_SQL package. For example:
EXECUTE IMMEDIATE 'TRUNCATE TABLE foo'; EXECUTE IMMEDIATE 'GRANT SELECT ON '|| tabname_v || ' TO ' || grantee_list;
The OPEN FOR statement assigns a multi-row query to a weakly typed cursor variable. The rows are then FETCHed and the cursor CLOSEd.
DECLARE TYPE cv_typ IS REF CURSOR; cv cv_typ; laccount_no NUMBER; lbalance NUMBER; BEGIN OPEN cv FOR 'SELECT account_no, balance FROM accounts WHERE balance < 500'; LOOP FETCH cv INTO laccount_no, lbalance; EXIT WHEN cv%NOTFOUND; -- Process the row. END LOOP; CLOSE cv; END;
Autonomous transactions execute within a block of code as separate transactions from the outer (main) transaction. Changes can be committed or rolled back in an autonomous transaction without committing or rolling back the main transaction. Changes committed in an autonomous transaction are visible to the main transaction, even though they occur after the start of the main transaction. Changes committed in an autonomous transaction are visible to other transactions as well. The RDBMS suspends the main transaction while the autonomous transaction executes:
PROCEDURE main IS BEGIN UPDATE ...-- Main transaction begins here. DELETE ... at_proc; -- Call the autonomous transaction. SELECT ... INSERT ... COMMIT; -- Main transaction ends here. END; PROCEDURE at_proc IS PRAGMA AUTONOMOUS_TRANSACTION; BEGIN -- Main transaction suspends here. SELECT ... INSERT ...-- Autonomous transaction begins here. UPDATE ... DELETE ... COMMIT; -- Autonomous transaction ends here. END; -- Main transaction resumes here.
So, changes made in the main transaction are not visible to the autonomous transaction and if the main transaction holds any locks that the autonomous transaction waits for, a deadlock occurs. Using the NOWAIT option on UPDATE statements in autonomous transactions can help to minimize this kind of deadlock. Functions and procedures (local program, standalone, or packaged), database triggers, top-level anonymous PL/SQL blocks, and object methods can be declared autonomous via the compiler directive PRAGMA AUTONOMOUS_TRANSACTION.
In the example below, the COMMIT does not make permanent pending changes in the calling program. Any rollback in the calling program would also have no effect on the changes committed in this autonomous procedure:
CREATE OR REPLACE PROCEDURE add_company ( name_in company.name%TYPE ) IS PRAGMA AUTONOMOUS_TRANSACTION; BEGIN determine_credit(name); create_account(name); ... COMMIT; -- Only commit this procedure's changes. END add_company;
|
|
|
|
| 1.7 Loops | 1.9 Cursors in PL/SQL |
Copyright (c) 2000 O'Reilly & Associates. All rights reserved.