6 Using Static SQL

Data Manipulation Language (DML) Statements

To manipulate Oracle data you can include DML operations, such as INSERT, UPDATE, and DELETE statements, directly in PL/SQL programs, without any special notation, as shown in Example 6-1. You can also include the SQL COMMIT statement directly in a PL/SQL program; see "Overview of Transaction Processing in PL/SQL".

CREATE TABLE employees_temp
  AS SELECT employee_id, first_name, last_name 
  FROM employees;
DECLARE
  emp_id          employees_temp.employee_id%TYPE;
  emp_first_name  employees_temp.first_name%TYPE;
  emp_last_name   employees_temp.last_name%TYPE;
BEGIN
   INSERT INTO employees_temp VALUES(299, 'Bob', 'Henry');
   UPDATE employees_temp
     SET first_name = 'Robert' WHERE employee_id = 299;
   DELETE FROM employees_temp WHERE employee_id = 299 
     RETURNING first_name, last_name
       INTO emp_first_name, emp_last_name;
   COMMIT;
   DBMS_OUTPUT.PUT_LINE( emp_first_name  || ' ' || emp_last_name);
END;
/

To find out how many rows are affected by DML statements, you can check the value of SQL%ROWCOUNT as shown in Example 6-2.

CREATE TABLE employees_temp AS SELECT * FROM employees;
BEGIN
  UPDATE employees_temp
    SET salary = salary * 1.05 WHERE salary < 5000;
  DBMS_OUTPUT.PUT_LINE('Updated ' || SQL%ROWCOUNT || ' salaries.');
END;
/

Wherever you can use literal values, or bind variables in some other programming language, you can directly substitute PL/SQL variables as shown in Example 6-3.

CREATE TABLE employees_temp
  AS SELECT first_name, last_name FROM employees;
DECLARE
   x VARCHAR2(20) := 'my_first_name';
   y VARCHAR2(25) := 'my_last_name';
BEGIN
   INSERT INTO employees_temp VALUES(x, y);
   UPDATE employees_temp SET last_name = x WHERE first_name = y;
   DELETE FROM employees_temp WHERE first_name = x;
   COMMIT;
END;
/

With this notation, you can use variables in place of values in the WHERE clause. To use variables in place of table names, column names, and so on, requires the EXECUTE IMMEDIATE statement that is explained in "Using Native Dynamic SQL".

For information on the use of PL/SQL records with SQL to update and insert data, see "Inserting Records Into the Database" and "Updating the Database with Record Values".

For additional information on assigning values to PL/SQL variables, see "Assigning SQL Query Results to PL/SQL Variables".

Transaction Control Language (TCL) Statements

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 statements 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. See "Overview of Transaction Processing in PL/SQL".

SQL Functions

Example 6-4 shows some queries that invoke 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. However, there are limitations on the use of pseudocolumns, including the restriction on the use of some pseudocolumns in assignments or conditional tests. For additional information, including restrictions, on the use of SQL pseudocolumns, see Oracle Database SQL Language Reference.

Topics:

• CURRVAL and NEXTVAL

• LEVEL

• ROWID

• ROWNUM

sequence_name.CURRVAL
sequence_name.NEXTVAL

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

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

DECLARE
  seq_value NUMBER;
BEGIN
  -- Generate initial sequence number
  seq_value := employees_seq.NEXTVAL;

  -- Print initial sequence number:
  DBMS_OUTPUT.PUT_LINE
    ('Initial sequence value: ' || TO_CHAR(seq_value));

  -- Use NEXTVAL to create unique number when inserting data:
     INSERT INTO employees_temp VALUES (employees_seq.NEXTVAL,
                                        'Lynette', 'Smith');

  -- Use CURRVAL to store same value somewhere else:
     INSERT INTO employees_temp2 VALUES (employees_seq.CURRVAL,
                                         'Morgan', 'Smith');

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

  -- Use CURRVAL to specify the record to delete:
     seq_value := employees_seq.CURRVAL;
     DELETE FROM employees_temp2 WHERE employee_id = seq_value;

  -- Udpate employee_id with NEXTVAL for specified record:
     UPDATE employees_temp SET employee_id = employees_seq.NEXTVAL
       WHERE first_name = 'Lynette' AND last_name = 'Smith';

  -- Display final value of CURRVAL:
     seq_value := employees_seq.CURRVAL;
     DBMS_OUTPUT.PUT_LINE
       ('Ending sequence value: ' || TO_CHAR(seq_value));
