6
Interaction Between PL/SQL and Oracle

Knowledge is of two kinds. We know a subject ourselves, or we know where we can find information upon it. --Samuel Johnson

This chapter helps you harness the power of Oracle. You learn how PL/SQL supports the SQL commands, functions, and operators that let you manipulate Oracle data. You also learn how to manage cursors, use cursor variables, and process transactions.

Overview of SQL Support in PL/SQL

By extending SQL, PL/SQL offers a unique combination of power and ease of use. You can manipulate Oracle data flexibly and safely because PL/SQL fully supports all SQL data manipulation statements (except EXPLAIN PLAN), transaction control statements, functions, pseudocolumns, and operators. PL/SQL also supports dynamic SQL, which enables you to execute SQL data definition, data control, and session control statements dynamically. In addition, PL/SQL conforms to the current ANSI/ISO SQL standard.

Data Manipulation

To manipulate Oracle data, you use the INSERT, UPDATE, DELETE, SELECT, and LOCK TABLE commands. INSERT adds new rows of data to database tables; UPDATE modifies rows; DELETE removes unwanted rows; SELECT retrieves rows that meet your search criteria; and LOCK TABLE temporarily limits access to a table.

Transaction Control

Oracle is transaction oriented; that is, Oracle uses transactions to ensure data integrity. A transaction is a series of SQL data manipulation statements that does a logical unit of work. For example, two UPDATE statements might credit one bank account and debit another.

Simultaneously, Oracle makes permanent or undoes all database changes made by a transaction. If your program fails in the middle of a transaction, Oracle detects the error and rolls back the transaction. Thus, the database is restored to its former state automatically.

You use the COMMIT, ROLLBACK, SAVEPOINT, and SET TRANSACTION commands to control transactions. COMMIT makes permanent any database changes made during the current transaction. ROLLBACK ends the current transaction and undoes any changes made since the transaction began. SAVEPOINT marks the current point in the processing of a transaction. Used with ROLLBACK, SAVEPOINT undoes part of a transaction. SET TRANSACTION sets transaction properties such as read/write access and isolation level.

SQL Functions

PL/SQL lets you use all the SQL functions including the following aggregate functions, which summarize entire columns of Oracle data: AVG, COUNT, GROUPING, MAX, MIN, STDDEV, SUM, and VARIANCE. Except for COUNT(*), all aggregate functions ignore nulls.

You can use the aggregate functions in SQL statements, but not in procedural statements. Aggregate functions operate on entire columns unless you use the SELECT GROUP BY statement to sort returned rows into subgroups. If you omit the GROUP BY clause, the aggregate function treats all returned rows as a single group.

You call an aggregate function using the syntax

function_name([ALL | DISTINCT] expression)
 

where expression refers to one or more database columns. If you specify ALL (the default), the aggregate function considers all column values including duplicates. If you specify DISTINCT, the aggregate function considers only distinct values. For example, the following statement returns the number of different job titles in the database table emp:

SELECT COUNT(DISTINCT job) INTO job_count FROM emp;
 

The function COUNT lets you use the asterisk (*) row operator, which returns the number of rows in a table. For example, the following statement returns the number of rows in table emp:

SELECT COUNT(*) INTO emp_count FROM emp;

SQL Operators

PL/SQL lets you use all the SQL comparison, set, and row operators in SQL statements. This section briefly describes some of these operators. For more information, see Oracle9i SQL Reference.

Comparison Operators

Typically, you use comparison operators in the WHERE clause of a data manipulation statement to form predicates, which compare one expression to another and always yield TRUE, FALSE, or NULL. You can use all the comparison operators listed below to form predicates. Moreover, you can combine predicates using the logical operators AND, OR, and NOT.

Operator	Description
ALL	Compares a value to each value in a list or returned by a subquery and yields TRUE if all of the individual comparisons yield TRUE.
ANY, SOME	Compares a value to each value in a list or returned by a subquery and yields TRUE if any of the individual comparisons yields TRUE.
BETWEEN	Tests whether a value lies in a specified range.
EXISTS	Returns TRUE if a subquery returns at least one row.
IN	Tests for set membership.
IS NULL	Tests for nulls.
LIKE	Tests whether a character string matches a specified pattern, which can include wildcards.

Set Operators

