6 Performing SQL Operations from PL/SQL

Data Manipulation

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

Transaction Control

Oracle is transaction oriented; that is, Oracle uses transactions to ensure data integrity. A transaction is a series of SQL data manipulation statements that does a logical unit of work. For example, two UPDATE statements might credit one bank account and debit another. It is important not to allow one operation to succeed while the other fails.

At the end of a transaction that makes database changes, Oracle makes all the changes permanent or undoes them all. If your program fails in the middle of a transaction, Oracle detects the error and rolls back the transaction, restoring the database to its former state.

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

SQL Functions

For example, the following example shows some queries that call SQL functions:

DECLARE
  job_count NUMBER;
  emp_count NUMBER;
BEGIN
  SELECT COUNT(DISTINCT job_id) INTO job_count FROM employees;
  SELECT COUNT(*) INTO emp_count FROM employees;
END;
/

SQL Pseudocolumns

PL/SQL recognizes the SQL pseudocolumns: CURRVAL, LEVEL, NEXTVAL, ROWID, and ROWNUM. In PL/SQL, pseudocolumns are only allowed in SQL queries, not in INSERT / UPDATE / DELETE statements, or in other PL/SQL statements such as assignments or conditional tests.

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 get the current or next value in a sequence, use dot notation:

sequence_name.CURRVAL
sequence_name.NEXTVAL

After creating a sequence, you can use it to generate unique sequence numbers for transaction processing. You can use CURRVAL and NEXTVAL only in a SELECT list, the VALUES clause, and the SET clause. The following example shows how to generate a new sequence number and refer to that same number in more than one statement:

CREATE TABLE employees_temp AS SELECT employee_id, first_name FROM employees;
CREATE TABLE employees_temp2 AS SELECT employee_id, first_name FROM employees;

DECLARE
  next_value NUMBER;
BEGIN
-- The NEXTVAL value is the same no matter what table you select from.
  SELECT employees_seq.NEXTVAL INTO next_value FROM dual;
-- You usually use NEXTVAL to create unique numbers when inserting data.
  INSERT INTO employees_temp VALUES (employees_seq.NEXTVAL, 'value 1');
-- If you need to store the same value somewhere else, you use CURRVAL.
  INSERT INTO employees_temp2 VALUES (employees_seq.CURRVAL, 'value 1');
-- Because NEXTVAL values might be referenced by different users and
-- applications, and some NEXTVAL values might not be stored in the
-- database, there might be gaps in the sequence.
END;
/

DROP TABLE employees_temp;
DROP TABLE employees_temp2;

Each time you reference the NEXTVAL value of a sequence, the sequence is incremented immediately and permanently, 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. You might use sequence numbers to give each row a unique identifier, and refer to those identifiers from other rows to set up parent-child relationships.

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.

In the START WITH clause, you specify a condition that identifies the root of the tree. You specify the direction in which the query traverses the tree (down from the root or up from the branches) with the PRIOR operator.

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.

When you select or fetch a physical rowid into a UROWID variable, you can use the function ROWIDTOCHAR, which converts the binary value to a character string. 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; use a subselect (shown in the following example) to get the first n sorted rows.

You can use ROWNUM in an UPDATE statement to assign unique values to each row in a table, or in the WHERE clause of a SELECT statement to limit the number of rows retrieved:

CREATE TABLE employees_temp AS SELECT * FROM employees;

DECLARE
   CURSOR c1 IS SELECT employee_id, salary FROM employees_temp
      WHERE salary > 2000 AND ROWNUM <= 10;  -- 10 arbitrary rows
   CURSOR c2 IS SELECT * FROM
     (SELECT employee_id, salary FROM employees_temp
       WHERE salary > 2000 ORDER BY salary DESC)
     WHERE ROWNUM < 5;  -- first 5 rows, in sorted order
BEGIN
-- Each row gets assigned a different number
  UPDATE employees_temp SET employee_id = ROWNUM;
END;
/

DROP TABLE employees_temp;

The value of ROWNUM increases only when a row is retrieved, so the only meaningful uses of ROWNUM in a WHERE clause are

... WHERE ROWNUM < constant;
... WHERE ROWNUM <= constant;

SQL Operators

PL/SQL lets you use all the SQL comparison, set, and row operators in SQL statements. This section briefly describes some of these operators. For more information, see Oracle Database 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 yield TRUE, FALSE, or NULL. You can use the comparison operators listed below to form predicates. 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.

Overview of Implicit Cursor Attributes

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.