END;
/

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

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;
/

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 Language Reference.

Topics:

• Comparison Operators

• Set Operators

• Row Operators

Implicit Cursors

Implicit cursors are managed automatically by PL/SQL so you are not required to write any code to handle these cursors. However, you can track information about the execution of an implicit cursor through its cursor attributes.

Topics:

• Attributes of Implicit Cursors

• Guidelines for Using Attributes of Implicit Cursors

CREATE TABLE dept_temp AS SELECT * FROM departments;
DECLARE
  dept_no NUMBER(4) := 270;
BEGIN
  DELETE FROM dept_temp WHERE department_id = dept_no;
  IF SQL%FOUND THEN  -- delete succeeded
    INSERT INTO dept_temp VALUES (270, 'Personnel', 200, 1700);
  END IF;
END;
/

CREATE TABLE employees_temp AS SELECT * FROM employees;
DECLARE
  mgr_no NUMBER(6) := 122;
BEGIN
  DELETE FROM employees_temp WHERE manager_id = mgr_no;
  DBMS_OUTPUT.PUT_LINE
    ('Number of employees deleted: ' || TO_CHAR(SQL%ROWCOUNT));
END;
/

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

Topics:

• Declaring a Cursor

• Opening a Cursor

• Fetching with a Cursor

• Fetching Bulk Data with a Cursor

• Closing a Cursor

• Attributes of Explicit Cursors

DECLARE
  my_emp_id     NUMBER(6);      -- variable for employee_id
  my_job_id     VARCHAR2(10);   -- variable for job_id
  my_sal        NUMBER(8,2);    -- variable for salary
  CURSOR c1 IS SELECT employee_id, job_id, salary FROM employees
      WHERE salary > 2000; 
  my_dept   departments%ROWTYPE;  -- variable for departments row
  CURSOR c2 RETURN departments%ROWTYPE IS 
      SELECT * FROM departments WHERE department_id = 110;

DECLARE
   CURSOR c1 (low  NUMBER DEFAULT 0, high NUMBER DEFAULT 99) IS
              SELECT * FROM departments WHERE department_id > low
              AND department_id < high;

DECLARE
   CURSOR c1 IS 
     SELECT employee_id, last_name, job_id, salary
     FROM employees
     WHERE salary > 2000; 
BEGIN
  OPEN c1;

DECLARE
  v_jobid     employees.job_id%TYPE;     -- variable for job_id
  v_lastname  employees.last_name%TYPE;  -- variable for last_name
  CURSOR c1 IS SELECT last_name, job_id FROM employees 
                 WHERE REGEXP_LIKE (job_id, 'S[HT]_CLERK');
  v_employees employees%ROWTYPE;         -- record variable for row
  CURSOR c2 is SELECT * FROM employees 
                 WHERE REGEXP_LIKE (job_id, '[ACADFIMKSA]_M[ANGR]');
BEGIN
  OPEN c1; -- open the cursor before fetching
  LOOP
    -- Fetches 2 columns into variables
    FETCH c1 INTO v_lastname, v_jobid;
    EXIT WHEN c1%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE( RPAD(v_lastname, 25, ' ') || v_jobid );
  END LOOP;
  CLOSE c1;
  DBMS_OUTPUT.PUT_LINE( '-------------------------------------' );
  OPEN c2;
  LOOP
    -- Fetches entire row into the v_employees record
    FETCH c2 INTO v_employees;
    EXIT WHEN c2%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE( RPAD(v_employees.last_name, 25, ' ') ||
                               v_employees.job_id );
  END LOOP;
  CLOSE c2;