Set operators combine the results of two queries into one result. INTERSECT returns all distinct rows selected by both queries. MINUS returns all distinct rows selected by the first query but not by the second. UNION returns all distinct rows selected by either query. UNION ALL returns all rows selected by either query, including all duplicates.

Row Operators

Row operators return or reference particular rows. ALL retains duplicate rows in the result of a query or in an aggregate expression. DISTINCT eliminates duplicate rows from the result of a query or from an aggregate expression. PRIOR refers to the parent row of the current row returned by a tree-structured query.

Managing Cursors

PL/SQL uses two types of cursors: implicit and explicit. PL/SQL declares a cursor implicitly for all SQL data manipulation statements, including queries that return only one row. However, for queries that return more than one row, you must declare an explicit cursor, use a cursor FOR loop, or use the BULK COLLECT clause.

Overview of Explicit Cursors

The set of rows returned by a query can consist of zero, one, or multiple rows, depending on how many rows meet your search criteria. When a query returns multiple rows, you can explicitly declare a cursor to process the rows. Moreover, you can declare a cursor in the declarative part of any PL/SQL block, subprogram, or package.

You use three commands to control a cursor: OPEN, FETCH, and CLOSE. First, you initialize the cursor with the OPEN statement, which identifies the result set. Then, you can execute FETCH repeatedly until all rows have been retrieved, or you can use the BULK COLLECT clause to fetch all rows at once. When the last row has been processed, you release the cursor with the CLOSE statement. You can process several queries in parallel by declaring and opening multiple cursors.

Declaring a Cursor

Forward references are not allowed in PL/SQL. So, you must declare a cursor before referencing it in other statements. When you declare a cursor, you name it and associate it with a specific query using the syntax

CURSOR cursor_name [(parameter[, parameter]...)]
 [RETURN return_type] IS select_statement;
 

where return_type must represent a record or a row in a database table, and parameter stands for the following syntax:

cursor_parameter_name [IN] datatype [{:= | DEFAULT} expression]
 

For example, you might declare cursors named c1 and c2, as follows:

DECLARE
 CURSOR c1 IS SELECT empno, ename, job, sal FROM emp 
 WHERE sal > 2000; 
 CURSOR c2 RETURN dept%ROWTYPE IS 
 SELECT * FROM dept WHERE deptno = 10;
 

The cursor name is an undeclared identifier, not the name of a PL/SQL variable. You cannot assign values to a cursor name or use it in an expression. However, cursors and variables follow the same scoping rules. Naming cursors after database tables is allowed but not recommended.

A cursor can take parameters, which can appear in the associated query wherever constants can appear. The formal parameters of a cursor must be IN parameters. Therefore, they cannot return values to actual parameters. Also, you cannot impose the constraint NOT NULL on a cursor parameter.

As the example below shows, you can initialize cursor parameters to default values. That way, you can pass different numbers of actual parameters to a cursor, accepting or overriding the default values as you please. Also, you can add new formal parameters without having to change every reference to the cursor.

DECLARE
 CURSOR c1 (low INTEGER DEFAULT 0,
 high INTEGER DEFAULT 99) IS SELECT ...
 

The scope of cursor parameters is local to the cursor, meaning that they can be referenced only within the query specified in the cursor declaration. The values of cursor parameters are used by the associated query when the cursor is opened.

Opening a Cursor

Opening the cursor executes the query and identifies the result set, which consists of all rows that meet the query search criteria. For cursors declared using the FOR UPDATE clause, the OPEN statement also locks those rows. An example of the OPEN statement follows:

DECLARE
 CURSOR c1 IS SELECT ename, job FROM emp WHERE sal < 3000;
 ...
BEGIN
 OPEN c1;
 ...
END;
 

Rows in the result set are not retrieved when the OPEN statement is executed. Rather, the FETCH statement retrieves the rows.

Passing Cursor Parameters

You use the OPEN statement to pass parameters to a cursor. Unless you want to accept default values, each formal parameter in the cursor declaration must have a corresponding actual parameter in the OPEN statement. For example, given the cursor declaration

DECLARE
 emp_name emp.ename%TYPE;
 salary emp.sal%TYPE;
 CURSOR c1 (name VARCHAR2, salary NUMBER) IS SELECT ...
 

any of the following statements opens the cursor:

OPEN c1(emp_name, 3000);
OPEN c1('ATTLEY', 1500);
OPEN c1(emp_name, salary);
 