Note: The SQL cursor has another attribute, %BULK_ROWCOUNT, designed for use with the FORALL statement. For more information, see "Counting Rows Affected by FORALL with the %BULK_ROWCOUNT Attribute".

DELETE FROM emp WHERE empno = my_empno;
IF SQL%FOUND THEN  -- delete succeeded
   INSERT INTO new_emp VALUES (my_empno, my_ename, ...);

DELETE FROM emp WHERE ...
IF SQL%ROWCOUNT > 10 THEN  -- more than 10 rows were deleted
   ...
END IF;

Selecting At Most One Row: SELECT INTO Statement

If you expect a query to only return one row, you can write a regular SQL SELECT statement with an additional INTO clause specifying the PL/SQL variable to hold the result:

If the query might return more than one row, but you do not care about values after the first, you can restrict any result set to a single row by comparing the ROWNUM value:

If the query might return no rows at all, use an exception handler to specify any actions to take when no data is found:

If you just want to check whether a condition exists in your data, you might be able to code the query with the COUNT(*) operator, which always returns a number and never raises the NO_DATA_FOUND exception:

Selecting Multiple Rows: BULK COLLECT Clause

If you need to bring a large quantity of data into local PL/SQL variables, rather than looping through a result set one row at a time, you can use the BULK COLLECT clause. When you query only certain columns, you can store all the results for each column in a separate collection variable:

SELECT employee_id, last_name, salary FROM employees
  BULK COLLECT INTO all_employee_ids, all_last_names, all_salaries;

When you query all the columns of a table, you can store the entire result set in a collection of records, which makes it convenient to loop through the results and refer to different columns:

SELECT * FROM employees BULK COLLECT INTO all_employees;
FOR i IN all_employees.FIRST .. all_employees.LAST
LOOP
  ...
END LOOP;

This technique can be very fast, but also very memory-intensive. If you use it often, you might be able to improve your code by doing more of the work in SQL:

• If you only need to loop once through the result set, use a FOR loop as described in the following sections. This technique avoids the memory overhead of storing a copy of the result set.

• If you are looping through the result set to scan for certain values or filter the results into a smaller set, do this scanning or filtering in the original query instead. You can add more WHERE clauses in simple cases, or use set operators such as INTERSECT and MINUS if you are comparing two or more sets of results.

• If you are looping through the result set and running another query or a DML statement for each result row, you can probably find a more efficient technique. For queries, look at including subqueries or EXISTS or NOT EXISTS clauses in the original query. For DML statements, look at the FORALL statement, which is much faster than coding these statements inside a regular loop.

Looping Through Multiple Rows: Cursor FOR Loop

Perhaps the most common case of a query is one where you issue the SELECT statement, then immediately loop once through the rows of the result set. PL/SQL lets you use a simple FOR loop for this kind of query:

The iterator variable for the FOR loop does not need to be declared in advance. It is a %ROWTYPE record whose field names match the column names from the query, and that exists only during the loop. When you use expressions rather than explicit column names, use column aliases so that you can refer to the corresponding values inside the loop:

Performing Complicated Query Processing: Explicit Cursors

For full control over query processing, you can use explicit cursors in combination with the OPEN, FETCH, and CLOSE statements.

You might want to specify a query in one place but retrieve the rows somewhere else, even in another subprogram. Or you might want to choose very different query parameters, such as ORDER BY or GROUP BY clauses, depending on the situation. Or you might want to process some rows differently than others, and so need more than a simple loop.

Because explicit cursors are so flexible, you can choose from different notations depending on your needs. The following sections describe all the query-processing features that explicit cursors provide.

Querying Data with PL/SQL: Implicit Cursor FOR Loop

With PL/SQL, it is very simple to issue a query, retrieve each row of the result into a %ROWTYPE record, and process each row in a loop:

• You include the text of the query directly in the FOR loop.

• PL/SQL creates a record variable with fields corresponding to the columns of the result set.

• You refer to the fields of this record variable inside the loop. You can perform tests and calculations, display output, or store the results somewhere else.

Here is an example that you can run in SQL*Plus. It does a query to get the name and status of every index that you can access.

BEGIN
  FOR item IN 
  (
    SELECT object_name, status FROM user_objects WHERE object_type = 'INDEX'
       AND object_name NOT LIKE '%$%'
  )
  LOOP
    dbms_output.put_line('Index = ' || item.object_name ||
      ', Status = ' || item.status);
  END LOOP;
END;
/

Before each iteration of the FOR loop, PL/SQL fetches into the implicitly declared record.