END;
/

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;  -- factor initially equals 2
   LOOP
      FETCH c1 INTO my_sal;
      EXIT WHEN c1%NOTFOUND;
      factor := factor + 1;  -- does not affect FETCH
   END LOOP;
   CLOSE c1;
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 IdsTab IS TABLE OF employees.employee_id%TYPE;
  TYPE NameTab IS TABLE OF employees.last_name%TYPE;
  ids  IdsTab;
  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 ids, names;
  CLOsE c1;
-- Here is where you process the elements in the collections
  FOR i IN ids.FIRST .. ids.LAST
    LOOP
      IF ids(i) > 140 THEN
          DBMS_OUTPUT.PUT_LINE( ids(i) );
       END IF;
    END LOOP;
  FOR i IN names.FIRST .. names.LAST
    LOOP
      IF names(i) LIKE '%Ma%' THEN
          DBMS_OUTPUT.PUT_LINE( names(i) );
       END IF;
    END LOOP;
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%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
-- Another form of this test is
-- "EXIT WHEN c1%NOTFOUND OR c1%NOTFOUND IS NULL;"
       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 OR c1%NOTFOUND IS NULL;
      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;
/

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. 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. See Example 6-13, "Fetching Bulk Data with a Cursor".

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.

Cursor FOR LOOP

Topics:

• Implicit Cursor FOR LOOP

• Explicit Cursor FOR LOOP

BEGIN
  FOR item IN 
  ( SELECT last_name, job_id
     FROM employees
     WHERE job_id LIKE '%CLERK%'
     AND manager_id > 120 )
  LOOP
    DBMS_OUTPUT.PUT_LINE
      ('Name = ' || item.last_name || ', Job = ' || item.job_id);
  END LOOP;
END;
/

DECLARE 
 CURSOR c1 IS SELECT last_name, job_id FROM employees 
                WHERE job_id LIKE '%CLERK%' AND manager_id > 120;
BEGIN
  FOR item IN c1
  LOOP
    DBMS_OUTPUT.PUT_LINE
      ('Name = ' || item.last_name || ', Job = ' || item.job_id);
  END LOOP;
END;
/

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 Example 6-18, full_name and dream_salary are aliases for expressions in the query:

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;
/

Using Correlated Subqueries

While a subquery is evaluated only once for each table, a correlated subquery is evaluated once for each row. Example 6-21 returns the name and salary of each employee whose salary exceeds the departmental average. For each row in the table, the correlated subquery computes the average salary for the corresponding department.

DECLARE
-- For each department, find the average salary.
-- Then find all the employees in
-- that department making more than that average salary.
  CURSOR c1 IS
    SELECT department_id, last_name, salary FROM employees t
    WHERE salary >
      ( SELECT AVG(salary)
        FROM employees
        WHERE t.department_id = department_id )
      ORDER BY department_id;
BEGIN
  FOR person IN c1
  LOOP
    DBMS_OUTPUT.PUT_LINE('Making above-average salary = ' || person.last_name);
  END LOOP;
END;
/

Writing Maintainable PL/SQL Subqueries

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.

Example 6-22 displays the wages paid to employees earning over a specified wage in a specified department.

DECLARE
  CURSOR c1 (job VARCHAR2, max_wage NUMBER) IS
    SELECT * FROM employees
      WHERE job_id = job
      AND salary > max_wage;
BEGIN
  FOR person IN c1('CLERK', 3000)
  LOOP
     -- process data record
    DBMS_OUTPUT.PUT_LINE
      ('Name = ' || person.last_name || ', salary = ' ||
        person.salary || ', Job Id = ' || person.job_id );
  END LOOP;
END;
/

In Example 6-23, several ways are shown to open a cursor.

DECLARE
  emp_job      employees.job_id%TYPE := 'ST_CLERK';
  emp_salary   employees.salary%TYPE := 3000;
  my_record employees%ROWTYPE;
  CURSOR c1 (job VARCHAR2, max_wage NUMBER) IS
    SELECT * FROM employees
      WHERE job_id = job
      AND salary > max_wage;