In the last example, when the identifier salary is used in the cursor declaration, it refers to the formal parameter. But, when it is used in the OPEN statement, it refers to the PL/SQL variable. To avoid confusion, use unique identifiers.

Formal parameters declared with a default value need not have a corresponding actual parameter. They can simply assume their default values when the OPEN statement is executed.

You can associate the actual parameters in an OPEN statement with the formal parameters in a cursor declaration using positional or named notation. The datatypes of each actual parameter and its corresponding formal parameter must be compatible.

Fetching with a Cursor

Unless you use the BULK COLLECT clause (discussed in the next section), the FETCH statement retrieves the rows in the result set one at a time. Each fetch retrieves the current row and then advances the cursor to the next row in the result set. An example follows:

FETCH c1 INTO my_empno, my_ename, my_deptno;
 

For each column value returned by the query associated with the cursor, there must be a corresponding, type-compatible variable in the INTO list. Typically, you use the FETCH statement in the following way:

LOOP
 FETCH c1 INTO my_record;
 EXIT WHEN c1%NOTFOUND;
 -- process data record
END LOOP;
 

The query can reference PL/SQL variables within its scope. However, any variables in the query are evaluated only when the cursor is opened. In the following example, each retrieved salary is multiplied by 2, even though factor is incremented after every fetch:

DECLARE
 my_sal emp.sal%TYPE;
 my_job emp.job%TYPE;
 factor INTEGER := 2;
 CURSOR c1 IS SELECT factor*sal FROM emp WHERE job = my_job;
BEGIN
 ...
 OPEN c1; -- here factor equals 2
 LOOP
 FETCH c1 INTO my_sal;
 EXIT WHEN c1%NOTFOUND;
 factor := factor + 1; -- does not affect FETCH
 END LOOP;
END;
 

To change the result set or the values of variables in the query, you must close and reopen the cursor with the input variables set to their new values.

However, you can use a different INTO list on separate fetches with the same cursor. Each fetch retrieves another row and assigns values to the target variables, as the following example shows:

DECLARE
 CURSOR c1 IS SELECT ename FROM emp;
 name1 emp.ename%TYPE;
 name2 emp.ename%TYPE;
  name3 emp.ename%TYPE;
BEGIN
 OPEN c1;
 FETCH c1 INTO name1; -- this fetches first row
 FETCH c1 INTO name2; -- this fetches second row
 FETCH c1 INTO name3; -- this fetches third row
 ...
 CLOSE c1;
END;
 

If you fetch past the last row in the result set, the values of the target variables are indeterminate.

Note: Eventually, the FETCH statement must fail to return a row, so when that happens, no exception is raised. To detect the failure, you must use the cursor attribute %FOUND or %NOTFOUND. For more information, see "Using Cursor Attributes"

Closing a Cursor

The CLOSE statement disables the cursor, and the result set becomes undefined. Once a cursor is closed, you can reopen it. Any other operation on a closed cursor raises the predefined exception INVALID_CURSOR.

Using Subqueries in Cursors

A subquery is a query (usually enclosed by parentheses) that appears within another SQL data manipulation statement. When evaluated, the subquery provides a value or set of values to the statement. Often, subqueries are used in the WHERE clause. For example, the following query returns employees not located in Chicago:

DECLARE
 CURSOR c1 IS SELECT empno, ename FROM emp
 WHERE deptno IN (SELECT deptno FROM dept 
 WHERE loc <> 'CHICAGO');
 

Using a subquery in the FROM clause, the following query returns the number and name of each department with five or more employees:

DECLARE
 CURSOR c1 IS SELECT t1.deptno, dname, "STAFF"
 FROM dept t1, (SELECT deptno, COUNT(*) "STAFF"
  FROM emp GROUP BY deptno) t2
 WHERE t1.deptno = t2.deptno AND "STAFF" >= 5;
 

Whereas a subquery is evaluated only once for each table, a correlated subquery is evaluated once for each row. Consider the query below, which returns the name and salary of each employee whose salary exceeds the departmental average. For each row in the emp table, the correlated subquery computes the average salary for that row's department. The row is returned if that row's salary exceeds the average.

DECLARE
 CURSOR c1 IS SELECT deptno, ename, sal FROM emp t 
 WHERE sal > (SELECT AVG(sal) FROM emp WHERE t.deptno = deptno)
 ORDER BY deptno;