The sequence of statements inside the loop is executed once for each row that satisfies the query. When you leave the loop, the cursor is closed automatically. The cursor is closed even if you use an EXIT or GOTO statement to leave the loop before all rows are fetched, or an exception is raised inside the loop.

See also: LOOP Statements

Querying Data with PL/SQL: Explicit Cursor FOR Loops

IIf you need to reference the same query from different parts of the same procedure, you can declare a cursor that specifies the query, and process the results using a FOR loop.

The following PL/SQ block runs two variations of the same query, first finding all the tables you can access, then all the indexes you can access:

DECLARE
  CURSOR c1 IS
    SELECT object_name, status FROM user_objects WHERE object_type = 'TABLE'
       AND object_name NOT LIKE '%$%';
BEGIN
  FOR item IN c1 LOOP
    dbms_output.put_line('Table = ' || item.object_name ||
      ', Status = ' || item.status);
  END LOOP;
END;
/

See also: LOOP Statements

Defining Aliases for Expression Values in a Cursor FOR Loop

In a cursor FOR loop, PL/SQL creates a %ROWTYPE record with fields corresponding to columns in the result set. The fields have the same names as corresponding columns in the SELECT list.

The select list might contain an expression, such as a column plus a constant, or two columns concatenated together. If so, use a column alias to give unique names to the appropriate columns.

In the following example, full_name and dream_salary are aliases for expressions in the query:

SET SERVEROUTPUT ON;

BEGIN
  FOR item IN
  (
    SELECT
      first_name || ' ' || last_name AS full_name,
      salary * 10 AS dream_salary
    FROM employees
    WHERE ROWNUM <= 5
  )
  LOOP
    dbms_output.put_line(item.full_name || ' dreams of making ' ||
      item.dream_salary);
  END LOOP;
END;
/

Overview of Explicit Cursors

When you need precise control over query processing, you can explicitly declare a cursor in the declarative part of any PL/SQL block, subprogram, or package.

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

This technique requires more code than other techniques such as the implicit cursor FOR loop. Its advantage is flexibility. You can:

• Process several queries in parallel by declaring and opening multiple cursors.

• Process multiple rows in a single loop iteration, skip rows, or split the processing into more than one loop.

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;

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

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

-- This cursor queries 3 columns.
-- Each column is fetched into a separate variable.
FETCH c1 INTO my_empno, my_ename, my_deptno;
-- This cursor was declared as SELECT * FROM employees.
-- An entire row is fetched into the my_employees record, which
-- is declared with the type employees%ROWTYPE.
FETCH c2 INTO my_employees;

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

DECLARE
  my_sal employees.salary%TYPE;
  my_job employees.job_id%TYPE;
  factor INTEGER := 2;
  CURSOR c1 IS
    SELECT factor*salary FROM employees WHERE job_id = 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;
/

DECLARE
   CURSOR c1 IS SELECT last_name FROM employees ORDER BY last_name;
   name1 employees.last_name%TYPE;
   name2 employees.last_name%TYPE;
   name3 employees.last_name%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;
/

DECLARE
  TYPE NumTab IS TABLE OF employees.employee_id%TYPE;
  TYPE NameTab IS TABLE OF employees.last_name%TYPE;
  nums  NumTab;
  names NameTab;
  CURSOR c1 IS
    SELECT employee_id, last_name
      FROM employees
      WHERE job_id = 'ST_CLERK';
BEGIN
  OPEN c1;
  FETCH c1 BULK COLLECT INTO nums, names;
-- Here is where you iterate through the elements in the NUMS and
-- NAMES collections.
  NULL;
  CLOSE c1;
END;
/

Writing Maintainable PL/SQL Queries

Instead of referring to local variables, you can declare a cursor that accepts parameters, and pass values for those parameters when you open the cursor. If the query is usually issued with certain values, you can make those values the defaults. You can use either positional notation or named notation to pass the parameter values.

DECLARE
  CURSOR c1 (name VARCHAR2, max_wage NUMBER) IS
    SELECT * FROM employees WHERE last_name = name and salary < max_wage;
BEGIN
  FOR person IN c1('Austin', 30000)
  LOOP
     -- process data record
    dbms_output.put_line('Name = ' || person.last_name ||
      ', salary = ' || person.salary);
  END LOOP;
END;
/

DECLARE
  emp_name employees.last_name%TYPE := 'Austin';
  emp_salary   employees.salary%TYPE := 30000;
  my_record employees%ROWTYPE;
  CURSOR c1 (name VARCHAR2, max_wage NUMBER) IS
    SELECT * FROM employees WHERE last_name = name and salary < max_wage;