BEGIN
-- Any of the following statements opens the cursor:
-- OPEN c1('ST_CLERK', 3000); OPEN c1('ST_CLERK', emp_salary);
-- OPEN c1(emp_job, 3000); OPEN c1(emp_job, emp_salary);
  OPEN c1(emp_job, 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 || ', Job Id = ' || my_record.job_id );
  END LOOP;
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
   -- Strong:
   TYPE empcurtyp IS REF CURSOR RETURN employees%ROWTYPE;
   -- Weak:
   TYPE genericcurtyp IS REF CURSOR;
   cursor1 empcurtyp;
   cursor2 genericcurtyp;
   my_cursor SYS_REFCURSOR; -- didn't need to declare a new type
   TYPE deptcurtyp IS REF CURSOR RETURN departments%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 subprogram.

In the RETURN clause of a REF CURSOR type definition, you can use %ROWTYPE to refer to a strongly typed cursor variable, as shown in Example 6-24.

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

You can also use %ROWTYPE to provide the datatype of a record variable, as shown in Example 6-25.

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

Example 6-26 specifies a user-defined RECORD type in the RETURN clause:

DECLARE
   TYPE EmpRecTyp IS RECORD (
      employee_id NUMBER,
      last_name VARCHAR2(25),
      salary   NUMBER(8,2));
   TYPE EmpCurTyp IS REF CURSOR RETURN EmpRecTyp;
   emp_cv EmpCurTyp;  -- declare cursor variable

Passing Cursor Variables As Parameters

You can declare cursor variables as the formal parameters of subprograms. Example 6-27 defines a REF CURSOR type, then declares a cursor variable of that type as a formal parameter.

DECLARE
   TYPE empcurtyp IS REF CURSOR RETURN employees%ROWTYPE;
   emp empcurtyp;
-- after result set is built,
-- process all the rows inside a single procedure
-- rather than invoking 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;
-- find employees matching a condition.
  OPEN emp FOR SELECT * FROM employees WHERE last_name LIKE 'R%';
  process_emp_cv(emp);
  CLOSE emp;
END;
/

Like all pointers, cursor variables increase the possibility of parameter aliasing. See "Overloading PL/SQL Subprogram Names".

Controlling Cursor Variables (OPEN-FOR, FETCH, and CLOSE Statements)

You use three statements to control a cursor variable: OPEN-FOR, FETCH, and CLOSE. First, you OPEN a cursor variable FOR a multiple-row query. Then, you FETCH rows from the result set. When all the rows are processed, you CLOSE the cursor variable.

Topics:

• Opening a Cursor Variable

• Using a Cursor Variable as a Host Variable

• Fetching from a Cursor Variable

• Closing a Cursor Variable

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;
/

CREATE PACKAGE emp_data AS
   TYPE empcurtyp IS REF CURSOR RETURN employees%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 employees
           WHERE commission_pct IS NOT NULL;
      ELSIF choice = 2 THEN
         OPEN emp_cv FOR SELECT *
           FROM employees
           WHERE salary > 2500;
      ELSIF choice = 3 THEN
         OPEN emp_cv FOR SELECT *
           FROM employees
           WHERE department_id = 100;
      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 employees;
      ELSIF choice = 2 THEN
         OPEN generic_cv FOR SELECT * FROM departments;
      ELSIF choice = 3 THEN
         OPEN generic_cv FOR SELECT * FROM jobs;
      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 employees;
  ELSIF :choice = 2 THEN
    OPEN :generic_cv FOR SELECT * FROM departments;
  ELSIF :choice = 3 THEN
    OPEN :generic_cv FOR SELECT * FROM jobs;
  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 employee_id < 120;
   LOOP 
      FETCH emp_cv INTO emp_rec; -- fetch from cursor variable
      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 job_id = 'SA_REP';
   FETCH emp_cv BULK COLLECT INTO names, sals;
   CLOSE emp_cv;