Overview of Implicit Cursors

Oracle implicitly opens a cursor to process each SQL statement not associated with an explicitly declared cursor. You can refer to the most recent implicit cursor as the SQL cursor. Although you cannot use the OPEN, FETCH, and CLOSE statements to control the SQL cursor, you can use cursor attributes to get information about the most recently executed SQL statement. See "Using Cursor Attributes".

Using Cursor FOR Loops

In most situations that require an explicit cursor, you can simplify coding by using a cursor FOR loop instead of the OPEN, FETCH, and CLOSE statements. A cursor FOR loop implicitly declares its loop index as a %ROWTYPE record, opens a cursor, repeatedly fetches rows of values from the result set into fields in the record, and closes the cursor when all rows have been processed.

Consider the PL/SQL block below, which computes results from an experiment, then stores the results in a temporary table. The FOR loop index c1_rec is implicitly declared as a record. Its fields store all the column values fetched from the cursor c1. Dot notation is used to reference individual fields.

-- available online in file 'examp7' 
DECLARE
 result temp.col1%TYPE;
 CURSOR c1 IS
 SELECT n1, n2, n3 FROM data_table WHERE exper_num = 1;
BEGIN
 FOR c1_rec IN c1 LOOP
 /* calculate and store the results */
 result := c1_rec.n2 / (c1_rec.n1 + c1_rec.n3);
 INSERT INTO temp VALUES (result, NULL, NULL);
 END LOOP;
 COMMIT;
END;
 

When the cursor FOR loop is entered, the cursor name cannot belong to a cursor already opened by an OPEN statement or enclosing cursor FOR loop. Before each iteration of the FOR loop, PL/SQL fetches into the implicitly declared record. The record is defined only inside the loop. You cannot refer to its fields outside the loop.

The sequence of statements inside the loop is executed once for each row that satisfies the query associated with the cursor. When you leave the loop, the cursor is closed automatically--even if you use an EXIT or GOTO statement to leave the loop prematurely or an exception is raised inside the loop.

Using Subqueries Instead of Explicit Cursors

You need not declare a cursor because PL/SQL lets you substitute a subquery. The following cursor FOR loop calculates a bonus, then inserts the result into a database table:

DECLARE
 bonus REAL;
BEGIN
 FOR emp_rec IN (SELECT empno, sal, comm FROM emp) LOOP
 bonus := (emp_rec.sal * 0.05) + (emp_rec.comm * 0.25);
 INSERT INTO bonuses VALUES (emp_rec.empno, bonus);
 END LOOP;
 COMMIT;
END;

Defining Aliases for Expression Values in a Cursor FOR Loop

Fields in the implicitly declared record hold column values from the most recently fetched row. The fields have the same names as corresponding columns in the SELECT list. But, what happens if a select item is an expression? Consider the following example:

CURSOR c1 IS
 SELECT empno, sal+NVL(comm,0), job FROM ...
 

In such cases, you must include an alias for the select item. In the following example, wages is an alias for the select item sal+NVL(comm,0):

CURSOR c1 IS
 SELECT empno, sal+NVL(comm,0) wages, job FROM ...
 

To reference the corresponding field, use the alias instead of a column name, as follows:

IF emp_rec.wages < 1000 THEN ...

Passing Parameters to a Cursor FOR Loop

You can pass parameters to the cursor in a cursor FOR loop. In the following example, you pass a department number. Then, you compute the total wages paid to employees in that department. Also, you determine how many employees have salaries higher than $2000 and/or commissions larger than their salaries.

-- available online in file 'examp8' 
DECLARE
 CURSOR emp_cursor(dnum NUMBER) IS
 SELECT sal, comm FROM emp WHERE deptno = dnum;
 total_wages NUMBER(11,2) := 0;
 high_paid NUMBER(4) := 0;
 higher_comm NUMBER(4) := 0;
BEGIN
 /* The number of iterations will equal the number of rows
 returned by emp_cursor. */
 FOR emp_record IN emp_cursor(20) LOOP
 emp_record.comm := NVL(emp_record.comm, 0);
 total_wages := total_wages + emp_record.sal +
 emp_record.comm;
 IF emp_record.sal > 2000.00 THEN
 high_paid := high_paid + 1;
 END IF;
 IF emp_record.comm > emp_record.sal THEN
 higher_comm := higher_comm + 1;
 END IF;
 END LOOP;
 INSERT INTO temp VALUES (high_paid, higher_comm,
 'Total Wages: ' || TO_CHAR(total_wages));
 COMMIT;