BEGIN
-- Any of the following statements opens the cursor:
-- OPEN c1('Austin', 3000);
-- OPEN c1('Austin', emp_salary);
-- OPEN c1(emp_name, 3000);
-- OPEN c1(emp_name, emp_salary);

  OPEN c1(emp_name, emp_salary);
  LOOP
     FETCH c1 INTO my_record;
     EXIT WHEN c1%NOTFOUND;
     -- process data record
    dbms_output.put_line('Name = ' || my_record.last_name ||
      ', salary = ' || my_record.salary);
  END LOOP;
END;
/

Overview of Explicit Cursor Attributes

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.

DECLARE
   CURSOR c1 IS SELECT last_name, salary FROM employees WHERE ROWNUM < 11;
   my_ename employees.last_name%TYPE;
   my_salary employees.salary%TYPE;
BEGIN
   OPEN c1;
   LOOP
      FETCH c1 INTO my_ename, my_salary;
      IF c1%FOUND THEN  -- fetch succeeded
         dbms_output.put_line('Name = ' || my_ename || ', salary = ' ||
            my_salary);
      ELSE  -- fetch failed, so exit loop
         EXIT;
      END IF;
   END LOOP;
END;
/

DECLARE
   CURSOR c1 IS SELECT last_name, salary FROM employees WHERE ROWNUM < 11;
   the_name employees.last_name%TYPE;
   the_salary employees.salary%TYPE;
BEGIN
   IF c1%ISOPEN = FALSE THEN  -- cursor was not already open
      OPEN c1;
   END IF;
   FETCH c1 INTO the_name, the_salary;
   CLOSE c1;
END;
/

DECLARE
   CURSOR c1 IS SELECT last_name, salary FROM employees WHERE ROWNUM < 11;
   my_ename employees.last_name%TYPE;
   my_salary employees.salary%TYPE;
BEGIN
   OPEN c1;
   LOOP
      FETCH c1 INTO my_ename, my_salary;
      IF c1%NOTFOUND THEN -- fetch failed, so exit loop
-- A shorter form of this test is "EXIT WHEN c1%NOTFOUND;"
         EXIT;
      ELSE   -- fetch succeeded
         dbms_output.put_line('Name = ' || my_ename || ', salary = ' ||
            my_salary);
      END IF;
   END LOOP;
END;
/

EXIT WHEN c1%NOTFOUND OR c1%NOTFOUND IS NULL;

DECLARE
   CURSOR c1 IS SELECT last_name FROM employees WHERE ROWNUM < 11;
   name employees.last_name%TYPE;
BEGIN
   OPEN c1;
   LOOP
      FETCH c1 INTO name;
      EXIT WHEN c1%NOTFOUND;
      dbms_output.put_line(c1%ROWCOUNT || '. ' || name);
      IF c1%ROWCOUNT = 5 THEN
         dbms_output.put_line('--- Fetched 5th record ---');
      END IF;
   END LOOP;
   CLOSE c1;
END;
/

What Are Cursor Variables (REF CURSORs)?

Cursor variables are like pointers to result sets. You use them when you want to perform a query in one subprogram, and process the results in a different subprogram (possibly one written in a different language). A cursor variable has datatype REF CURSOR, and you might see them referred to informally as REF CURSORs.

Unlike an explicit cursor, which always refers to the same query work area, a cursor variable can refer to different work areas. You cannot use a cursor variable where a cursor is expected, or vice versa.

Why Use Cursor Variables?

You use cursor variables to pass query result sets between PL/SQL stored subprograms and various clients. PL/SQL and its clients 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 database server can all refer to the same work area.

A query work area remains accessible as long as any cursor variable points to it, as you pass the value of a cursor variable 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. You can also reduce network traffic by having a PL/SQL block open or close several host cursor variables in a single round trip.

Declaring REF CURSOR Types and Cursor Variables

To create cursor variables, 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. In the following example, you declare a REF CURSOR type that represents a result set from the DEPARTMENTS table:

DECLARE
   TYPE DeptCurTyp IS REF CURSOR RETURN departments%ROWTYPE;

REF CURSOR types can be strong (with a return type) or weak (with no return type).

Strong REF CURSOR types are less error prone because the PL/SQL compiler lets you associate a strongly typed cursor variable only with queries that return the right set of columns. Weak REF CURSOR types are more flexible because the compiler lets you associate a weakly typed cursor variable with any query.