-- 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 example, 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. For 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 act 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 specification, as illustrated in Example 6-34.

• If you bind a host cursor variable into PL/SQL from an OCI client, 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.

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

CREATE PACKAGE emp_data AS
  TYPE EmpCurTyp IS REF CURSOR RETURN employees%ROWTYPE;
-- emp_cv EmpCurTyp; -- not allowed
  PROCEDURE open_emp_cv;
END emp_data;
/
CREATE PACKAGE BODY emp_data AS
-- emp_cv EmpCurTyp; -- not allowed
PROCEDURE open_emp_cv IS
  emp_cv EmpCurTyp; -- this is legal
  BEGIN
    OPEN emp_cv FOR SELECT * FROM employees;
  END open_emp_cv;
END emp_data;
/

Restrictions on Cursor Expressions

The following are 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: Cursor Expressions

In Example 6-35, 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,
-- second 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,
-- the result set from a subquery is processed
-- the set can be passed to a procedure
-- for processing rather than 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 "Performing Multiple Transformations with Pipelined Table Functions".

Using COMMIT in PL/SQL

The COMMIT statement ends the current transaction, making any changes made during that transaction permanent, and visible to other users. Transactions are not tied to PL/SQL BEGIN-END blocks. A block can contain multiple transactions, and a transaction can span multiple blocks.

Example 6-36 illustrates 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.

CREATE TABLE accounts (account_id NUMBER(6), balance NUMBER (10,2));
INSERT INTO accounts VALUES (7715, 6350.00); 
INSERT INTO accounts VALUES (7720, 5100.50); 
DECLARE
  transfer NUMBER(8,2) := 250;
BEGIN
  UPDATE accounts SET balance = balance - transfer
    WHERE account_id = 7715;
  UPDATE accounts SET balance = balance + transfer
    WHERE account_id = 7720;
  COMMIT COMMENT 'Transfer from 7715 to 7720'
    WRITE IMMEDIATE NOWAIT;
END;
/

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

Asynchronous commit provides more control for the user with the WRITE clause. This option specifies the priority with which the redo information generated by the commit operation is written to the redo log.

For more information on using COMMIT, see "Committing Transactions" in Oracle Database Advanced Application Developer's Guide. For information about distributed transactions, see Oracle Database Concepts.

Using ROLLBACK in PL/SQL

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.

Example 6-37 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.

CREATE TABLE emp_name AS SELECT employee_id, last_name
  FROM employees;
CREATE UNIQUE INDEX empname_ix ON emp_name (employee_id);
CREATE TABLE emp_sal AS SELECT employee_id, salary FROM employees;
CREATE UNIQUE INDEX empsal_ix ON emp_sal (employee_id);
CREATE TABLE emp_job AS SELECT employee_id, job_id FROM employees;
CREATE UNIQUE INDEX empjobid_ix ON emp_job (employee_id);

DECLARE
   emp_id       NUMBER(6);
   emp_lastname VARCHAR2(25);
   emp_salary   NUMBER(8,2);
   emp_jobid    VARCHAR2(10);
BEGIN
   SELECT employee_id, last_name, salary,
     job_id INTO emp_id, emp_lastname, emp_salary, emp_jobid
     FROM employees
     WHERE employee_id = 120;
   INSERT INTO emp_name VALUES (emp_id, emp_lastname);
   INSERT INTO emp_sal VALUES (emp_id, emp_salary);
   INSERT INTO emp_job VALUES (emp_id, emp_jobid);
EXCEPTION
   WHEN DUP_VAL_ON_INDEX THEN
      ROLLBACK;
      DBMS_OUTPUT.PUT_LINE('Inserts were rolled back');
END;
/

Using SAVEPOINT in PL/SQL

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. The number of active savepoints for each session is unlimited.

Example 6-38 marks a savepoint before doing an insert. If the INSERT statement tries to store a duplicate value in the employee_id column, the predefined exception DUP_VAL_ON_INDEX is raised. In that case, you roll back to the savepoint, undoing just the insert.