END;
/
 

Overview of Transaction Processing in PL/SQL

This section explains how to do transaction processing. You learn the basic techniques that safeguard the consistency of your database, including how to control whether changes to Oracle data are made permanent or undone.

The jobs or tasks that Oracle manages are called sessions. A user session is started when you run an application program or an Oracle tool and connect to Oracle. To allow user sessions to work "simultaneously" and share computer resources, Oracle must control concurrency, the accessing of the same data by many users. Without adequate concurrency controls, there might be a loss of data integrity. That is, changes to data might be made in the wrong order.

Oracle uses locks to control concurrent access to data. A lock gives you temporary ownership of a database resource such as a table or row of data. Thus, data cannot be changed by other users until you finish with it. You need never explicitly lock a resource because default locking mechanisms protect Oracle data and structures. However, you can request data locks on tables or rows when it is to your advantage to override default locking. You can choose from several modes of locking such as row share and exclusive.

A deadlock can occur when two or more users try to access the same schema object. For example, two users updating the same table might wait if each tries to update a row currently locked by the other. Because each user is waiting for resources held by another user, neither can continue until Oracle breaks the deadlock by signaling an error to the last participating transaction.

When a table is being queried by one user and updated by another at the same time, Oracle generates a read-consistent view of the data for the query. That is, once a query begins and as it proceeds, the data read by the query does not change. As update activity continues, Oracle takes snapshots of the table's data and records changes in a rollback segment. Oracle uses rollback segments to build read-consistent query results and to undo changes if necessary.

How Transactions Guard Your Database

A transaction is a series of SQL data manipulation statements that does a logical unit of work. Oracle treats the series of SQL statements as a unit so that all the changes brought about by the statements are either committed (made permanent) or rolled back (undone) at the same time. If your program fails in the middle of a transaction, the database is automatically restored to its former state.

The first SQL statement in your program begins a transaction. When one transaction ends, the next SQL statement automatically begins another transaction. Thus, every SQL statement is part of a transaction. A distributed transaction includes at least one SQL statement that updates data at multiple nodes in a distributed database.

The COMMIT and ROLLBACK statements ensure that all database changes brought about by SQL operations are either made permanent or undone at the same time. All the SQL statements executed since the last commit or rollback make up the current transaction. The SAVEPOINT statement names and marks the current point in the processing of a transaction.

Making Changes Permanent with COMMIT

The COMMIT statement ends the current transaction and makes permanent any changes made during that transaction. Until you commit the changes, other users cannot access the changed data; they see the data as it was before you made the changes.

Consider a simple transaction that transfers money from one bank account to another. The transaction requires two updates because it debits the first account, then credits the second. In the example below, after crediting the second account, you issue a commit, which makes the changes permanent. Only then do other users see the changes.

BEGIN
 ...
 UPDATE accts SET bal = my_bal - debit
 WHERE acctno = 7715;
 ...
 UPDATE accts SET bal = my_bal + credit
 WHERE acctno = 7720;
 COMMIT WORK;
END;
 

The COMMIT statement releases all row and table locks. It also erases any savepoints (discussed later) marked since the last commit or rollback. The optional keyword WORK has no effect other than to improve readability. The keyword END signals the end of a PL/SQL block, not the end of a transaction. Just as a block can span multiple transactions, a transaction can span multiple blocks.

The optional COMMENT clause lets you specify a comment to be associated with a distributed transaction. When you issue a commit, changes to each database affected by a distributed transaction are made permanent. However, if a network or machine fails during the commit, the state of the distributed transaction might be unknown or in doubt. In that case, Oracle stores the text specified by COMMENT in the data dictionary along with the transaction ID. The text must be a quoted literal up to 50 characters long. An example follows:

COMMIT COMMENT 'In-doubt order transaction; notify Order Entry';
 

PL/SQL does not support the FORCE clause, which, in SQL, manually commits an in-doubt distributed transaction. For example, the following COMMIT statement is not allowed:

COMMIT FORCE '23.51.54'; -- not allowed

Undoing Changes with ROLLBACK

