Release 8.1.5
A67842-01
 Library |
 Product |
 Contents |
 Index |
| PL/SQL User's Guide and Reference Release 8.1.5 A67842-01 |
|
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.
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 (for more information, see Chapter 10, "Native Dynamic SQL"). In addition, PL/SQL conforms to the current ANSI/ISO SQL standard.
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.
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.
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 specify the asterisk (*) option, 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;
PL/SQL recognizes the following SQL pseudocolumns, which return specific data items: CURRVAL, LEVEL, NEXTVAL, ROWID, and ROWNUM. Pseudocolumns are not actual columns in a table but they behave like columns. For example, you can select values from a pseudocolumn. However, you cannot insert into, update, or delete from a pseudocolumn. Also, pseudocolumns are allowed in SQL statements, but not in procedural statements.
A sequence is a schema object that generates sequential numbers. When you create a sequence, you can specify its initial value and an increment. CURRVAL returns the current value in a specified sequence.
Before you can reference CURRVAL in a session, you must use NEXTVAL to generate a number. A reference to NEXTVAL stores the current sequence number in CURRVAL. NEXTVAL increments the sequence and returns the next value. To obtain the current or next value in a sequence, you must use dot notation, as follows:
sequence_name.CURRVAL sequence_name.NEXTVAL
After creating a sequence, you can use it to generate unique sequence numbers for transaction processing. However, you can use CURRVAL and NEXTVAL only in a SELECT list, the VALUES clause, and the SET clause. In the following example, you use a sequence to insert the same employee number into two tables:
INSERT INTO emp VALUES (empno_seq.NEXTVAL, my_ename, ...); INSERT INTO sals VALUES (empno_seq.CURRVAL, my_sal, ...);
If a transaction generates a sequence number, the sequence is incremented immediately whether you commit or roll back the transaction.
You use LEVEL with the SELECT CONNECT BY statement to organize rows from a database table into a tree structure. LEVEL returns the level number of a node in a tree structure. The root is level 1, children of the root are level 2, grandchildren are level 3, and so on.
You specify the direction in which the query walks the tree (down from the root or up from the branches) with the PRIOR operator. In the START WITH clause, you specify a condition that identifies the root of the tree.
ROWID returns the rowid (binary address) of a row in a database table. You can use variables of type UROWID to store rowids in a readable format. In the following example, you declare a variable named row_id for that purpose:
DECLARE row_id UROWID;
When you select or fetch a rowid into a UROWID variable, you can use the function ROWIDTOCHAR, which converts the binary value to an 18-byte character string. Then, you can compare the UROWID variable to the ROWID pseudocolumn in the WHERE clause of an UPDATE or DELETE statement to identify the latest row fetched from a cursor. For an example, see "Fetching Across Commits".
ROWNUM returns a number indicating the order in which a row was selected from a table. The first row selected has a ROWNUM of 1, the second row has a ROWNUM of 2, and so on. If a SELECT statement includes an ORDER BY clause, ROWNUMs are assigned to the retrieved rows before the sort is done.
You can use ROWNUM in an UPDATE statement to assign unique values to each row in a table. Also, you can use ROWNUM in the WHERE clause of a SELECT statement to limit the number of rows retrieved, as follows:
DECLARE CURSOR c1 IS SELECT empno, sal FROM emp WHERE sal > 2000 AND ROWNUM < 10; -- returns 10 rows
The value of ROWNUM increases only when a row is retrieved, so the only meaningful use of ROWNUM in a WHERE clause is
... WHERE ROWNUM < constant;
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 Oracle8i SQL Reference.
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.
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 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.
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 or use a cursor FOR loop.
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 use the FETCH statement to retrieve the first row. You can execute FETCH repeatedly until all rows have been retrieved. 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.
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 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.
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. (See "Positional and Named Notation".) The datatypes of each actual parameter and its corresponding formal parameter must be compatible.
The FETCH statement retrieves the rows in the result set one at a time. After each fetch, the cursor advances 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"
The CLOSE statement disables the cursor, and the result set becomes undefined. An example of the CLOSE statement follows:
CLOSE c1;
Once a cursor is closed, you can reopen it. Any other operation on a closed cursor raises the predefined exception INVALID_CURSOR.
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 per table, a correlated subquery is evaluated once per 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;
Oracle implicitly opens a cursor to process each SQL statement not associated with an explicitly declared cursor. PL/SQL lets you refer to the most recent implicit cursor as the SQL cursor.
You cannot use the OPEN, FETCH, and CLOSE statements to control the SQL cursor. But, you can use cursor attributes to get information about the most recently executed SQL statement. See "Using Cursor Attributes".
You can separate a cursor specification (spec for short) from its body for placement in a package. That way, you can change the cursor body without having to change the cursor spec. You code the cursor spec in the package spec using the syntax
CURSOR cursor_name [(parameter[, parameter]...)] RETURN return_type;
In the following example, you use the %ROWTYPE attribute to provide a record type that represents a row in the database table emp:
CREATE PACKAGE emp_actions AS /* Declare cursor spec. */ CURSOR c1 RETURN emp%ROWTYPE; ... END emp_actions; CREATE PACKAGE BODY emp_actions AS /* Define cursor body. */ CURSOR c1 RETURN emp%ROWTYPE IS SELECT * FROM emp WHERE sal > 3000; ... END emp_actions;
The cursor spec has no SELECT statement because the RETURN clause defines the datatype of the result value. However, the cursor body must have a SELECT statement and the same RETURN clause as the cursor spec. Also, the number and datatypes of items in the SELECT list and the RETURN clause must match.
Packaged cursors increase flexibility. For instance, you can change the cursor body in the last example, as follows, without having to change the cursor spec:
CREATE PACKAGE BODY emp_actions AS /* Define cursor body. */ CURSOR c1 RETURN emp%ROWTYPE IS SELECT * FROM emp WHERE deptno = 20; -- new WHERE clause ... END emp_actions;
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 that was already opened by an OPEN statement or by an enclosing cursor FOR loop. Before each iteration of the FOR loop, PL/SQL fetches into the implicitly declared record, which is equivalent to a record declared explicitly as follows:
c1_rec c1%ROWTYPE;
The record is defined only inside the loop. You cannot refer to its fields outside the loop. For example, the following reference is illegal:
FOR c1_rec IN c1 LOOP ... END LOOP; result := c1_rec.n2 + 3; -- illegal
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.
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;
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 ...
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;
Like a cursor, a cursor variable points to the current row in the result set of a multi-row query. But, cursors differ from cursor variables the way constants differ from variables. Whereas a cursor is static, a cursor variable is dynamic because it is not tied to a specific query. You can open a cursor variable for any type-compatible query. This gives you more flexibility.
Also, you can assign new values to a cursor variable and pass it as a parameter to local and stored subprograms. This gives you an easy way to centralize data retrieval.
Cursor variables are available to every PL/SQL client. For example, you can declare a cursor variable in a PL/SQL host environment such as an OCI or Pro*C program, then pass it as input host variable (bind variable) to PL/SQL. Moreover, application development tools such as Oracle Forms and Oracle Reports, which have a PL/SQL engine, can use cursor variables entirely on the client side.
The Oracle server also has a PL/SQL engine. So, you can pass cursor variables back and forth between an application and server via remote procedure calls (RPCs).
Cursor variables are like C or Pascal pointers, which hold the memory location (address) of some item instead of the item itself. So, declaring a cursor variable creates a pointer, not an item. In PL/SQL, a pointer has datatype REF X, where REF is short for REFERENCE and X stands for a class of objects. Therefore, a cursor variable has datatype REF CURSOR.
To execute a multi-row query, Oracle opens an unnamed work area that stores processing information. To access the information, you can use an explicit cursor, which names the work area. Or, you can use a cursor variable, which points to the work area. Whereas a cursor always refers to the same query work area, a cursor variable can refer to different work areas. So, cursors and cursor variables are not interoperable; that is, you cannot use one where the other is expected.
Mainly, you use cursor variables to pass query result sets between PL/SQL stored subprograms and various clients. Neither PL/SQL nor any of its clients owns a result set; they simply share a pointer to the query work area in which the result set is stored. For example, an OCI client, Oracle Forms application, and Oracle server can all refer to the same work area.
A query work area remains accessible as long as any cursor variable points to it. Therefore, you can pass the value of a cursor variable freely from one scope to another. For example, if you pass a host cursor variable to a PL/SQL block embedded in a Pro*C program, the work area to which the cursor variable points remains accessible after the block completes.
If you have a PL/SQL engine on the client side, calls from client to server impose no restrictions. For example, you can declare a cursor variable on the client side, open and fetch from it on the server side, then continue to fetch from it back on the client side. Also, you can reduce network traffic by having a PL/SQL block open (or close) several host cursor variables in a single round trip.
To create cursor variables, you take two steps. First, you define a REF CURSOR type, then declare cursor variables of that type. You can define REF CURSOR types in any PL/SQL block, subprogram, or package using the syntax
TYPE ref_type_name IS REF CURSOR RETURN return_type;
where ref_type_name is a type specifier used in subsequent declarations of cursor variables and return_type must represent a record or a row in a database table. In the following example, you specify a return type that represents a row in the database table dept:
DECLARE TYPE DeptCurTyp IS REF CURSOR RETURN dept%ROWTYPE;
REF CURSOR types can be strong (restrictive) or weak (nonrestrictive). As the next example shows, a strong REF CURSOR type definition specifies a return type, but a weak definition does not:
DECLARE TYPE EmpCurTyp IS REF CURSOR RETURN emp%ROWTYPE; -- strong TYPE GenericCurTyp IS REF CURSOR; -- weak
Strong REF CURSOR types are less error prone because the PL/SQL compiler lets you associate a strongly typed cursor variable only with type-compatible queries. However, weak REF CURSOR types are more flexible because the compiler lets you associate a weakly typed cursor variable with any query.
Once you define a REF CURSOR type, you can declare cursor variables of that type in any PL/SQL block or subprogram. In the following example, you declare the cursor variable dept_cv:
DECLARE TYPE DeptCurTyp IS REF CURSOR RETURN dept%ROWTYPE; dept_cv DeptCurTyp; -- declare cursor variable
Note: You cannot declare cursor variables in a package. Unlike packaged variables, cursor variables do not have persistent state. Remember, declaring a cursor variable creates a pointer, not an item. So, cursor variables cannot be saved in the database.
Cursor variables follow the usual scoping and instantiation rules. Local PL/SQL cursor variables are instantiated when you enter a block or subprogram and cease to exist when you exit.
In the RETURN clause of a REF CURSOR type definition, you can use %ROWTYPE to specify a record type that represents a row returned by a strongly (not weakly) typed cursor variable, as follows:
DECLARE TYPE TmpCurTyp IS REF CURSOR RETURN emp%ROWTYPE; tmp_cv TmpCurTyp; -- declare cursor variable TYPE EmpCurTyp IS REF CURSOR RETURN tmp_cv%ROWTYPE; emp_cv EmpCurTyp; -- declare cursor variable
Likewise, you can use %TYPE to provide the datatype of a record variable, as the following example shows:
DECLARE dept_rec dept%ROWTYPE; -- declare record variable TYPE DeptCurTyp IS REF CURSOR RETURN dept_rec%TYPE; dept_cv DeptCurTyp; -- declare cursor variable
In the final example, you specify a user-defined RECORD type in the RETURN clause:
DECLARE TYPE EmpRecTyp IS RECORD ( empno NUMBER(4), ename VARCHAR2(1O), sal NUMBER(7,2)); TYPE EmpCurTyp IS REF CURSOR RETURN EmpRecTyp; emp_cv EmpCurTyp; -- declare cursor variable
You can declare cursor variables as the formal parameters of functions and procedures. In the following example, you define the REF CURSOR type EmpCurTyp, then declare a cursor variable of that type as the formal parameter of a procedure:
DECLARE TYPE EmpCurTyp IS REF CURSOR RETURN emp%ROWTYPE; PROCEDURE open_emp_cv (emp_cv IN OUT EmpCurTyp) IS ...
Caution: Like all pointers, cursor variables increase the possibility of parameter aliasing. For more information, see "Parameter Aliasing".
You use three statements to control a cursor variable: OPEN-FOR, FETCH, and CLOSE. First, you OPEN a cursor variable FOR a multi-row query. Then, you FETCH rows from the result set one at a time. When all the rows are processed, you CLOSE the cursor variable.
The OPEN-FOR statement associates a cursor variable with a multi-row query, executes the query, and identifies the result set. Here is the syntax:
OPEN {cursor_variable_name | :host_cursor_variable_name} FOR select_statement;
where host_cursor_variable_name identifies a cursor variable declared in a PL/SQL host environment such as an OCI or Pro*C program.
Unlike cursors, cursor variables take no parameters. However, no flexibility is lost because you can pass whole queries (not just parameters) to a cursor variable. The query can reference host variables and PL/SQL variables, parameters, and functions but cannot be FOR UPDATE.
In the example below, you open the cursor variable emp_cv. Notice that you can apply cursor attributes (%FOUND, %NOTFOUND, %ISOPEN, and %ROWCOUNT) to a cursor variable.
IF NOT emp_cv%ISOPEN THEN /* Open cursor variable. */ OPEN emp_cv FOR SELECT * FROM emp; END IF;
Other OPEN-FOR statements can open the same cursor variable for different queries. You need not close a cursor variable before reopening it. (Recall that consecutive OPENs of a static cursor raise the predefined exception CURSOR_ALREADY_OPEN.) When you reopen a cursor variable for a different query, the previous query is lost.
Typically, you open a cursor variable by passing it to a stored procedure that declares a cursor variable as one of its formal parameters. For example, the following packaged procedure opens the cursor variable emp_cv:
CREATE PACKAGE emp_data AS ... TYPE EmpCurTyp IS REF CURSOR RETURN emp%ROWTYPE; PROCEDURE open_emp_cv (emp_cv IN OUT EmpCurTyp); END emp_data; CREATE PACKAGE BODY emp_data AS ... PROCEDURE open_emp_cv (emp_cv IN OUT EmpCurTyp) IS BEGIN OPEN emp_cv FOR SELECT * FROM emp; END open_emp_cv; END emp_data;
When you declare a cursor variable as the formal parameter of a subprogram that opens the cursor variable, you must specify the IN OUT mode. That way, the subprogram can pass an open cursor back to the caller.
Alternatively, you can use a stand-alone procedure to open the cursor variable. Simply define the REF CURSOR type in a separate package, then reference that type in the stand-alone procedure. For instance, if you create the following bodiless package, you can create stand-alone procedures that reference the types it defines:
CREATE PACKAGE cv_types AS TYPE GenericCurTyp IS REF CURSOR; TYPE EmpCurTyp IS REF CURSOR RETURN emp%ROWTYPE; TYPE DeptCurTyp IS REF CURSOR RETURN dept%ROWTYPE; ... END cv_types;
In the next example, you create a stand-alone procedure that references the REF CURSOR type EmpCurTyp, which is defined in the package cv_types:
CREATE PROCEDURE open_emp_cv (emp_cv IN OUT cv_types.EmpCurTyp) AS BEGIN OPEN emp_cv FOR SELECT * FROM emp; END open_emp_cv;
To centralize data retrieval, you can group type-compatible queries in a stored procedure. In the example below, the packaged procedure declares a selector as one of its formal parameters. (In this context, a selector is a variable used to select one of several alternatives in a conditional control statement.) When called, the procedure opens the cursor variable emp_cv for the chosen query.
CREATE PACKAGE emp_data AS TYPE GenericCurTyp IS REF CURSOR; TYPE EmpCurTyp IS REF CURSOR RETURN emp%ROWTYPE; PROCEDURE open_emp_cv (emp_cv IN OUT EmpCurTyp, choice NUMBER); END emp_data; CREATE PACKAGE BODY emp_data AS PROCEDURE open_emp_cv ( emp_cv IN OUT EmpCurTyp, choice NUMBER) IS BEGIN IF choice = 1 THEN OPEN emp_cv FOR SELECT * FROM emp WHERE comm IS NOT NULL; ELSIF choice = 2 THEN OPEN emp_cv FOR SELECT * FROM emp WHERE sal > 2500; ELSIF choice = 3 THEN OPEN emp_cv FOR SELECT * FROM emp WHERE deptno = 20; END IF; END open_emp_cv; END emp_data;
For more flexibility, you can pass a cursor variable and a selector to a stored procedure that executes queries with different return types. Consider this example:
CREATE PACKAGE BODY emp_data AS PROCEDURE open_cv ( generic_cv IN OUT GenericCurTyp, choice NUMBER) IS BEGIN IF choice = 1 THEN OPEN generic_cv FOR SELECT * FROM emp; ELSIF choice = 2 THEN OPEN generic_cv FOR SELECT * FROM dept; ELSIF choice = 3 THEN OPEN generic_cv FOR SELECT * FROM salgrade; END IF; END open_cv; END emp_data;
You can declare a cursor variable in a PL/SQL host environment such as an OCI or Pro*C program. To use the cursor variable, you must pass it as a host variable to PL/SQL. In the following Pro*C example, you pass a host cursor variable and selector to a PL/SQL block, which opens the cursor variable for the chosen query:
EXEC SQL BEGIN DECLARE SECTION; ... /* Declare host cursor variable. */ SQL_CURSOR generic_cv; int choice; EXEC SQL END DECLARE SECTION; ... /* Initialize host cursor variable. */ EXEC SQL ALLOCATE :generic_cv; ... /* Pass host cursor variable and selector to PL/SQL block. */ EXEC SQL EXECUTE BEGIN IF :choice = 1 THEN OPEN :generic_cv FOR SELECT * FROM emp; ELSIF :choice = 2 THEN OPEN :generic_cv FOR SELECT * FROM dept; ELSIF :choice = 3 THEN OPEN :generic_cv FOR SELECT * FROM salgrade; END IF; END; END-EXEC;
Host cursor variables are compatible with any query return type. They behave just like weakly typed PL/SQL cursor variables.
The FETCH statement retrieves rows one at a time from the result set of a multi-row query. Here is the syntax:
FETCH {cursor_variable_name | :host_cursor_variable_name} INTO {variable_name[, variable_name]... | record_name};
In the next example, you fetch rows from the cursor variable emp_cv into the user-defined record emp_rec:
LOOP /* Fetch from cursor variable. */ FETCH emp_cv INTO emp_rec; EXIT WHEN emp_cv%NOTFOUND; -- exit when last row is fetched -- process data record END LOOP;
Any variables in the associated query are evaluated only when the cursor variable is opened. To change the result set or the values of variables in the query, you must reopen the cursor variable with the variables set to their new values. However, you can use a different INTO clause on separate fetches with the same cursor variable. Each fetch retrieves another row from the same result set.
PL/SQL makes sure the return type of the cursor variable is compatible with the INTO clause of the FETCH statement. For each column value returned by the query associated with the cursor variable, there must be a corresponding, type-compatible field or variable in the INTO clause. Also, the number of fields or variables must equal the number of column values. Otherwise, you get an error.
The error occurs at compile time if the cursor variable is strongly typed or at run time if it is weakly typed. At run time, PL/SQL raises the predefined exception ROWTYPE_MISMATCH before the first fetch. So, if you trap the error and execute the FETCH statement using a different INTO clause, no rows are lost.
When you declare a cursor variable as the formal parameter of a subprogram that fetches from the cursor variable, you must specify the IN or IN OUT mode. However, if the subprogram also opens the cursor variable, you must specify the IN OUT mode.
If you try to fetch from a closed or never-opened cursor variable, PL/SQL raises the predefined exception INVALID_CURSOR.
The CLOSE statement disables a cursor variable. After that, the associated result set is undefined. Here is the syntax:
CLOSE {cursor_variable_name | :host_cursor_variable_name);
In the following example, when the last row is processed, you close the cursor variable emp_cv:
LOOP FETCH emp_cv INTO emp_rec; EXIT WHEN emp_cv%NOTFOUND; -- process data record END LOOP; /* Close cursor variable. */ CLOSE emp_cv;
When declaring a cursor variable as the formal parameter of a subprogram that closes the cursor variable, you must specify the IN or IN OUT mode.
If you try to close an already-closed or never-opened cursor variable, PL/SQL raises the predefined exception INVALID_CURSOR.
Consider the stored procedure below, which searches the database of a main library for books, periodicals, and tapes. A master table stores the title and category code (where 1 = book, 2 = periodical, 3 = tape) of each item. Three detail tables store category-specific information. When called, the procedure searches the master table by title, uses the associated category code to pick an OPEN-FOR statement, then opens a cursor variable for a query of the proper detail table.
CREATE PACKAGE cv_types AS TYPE LibCurTyp IS REF CURSOR; ... END cv_types; CREATE PROCEDURE find_item (title VARCHAR2(100), lib_cv IN OUT cv_types.LibCurTyp) AS code BINARY_INTEGER; BEGIN SELECT item_code FROM titles INTO code WHERE item_title = title; IF code = 1 THEN OPEN lib_cv FOR SELECT * FROM books WHERE book_title = title; ELSIF code = 2 THEN OPEN lib_cv FOR SELECT * FROM periodicals WHERE periodical_title = title; ELSIF code = 3 THEN OPEN lib_cv FOR SELECT * FROM tapes WHERE tape_title = title; END IF; END find_item;
A client-side application in a branch library might use the following PL/SQL block to display the retrieved information:
DECLARE lib_cv cv_types.LibCurTyp; book_rec books%ROWTYPE; periodical_rec periodicals%ROWTYPE; tape_rec tapes%ROWTYPE; BEGIN get_title(:title); -- title is a host variable find_item(:title, lib_cv); FETCH lib_cv INTO book_rec; display_book(book_rec); EXCEPTION WHEN ROWTYPE_MISMATCH THEN BEGIN FETCH lib_cv INTO periodical_rec; display_periodical(periodical_rec); EXCEPTION WHEN ROWTYPE_MISMATCH THEN FETCH lib_cv INTO tape_rec; display_tape(tape_rec); END; END;
The following Pro*C program prompts the user to select a database table, opens a cursor variable for a query of that table, then fetches rows returned by the query:
#include <stdio.h> #include <sqlca.h> void sql_error(); main() { char temp[32]; EXEC SQL BEGIN DECLARE SECTION; char * uid = "scott/tiger"; SQL_CURSOR generic_cv; /* cursor variable */ int table_num; /* selector */ struct /* EMP record */ { int emp_num; char emp_name[11]; char job_title[10]; int manager; char hire_date[10]; float salary; float commission; int dept_num; } emp_rec; struct /* DEPT record */ { int dept_num; char dept_name[15]; char location[14]; } dept_rec; struct /* BONUS record */ { char emp_name[11]; char job_title[10]; float salary; } bonus_rec; EXEC SQL END DECLARE SECTION; /* Handle Oracle errors. */ EXEC SQL WHENEVER SQLERROR DO sql_error(); /* Connect to Oracle. */ EXEC SQL CONNECT :uid; /* Initialize cursor variable. */ EXEC SQL ALLOCATE :generic_cv; /* Exit loop when done fetching. */ EXEC SQL WHENEVER NOT FOUND DO break; for (;;) { printf("\n1 = EMP, 2 = DEPT, 3 = BONUS"); printf("\nEnter table number (0 to quit): "); gets(temp); table_num = atoi(temp); if (table_num <= 0) break; /* Open cursor variable. */ EXEC SQL EXECUTE BEGIN IF :table_num = 1 THEN OPEN :generic_cv FOR SELECT * FROM emp; ELSIF :table_num = 2 THEN OPEN :generic_cv FOR SELECT * FROM dept; ELSIF :table_num = 3 THEN OPEN :generic_cv FOR SELECT * FROM bonus; END IF; END; END-EXEC; for (;;) { switch (table_num) { case 1: /* Fetch row into EMP record. */ EXEC SQL FETCH :generic_cv INTO :emp_rec; break; case 2: /* Fetch row into DEPT record. */ EXEC SQL FETCH :generic_cv INTO :dept_rec; break; case 3: /* Fetch row into BONUS record. */ EXEC SQL FETCH :generic_cv INTO :bonus_rec; break; } /* Process data record here. */ } /* Close cursor variable. */ EXEC SQL CLOSE :generic_cv; } exit(0); } void sql_error() { /* Handle SQL error here. */ }
A host variable is a variable you declare in a host environment, then pass to one or more PL/SQL programs, which can use it like any other variable. In the SQL*Plus environment, to declare a host variable, use the command VARIABLE. For example, you declare a variable of type NUMBER as follows:
VARIABLE return_code NUMBER
Both SQL*Plus and PL/SQL can reference the host variable, and SQL*Plus can display its value.
Note: If you declare a host variable with the same name as a PL/SQL program variable, the latter takes precedence.
To reference a host variable in PL/SQL, you must prefix its name with a colon (:), as the following example shows:
BEGIN :return_code := 0; IF credit_check_ok(acct_no) THEN :return_code := 1; END IF; ... END;
To display the value of a host variable in SQL*Plus, use the PRINT command, as follows:
SQL> PRINT return_code RETURN_CODE ----------- 1
The SQL*Plus datatype REFCURSOR lets you declare cursor variables, which you can use to return query results from stored subprograms. In the script below, you declare a host variable of type REFCURSOR. You use the SQL*Plus command SET AUTOPRINT ON to display the query results automatically.
CREATE PACKAGE emp_data AS TYPE EmpRecTyp IS RECORD ( emp_id NUMBER(4), emp_name CHAR(10), job_title CHAR(9), dept_name CHAR(14), dept_loc CHAR(13)); TYPE EmpCurTyp IS REF CURSOR RETURN EmpRecTyp; PROCEDURE get_staff ( dept_no IN NUMBER, emp_cv IN OUT EmpCurTyp); END; / CREATE PACKAGE BODY emp_data AS PROCEDURE get_staff ( dept_no IN NUMBER, emp_cv IN OUT EmpCurTyp) IS BEGIN OPEN emp_cv FOR SELECT empno, ename, job, dname, loc FROM emp, dept WHERE emp.deptno = dept_no AND emp.deptno = dept.deptno ORDER BY empno; END; END; / COLUMN EMPNO HEADING Number COLUMN ENAME HEADING Name COLUMN JOB HEADING JobTitle COLUMN DNAME HEADING Department COLUMN LOC HEADING Location SET AUTOPRINT ON VARIABLE cv REFCURSOR EXECUTE emp_data.get_staff(20, :cv)
When passing host cursor variables to PL/SQL, you can reduce network traffic by grouping OPEN-FOR statements. For example, the following PL/SQL block opens five cursor variables in a single round trip:
/* anonymous PL/SQL block in host environment */ BEGIN OPEN :emp_cv FOR SELECT * FROM emp; OPEN :dept_cv FOR SELECT * FROM dept; OPEN :grade_cv FOR SELECT * FROM salgrade; OPEN :pay_cv FOR SELECT * FROM payroll; OPEN :ins_cv FOR SELECT * FROM insurance; END;
This might be useful in Oracle Forms, for instance, when you want to populate a multi-block form.
When you pass host cursor variables to a PL/SQL block for opening, the query work areas to which they point remain accessible after the block completes. That allows your OCI or Pro*C program to use these work areas for ordinary cursor operations. In the following example, you open several such work areas in a single round trip:
BEGIN OPEN :c1 FOR SELECT 1 FROM dual; OPEN :c2 FOR SELECT 1 FROM dual; OPEN :c3 FOR SELECT 1 FROM dual; OPEN :c4 FOR SELECT 1 FROM dual; OPEN :c5 FOR SELECT 1 FROM dual; ... END;
The cursors assigned to c1, c2, c3, c4, and c5 behave normally, and you can use them for any purpose. When finished, simply release the cursors, as follows:
BEGIN CLOSE :c1; CLOSE :c2; CLOSE :c3; CLOSE :c4; CLOSE :c5; ... END;
If both cursor variables involved in an assignment are strongly typed, they must have the same datatype. In the following example, even though the cursor variables have the same return type, the assignment raises an exception because they have different datatypes:
DECLARE TYPE EmpCurTyp IS REF CURSOR RETURN emp%ROWTYPE; TYPE TmpCurTyp IS REF CURSOR RETURN emp%ROWTYPE; PROCEDURE open_emp_cv ( emp_cv IN OUT EmpCurTyp, tmp_cv IN OUT TmpCurTyp) IS BEGIN ... emp_cv := tmp_cv; -- causes 'wrong type' error END;
However, if one or both cursor variables are weakly typed, they need not have the same datatype.
If you try to fetch from, close, or apply cursor attributes to a cursor variable that does not point to a query work area, PL/SQL raises INVALID_CURSOR. You can make a cursor variable (or parameter) point to a query work area in two ways:
OPEN the cursor variable FOR the query.
OPENed host cursor variable or PL/SQL cursor variable.
The following example shows how these ways interact:
DECLARE TYPE EmpCurTyp IS REF CURSOR RETURN emp%ROWTYPE; emp_cv1 EmpCurTyp; emp_cv2 EmpCurTyp; emp_rec emp%ROWTYPE; BEGIN /* The following assignment is useless because emp_cv1 does not point to a query work area yet. */ emp_cv2 := emp_cv1; -- useless /* Make emp_cv1 point to a query work area. */ OPEN emp_cv1 FOR SELECT * FROM emp; /* Use emp_cv1 to fetch first row from emp table. */ FETCH emp_cv1 INTO emp_rec; /* The following fetch raises an exception because emp_cv2 does not point to a query work area yet. */ FETCH emp_cv2 INTO emp_rec; -- raises INVALID_CURSOR EXCEPTION WHEN INVALID_CURSOR THEN /* Make emp_cv1 and emp_cv2 point to same work area. */ emp_cv2 := emp_cv1; /* Use emp_cv2 to fetch second row from emp table. */ FETCH emp_cv2 INTO emp_rec; /* Reuse work area for another query. */ OPEN emp_cv2 FOR SELECT * FROM old_emp; /* Use emp_cv1 to fetch first row from old_emp table. The following fetch succeeds because emp_cv1 and emp_cv2 point to the same query work area. */ FETCH emp_cv1 INTO emp_rec; -- succeeds END;
Be careful when passing cursor variables as parameters. At run time, PL/SQL raises ROWTYPE_MISMATCH if the return types of the actual and formal parameters are incompatible.
In the Pro*C example below, you define a packaged REF CURSOR type, specifying the return type emp%ROWTYPE. Next, you create a stand-alone procedure that references the new type. Then, inside a PL/SQL block, you open a host cursor variable for a query of the dept table. Later, when you pass the open host cursor variable to the stored procedure, PL/SQL raises ROWTYPE_MISMATCH because the return types of the actual and formal parameters are incompatible.
CREATE PACKAGE cv_types AS TYPE EmpCurTyp IS REF CURSOR RETURN emp%ROWTYPE; ... END cv_types; / CREATE PROCEDURE open_emp_cv (emp_cv IN OUT cv_types.EmpCurTyp) AS BEGIN OPEN emp_cv FOR SELECT * FROM emp; END open_emp_cv; / -- anonymous PL/SQL block in Pro*C program EXEC SQL EXECUTE BEGIN OPEN :cv FOR SELECT * FROM dept; ... open_emp_cv(:cv); -- raises ROWTYPE_MISMATCH END; END-EXEC;
Currently, cursor variables are subject to the following restrictions:
CREATE PACKAGE emp_stuff AS TYPE EmpCurTyp IS REF CURSOR RETURN EmpRecTyp; emp_cv IN OUT EmpCurTyp; -- illegal END emp_stuff;
OPEN-FOR statement cannot be FOR UPDATE.
REF CURSOR types to specify column types in a CREATE TABLE or CREATE VIEW statement. So, database columns cannot store the values of cursor variables.
REF CURSOR type to specify the element type of a collection, which means that elements in a index-by table, nested table, or varray cannot store the values of cursor variables.
FOR loop is illegal:
DECLARE CURSOR emp_cur IS SELECT * FROM emp; -- static cursor TYPE EmpCurTyp IS REF CURSOR RETURN emp%ROWTYPE; emp_cv EmpCurTyp; -- cursor variable BEGIN ... FOR emp_rec IN emp_cv LOOP ... -- illegal END;
Each cursor or cursor variable has four attributes: %FOUND, %ISOPEN %NOTFOUND, and %ROWCOUNT. When appended to the cursor or cursor variable, these attributes return useful information about the execution of a data manipulation statement. You can use cursor attributes in procedural statements but not in SQL statements.
Explicit cursor attributes return information about the execution of a multi-row query. When an explicit cursor or a cursor variable is opened, the rows that satisfy the associated query are identified and form the result set. Rows are fetched from the result set one at a time.
After a cursor or cursor variable is opened but before the first fetch, %FOUND yields NULL. Thereafter, it yields TRUE if the last fetch returned a row, or FALSE if the last fetch failed to return a row. In the following example, you use %FOUND to select an action:
LOOP FETCH c1 INTO my_ename, my_sal, my_hiredate; IF c1%FOUND THEN -- fetch succeeded ... ELSE -- fetch failed, so exit loop EXIT; END IF; END LOOP;
If a cursor or cursor variable is not open, referencing it with %FOUND raises the predefined exception INVALID_CURSOR.
%ISOPEN yields TRUE if its cursor or cursor variable is open; otherwise, %ISOPEN yields FALSE. In the following example, you use %ISOPEN to select an action:
IF c1%ISOPEN THEN -- cursor is open ... ELSE -- cursor is closed, so open it OPEN c1; END IF;
%NOTFOUND is the logical opposite of %FOUND. %NOTFOUND yields FALSE if the last fetch returned a row, or TRUE if the last fetch failed to return a row. In the following example, you use %NOTFOUND to exit a loop when FETCH fails to return a row:
LOOP FETCH c1 INTO my_ename, my_sal, my_hiredate; EXIT WHEN c1%NOTFOUND; ... END LOOP;
Before the first fetch, %NOTFOUND evaluates to NULL. So, if FETCH never executes successfully, the loop is never exited. That is because the EXIT WHEN statement executes only if its WHEN condition is true. To be safe, you might want to use the following EXIT statement instead:
EXIT WHEN c1%NOTFOUND OR ci%NOTFOUND IS NULL;
If a cursor or cursor variable is not open, referencing it with %NOTFOUND raises INVALID_CURSOR.
When its cursor or cursor variable is opened, %ROWCOUNT is zeroed. Before the first fetch, %ROWCOUNT yields 0. Thereafter, it yields the number of rows fetched so far. The number is incremented if the last fetch returned a row. In the next example, you use %ROWCOUNT to take action if more than ten rows have been fetched:
LOOP FETCH c1 INTO my_ename, my_deptno; IF c1%ROWCOUNT > 10 THEN ... END IF; ... END LOOP;
If a cursor or cursor variable is not open, referencing it with %ROWCOUNT raises INVALID_CURSOR.
Table 5-1 shows what each cursor attribute yields before and after you execute an OPEN, FETCH, or CLOSE statement.
Suppose you have a table named data_table that holds data collected from laboratory experiments, and you want to analyze the data from experiment 1. In the following example, you compute the results and store them in a database table named temp:
-- available online in file 'examp5' DECLARE num1 data_table.n1%TYPE; -- Declare variables num2 data_table.n2%TYPE; -- having same types as num3 data_table.n3%TYPE; -- database columns result temp.col1%TYPE; CURSOR c1 IS SELECT n1, n2, n3 FROM data_table WHERE exper_num = 1; BEGIN OPEN c1; LOOP FETCH c1 INTO num1, num2, num3; EXIT WHEN c1%NOTFOUND; -- TRUE when FETCH finds no more rows result := num2/(num1 + num3); INSERT INTO temp VALUES (result, NULL, NULL); END LOOP; CLOSE c1; COMMIT; END;
In the next example, you check all storage bins that contain part number 5469, withdrawing their contents until you accumulate 1000 units:
-- available online in file 'examp6' DECLARE CURSOR bin_cur(part_number NUMBER) IS SELECT amt_in_bin FROM bins WHERE part_num = part_number AND amt_in_bin > 0 ORDER BY bin_num FOR UPDATE OF amt_in_bin; bin_amt bins.amt_in_bin%TYPE; total_so_far NUMBER(5) := 0; amount_needed CONSTANT NUMBER(5) := 1000; bins_looked_at NUMBER(3) := 0; BEGIN OPEN bin_cur(5469); WHILE total_so_far < amount_needed LOOP FETCH bin_cur INTO bin_amt; EXIT WHEN bin_cur%NOTFOUND; -- if we exit, there's not enough to fill the order bins_looked_at := bins_looked_at + 1; IF total_so_far + bin_amt < amount_needed THEN UPDATE bins SET amt_in_bin = 0 WHERE CURRENT OF bin_cur; -- take everything in the bin total_so_far := total_so_far + bin_amt; ELSE -- we finally have enough UPDATE bins SET amt_in_bin = amt_in_bin - (amount_needed - total_so_far) WHERE CURRENT OF bin_cur; total_so_far := amount_needed; END IF; END LOOP; CLOSE bin_cur; INSERT INTO temp VALUES (NULL, bins_looked_at, '<- bins looked at'); COMMIT; END;
Implicit cursor attributes return information about the execution of an INSERT, UPDATE, DELETE, or SELECT INTO statement. The values of the cursor attributes always refer to the most recently executed SQL statement. Before Oracle opens the SQL cursor, the implicit cursor attributes yield NULL.
Until a SQL data manipulation statement is executed, %FOUND yields NULL. Thereafter, %FOUND yields TRUE if an INSERT, UPDATE, or DELETE statement affected one or more rows, or a SELECT INTO statement returned one or more rows. Otherwise, %FOUND yields FALSE. In the following example, you use %FOUND to insert a row if a delete succeeds:
DELETE FROM emp WHERE empno = my_empno; IF SQL%FOUND THEN -- delete succeeded INSERT INTO new_emp VALUES (my_empno, my_ename, ...);
Oracle closes the SQL cursor automatically after executing its associated SQL statement. As a result, %ISOPEN always yields FALSE.
%NOTFOUND is the logical opposite of %FOUND. %NOTFOUND yields TRUE if an INSERT, UPDATE, or DELETE statement affected no rows, or a SELECT INTO statement returned no rows. Otherwise, %NOTFOUND yields FALSE.
%ROWCOUNT yields the number of rows affected by an INSERT, UPDATE, or DELETE statement, or returned by a SELECT INTO statement. %ROWCOUNT yields 0 if an INSERT, UPDATE, or DELETE statement affected no rows, or a SELECT INTO statement returned no rows. In the following example, you use %ROWCOUNT to take action if more than ten rows have been deleted:
DELETE FROM emp WHERE ... IF SQL%ROWCOUNT > 10 THEN -- more than 10 rows were deleted ... END IF;
If a SELECT INTO statement returns more than one row, PL/SQL raises the predefined exception TOO_MANY_ROWS and %ROWCOUNT yields 1, not the actual number of rows that satisfy the query.
The values of the cursor attributes always refer to the most recently executed SQL statement, wherever that statement is. It might be in a different scope (for example, in a sub-block). So, if you want to save an attribute value for later use, assign it to a Boolean variable immediately. In the following example, relying on the IF condition is dangerous because the procedure check_status might have changed the value of %NOTFOUND:
BEGIN ... UPDATE parts SET quantity = quantity - 1 WHERE partno = part_id; check_status(part_id); -- procedure call IF SQL%NOTFOUND THEN -- dangerous! ... END; END;
You can improve the code as follows:
BEGIN ... UPDATE parts SET quantity = quantity - 1 WHERE partno = part_id; sql_notfound := SQL%NOTFOUND; -- assign value to Boolean variable check_status(part_id); IF sql_notfound THEN ... END;
If a SELECT INTO statement fails to return a row, PL/SQL raises the predefined exception NO_DATA_FOUND whether you check %NOTFOUND on the next line or not. Consider the following example:
BEGIN ... SELECT sal INTO my_sal FROM emp WHERE empno = my_empno; -- might raise NO_DATA_FOUND IF SQL%NOTFOUND THEN -- condition tested only when false ... -- this action is never taken END IF;
The check is useless because the IF condition is tested only when %NOTFOUND is false. When PL/SQL raises NO_DATA_FOUND, normal execution stops and control transfers to the exception-handling part of the block.
However, a SELECT INTO statement that calls a SQL aggregate function never raises NO_DATA_FOUND because aggregate functions always return a value or a null. In such cases, %NOTFOUND yields FALSE, as the following example shows:
BEGIN ... SELECT MAX(sal) INTO my_sal FROM emp WHERE deptno = my_deptno; -- never raises NO_DATA_FOUND IF SQL%NOTFOUND THEN -- always tested but never true ... -- this action is never taken END IF; EXCEPTION WHEN NO_DATA_FOUND THEN ... -- never invoked
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.
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.
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 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 illegal:
COMMIT FORCE '23.51.54'; -- illegal
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;
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.
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 per session is unlimited. An active savepoint is one marked since the last commit or rollback.
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.
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;
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; 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.
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.
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.
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 FOR UPDATE clause 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 the table has 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 table is 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;
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 Oracle8i 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.
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;
PL/SQL was designed for high-speed transaction processing. As a result, the compiler limits the number of tokens (identifiers, keywords, operators, and so on) that a program unit (block, subprogram, or package) can contain. Units that exceed the limit cause a program too large compilation error. Generally, unit specs larger than 32K and unit bodies larger than 64K exceed the token limit. However, smaller units can also exceed the limit if they contain many variables or complex SQL statements.
Typically, this problem occurs with package bodies or anonymous blocks. With a package, the best solution is to divide it into smaller packages. With a block, the best solution is to redefine it as a series of subprograms, which can be stored in the database. For more information, see Chapter 7, "Subprograms".
Another solution is to break the block into two sub-blocks. Consider the SQL*Plus script below. Before the first block terminates, it inserts any data the second block needs into a database table called temp. When the second block starts executing, it selects the data from temp. This approximates the passing of parameters from one procedure to another.
DECLARE mode NUMBER; median NUMBER; BEGIN ... INSERT INTO temp (col1, col2, col3) VALUES (mode, median, 'blockA'); END; / DECLARE mode NUMBER; median NUMBER; BEGIN SELECT col1, col2 INTO mode, median FROM temp WHERE col3 = 'blockA'; ... END; /
This method works unless you must re-execute the first block while the second block is still executing, or unless two or more users must run the script concurrently.
Alternatively, you can embed the blocks in a host language such as C or COBOL. That way, you can re-execute the first block using flow-of-control statements. Also, you can store data in global host variables instead of a database table. For example, you might embed the following two blocks in a Pro*C program:
/* The host variables 'my_sal', 'my_comm', and 'my_empno' are assigned values in the host environment. The host variable 'comm_ind' is an indicator variable. */ BEGIN SELECT sal, comm INTO :my_sal, :my_comm:comm_ind FROM emp WHERE empno = :my_empno; IF :my_comm:comm_ind IS NULL THEN ... END IF; END; BEGIN ... IF :my_comm:comm_ind > 1000 THEN :my_sal := :my_sal * 1.10; UPDATE emp SET sal = :my_sal WHERE empno = :my_empno; END IF; END;
A transaction is a series of SQL statements that does a logical unit of work. Often, one transaction starts another. In some applications, a transaction must operate outside the scope of the transaction that started it. This can happen, for example, when a transaction calls out to a data cartridge.
An autonomous transaction is an independent transaction started by another transaction, the main transaction. Autonomous transactions let you suspend the main transaction, do SQL operations, commit or roll back those operations, then resume the main transaction. Figure 5-1 shows how control flows from the main transaction (MT) to an autonomous transaction (AT) and back again.
Once started, an autonomous transaction is fully independent. It shares no locks, resources, or commit-dependencies with the main transaction. So, you can log events, increment retry counters, and so on, even if the main transaction rolls back.
More important, autonomous transactions help you build modular, reusable software components. For example, stored procedures can start and finish autonomous transactions on their own. A calling application need not know about a procedure's autonomous operations, and the procedure need not know about the application's transaction context. That makes autonomous transactions less error-prone than regular transactions and easier to use.
Furthermore, autonomous transactions have all the functionality of regular transactions. They allow parallel queries, distributed processing, and all the transaction control statements including SET TRANSACTION.
To define autonomous transactions, you use the pragma (compiler directive) AUTONOMOUS_TRANSACTION. The pragma instructs the PL/SQL compiler to mark a routine as autonomous (independent). In this context, the term routine includes
You can code the pragma anywhere in the declarative section of a routine. But, for readability, code the pragma at the top of the section. The syntax follows:
PRAGMA AUTONOMOUS_TRANSACTION;
In the following example, you mark a packaged function as autonomous:
CREATE PACKAGE banking AS ... FUNCTION balance (acct_id INTEGER) RETURN REAL; END banking; CREATE PACKAGE BODY banking AS ... FUNCTION balance (acct_id INTEGER) RETURN REAL IS PRAGMA AUTONOMOUS_TRANSACTION; my_bal REAL; BEGIN ... END; END banking;
Restriction: You cannot use the pragma to mark all subprograms in a package (or all methods in an object type) as autonomous. Only individual routines can be marked autonomous. For example, the following pragma is illegal:
CREATE PACKAGE banking AS PRAGMA AUTONOMOUS_TRANSACTION; -- illegal ... FUNCTION balance (acct_id INTEGER) RETURN REAL; END banking;
In the next example, you mark a stand-alone procedure as autonomous:
CREATE PROCEDURE close_account (acct_id INTEGER, OUT balance) AS PRAGMA AUTONOMOUS_TRANSACTION; my_bal REAL; BEGIN ... END;
In the following example, you mark a PL/SQL block as autonomous:
DECLARE PRAGMA AUTONOMOUS_TRANSACTION; my_empno NUMBER(4); BEGIN ... END;
Restriction: You cannot mark a nested PL/SQL block as autonomous.
In the example below, you mark a database trigger as autonomous. Unlike regular triggers, autonomous triggers can contain transaction control statements such as COMMIT and ROLLBACK.
CREATE TRIGGER parts_trigger BEFORE INSERT ON parts FOR EACH ROW DECLARE PRAGMA AUTONOMOUS_TRANSACTION; BEGIN INSERT INTO parts_log VALUES(:new.pnum, :new.pname); COMMIT; -- allowed only in autonomous triggers END;
Although an autonomous transaction is started by another transaction, it is not a nested transaction for the following reasons:
As Figure 5-2 shows, the main transaction shares its context with nested transactions, but not with autonomous transactions. Likewise, when one autonomous routine calls another (or itself recursively), the routines share no transaction context. However, when an autonomous routine calls a non-autonomous routine, the routines share the same transaction context.
As Figure 5-3 shows, changes made by an autonomous transaction become visible to other transactions when the autonomous transaction commits. The changes also become visible to the main transaction when it resumes, but only if its isolation level is set to READ COMMITTED (the default).
If you set the isolation level of the main transaction to SERIALIZABLE, as follows, changes made by its autonomous transactions are not visible to the main transaction when it resumes:
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
The first SQL statement in an autonomous routine begins a transaction. When one transaction ends, the next SQL statement begins another transaction. All SQL statements executed since the last commit or rollback make up the current transaction. To control autonomous transactions, use the following statements, which apply only to the current (active) transaction:
COMMIT ends the current transaction and makes permanent changes made during that transaction. ROLLBACK ends the current transaction and undoes changes made during that transaction. ROLLBACK TO undoes part of a transaction. SAVEPOINT names and marks the current point in a transaction. SET TRANSACTION sets transaction properties such as read/write access and isolation level.
Note: Transaction properties set in the main transaction apply only to that transaction, not to its autonomous transactions, and vice versa.
When you enter the executable section of an autonomous routine, the main transaction suspends. When you exit the routine, the main transaction resumes.
To exit normally, you must explicitly commit or roll back all autonomous transactions. If the routine (or any routine called by it) has pending transactions, an exception is raised, and the pending transactions are rolled back.
COMMIT and ROLLBACK end the active autonomous transaction but do not exit the autonomous routine. As Figure 5-4 shows, when one transaction ends, the next SQL statement begins another transaction.
The scope of a savepoint is the transaction in which it is defined. Savepoints defined in the main transaction are unrelated to savepoints defined in its autonomous transactions. In fact, the main transaction and an autonomous transaction can use the same savepoint names.
You can roll back only to savepoints marked in the current transaction. So, when in an autonomous transaction, you cannot roll back to a savepoint marked in the main transaction. To do so, you must resume the main transaction by exiting the autonomous routine.
When in the main transaction, rolling back to a savepoint marked before you started an autonomous transaction does not roll back the autonomous transaction. Remember, autonomous transactions are fully independent of the main transaction.
To avoid some common errors, keep the following points in mind when designing autonomous transactions:
TRANSACTIONS specifies the maximum number of concurrent transactions. That number might be exceeded if autonomous transactions (which run concurrently with the main transaction) are not taken into account.
Among other things, you can use database triggers to log events transparently. Suppose you want to track all inserts into a table, even those that roll back. In the example below, you use a trigger to insert duplicate rows into a shadow table. Because it is autonomous, the trigger can commit inserts into the shadow table whether or not you commit inserts into the main table.
-- create a main table and its shadow table CREATE TABLE parts (pnum NUMBER(4), pname VARCHAR2(15)); CREATE TABLE parts_log (pnum NUMBER(4), pname VARCHAR2(15)); -- ceate an autonomous trigger that inserts into the -- shadow table before each insert into the main table CREATE TRIGGER parts_trig BEFORE INSERT ON parts FOR EACH ROW DECLARE PRAGMA AUTONOMOUS_TRANSACTION; BEGIN INSERT INTO parts_log VALUES(:new.pnum, :new.pname); COMMIT; END; -- insert a row into the main table, and then commit the insert INSERT INTO parts VALUES (1040, 'Head Gasket'); COMMIT; -- insert another row, but then roll back the insert INSERT INTO parts VALUES (2075, 'Oil Pan'); ROLLBACK; -- show that only committed inserts add rows to the main table SELECT * FROM parts ORDER BY pnum; PNUM PNAME ------- --------------- 1040 Head Gasket -- show that both committed and rolled-back inserts add rows -- to the shadow table SELECT * FROM parts_log ORDER BY pnum; PNUM PNAME ------- --------------- 1040 Head Gasket 2075 Oil Pan
A function called from SQL statements must obey certain rules meant to control side effects. (See "Controlling Sides Effects".) To check for violations of the rules, you can use the pragma RESTRICT_REFERENCES, which instructs the compiler to report reads of/writes to database tables, package variables, or both. (See Oracle8i Application Developer's Guide - Fundamentals.)
However, all autonomous routines have read/write access to the database. So, they never violate the rules "read no database state" and "write no database state." This can be useful, as the example below shows. When you call the packaged function log_msg from a query, it inserts a message into database table debug_output without violating the rule "write no database state."
-- create the debug table CREATE TABLE debug_output (msg VARCHAR2(200)); -- create the package spec CREATE PACKAGE debugging AS FUNCTION log_msg (msg VARCHAR2) RETURN VARCHAR2; PRAGMA RESTRICT_REFERENCES(log_msg, WNDS, RNDS); END debugging; -- create the package body CREATE PACKAGE BODY debugging AS FUNCTION log_msg (msg VARCHAR2) RETURN VARCHAR2 IS PRAGMA AUTONOMOUS_TRANSACTION; BEGIN -- the following insert does not violate the constraint -- WNDS because this is an autonomous routine INSERT INTO debug_output VALUES (msg); COMMIT; RETURN msg; END; END debugging; -- call the packaged function from a query DECLARE my_empno NUMBER(4); my_ename VARCHAR2(15); BEGIN ... SELECT debugging.log_msg(ename) INTO my_ename FROM emp WHERE empno = my_empno; -- even if you roll back in this scope, the insert -- into 'debug_output' remains committed because -- it is part of an autonomous transaction IF ... THEN ROLLBACK; END IF; END;
This section gives several techniques for improving performance and explains how your applications can use them.
Collection types (see Chapter 4) and object types (see Chapter 9) increase your productivity by allowing for realistic data modeling. Complex real-world entities and relationships map directly into object types. And, a well-constructed object model can improve application performance by eliminating table joins, reducing round trips, and the like.
Client programs, including PL/SQL programs, can declare objects and collections, pass them as parameters, store them in the database, retrieve them, and so on. Also, by encapsulating operations with data, object types let you move data-maintenance code out of SQL scripts and PL/SQL blocks into methods.
Objects and collections are more efficient to store and retrieve because they can be manipulated as a whole. Also, object support is integrated architecturally with the database, so it can take advantage of the many scalability and performance improvements built into Oracle8i.
When SQL statements execute inside a loop using collection elements as bind variables, context switching between the PL/SQL and SQL engines can slow down execution. For example, the following UPDATE statement is sent to the SQL engine with each iteration of the FOR loop:
DECLARE TYPE NumList IS VARRAY(20) OF NUMBER; depts NumList := NumList(10, 30, 70, ...); -- department numbers BEGIN ... FOR i IN depts.FIRST..depts.LAST LOOP ... UPDATE emp SET sal = sal * 1.10 WHERE deptno = depts(i); END LOOP; END;
In such cases, if the SQL statement affects five or more database rows, the use of bulk binds can improve performance considerably. For example, the following UPDATE statement is sent to the SQL engine just once, with the entire nested table:
FORALL i IN depts.FIRST..depts.LAST UPDATE emp SET sal = sal * 1.10 WHERE deptno = depts(i);
To maximize performance, rewrite your programs as follows:
INSERT, UPDATE, or DELETE statement executes inside a loop and references collection elements, move it into a FORALL statement.
SELECT INTO, FETCH INTO, or RETURNING INTO clause references a collection, incorporate the BULK COLLECT clause.
Note: These are not a trivial tasks. They require careful analysis of program control-flows and dependencies. For more information about bulk binding, see "Taking Advantage of Bulk Binds".
Some programs (a general-purpose report writer for example) must build and process a variety of SQL statements at run time. So, their full text is unknown until then. Such statements can, and probably will, change from execution to execution. So, they are called dynamic SQL statements.
Formerly, to execute dynamic SQL statements, you had to use the supplied package DBMS_SQL. Now, within PL/SQL, you can execute any kind of dynamic SQL statement using an interface called native dynamic SQL.
Native dynamic SQL is easier to use and much faster than package DBMS_SQL. In the following example, you declare a cursor variable, then associate it with a dynamic SELECT statement that returns rows from database table emp:
DECLARE TYPE EmpCurTyp IS REF CURSOR; emp_cv EmpCurTyp; my_ename VARCHAR2(15); my_sal NUMBER := 1000; BEGIN OPEN emp_cv FOR 'SELECT ename, sal FROM emp WHERE sal > :s' USING my_sal; ... END;
For more information, see Chapter 10, "Native Dynamic SQL".
PL/SQL is specialized for SQL transaction processing. So, some tasks are more quickly done in a lower-level language such as C, which is very efficient at machine-precision calculations.
PL/SQL extends the functionality of the Oracle server by providing an interface for calling routines written in other languages. Standard libraries already written and available in other languages can be called from PL/SQL programs. This promotes reusability, efficiency, and modularity.
To speed up execution, you can rewrite computation-bound programs in C. In addition, you can move such programs from client to server, where they will execute faster thanks to more computing power and less across-network communication.
For example, you can write methods for an image object type in C, store them in a dynamic link library (DLL), register the library with PL/SQL, then call it from your applications. At run time, the library loads dynamically and, for safety, runs in a separate address space (implemented as a separate process).
For more information, see Oracle8i Application Developer's Guide - Fundamentals.
By default, OUT and IN OUT parameters are passed by value. That is, the value of an IN OUT actual parameter is copied into the corresponding formal parameter. Then, if the subprogram exits normally, the values assigned to OUT and IN OUT formal parameters are copied into the corresponding actual parameters.
When the parameters hold large data structures such as collections, records, and instances of object types, all this copying slows down execution and uses up memory. To prevent that, you can specify the NOCOPY hint, which allows the PL/SQL compiler to pass OUT and IN OUT parameters by reference. In the following example, you ask the compiler to pass IN OUT parameter my_unit by reference instead of by value:
DECLARE TYPE Platoon IS VARRAY(200) OF Soldier; PROCEDURE reorganize (my_unit IN OUT NOCOPY Platoon) IS ... BEGIN ... END;
For more information, see "NOCOPY Compiler Hint".
Often, applications need information about the row affected by a SQL operation, for example, to generate a report or take a subsequent action. The INSERT, UPDATE, and DELETE statements can include a RETURNING clause, which returns column values from the affected row into PL/SQL variables or host variables. This eliminates the need to SELECT the row after an insert or update, or before a delete. As a result, fewer network round trips, less server CPU time, fewer cursors, and less server memory are required.
In the following example, you update the salary of an employee and at the same time retrieve the employee's name and new salary into PL/SQL variables.
PROCEDURE update_salary (emp_id NUMBER) IS name VARCHAR2(15); new_sal NUMBER; BEGIN UPDATE emp SET sal = sal * 1.1 WHERE empno = emp_id RETURNING ename, sal INTO name, new_sal;
To help you manage the use of memory, PL/SQL provides the pragma SERIALLY_REUSABLE, which lets you mark some packages as serially reusable. You can so mark a package if its state is needed only for the duration of one call to the server (for example, an OCI call to the server or a server-to-server RPC).
The global memory for such packages is pooled in the System Global Area (SGA), not allocated to individual users in the User Global Area (UGA). That way, the package work area can be reused. When the call to the server ends, the memory is returned to the pool. Each time the package is reused, its public variables are initialized to their default values or to NULL.
The maximum number of work areas needed for a package is the number of concurrent users of that package, which is usually much smaller than the number of logged-on users. The increased use of SGA memory is more than offset by the decreased use of UGA memory. Also, Oracle ages-out work areas not in use if it needs to reclaim SGA memory.
For packages without a body, you code the pragma in the package spec using the following syntax:
PRAGMA SERIALLY_REUSABLE;
For packages with a body, you must code the pragma in the spec and body. You cannot code the pragma only in the body. The following example shows how a public variable in a serially reusable package behaves across call boundaries:
CREATE OR REPLACE PACKAGE sr_pkg IS PRAGMA SERIALLY_REUSABLE; num NUMBER := 0; PROCEDURE init_pkg_state(n NUMBER); PROCEDURE print_pkg_state; END sr_pkg; / CREATE OR REPLACE PACKAGE BODY sr_pkg IS PRAGMA SERIALLY_REUSABLE; /* Initialize package state. */ PROCEDURE init_pkg_state (n NUMBER) IS BEGIN sr_pkg.num := n; END; /* Print package state. */ PROCEDURE print_pkg_state IS BEGIN DBMS_OUTPUT.PUT_LINE('Num is: ' || sr_pkg.num); END; END sr_pkg; / BEGIN /* Initialize package state. */ sr_pkg.init_pkg_state(4); /* On same server call, print package state. */ sr_pkg.print_pkg_state; -- prints 4 END; / -- subsequent server call BEGIN -- package's public variable will be initialized to its -- default value automatically sr_pkg.print_pkg_state; -- prints 0 END;
For more information, see Oracle8i Supplied Packages Reference.
When you need to declare an integer variable, use the datatype PLS_INTEGER, which is the most efficient numeric type. That is because PLS_INTEGER values require less storage than INTEGER or NUMBER values, which are represented internally as 22-byte Oracle numbers. Also, PLS_INTEGER operations use machine arithmetic, so they are faster than BINARY_INTEGER, INTEGER, or NUMBER operations, which use library arithmetic.
Furthermore, INTEGER, NATURAL, NATURALN, POSITIVE, POSITIVEN, and SIGNTYPE are constrained subtypes. So, their variables require precision checking at run time, which can affect performance.
In PL/SQL, using the NOT NULL constraint incurs a performance cost. Consider the following example:
PROCEDURE calc_m IS m NUMBER NOT NULL; a NUMBER; b NUMBER; BEGIN ... m := a + b;
Because m is constrained by NOT NULL, the value of the expression a + b is assigned to a temporary variable, which is then tested for nullity. If the variable is not null, its value is assigned to m. Otherwise, an exception is raised. However, if m were not constrained, the value would be assigned to m directly.
A more efficient way to write the last example follows:
PROCEDURE calc_m IS m NUMBER; -- no constraint a NUMBER; b NUMBER; BEGIN ... m := a + b; IF m IS NULL THEN ... -- enforce constraint programmatically END;
Note that the subtypes NATURALN and POSTIVEN are defined as NOT NULL. So, using them incurs the same performance cost.
When evaluating a logical expression, PL/SQL uses short-circuit evaluation. That is, PL/SQL stops evaluating the expression as soon as the result can be determined. For example, in the following OR expression, when the value of sal is less than 1500, the left operand yields TRUE, so PL/SQL need not evaluate the right operand (because OR returns TRUE if either of its operands is true):
IF (sal < 1500) OR (comm IS NULL) THEN ... END IF;
Now, consider the following AND expression:
IF credit_ok(cust_id) AND (loan < 5000) THEN ... END IF;
The Boolean function credit_ok is always called. However, if you switch the operands of AND as follows
IF (loan < 5000) AND credit_ok(cust_id) THEN ... END IF;
the function is called only when the expression loan < 5000 is true (because AND returns TRUE only if both its operands are true).
The same idea applies to EXIT-WHEN statements.
At run time, PL/SQL converts between structurally different datatypes implicitly. For instance, assigning a PLS_INTEGER variable to a NUMBER variable results in a conversion because their internal representations are different.
Avoiding implicit conversions can improve performance. Look at the example below. The integer literal 15 is represented internally as a signed 4-byte quantity, so PL/SQL must convert it to an Oracle number before the addition. However, the floating-point literal 15.0 is represented as a 22-byte Oracle number, so no conversion is necessary.
DECLARE n NUMBER; c CHAR(5); BEGIN n := n + 15; -- converted n := n + 15.0; -- not converted
Here is another example:
DECLARE c CHAR(5); BEGIN c := 25; -- converted c := '25'; -- not converted
PL/SQL Version 2 allows some abnormal behavior that Version 8 disallows. Specifically, Version 2 allows you to
RECORD and TABLE types when declaring variables
RETURN clause of a function spec
IN parameter
IN parameter to another subprogram as OUT parameters
OUT parameter on the right-hand side of an assignment statement
OUT parameters in the FROM list of a SELECT statement
For backward compatibility, you might want to keep this particular Version 2 behavior. You can do that by setting the PLSQL_V2_COMPATIBILITY flag. On the server side, you can set the flag in two ways:
PLSQL_V2_COMPATIBILITY=TRUE
ALTER SESSION SET PLSQL_V2_COMPATIBILITY = TRUE; ALTER SYSTEM SET PLSQL_V2_COMPATIBILITY = TRUE;
If you specify FALSE (the default), only Version 8 behavior is allowed.
On the client side, a command-line option sets the flag. For example, in the Oracle Precompilers environment, you specify the runtime option DBMS on the command line, as follows:
... DBMS=V7 ...