Because there is no type checking with a weak REF CURSOR, all such types are interchangeable. Instead of creating a new type, you can use the predefined type SYS_REFCURSOR.

Once you define a REF CURSOR type, you can declare cursor variables of that type in any PL/SQL block or subprogram.

DECLARE
   TYPE EmpCurTyp IS REF CURSOR RETURN emp%ROWTYPE;  -- strong
   TYPE GenericCurTyp IS REF CURSOR;  -- weak
   cursor1 EmpCurTyp;
   cursor2 GenericCurTyp;
   my_cursor SYS_REFCURSOR; -- didn't need to declare a new type above

The following example declares the cursor variable dept_cv:

DECLARE
   TYPE DeptCurTyp IS REF CURSOR RETURN dept%ROWTYPE;
   dept_cv DeptCurTyp;  -- declare cursor variable

To avoid declaring the same REF CURSOR type in each subprogram that uses it, you can put the REF CURSOR declaration in a package spec. You can declare cursor variables of that type in the corresponding package body, or within your own procedure or function.

DECLARE
   TYPE TmpCurTyp IS REF CURSOR RETURN employees%ROWTYPE;
   tmp_cv TmpCurTyp;  -- declare cursor variable
   TYPE EmpCurTyp IS REF CURSOR RETURN tmp_cv%ROWTYPE;
   emp_cv EmpCurTyp;  -- declare cursor variable
BEGIN
  NULL;
END;
/

DECLARE
   dept_rec departments%ROWTYPE;  -- declare record variable
   TYPE DeptCurTyp IS REF CURSOR RETURN dept_rec%TYPE;
   dept_cv DeptCurTyp;  -- declare cursor variable
BEGIN
  NULL;
END;
/

DECLARE
   TYPE EmpRecTyp IS RECORD (
      employee_id NUMBER,
      last_name VARCHAR2(30),
      salary   NUMBER(7,2));
   TYPE EmpCurTyp IS REF CURSOR RETURN EmpRecTyp;
   emp_cv EmpCurTyp;  -- declare cursor variable
BEGIN
  NULL;
END;
/

DECLARE
   TYPE EmpCurTyp IS REF CURSOR RETURN employees%ROWTYPE;
   emp EmpCurTyp;

-- Once we have a result set, we can process all the rows
-- inside a single procedure rather than calling a procedure
-- for each row.
   PROCEDURE process_emp_cv (emp_cv IN EmpCurTyp) IS
      person employees%ROWTYPE;
   BEGIN
      dbms_output.put_line('-----');
      dbms_output.put_line('Here are the names from the result set:');
      LOOP
         FETCH emp_cv INTO person;
         EXIT WHEN emp_cv%NOTFOUND;
         dbms_output.put_line('Name = ' || person.first_name ||
            ' ' || person.last_name);
      END LOOP;
   END;

BEGIN
-- First find 10 arbitrary employees.
  OPEN emp FOR SELECT * FROM employees WHERE ROWNUM < 11;
  process_emp_cv(emp);
  CLOSE emp;
-- Then find employees matching a condition.
  OPEN emp FOR SELECT * FROM employees WHERE last_name LIKE 'R%';
  process_emp_cv(emp);
  CLOSE emp;
END;
/

Controlling Cursor Variables: OPEN-FOR, FETCH, and CLOSE

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. When all the rows are processed, you CLOSE the cursor variable.

OPEN {cursor_variable | :host_cursor_variable} FOR
{  select_statement
 | dynamic_string [USING bind_argument[, bind_argument]...] };

DECLARE
   TYPE EmpCurTyp IS REF CURSOR RETURN employees%ROWTYPE;
   emp_cv EmpCurTyp;
BEGIN
   IF NOT emp_cv%ISOPEN THEN
      /* Open cursor variable. */
      OPEN emp_cv FOR SELECT * FROM employees;
   END IF;
   CLOSE emp_cv;
END;
/

CREATE PACKAGE emp_data AS
  TYPE EmpCurTyp IS REF CURSOR RETURN employees%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 employees;
  END open_emp_cv;
END emp_data;
/

DROP PACKAGE emp_data;

CREATE PACKAGE emp_data AS
   TYPE EmpCurTyp IS REF CURSOR RETURN emp%ROWTYPE;
   PROCEDURE open_emp_cv (emp_cv IN OUT EmpCurTyp, choice INT);
END emp_data;

CREATE PACKAGE BODY emp_data AS
   PROCEDURE open_emp_cv (emp_cv IN OUT EmpCurTyp, choice INT) 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;