CREATE TABLE emp_name
  AS SELECT employee_id, last_name, salary
  FROM employees;
CREATE UNIQUE INDEX empname_ix ON emp_name (employee_id);

DECLARE
   emp_id        employees.employee_id%TYPE;
   emp_lastname  employees.last_name%TYPE;
   emp_salary    employees.salary%TYPE;
BEGIN
   SELECT employee_id, last_name, salary
     INTO emp_id, emp_lastname, emp_salary 
     FROM employees
     WHERE employee_id = 120;
   UPDATE emp_name SET salary = salary * 1.1
     WHERE employee_id = emp_id;
   DELETE FROM emp_name WHERE employee_id = 130;
   SAVEPOINT do_insert;
   INSERT INTO emp_name VALUES (emp_id, emp_lastname, emp_salary);
EXCEPTION
   WHEN DUP_VAL_ON_INDEX THEN
      ROLLBACK TO do_insert;
      DBMS_OUTPUT.PUT_LINE('Insert was rolled back');
END;
/

When you roll back to a savepoint, any savepoints marked after that savepoint are erased. The savepoint to which you roll back is not 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, but you can only roll back to the most recently marked savepoint.

Savepoint names are undeclared identifiers. Reusing a savepoint name within a transaction moves the savepoint from its old position to the current point in the transaction. This means that a rollback to the savepoint affects only the current part of your transaction, as shown in Example 6-39.

CREATE TABLE emp_name AS SELECT employee_id, last_name, salary
  FROM employees;
CREATE UNIQUE INDEX empname_ix ON emp_name (employee_id);

DECLARE
   emp_id        employees.employee_id%TYPE;
   emp_lastname  employees.last_name%TYPE;
   emp_salary    employees.salary%TYPE;
BEGIN
   SELECT employee_id, last_name, salary INTO emp_id, emp_lastname,
     emp_salary FROM employees WHERE employee_id = 120;
   SAVEPOINT my_savepoint;
   UPDATE emp_name SET salary = salary * 1.1
     WHERE employee_id = emp_id;
   DELETE FROM emp_name WHERE employee_id = 130;
   -- Move my_savepoint to current point
   SAVEPOINT my_savepoint;
   INSERT INTO emp_name VALUES (emp_id, emp_lastname, emp_salary);
EXCEPTION
   WHEN DUP_VAL_ON_INDEX THEN
      ROLLBACK TO my_savepoint;
      DBMS_OUTPUT.PUT_LINE('Transaction rolled back.');
END;
/

How Oracle Database 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. Usually, 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.

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.

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

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 statement, Oracle commits the transaction. If you execute a ROLLBACK statement or stop the SQL*Plus session, Oracle rolls back the transaction.

Setting Transaction Properties (SET TRANSACTION Statement)

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 Example 6-40 a store manager uses a read-only transaction to gather order totals for the day, the past week, and the past month. The totals are unaffected by other users updating the database during the transaction.

DECLARE
   daily_order_total   NUMBER(12,2);
   weekly_order_total  NUMBER(12,2); 
   monthly_order_total NUMBER(12,2);
BEGIN
   COMMIT; -- ends previous transaction
   SET TRANSACTION READ ONLY NAME 'Calculate Order Totals';
   SELECT SUM (order_total) INTO daily_order_total FROM orders
     WHERE order_date = SYSDATE;
   SELECT SUM (order_total) INTO weekly_order_total FROM orders
     WHERE order_date = SYSDATE - 7;
   SELECT SUM (order_total) INTO monthly_order_total FROM orders
     WHERE order_date = SYSDATE - 30;
   COMMIT; -- ends read-only transaction
END;
/

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

Restrictions on SET TRANSACTION

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

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.

Topics:

• Using FOR UPDATE

• Using LOCK TABLE

• Fetching Across Commits

DECLARE
   CURSOR c1 IS SELECT employee_id, salary FROM employees
      WHERE job_id = 'SA_REP' AND commission_pct > .10
      FOR UPDATE NOWAIT;