The ROLLBACK statement ends the current transaction and undoes any changes made during that transaction. Rolling back is useful for two reasons. First, if you make a mistake like deleting the wrong row from a table, a rollback restores the original data. Second, if you start a transaction that you cannot finish because an exception is raised or a SQL statement fails, a rollback lets you return to the starting point to take corrective action and perhaps try again.

Consider the example below, in which you insert information about an employee into three different database tables. All three tables have a column that holds employee numbers and is constrained by a unique index. If an INSERT statement tries to store a duplicate employee number, the predefined exception DUP_VAL_ON_INDEX is raised. In that case, you want to undo all changes, so you issue a rollback in the exception handler.

DECLARE
 emp_id INTEGER;
 ...
BEGIN
 SELECT empno, ... INTO emp_id, ... FROM new_emp WHERE ...
 ...
 INSERT INTO emp VALUES (emp_id, ...);
 INSERT INTO tax VALUES (emp_id, ...);
 INSERT INTO pay VALUES (emp_id, ...);
 ...
EXCEPTION
 WHEN DUP_VAL_ON_INDEX THEN
 ROLLBACK;
 ...
END;

Statement-Level Rollbacks

Before executing a SQL statement, Oracle marks an implicit savepoint. Then, if the statement fails, Oracle rolls it back automatically. For example, if an INSERT statement raises an exception by trying to insert a duplicate value in a unique index, the statement is rolled back. Only work started by the failed SQL statement is lost. Work done before that statement in the current transaction is kept.

Oracle can also roll back single SQL statements to break deadlocks. Oracle signals an error to one of the participating transactions and rolls back the current statement in that transaction.

Before executing a SQL statement, Oracle must parse it, that is, examine it to make sure it follows syntax rules and refers to valid schema objects. Errors detected while executing a SQL statement cause a rollback, but errors detected while parsing the statement do not.

Undoing Partial Changes with SAVEPOINT

SAVEPOINT names and marks the current point in the processing of a transaction. Used with the ROLLBACK TO statement, savepoints let you undo parts of a transaction instead of the whole transaction. In the example below, you mark a savepoint before doing an insert. If the INSERT statement tries to store a duplicate value in the empno column, the predefined exception DUP_VAL_ON_INDEX is raised. In that case, you roll back to the savepoint, undoing just the insert.

DECLARE
 emp_id emp.empno%TYPE;
BEGIN
 UPDATE emp SET ... WHERE empno = emp_id;
 DELETE FROM emp WHERE ...
 ...
 SAVEPOINT do_insert;
 INSERT INTO emp VALUES (emp_id, ...);
EXCEPTION
 WHEN DUP_VAL_ON_INDEX THEN
 ROLLBACK TO do_insert;
END;
 

When you roll back to a savepoint, any savepoints marked after that savepoint are erased. However, the savepoint to which you roll back is not erased. For example, if you mark five savepoints, then roll back to the third, only the fourth and fifth are erased. A simple rollback or commit erases all savepoints.

If you mark a savepoint within a recursive subprogram, new instances of the SAVEPOINT statement are executed at each level in the recursive descent. However, you can only roll back to the most recently marked savepoint.

Savepoint names are undeclared identifiers and can be reused within a transaction. This moves the savepoint from its old position to the current point in the transaction. Thus, a rollback to the savepoint affects only the current part of your transaction. An example follows:

BEGIN
 SAVEPOINT my_point;
 UPDATE emp SET ... WHERE empno = emp_id;
 ...
 SAVEPOINT my_point; -- move my_point to current point
 INSERT INTO emp VALUES (emp_id, ...);
EXCEPTION
 WHEN OTHERS THEN
 ROLLBACK TO my_point;
END;
 

The number of active savepoints for each session is unlimited. An active savepoint is one marked since the last commit or rollback.

How Oracle Does Implicit Rollbacks

Before executing an INSERT, UPDATE, or DELETE statement, Oracle marks an implicit savepoint (unavailable to you). If the statement fails, Oracle rolls back to the savepoint. Normally, just the failed SQL statement is rolled back, not the whole transaction. However, if the statement raises an unhandled exception, the host environment determines what is rolled back.

If you exit a stored subprogram with an unhandled exception, PL/SQL does not assign values to OUT parameters. Also, PL/SQL does not roll back database work done by the subprogram.