END emp_data;

CREATE PACKAGE admin_data AS
   TYPE GenCurTyp IS REF CURSOR;
   PROCEDURE open_cv (generic_cv IN OUT GenCurTyp, choice INT);
END admin_data;

CREATE PACKAGE BODY admin_data AS
   PROCEDURE open_cv (generic_cv IN OUT GenCurTyp, choice INT) 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;
END admin_data;

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;

DECLARE
   TYPE EmpCurTyp IS REF CURSOR RETURN employees%ROWTYPE;
   emp_cv EmpCurTyp;
   emp_rec employees%ROWTYPE;
BEGIN
   OPEN emp_cv FOR SELECT * FROM employees WHERE salary < 3000;
   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
      dbms_output.put_line('Name = ' || emp_rec.first_name || ' ' ||
         emp_rec.last_name);
   END LOOP;
   CLOSE emp_cv;
END;
/

DECLARE
   TYPE EmpCurTyp IS REF CURSOR;
   TYPE NameList IS TABLE OF employees.last_name%TYPE;
   TYPE SalList IS TABLE OF employees.salary%TYPE;
   emp_cv EmpCurTyp;
   names  NameList;
   sals   SalList;
BEGIN
   OPEN emp_cv FOR SELECT last_name, salary FROM employees WHERE salary < 3000;
   FETCH emp_cv BULK COLLECT INTO names, sals;
   CLOSE emp_cv;
-- Now loop through the NAMES and SALS collections.
   FOR i IN names.FIRST .. names.LAST
   LOOP
      dbms_output.put_line('Name = ' || names(i) || ', salary = ' ||
         sals(i));
   END LOOP;
END;
/

Reducing Network Traffic When Passing Host Cursor Variables to PL/SQL

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 multiple cursor variables in a single round trip:

/* anonymous PL/SQL block in host environment */ 
BEGIN 
   OPEN :emp_cv FOR SELECT * FROM employees; 
   OPEN :dept_cv FOR SELECT * FROM departments; 
   OPEN :loc_cv FOR SELECT * FROM locations; 
END;

This technique 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, so your OCI or Pro*C program can 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;
END;

The cursors assigned to c1, c2, and c3 behave normally, and you can use them for any purpose. When finished, release the cursors as follows:

BEGIN 
   CLOSE :c1;
   CLOSE :c2;
   CLOSE :c3;
END;

Avoiding Errors with Cursor Variables

If both cursor variables involved in an assignment are strongly typed, they must have exactly the same datatype (not just the same return type). If one or both cursor variables are weakly typed, they can have different datatypes.

If you try to fetch from, close, or refer to cursor attributes of a cursor variable that does not point to a query work area, PL/SQL raises the INVALID_CURSOR exception. You can make a cursor variable (or parameter) point to a query work area in two ways:

• OPEN the cursor variable FOR the query.

• Assign to the cursor variable the value of an already OPENed host cursor variable or PL/SQL cursor variable.

If you assign an unopened cursor variable to another cursor variable, the second one remains invalid even after you open the first one.

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.

Restrictions on Cursor Variables

Currently, cursor variables are subject to the following restrictions:

• You cannot declare cursor variables in a package spec. For example, the following declaration is not allowed:

  CREATE PACKAGE emp_stuff AS
     TYPE EmpCurTyp IS REF CURSOR RETURN emp%ROWTYPE;
     emp_cv EmpCurTyp;  -- not allowed
  END emp_stuff;
  
  

• You cannot pass cursor variables to a procedure that is called through a database link.

• If you pass a host cursor variable to PL/SQL, you cannot fetch from it on the server side unless you also open it there on the same server call.

• You cannot use comparison operators to test cursor variables for equality, inequality, or nullity.

• You cannot assign nulls to a cursor variable.

• Database columns cannot store the values of cursor variables. There is no equivalent type to use in a CREATE TABLE statement.

• You cannot store cursor variables in an associative array, nested table, or varray.

• Cursors and cursor variables are not interoperable; that is, you cannot use one where the other is expected. For example, you cannot reference a cursor variable in a cursor FOR loop.

Restrictions on Cursor Expressions

• You cannot use a cursor expression with an implicit cursor.

• Cursor expressions can appear only:

  • In a SELECT statement that is not nested in any other query expression, except when it is a subquery of the cursor expression itself.

  • As arguments to table functions, in the FROM clause of a SELECT statement.

• Cursor expressions can appear only in the outermost SELECT list of the query specification.