DECLARE
  CURSOR c1 IS SELECT last_name, department_name
    FROM employees, departments
    WHERE employees.department_id = departments.department_id 
    AND job_id = 'SA_MAN'
    FOR UPDATE OF salary;

DECLARE
   my_emp_id NUMBER(6);
   my_job_id VARCHAR2(10);
   my_sal    NUMBER(8,2);
   CURSOR c1 IS SELECT employee_id, job_id, salary
    FROM employees FOR UPDATE;
BEGIN
   OPEN c1;
   LOOP
      FETCH c1 INTO my_emp_id, my_job_id, my_sal;
      IF my_job_id = 'SA_REP' THEN
        UPDATE employees SET salary = salary * 1.02
          WHERE CURRENT OF c1;
      END IF;
      EXIT WHEN c1%NOTFOUND;
   END LOOP;
END;
/

LOCK TABLE employees IN ROW SHARE MODE NOWAIT;

DECLARE
-- if "FOR UPDATE OF salary" is included on following line,
-- an error is raised
   CURSOR c1 IS SELECT * FROM employees;
   emp_rec  employees%ROWTYPE;
BEGIN
   OPEN c1;
   LOOP
     -- FETCH fails on the second iteration with FOR UPDATE
     FETCH c1 INTO emp_rec;
     EXIT WHEN c1%NOTFOUND;
     IF emp_rec.employee_id = 105 THEN
       UPDATE employees SET salary = salary * 1.05
         WHERE employee_id = 105;
     END IF;
     COMMIT;  -- releases locks
   END LOOP;
END;
/

DECLARE
   CURSOR c1 IS SELECT last_name, job_id, rowid
     FROM employees;
   my_lastname   employees.last_name%TYPE;
   my_jobid      employees.job_id%TYPE;
   my_rowid      UROWID;
BEGIN
   OPEN c1;
   LOOP
      FETCH c1 INTO my_lastname, my_jobid, my_rowid;
      EXIT WHEN c1%NOTFOUND;
      UPDATE employees SET salary = salary * 1.02 WHERE rowid = my_rowid;
      -- this mimics WHERE CURRENT OF c1
      COMMIT;
   END LOOP;
   CLOSE c1;
END;
/

DECLARE
   CURSOR c1 IS SELECT employee_id, last_name, salary, rowid FROM employees;
   emp_rec c1%ROWTYPE;
BEGIN
   OPEN c1;
   LOOP
      FETCH c1 INTO emp_rec;
      EXIT WHEN c1%NOTFOUND;
      IF emp_rec.salary = 0 THEN
         DELETE FROM employees 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 subprograms. A calling application does not need to know whether operations done by that stored subprogram 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 subprograms

• 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 is PRAGMA AUTONOMOUS_TRANSACTION.

Example 6-43 marks a packaged function as autonomous. 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.

CREATE OR REPLACE PACKAGE emp_actions AS  -- package specification
   FUNCTION raise_salary (emp_id NUMBER, sal_raise NUMBER)
     RETURN NUMBER;
END emp_actions;
/
CREATE OR REPLACE PACKAGE BODY emp_actions AS  -- package body
-- code for function raise_salary
   FUNCTION raise_salary (emp_id NUMBER, sal_raise NUMBER)
     RETURN NUMBER IS
     PRAGMA AUTONOMOUS_TRANSACTION;
     new_sal NUMBER(8,2);
   BEGIN
     UPDATE employees SET salary =
       salary + sal_raise WHERE employee_id = emp_id;
     COMMIT;
     SELECT salary INTO new_sal FROM employees
       WHERE employee_id = emp_id;
     RETURN new_sal;
   END raise_salary;
END emp_actions;
/

Example 6-44 marks a standalone subprogram as autonomous.

CREATE PROCEDURE lower_salary (emp_id NUMBER, amount NUMBER) AS
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  UPDATE employees SET salary =
    salary - amount WHERE employee_id = emp_id;
  COMMIT;