Ending Transactions

A good programming practice is to commit or roll back every transaction explicitly. Whether you issue the commit or rollback in your PL/SQL program or in the host environment depends on the flow of application logic. If you neglect to commit or roll back a transaction explicitly, the host environment determines its final state.

For example, in the SQL*Plus environment, if your PL/SQL block does not include a COMMIT or ROLLBACK statement, the final state of your transaction depends on what you do after running the block. If you execute a data definition, data control, or COMMIT statement or if you issue the EXIT, DISCONNECT, or QUIT command, Oracle commits the transaction. If you execute a ROLLBACK statement or abort the SQL*Plus session, Oracle rolls back the transaction.

In the Oracle Precompiler environment, if your program does not terminate normally, Oracle rolls back your transaction. A program terminates normally when it explicitly commits or rolls back work and disconnects from Oracle using the RELEASE parameter, as follows:

EXEC SQL COMMIT WORK RELEASE;

Setting Transaction Properties with SET TRANSACTION

You use the SET TRANSACTION statement to begin a read-only or read-write transaction, establish an isolation level, or assign your current transaction to a specified rollback segment. Read-only transactions are useful for running multiple queries against one or more tables while other users update the same tables.

During a read-only transaction, all queries refer to the same snapshot of the database, providing a multi-table, multi-query, read-consistent view. Other users can continue to query or update data as usual. A commit or rollback ends the transaction. In the example below, as a store manager, you use a read-only transaction to gather sales figures for the day, the past week, and the past month. The figures are unaffected by other users updating the database during the transaction.

DECLARE
 daily_sales REAL;
 weekly_sales REAL;
 monthly_sales REAL;
BEGIN
 ...
 COMMIT; -- ends previous transaction
 SET TRANSACTION READ ONLY NAME 'Calculate sales figures';
 SELECT SUM(amt) INTO daily_sales FROM sales
 WHERE dte = SYSDATE;
 SELECT SUM(amt) INTO weekly_sales FROM sales
 WHERE dte > SYSDATE - 7;
 SELECT SUM(amt) INTO monthly_sales FROM sales
 WHERE dte > SYSDATE - 30;
 COMMIT; -- ends read-only transaction
 ...
END;
 

The SET TRANSACTION statement must be the first SQL statement in a read-only transaction and can only appear once in a transaction. If you set a transaction to READ ONLY, subsequent queries see only changes committed before the transaction began. The use of READ ONLY does not affect other users or transactions.

Restrictions on SET TRANSACTION

Only the SELECT INTO, OPEN, FETCH, CLOSE, LOCK TABLE, COMMIT, and ROLLBACK statements are allowed in a read-only transaction. Also, queries cannot be FOR UPDATE.

Overriding Default Locking

By default, Oracle locks data structures for you automatically. However, you can request specific data locks on rows or tables when it is to your advantage to override default locking. Explicit locking lets you share or deny access to a table for the duration of a transaction.

With the LOCK TABLE statement, you can explicitly lock entire tables. With the SELECT FOR UPDATE statement, you can explicitly lock specific rows of a table to make sure they do not change before an update or delete is executed. However, Oracle automatically obtains row-level locks at update or delete time. So, use the FOR UPDATE clause only if you want to lock the rows before the update or delete.

Using FOR UPDATE

When you declare a cursor that will be referenced in the CURRENT OF clause of an UPDATE or DELETE statement, you must use the FOR UPDATE clause to acquire exclusive row locks. An example follows:

DECLARE
 CURSOR c1 IS SELECT empno, sal FROM emp
 WHERE job = 'SALESMAN' AND comm > sal 
 FOR UPDATE NOWAIT;
 

The SELECT ... FOR UPDATE statement identifies the rows that will be updated or deleted, then locks each row in the result set. This is useful when you want to base an update on the existing values in a row. In that case, you must make sure the row is not changed by another user before the update.

The optional keyword NOWAIT tells Oracle not to wait if requested rows have been locked by another user. Control is immediately returned to your program so that it can do other work before trying again to acquire the lock. If you omit the keyword NOWAIT, Oracle waits until the rows are available.

All rows are locked when you open the cursor, not as they are fetched. The rows are unlocked when you commit or roll back the transaction. So, you cannot fetch from a FOR UPDATE cursor after a commit. (For a workaround, see "Fetching Across Commits".)