• Cursor expressions cannot appear in view declarations.

• You cannot perform BIND and EXECUTE operations on cursor expressions.

Example of Cursor Expressions

In this example, we find a specified location ID, and a cursor from which we can fetch all the departments in that location. As we fetch each department's name, we also get another cursor that lets us fetch their associated employee details from another table.

DECLARE
   TYPE emp_cur_typ IS REF CURSOR;
   emp_cur emp_cur_typ;
   dept_name departments.department_name%TYPE;
   emp_name employees.last_name%TYPE;
   CURSOR c1 IS SELECT
      department_name,
-- The 2nd item in the result set is another result set,
-- which is represented as a ref cursor and labelled "employees".
      CURSOR
      (
         SELECT e.last_name FROM employees e
         WHERE e.department_id = d.department_id
      ) employees
   FROM departments d
      WHERE department_name like 'A%';

BEGIN
   OPEN c1;
   LOOP
      FETCH c1 INTO dept_name, emp_cur;
      EXIT WHEN c1%NOTFOUND;
      dbms_output.put_line('Department: ' || dept_name);
-- For each row in the result set, we can process the result
-- set from a subquery. We could pass the ref cursor to a procedure
-- instead of processing it here in the loop.
      LOOP
         FETCH emp_cur INTO emp_name;
         EXIT WHEN emp_cur%NOTFOUND;
         dbms_output.put_line('   Employee: ' || emp_name);
      END LOOP;
   END LOOP;
   CLOSE c1;
END;
/

Constructing REF CURSORs with Cursor Subqueries

You can use cursor subqueries, also know as cursor expressions, to pass sets of rows as parameters to functions. For example, this statement passes a parameter to the StockPivot function consisting of a REF CURSOR that represents the rows returned by the cursor subquery:

SELECT * FROM TABLE(StockPivot(CURSOR(SELECT * FROM StockTable)));

Cursor subqueries are often used with table functions, which are explained in "Setting Up Transformation Pipelines with Table Functions ".

Using COMMIT, SAVEPOINT, and ROLLBACK in PL/SQL

You can include COMMIT, SAVEPOINT, and ROLLBACK statements directly in your PL/SQL programs.

The COMMIT statement ends the current transaction, making any changes made during that transaction permanent, and visible to other users.

The ROLLBACK statement ends the current transaction and undoes any changes made during that transaction. If you make a mistake, such as deleting the wrong row from a table, a rollback restores the original data. If you cannot finish a transaction because an exception is raised or a SQL statement fails, a rollback lets you take corrective action and perhaps start over.

SAVEPOINT names and marks the current point in the processing of a transaction. Savepoints let you roll back part of a transaction instead of the whole transaction.

Consider a transaction that transfers money from one bank account to another. It is important that the money come out of one account, and into the other, at exactly the same moment. Otherwise, a problem partway through might make the money be lost from both accounts or be duplicated in both accounts.

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

Transactions are not tied to PL/SQL BEGIN-END blocks. A block can contain multiple transactions, and a transaction can span multiple blocks.

The optional COMMENT clause lets you specify a comment to be associated with a distributed transaction. 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:

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

PL/SQL does not support the FORCE clause of SQL, which manually commits an in-doubt distributed transaction.

The following example inserts information about an employee into three different database tables. If an INSERT statement tries to store a duplicate employee number, the predefined exception DUP_VAL_ON_INDEX is raised. To make sure that changes to all three tables are undone, the exception handler executes a ROLLBACK.

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;

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;

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;

How Oracle Does Implicit Rollbacks

Before executing an INSERT, UPDATE, or DELETE statement, Oracle marks an implicit savepoint (unavailable to you). If the statement fails, Oracle rolls back to the savepoint. Normally, just the failed SQL statement is rolled back, not the whole transaction. 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, and does not do any rollback.

Ending Transactions

You should explicitly commit or roll back every transaction. Whether you issue the commit or rollback in your PL/SQL program or from a client program depends on the application logic. If you do not commit or roll back a transaction explicitly, the client 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.

Oracle precompiler programs roll back the transaction unless the program explicitly commits or rolls back work, and disconnects using the RELEASE parameter:

EXEC SQL COMMIT WORK RELEASE;

Setting Transaction Properties with SET TRANSACTION

You use the SET TRANSACTION statement to begin a read-only or read-write transaction, establish an isolation level, or assign your current transaction to a specified rollback segment. Read-only transactions are useful for running multiple queries 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 a store manager uses a read-only transaction to gather sales figures for the day, the past week, and the past month. The figures are unaffected by other users updating the database during the transaction.

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

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