END lower_salary;
/

Example 6-45 marks a PL/SQL block as autonomous. However, you cannot mark a nested PL/SQL block as autonomous.

DECLARE
  PRAGMA AUTONOMOUS_TRANSACTION;
  emp_id NUMBER(6);
  amount NUMBER(6,2);
BEGIN
  emp_id := 200;
  amount := 200;
  UPDATE employees SET salary = salary - amount WHERE employee_id = emp_id;
  COMMIT;
END;
/

Example 6-46 marks a database trigger as autonomous. Unlike regular triggers, autonomous triggers can contain transaction control statements such as COMMIT and ROLLBACK.

CREATE TABLE emp_audit ( emp_audit_id NUMBER(6), up_date DATE, 
                         new_sal NUMBER(8,2), old_sal NUMBER(8,2) );

CREATE OR REPLACE TRIGGER audit_sal
   AFTER UPDATE OF salary ON employees FOR EACH ROW
DECLARE 
   PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
-- bind variables are used here for values
   INSERT INTO emp_audit VALUES( :old.employee_id, SYSDATE, 
                                 :new.salary, :old.salary );
  COMMIT;
END;
/

Topics:

• Comparison of Autonomous Transactions and Nested Transactions

• Transaction Context

• Transaction Visibility

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

Topics:

• Entering and Exiting

• Committing and Rolling Back

• Using Savepoints

• Avoiding Errors with Autonomous Transactions

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 Example 6-47, 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 TABLE emp_audit ( emp_audit_id NUMBER(6), up_date DATE,
                         new_sal NUMBER(8,2), old_sal NUMBER(8,2) );

-- create an autonomous trigger that inserts
-- into the audit table before each update
-- of salary in the employees table
CREATE OR REPLACE TRIGGER audit_sal
   BEFORE UPDATE OF salary ON employees FOR EACH ROW
DECLARE 
   PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  INSERT INTO emp_audit VALUES( :old.employee_id, SYSDATE, 
                                :new.salary, :old.salary );
  COMMIT;
END;
/
-- update the salary of an employee, and then commit the insert
UPDATE employees SET salary
  salary * 1.05 WHERE employee_id = 115;
COMMIT;

-- update another salary, then roll back the update
UPDATE employees SET salary = salary * 1.05 WHERE employee_id = 116;
ROLLBACK;

-- show that both committed and rolled-back updates
-- add rows to audit table
SELECT * FROM emp_audit WHERE emp_audit_id = 115 OR emp_audit_id = 116;

Unlike regular triggers, autonomous triggers can execute DDL statements using native dynamic SQL, explained in Chapter 7, "Using Dynamic SQL". In the following example, trigger drop_temp_table drops a temporary database table after a row is inserted in table emp_audit.

CREATE TABLE emp_audit ( emp_audit_id NUMBER(6), up_date DATE,
                         new_sal NUMBER(8,2), old_sal NUMBER(8,2) );
CREATE TABLE temp_audit ( emp_audit_id NUMBER(6), up_date DATE);

CREATE OR REPLACE TRIGGER drop_temp_table
   AFTER INSERT ON emp_audit
DECLARE 
   PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
   EXECUTE IMMEDIATE 'DROP TABLE temp_audit';
   COMMIT;
END;
/

For more information about database triggers, see Chapter 9, "Using Triggers".

Invoking Autonomous Functions from SQL

A function invoked 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 Advanced Application Developer's Guide.

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 Example 6-48 shows. When you invoke 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;
/
-- invoke the packaged function from a query
DECLARE
   my_emp_id    NUMBER(6);
   my_last_name VARCHAR2(25);
   my_count     NUMBER;
BEGIN
   my_emp_id := 120;
   SELECT debugging.log_msg(last_name)
     INTO my_last_name FROM employees
     WHERE employee_id = my_emp_id;
-- even if you roll back in this scope, the insert into 'debug_output' remains
-- committed because it is part of an autonomous transaction
   ROLLBACK;
END;
/