When querying multiple tables, you can use the FOR UPDATE clause to confine row locking to particular tables. Rows in a table are locked only if the FOR UPDATE OF clause refers to a column in that table. For example, the following query locks rows in the emp table but not in the dept table:

DECLARE
 CURSOR c1 IS SELECT ename, dname FROM emp, dept
 WHERE emp.deptno = dept.deptno AND job = 'MANAGER'
 FOR UPDATE OF sal;
 

As the next example shows, you use the CURRENT OF clause in an UPDATE or DELETE statement to refer to the latest row fetched from a cursor:

DECLARE
 CURSOR c1 IS SELECT empno, job, sal FROM emp FOR UPDATE;
 ...
BEGIN
 OPEN c1;
 LOOP
 FETCH c1 INTO ...
 ...
 UPDATE emp SET sal = new_sal WHERE CURRENT OF c1;
 END LOOP;

Using LOCK TABLE

You use the LOCK TABLE statement to lock entire database tables in a specified lock mode so that you can share or deny access to them. For example, the statement below locks the emp table in row share mode. Row share locks allow concurrent access to a table; they prevent other users from locking the entire table for exclusive use. Table locks are released when your transaction issues a commit or rollback.

LOCK TABLE emp IN ROW SHARE MODE NOWAIT;
 

The lock mode determines what other locks can be placed on the table. For example, many users can acquire row share locks on a table at the same time, but only one user at a time can acquire an exclusive lock. While one user has an exclusive lock on a table, no other users can insert, delete, or update rows in that table. For more information about lock modes, see Oracle9i Application Developer's Guide - Fundamentals.

A table lock never keeps other users from querying a table, and a query never acquires a table lock. Only if two different transactions try to modify the same row will one transaction wait for the other to complete.

Fetching Across Commits

The FOR UPDATE clause acquires exclusive row locks. All rows are locked when you open the cursor, and they are unlocked when you commit your transaction. So, you cannot fetch from a FOR UPDATE cursor after a commit. If you do, PL/SQL raises an exception. In the following example, the cursor FOR loop fails after the tenth insert:

DECLARE
 CURSOR c1 IS SELECT ename FROM emp FOR UPDATE OF sal;
 ctr NUMBER := 0;
BEGIN
 FOR emp_rec IN c1 LOOP -- FETCHes implicitly
 ...
 ctr := ctr + 1;
 INSERT INTO temp VALUES (ctr, 'still going');
 IF ctr >= 10 THEN
 COMMIT; -- releases locks
 END IF;
 END LOOP;
END;
 

If you want to fetch across commits, do not use the FOR UPDATE and CURRENT OF clauses. Instead, use the ROWID pseudocolumn to mimic the CURRENT OF clause. Simply select the rowid of each row into a UROWID variable. Then, use the rowid to identify the current row during subsequent updates and deletes. An example follows:

DECLARE
 CURSOR c1 IS SELECT ename, job, rowid FROM emp;
 my_ename emp.ename%TYPE;
 my_job emp.job%TYPE;
 my_rowid UROWID;
BEGIN
 OPEN c1;
 LOOP
 FETCH c1 INTO my_ename, my_job, my_rowid;
  EXIT WHEN c1%NOTFOUND;
 UPDATE emp SET sal = sal * 1.05 WHERE rowid = my_rowid;
 -- this mimics WHERE CURRENT OF c1
 COMMIT;
 END LOOP;
 CLOSE c1;
END;
 

Be careful. In the last example, the fetched rows are not locked because no FOR UPDATE clause is used. So, other users might unintentionally overwrite your changes. Also, the cursor must have a read-consistent view of the data, so rollback segments used in the update are not released until the cursor is closed. This can slow down processing when many rows are updated.

The next example shows that you can use the %ROWTYPE attribute with cursors that reference the ROWID pseudocolumn:

DECLARE
 CURSOR c1 IS SELECT ename, sal, rowid FROM emp;
 emp_rec c1%ROWTYPE;
BEGIN
 OPEN c1;
 LOOP
 FETCH c1 INTO emp_rec;
 EXIT WHEN c1%NOTFOUND;
 ...
 IF ... THEN
 DELETE FROM emp WHERE rowid = emp_rec.rowid;
 END IF;
 END LOOP;
 CLOSE c1;
END;