Overriding Default Locking

By default, Oracle locks data structures for you automatically, which is a major strength of the Oracle database: different applications can read and write to the same data without harming each other's data or coordinating with each other.

You can request data locks on specific rows or entire tables if you need to override default locking. Explicit locking lets you deny access to data 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 after you have read them. That way, you can check which or how many rows will be affected by an UPDATE or DELETE statement before issuing the statement, and no other application can change the rows in the meantime.

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

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

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

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

All rows are locked when you open the cursor, not as they are fetched. The rows are unlocked when you commit or roll back the transaction. Since the rows are no longer locked, 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.. 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 Oracle Database 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.

PL/SQL raises an exception if you try to fetch from a FOR UPDATE cursor after doing a commit. The FOR UPDATE clause locks the rows when you open the cursor, and unlocks them when you commit.

DECLARE
   CURSOR c1 IS SELECT ename FROM emp FOR UPDATE OF sal;
BEGIN
   FOR emp_rec IN c1 LOOP  -- FETCH fails on the second iteration
      INSERT INTO temp VALUES ('still going');
      COMMIT;  -- releases locks
   END LOOP;
END;

If you want to fetch across commits, use the ROWID pseudocolumn to mimic the CURRENT OF clause. Select the rowid of each row into a UROWID variable, then use the rowid to identify the current row during subsequent updates and deletes:

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;

Because the fetched rows are not locked by a FOR UPDATE clause, other users might unintentionally overwrite your changes. The extra space needed for read consistency is not released until the cursor is closed, which can slow down processing for large updates.

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;

Advantages of Autonomous Transactions

Once started, an autonomous transaction is fully independent. It shares no locks, resources, or commit-dependencies with the main transaction. 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. You can encapsulate autonomous transactions within stored procedures. A calling application does not need to know whether operations done by that stored procedure succeeded or failed.

Defining Autonomous Transactions

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

• Top-level (not nested) anonymous PL/SQL blocks

• Local, standalone, and packaged functions and procedures

• Methods of a SQL object type

• Database triggers

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.

The next example marks a standalone procedure as autonomous:

CREATE PROCEDURE close_account (acct_id INTEGER, OUT balance) AS
   PRAGMA AUTONOMOUS_TRANSACTION;
   my_bal REAL;
BEGIN ... END;

The following example marks 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.

The example below marks 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;

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

Controlling Autonomous Transactions

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

• ROLLBACK [TO savepoint_name]

• SAVEPOINT savepoint_name

• SET TRANSACTION

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. When one transaction ends, the next SQL statement begins another transaction. A single autonomous routine could contain several autonomous transactions, if it issued several COMMIT statements.

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. 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:

• If an autonomous transaction attempts to access a resource held by the main transaction, a deadlock can occur. Oracle raises an exception in the autonomous transaction, which is rolled back if the exception goes unhandled.

• The Oracle initialization parameter TRANSACTIONS specifies the maximum number of concurrent transactions. That number might be exceeded because an autonomous transaction runs concurrently with the main transaction.

• If you try to exit an active autonomous transaction without committing or rolling back, Oracle raises an exception. If the exception goes unhandled, the transaction is rolled back.

Using Autonomous Triggers

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 changes to the shadow table whether or not you commit changes to 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));

-- create 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

Unlike regular triggers, autonomous triggers can execute DDL statements using native dynamic SQL (discussed in Chapter 7, " Performing SQL Operations with Native Dynamic SQL"). In the following example, trigger bonus_trig drops a temporary database table after table bonus is updated:

CREATE TRIGGER bonus_trig
AFTER UPDATE ON bonus
DECLARE
   PRAGMA AUTONOMOUS_TRANSACTION;  -- enables trigger to perform DDL
BEGIN
   EXECUTE IMMEDIATE 'DROP TABLE temp_bonus';
END;

For more information about database triggers, see Oracle Database Application Developer's Guide - Fundamentals.

Calling Autonomous Functions from SQL

A function called from SQL statements must obey certain rules meant to control side effects. (See "Controlling Side Effects of PL/SQL Subprograms".) To check for violations of the rules, you can use the pragma RESTRICT_REFERENCES. The pragma asserts that a function does not read or write database tables or package variables. (For more information, See Oracle Database Application Developer's Guide - Fundamentals.)

However, by definition, autonomous routines never violate the rules "read no database state" (RNDS) and "write no database state" (WNDS) no matter what they do. 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 BODYq 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;