Skip Headers
Oracle® Database PL/SQL Language Reference
11g Release 2 (11.2)

Part Number E10472-02
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
View PDF

6 PL/SQL Static SQL

Static SQL is a PL/SQL feature that allows SQL syntax directly in a PL/SQL statement. This chapter describes static SQL and explains how to use it.

Topics:

Description of Static SQL

These are the PL/SQL Static SQL statements, which have the same syntax as the corresponding SQL statements, except as noted:

A PL/SQL static SQL statement can have a PL/SQL identifier wherever its SQL counterpart can have a placeholder for a bind argument. The PL/SQL identifier must identify either a variable or a formal parameter, not a function that is visible only in the PL/SQL unit that includes the static SQL statement.

In Example 6-1, a PL/SQL anonymous block declares three PL/SQL variables and uses them in the static SQL statements INSERT, UPDATE, DELETE. The block also uses the static SQL statement COMMIT.

Example 6-1 Static SQL Statements

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

Result:

Robert Henry

To use PL/SQL identifiers for table names, column names, and so on, use the EXECUTE IMMEDIATE statement, explained in "Native Dynamic SQL".

Note:

After PL/SQL code runs a DML statement, the values of some variables are undefined. For example:
  • After a FETCH or SELECT statement raises an exception, the values of the define variables after that statement are undefined.

  • After a DML statement that affects zero rows, the values of the OUT bind variables are undefined, unless the DML statement is a BULK or multiple-row operation.

Resolution of Names in Static SQL Statements

When the PL/SQL compiler finds a static SQL statement:

  1. If the statement is a SELECT statement, the PL/SQL compiler removes the INTO clause.

  2. The PL/SQL compiler sends the statement to the SQL subsystem.

  3. The SQL subsystem checks the syntax of the statement.

    If the syntax is incorrect, the compilation of the PL/SQL unit fails. If the syntax is correct, the SQL subsystem determines the names of the tables and tries to resolve the other identifiers in the scope of the SQL statement.

  4. If the SQL subsystem cannot resolve an identifier in the scope of the SQL statement, the SQL subsystem sends the identifier back to the PL/SQL compiler. The identifier is called an escaped identifier.

  5. The PL/SQL compiler tries to resolve the escaped identifier.

    First, the compiler tries to resolve the identifier in the scope of the PL/SQL unit. If that fails, the compiler tries to resolve the identifier in the scope of the schema. If that fails, the compilation of the PL/SQL unit fails. For more information about PL/SQL name resolution, see Appendix B, "PL/SQL Name Resolution".

  6. If the compilation of the PL/SQL unit succeeds, the PL/SQL compiler generates the text of the regular SQL statement that is equivalent to the static SQL statement and stores that text with the generated machine code.

  7. At run time, the PL/SQL run-time system calls routines that parse, bind, and run the regular SQL statement.

    The bind arguments are the escaped identifiers (see step 4).

  8. If the statement is a SELECT statement, the PL/SQL run-time system stores the results in the PL/SQL targets specified in the INTO clause that the PL/SQL compiler removed in step 1.

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 more information about SQL pseudocolumns, including restrictions, see Oracle Database SQL Language Reference.

Topics:

CURRVAL and NEXTVAL

CURRVAL and NEXTVAL are sequence pseudocolumns. 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

The sequence_name can be either local or remote.

Each time you reference sequence_name.NEXTVAL, the sequence is incremented immediately and permanently, whether you commit or roll back the transaction.

Example 6-2 generates a sequence number and refers to that number in multiple statements. (The sequence must exist. Example 6-2 uses the sequence EMPLOYEES_SEQ, which is in the sample schema HR. To create a sequence, use the SQL statement CREATE SEQUENCE.)

Example 6-2 CURRVAL and NEXTVAL Pseudocolumns

DROP TABLE employees_temp;
CREATE TABLE employees_temp AS
  SELECT employee_id, first_name, last_name
  FROM employees;
 
DROP TABLE employees_temp2;
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 (employee_id, first_name, last_name) 
     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 database,
     there might be gaps in sequence. */
 
  -- Use CURRVAL to specify 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;
/

Usage Notes

  • You can use sequence_name.CURRVAL and sequence_name.NEXTVAL wherever you can use a NUMBER expression.

  • Using sequence_name.CURRVAL or sequence_name.NEXTVAL to provide an initial value for an ADT method parameter causes a compilation error.

  • PL/SQL evaluates every occurrence of sequence_name.CURRVAL and sequence_name.NEXTVAL (unlike SQL, which evaluates a sequence expression once for every row in which it appears).

See Also:

LEVEL

LEVEL is a hierarchical query pseudocolumn. If a table contains hierarchical data, you can select rows in a hierarchical order using the hierarchical query clause of the SELECT statement. For each row returned by a hierarchical query, the LEVEL pseudocolumn returns 1 for a root row, 2 for a child of a root, and so on.

See Also:

ROWID

The ROWID pseudocolumn 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 ROWIDTOCHAR function, 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 "Simulating CURRENT OF Clause with ROWID Pseudocolumn".

See Also:

Oracle Database SQL Language Reference for more information about the ROWID pseudocolumn

ROWNUM

For each row returned by a query, the ROWNUM pseudocolumn returns a number indicating the order in which Oracle Database selects the row from a table or set of joined rows. The first row selected has a ROWNUM of 1, the second has 2, and so on.

Note:

The SQL function ROW_NUMBER provides superior support for ordering the results of a query.

See Also:

Cursors

A cursor is a pointer to a private SQL area that stores information about processing a specific SELECT or DML statement.

The cursors that this chapter explains are session cursors. A session cursor lives in session memory until the session ends, when it ceases to exist. Session cursors are different from the cursors in the private SQL area of the program global area (PGA), explained in Oracle Database Concepts.

A session cursor that is constructed and managed by PL/SQL is called an implicit cursor. A session cursor that you construct and manage is called an explicit cursor. With an explicit cursor, you can retrieve the rows of a result set one at a time.

You can get information about any session cursor from its attributes (which you can use in procedural statements, but not in SQL statements).

To list the session cursors that each user session currently has opened and parsed, query the dynamic performance view V$OPEN_CURSOR, described in Oracle Database Reference.

Topics:

Implicit Cursors

PL/SQL opens an implicit cursor every time you run a SELECT or DML statement. You cannot control an implicit cursor, but you can get information from its attributes.

The syntax of an implicit cursor attribute value is SQLattribute (therefore, an implicit cursor is also called a SQL cursor). SQLattribute always refers to the most recently run SELECT or DML statement. If no such statement has run, the value of SQLattribute is NULL.

An implicit cursor closes after its associated statement runs; however, its attribute values remain available until another SELECT or DML statement runs.

The most recently run SELECT or DML statement might be in a different scope. To save an attribute value for later use, assign it to a local variable immediately. Otherwise, other operations, such as subprogram calls, might change the value of the attribute before you can test it.

The implicit cursor attributes are:

See Also:

"Implicit Cursor Attribute" for a syntax diagram

SQL%ISOPEN Attribute: Is the Cursor Open?

SQL%ISOPEN always returns FALSE, because an implicit cursor always closes after its associated statement runs.

SQL%FOUND Attribute: Were Any Rows Affected?

SQL%FOUND returns:

  • NULL if no SELECT or DML statement has run

  • TRUE if a SELECT statement returned one or more rows or a DML statement affected one or more rows

  • FALSE otherwise

Example 6-3 uses SQL%FOUND to determine if a DELETE statement affected any rows.

Example 6-3 SQL%FOUND Attribute

DROP TABLE dept_temp;
CREATE TABLE dept_temp AS
  SELECT * FROM departments;
 
CREATE OR REPLACE PROCEDURE p (
  dept_no NUMBER
) AUTHID DEFINER AS
BEGIN
  DELETE FROM dept_temp
  WHERE department_id = dept_no;
 
  IF SQL%FOUND THEN
    DBMS_OUTPUT.PUT_LINE (
      'Delete succeeded for department number ' || dept_no
    );
  ELSE
    DBMS_OUTPUT.PUT_LINE ('No department number ' || dept_no);
  END IF;
END;
/
BEGIN
  p(270);
  p(400);
END;
/

SQL%NOTFOUND Attribute: Were No Rows Affected?

SQL%NOTFOUND (the logical opposite of SQL%FOUND) returns:

  • NULL if no SELECT or DML statement has run

  • FALSE if a SELECT statement returned one or more rows or a DML statement affected one or more rows

  • TRUE otherwise

The SQL%NOTFOUND attribute is not useful with the PL/SQL SELECT INTO statement, because:

  • If the SELECT INTO statement returns no rows, PL/SQL raises the predefined exception NO_DATA_FOUND immediately, before you can check SQL%NOTFOUND.

  • A SELECT INTO statement that invokes a SQL aggregate function always returns a value (possibly NULL). After such a statement, the SQL%NOTFOUND attribute is always FALSE, so checking it is unnecessary.

SQL%ROWCOUNT Attribute: How Many Rows Were Affected?

SQL%ROWCOUNT returns:

  • NULL if no SELECT or DML statement has run

  • Otherwise, the number of rows returned by a SELECT statement or affected by a DML statement

Example 6-4 uses SQL%ROWCOUNT to determine the number of rows that were deleted.

Example 6-4 SQL%ROWCOUNT Attribute

DROP TABLE employees_temp;
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;
/

Result:

Number of employees deleted: 8

If a SELECT INTO statement without a BULK COLLECT clause returns multiple rows, PL/SQL raises the predefined exception TOO_MANY_ROWS and SQL%ROWCOUNT returns 1, not the actual number of rows that satisfy the query.

The value of SQL%ROWCOUNT attribute is unrelated to the state of a transaction. Therefore:

  • When a transaction rolls back to a savepoint, the value of SQL%ROWCOUNT is not restored to the value it had before the savepoint.

  • When an autonomous transaction ends, SQL%ROWCOUNT is not restored to the original value in the parent transaction.

Explicit Cursors

You must declare an explicit cursor, giving it a name and associating it with a query—usually one that returns multiple rows. After declaring an explicit cursor, you must open it (with the OPEN statement), fetch rows one at a time from the result set (with the FETCH statement), and close the cursor (with the CLOSE statement). After closing the cursor, you can neither fetch records from the result set nor see the cursor attribute values.

Topics:

Declaring Explicit Cursors

You must declare an explicit cursor before referencing it in other statements. You give the cursor a name and associate it with a specific query. You can optionally declare a return type for the cursor, such as table_name%ROWTYPE. You can optionally specify parameters that you use in the WHERE clause instead of referring to local variables. These parameters can have initial values. Example 6-5 declares two explicit cursors.

Note:

An explicit cursor declared in a package specification is affected by the AUTHID clause of the package. For more information, see "CREATE PACKAGE Statement".

Example 6-5 Explicit Cursor Declaration

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;
BEGIN
  NULL;
END;
/

The cursor is not a PL/SQL variable. You cannot assign a value to a cursor or use it in an expression. Cursors and variables follow the same scoping rules. Naming cursors after database tables is possible but not recommended.

A cursor can take parameters, which can appear in the associated query wherever constants can appear. The formal parameters of a cursor must be IN parameters; they supply values in the query, but do not return any values from the query. You cannot impose the NOT NULL constraint on a cursor parameter.

As this example shows, you can initialize cursor parameters to initial values. You can pass different numbers of actual parameters to a cursor, accepting or overriding the initial values. Also, you can add formal parameters without having to change existing references to the cursor.

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

Cursor parameters can be referenced only in the query specified in the cursor declaration. The parameter values are used by the associated query when the cursor is opened.

Opening Explicit Cursors

You open an explicit cursor with the OPEN statement (for its syntax, see "OPEN Statement"). The OPEN statement allocates database resources to process the query, identifies the result set, and positions the cursor before the first row of the result set. (If the query has a FOR UPDATE clause, processing the query includes locking the rows of the result set—see "SELECT FOR UPDATE and FOR UPDATE Cursors".)

Example 6-6 declares and opens an explicit cursor.

Example 6-6 Opening an Explicit Cursor

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

Generally, PL/SQL parses an explicit cursor only the first time it is opened and parses a SQL statement (creating an implicit cursor) only the first time the statement runs. All parsed SQL statements are cached. A SQL statement is reparsed only if it is aged out of the cache by a new SQL statement. Although you must close a cursor before you can reopen it, PL/SQL need not reparse the associated query. If you close and immediately reopen a cursor, PL/SQL does not reparse the associated query.

After opening an explicit cursor, use the FETCH statement to retrieve the rows of the result set.

Fetching Data with Explicit Cursors

Unless you use the BULK COLLECT clause, explained in "Fetching Data with Explicit Cursors", the FETCH statement retrieves the rows in the result set one at a time. Each fetch retrieves the current row and advances the cursor to the next row in the result set. You can store each column in a separate variable, or store the entire row in a record that has the appropriate fields, usually declared using %ROWTYPE.

For each column value returned by the query associated with the cursor, there must be a corresponding, type-compatible variable in the INTO list.

Typically, you use the FETCH statement inside a LOOP statement. A sequence of FETCH statements always runs out of data to retrieve, and no exception is raised when a FETCH statement returns no data. To detect this condition, use the cursor attribute %FOUND or %NOTFOUND, as in Example 6-7.

Example 6-7 Fetching with a Cursor

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

Result:

Nayer                    ST_CLERK
Mikkilineni              ST_CLERK
Landry                   ST_CLERK
...
Higgins                  AC_MGR

The query can reference PL/SQL variables in its scope. Any variables in the query are evaluated only when the cursor is opened. In Example 6-8, each retrieved salary is multiplied by 2, even though factor is incremented after every fetch.

Example 6-8 Referencing PL/SQL Variables in Its Scope

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;
  LOOP
    FETCH c1 INTO my_sal;
    EXIT WHEN c1%NOTFOUND;
    factor := factor + 1;  -- does not affect FETCH
  END LOOP;
  CLOSE c1;
END;
/

To change the result set or the values of variables in the query, you must close and reopen the cursor with the input variables set to their new values. However, you can use a different INTO list on separate fetches with the same cursor. Each fetch retrieves another row and assigns values to the target variables, as Example 6-9 shows.

Example 6-9 Fetching the Same Cursor Into Different Variables

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;  -- fetches first row
  FETCH c1 INTO name2;  -- fetches second row
  FETCH c1 INTO name3;  -- fetches third row
  CLOSE c1;
END;
/

If you fetch past the last row in the result set, the values of the target variables are undefined. Eventually, the FETCH statement fails to return a row. When that happens, no exception is raised. To detect the failure, use the cursor attribute %FOUND or %NOTFOUND. For more information, see "Cursor Expressions".

Fetching Bulk Data with Explicit Cursors

The BULK COLLECT clause lets you fetch all rows from the result set simultaneously. See "Retrieving Query Results into Collections". Example 6-10 bulk-fetches from a cursor into two collections.

Example 6-10 Fetching Bulk Data with a Cursor

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;
  -- Process collection elements
  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;
/

Result:

141
142
143
144
Markle
Marlow
Mallin
Matos

Closing Explicit Cursors

The CLOSE statement disables the cursor, and the result set becomes undefined. Once a cursor is closed, you can reopen it, which runs the query again with the latest values of any cursor parameters and variables referenced in the WHERE clause. Any other operation on a closed cursor raises the predefined exception INVALID_CURSOR.

Explicit Cursor Attributes

Note:

This topic applies to both explicit cursors and cursor variables. Explicit cursors and cursor variables are called named cursors, because you can reference them by their names. For more information about cursor variables, see "Cursor Variables".

The syntax for the value of a named cursor attribute is cursor_name immediately followed by attribute (for example, c1%ISOPEN).

The named cursor attributes are:

If a named cursor is not open, referencing any attribute except %ISOPEN raises INVALID_CURSOR.

%ISOPEN Attribute: Is the Cursor Open?

%ISOPEN returns TRUE if its named cursor is open; FALSE otherwise. Example 6-11 uses %ISOPEN to select an action.

Example 6-11 %ISOPEN Attribute

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
    OPEN c1;
  END IF;
  FETCH c1 INTO the_name, the_salary;
  CLOSE c1;
END;
/
%FOUND Attribute: Has a Row Been Fetched?

%FOUND returns:

  • NULL after the named cursor is opened but before the first fetch

  • TRUE if the last fetch from the named cursor returned a row

  • FALSE otherwise

Example 6-12 uses %FOUND to select an action.

Example 6-12 %FOUND Attribute

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
      EXIT;
    END IF;
  END LOOP;
END;
/

Result:

Name = King, salary = 24100
Name = Kochhar, salary = 17000
Name = De Haan, salary = 17000
Name = Hunold, salary = 9100
Name = Ernst, salary = 6000
Name = Austin, salary = 4800
Name = Pataballa, salary = 4800
Name = Lorentz, salary = 4300
Name = Greenberg, salary = 12008
Name = Faviet, salary = 9000
%NOTFOUND Attribute: Has No Row Been Fetched?

%NOTFOUND (the logical opposite of %FOUND) returns:

  • NULL after the named cursor is opened but before the first fetch

  • FALSE if the last fetch from the named cursor returned a row

  • TRUE otherwise

Example 6-13 uses %NOTFOUND to exit a loop when FETCH fails to return a row.

Example 6-13 %NOTFOUND Attribute

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

Result:

Name = King, salary = 24100
Name = Kochhar, salary = 17000
Name = De Haan, salary = 17000
Name = Hunold, salary = 9100
Name = Ernst, salary = 6000
Name = Austin, salary = 4800
Name = Pataballa, salary = 4800
Name = Lorentz, salary = 4300
Name = Greenberg, salary = 12008
Name = Faviet, salary = 9000

In Example 6-13, if FETCH never fetches a row, the loop is never exited. The EXIT WHEN statement runs only if its WHEN condition is TRUE. Before the first fetch, %NOTFOUND returns NULL. To prevent infinite looping, use this EXIT statement instead:

EXIT WHEN c1%NOTFOUND OR (c1%NOTFOUND IS NULL);
%ROWCOUNT Attribute: How Many Rows Were Fetched?

%ROWCOUNT returns:

  • Zero after the named cursor is opened but before the first fetch

  • Otherwise, the number of rows fetched.

Example 6-14 uses %ROWCOUNT to determine when the fifth row is fetched.

Example 6-14 %ROWCOUNT Attribute

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

Result:

1. Abel
2. Ande
3. Atkinson
4. Austin
5. Baer
--- Fetched 5th record ---
6. Baida
7. Banda
8. Bates
9. Bell
10. Bernstein

Table 6-1 shows the value of each cursor attribute before and after OPEN, FETCH, and CLOSE statements run.

Table 6-1 Named Cursor Attribute Values

Point in Time %FOUND Value %ISOPEN Value %NOTFOUND Value %ROWCOUNT Value

Before OPEN

exception

FALSE

exception

exception

After OPEN

NULL

TRUE

NULL

0

Before first FETCH

NULL

TRUE

NULL

0

After first FETCH

TRUE

TRUE

FALSE

1

Before each successive FETCH except last

TRUE

TRUE

FALSE

1

After each successive FETCH except last

TRUE

TRUE

FALSE

data dependent

Before last FETCH

TRUE

TRUE

FALSE

data dependent

After last FETCH

FALSE

TRUE

TRUE

data dependent

Before CLOSE

FALSE

TRUE

TRUE

data dependent

After CLOSE

exception

FALSE

exception

exception


In Table 6-1:

  • Referencing %FOUND, %NOTFOUND, or %ROWCOUNT before a cursor is opened or after it is closed raises INVALID_CURSOR.

  • After the first FETCH, if the result set was empty, %FOUND returns FALSE, %NOTFOUND returns TRUE, and %ROWCOUNT returns 0.

When Explicit Cursors Need Column Aliases

When the SELECT statement of an explicit cursor includes a calculated column (an expression), that column must have an alias if either of the following is true:

  • You use the cursor to fetch into a record that was declared with %ROWTYPE.

  • You want to reference the calculated column in your program.

In Example 6-15, the calculated column in the explicit cursor needs an alias for both of the preceding reasons.

Example 6-15 Explicit Cursor with Calculated Column that Needs Alias

DECLARE
  CURSOR c1 IS
    SELECT employee_id,
           (salary * .05) raise
    FROM employees
    WHERE job_id LIKE '%_MAN';
  emp_rec c1%ROWTYPE;
BEGIN
  OPEN c1;
  LOOP
    FETCH c1 INTO emp_rec;
    EXIT WHEN c1%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE (
      'Raise for employee #' || emp_rec.employee_id ||
      ' is $' || emp_rec.raise
    ); 
  END LOOP;
  CLOSE c1;
END;
/

Explicit Cursors that Accept Parameters

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. You can use either positional notation or named notation to pass the parameter values (for information about parameter notations, see "Positional, Named, and Mixed Notation for Actual Parameters").

If the query is usually issued with certain values, you can make those values the initial values of the cursor parameters. A cursor parameter declared with an initial value does not need a corresponding actual parameter. If you omit the actual parameter, the formal parameter assumes its initial value when the OPEN statement runs. If the initial value of a formal parameter is an expression, and you provide a corresponding actual parameter in the OPEN statement, the expression is not evaluated.

Tip:

To avoid confusion, use different names for cursor parameters and the PL/SQL variables that you pass to those parameters.

Example 6-16 declares a cursor that accepts two parameters, and then uses a cursor FOR LOOP to display the wages paid to employees who earn more than a specified wage in a specified department.

Example 6-16 Passing Parameters to a Cursor FOR LOOP

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-17 declares a cursor that accepts two parameters, and shows several ways to open that cursor.

Example 6-17 Passing Parameters to Explicit Cursors

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
/* Open the cursor with one of these statements:
   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;
     DBMS_OUTPUT.PUT_LINE
       ('Name = ' || my_record.last_name || ', salary = ' ||
        my_record.salary || ', Job Id = ' || my_record.job_id );
  END LOOP;
END;
/

Result:

Name = Nayer, salary = 3200, Job Id = ST_CLERK
Name = Bissot, salary = 3300, Job Id = ST_CLERK
Name = Mallin, salary = 3300, Job Id = ST_CLERK
Name = Ladwig, salary = 3600, Job Id = ST_CLERK
Name = Stiles, salary = 3200, Job Id = ST_CLERK
Name = Rajs, salary = 3500, Job Id = ST_CLERK
Name = Davies, salary = 3100, Job Id = ST_CLERK

Queries

PL/SQL lets you perform queries and access individual fields or entire rows from the result set. In traditional database programming, you process query results using an internal data structure called a cursor. In most situations, PL/SQL can manage the cursor for you, so that code to process query results is straightforward and compact. This section explains how to process both simple queries where PL/SQL manages everything, and complex queries where you interact with the cursor.

To process a multiple-row query, you must use either a cursor FOR loop (an implicit cursor in a FOR LOOP) or an explicit cursor and FETCH statement. The cursor FOR loop needs less code, but the explicit cursor is more flexible. For example, you can:

Topics:

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 multiple rows, 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.

For more information about the SELECT INTO statement, see "SELECT INTO Statement".

Selecting Multiple Rows (BULK COLLECT Clause)

If you must 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-10, "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 must 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.

See Also:

Looping Through Multiple Rows (Cursor FOR LOOP Statement)

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

The iterator variable for the cursor FOR LOOP does need not 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.

Topics:

See Also:

"Cursor FOR LOOP Statement" for the syntax of the cursor FOR LOOP

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.

In Example 6-18, a query returns the name and job ID of employees with manager IDs greater than 120.

Example 6-18 Implicit 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;
/

Result:

Name = Bissot, Job = ST_CLERK
Name = Atkinson, Job = ST_CLERK
Name = Marlow, Job = ST_CLERK
...
Name = Grant, Job = SH_CLERK

Before each iteration of the FOR LOOP, PL/SQL fetches into the implicitly declared record. The sequence of statements inside the loop runs 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 "Cursor FOR LOOP Statement".

Explicit Cursor FOR LOOP

If you must reference the same query from different parts of the same subprogram, you can declare a cursor that specifies the query, and process the results using a FOR LOOP statement.

Example 6-19 Explicit Cursor FOR LOOP

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

Result:

Name = Bissot, Job = ST_CLERK
Name = Atkinson, Job = ST_CLERK
Name = Marlow, Job = ST_CLERK
...
Name = Grant, Job = SH_CLERK

Column Aliases for Expression Values in 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. If so, use a column alias to give unique names to the appropriate columns.

In Example 6-20, full_name and dream_salary are aliases for expressions in the query.

Example 6-20 Alias for Expressions in 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;
/

Result:

Steven King dreams of making 241000
Neena Kochhar dreams of making 170000
Lex De Haan dreams of making 170000
Alexander Hunold dreams of making 91000
Bruce Ernst dreams of making 60000

Processing Complicated Queries with 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.

For more information about explicit cursors, see "Explicit Cursors".

Subqueries

A subquery is a query (usually enclosed in parentheses) that appears in another DML statement. The DML statement acts upon the result set of the subquery. For example:

Example 6-21 uses subqueries in its cursor declarations.

Example 6-21 Subqueries in Cursor Declarations

DECLARE
  CURSOR c1 IS
    SELECT employee_id, last_name
    FROM employees
    WHERE salary > ( SELECT AVG(salary) FROM employees );

  CURSOR c2 IS
    SELECT *
    FROM ( SELECT last_name, salary
           FROM employees
           ORDER BY salary DESC, last_name
         )
    ORDER BY salary DESC, last_name;
BEGIN
  FOR person IN c1
  LOOP
    DBMS_OUTPUT.PUT_LINE ('Above-average salary: ' || person.last_name);
  END LOOP;

  FOR person IN c2
  LOOP
    DBMS_OUTPUT.PUT_LINE
      ('Highest paid: ' || person.last_name || ' $' || person.salary);
  END LOOP;
END;
/

Result:

Above-average salary: King
Above-average salary: Kochhar
Above-average salary: De Haan
...
Above-average salary: Gietz
Highest paid: King $24100
Highest paid: De Haan $17000
Highest paid: Kochhar $17000
...
Highest paid: Olson $2100

Using a subquery in the FROM clause, the query in Example 6-22 returns the number and name of each department with five or more employees.

Example 6-22 Subquery in FROM Clause

DECLARE
  CURSOR c1 IS
    SELECT t1.department_id, department_name, staff
    FROM departments t1,
         ( SELECT department_id, COUNT(*) AS staff
           FROM employees
           GROUP BY department_id
         ) t2
    WHERE (t1.department_id = t2.department_id) AND staff >= 5;

BEGIN
   FOR dept IN c1
   LOOP
     DBMS_OUTPUT.PUT_LINE ('Department = '
       || dept.department_name || ', staff = ' || dept.staff);
   END LOOP;
END;
/

Result:

Department = Shipping, staff = 45
Department = IT, staff = 5
Department = Finance, staff = 6
Department = Sales, staff = 34
Department = Purchasing, staff = 6

While a subquery is evaluated once for each table, a correlated subquery is evaluated once for each row. Example 6-23 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.

Example 6-23 Correlated Subquery

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

Result:

Making above-average salary = Hartstein
Making above-average salary = Raphaely
Making above-average salary = Weiss
...
Making above-average salary = Higgins

Cursor Variables

A cursor variable is like an explicit cursor, except that it is not limited to one query. You can open a cursor variable for a query, process the result set, and then use the cursor variable for another query. A cursor variable has the same attributes as an explicit cursor (for details, see "Explicit Cursor Attributes").

The data type of a cursor variable is REF CURSOR. Informally, a cursor variable is sometimes called a REF CURSOR.

Topics:

Purpose of Cursor Variables

You use cursor variables to pass query result sets between PL/SQL stored subprograms and their clients. This is possible because PL/SQL and its clients share a pointer to the work area where the result set is stored.

Cursor variables are available to every PL/SQL client. For example, you can declare a cursor variable in a PL/SQL host environment such as an OCI or Pro*C program, then pass it as an input host variable (bind variable) to PL/SQL. Application development tools such as Oracle Forms, which have a PL/SQL engine, can use cursor variables entirely on the client side. Or, you can pass cursor variables back and forth between a client and the database server through remote subprogram calls.

A work area remains accessible while any cursor variable points to it, even if 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, and 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.

Cursor variables are also useful for passing query results between subprograms. Opening the cursor variable in one subprogram and processing it in a different subprogram helps to centralize data retrieval. This technique is also useful for multilanguage applications, where a PL/SQL subprogram might return a result set to a subprogram written in a different language, such as Java or Visual Basic.

Cursor Variable Creation

To create a cursor variable, you define a REF CURSOR type, and then declare a cursor variable of that type.

You can define REF CURSOR types in any PL/SQL block, subprogram, or package. In this 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;
BEGIN
  NULL;
END;
/

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 type, you can use the predefined type SYS_REFCURSOR.

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

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

Example 6-24 Cursor Variable Declarations

DECLARE
  TYPE empcurtyp IS REF CURSOR RETURN employees%ROWTYPE;  -- strong
  TYPE genericcurtyp IS REF CURSOR;  -- weak

  cursor1  empcurtyp;
  cursor2  genericcurtyp;
  my_cursor SYS_REFCURSOR;  -- no new type needed
  TYPE deptcurtyp IS REF CURSOR RETURN departments%ROWTYPE;
  dept_cv deptcurtyp;  -- declare cursor variable
BEGIN
  NULL;
END;
/

To avoid declaring the same REF CURSOR type in each subprogram that uses it, you can put the REF CURSOR declaration in a package specification. You can declare cursor variables of that type in the corresponding package body, or in 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-25.

Example 6-25 Cursor Variables Returning %ROWTYPE Variables

DECLARE
  TYPE TmpCurTyp IS REF CURSOR RETURN employees%ROWTYPE;
  tmp_cv TmpCurTyp;

  TYPE EmpCurTyp IS REF CURSOR RETURN tmp_cv%ROWTYPE;
  emp_cv EmpCurTyp;
BEGIN
  NULL;
END;
/

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

Example 6-26 %ROWTYPE Attribute Provides Data Type

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

Example 6-27 specifies a user-defined RECORD type in the RETURN clause.

Example 6-27 Cursor Variable Returning a Record Type

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;
BEGIN
  NULL;
END;
/

Cursor Variables as Subprogram Parameters

You can declare cursor variables as the formal parameters of subprograms.

When you declare a cursor variable as the formal parameter of a subprogram that fetches from the cursor variable, you must specify the IN or IN OUT mode. However, if the subprogram also opens the cursor variable, you must specify the IN OUT mode.

Example 6-28 defines a REF CURSOR type and then declares a cursor variable of that type as a formal parameter.

Example 6-28 Cursor Variable as Parameter

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

  /* After building result set,
     process all 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
  -- 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;
/

Result:

-----
Here are the names from the result set:
Name = Steven King
Name = Neena Kochhar
Name = Lex De Haan
Name = Alexander Hunold
Name = Bruce Ernst
Name = David Austin
Name = Valli Pataballa
Name = Diana Lorentz
Name = Nancy Greenberg
Name = Daniel Faviet
-----
Here are the names from the result set:
Name = Trenna Rajs
Name = Den Raphaely
Name = Michael Rogers
Name = John Russell

Like all pointers, cursor variables increase the possibility of parameter aliasing. See "Overloaded Subprograms".

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.

Cursor Variable Control (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 Cursor Variables

The OPEN FOR statement associates a cursor variable with a multiple-row query, runs the query, and identifies the result set. The cursor variable can be declared in either PL/SQL or a PL/SQL host environment, such as an OCI program.

Unlike cursors, cursor variables do not take parameters. Instead, you can pass whole queries (not just parameters) to a cursor variable. Although a PL/SQL stored subprogram can open a cursor variable and pass it back to a calling subprogram, the calling and called subprograms must be in the same instance. You cannot pass or return cursor variables to procedures and functions called through database links. When you declare a cursor variable as the formal parameter of a subprogram that opens the cursor variable, you must specify the IN OUT mode. That way, the subprogram can pass an open cursor back to the caller.

For the syntax of the OPEN FOR statement, see "OPEN FOR Statement". This section explains the static SQL case, which uses select_statement. For the dynamic SQL case, which uses dynamic_string, see "OPEN FOR Statement".

The SELECT statement for the query can be coded directly in the statement, or can be a string variable or string literal. When you use a string as the query, it can include placeholders for bind variables, and you specify the corresponding values with a USING clause.

Example 6-29 opens a cursor variable.

Example 6-29 Opening Cursor Variable

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

Other OPEN FOR statements can open the same cursor variable for different queries. You need not close a cursor variable before reopening it. Consecutive OPENs of a static cursor raise the predefined exception CURSOR_ALREADY_OPEN. When you reopen a cursor variable for a different query, the previous query is lost.

Typically, you open a cursor variable by passing it to a stored subprogram that declares an IN OUT parameter that is a cursor variable, as in Example 6-30.

Example 6-30 Stored Procedure to Open a Cursor Variable

CREATE OR REPLACE 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 OR REPLACE 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;
/

You can also use a standalone stored subprogram to open the cursor variable. Define the REF CURSOR type in a package, then reference that type in the parameter declaration for the stored subprogram.

To centralize data retrieval, you can group type-compatible queries in a stored subprogram. In Example 6-31, the packaged subprogram declares a selector as one of its formal parameters. When invoked, the subprogram opens the cursor variable emp_cv for the chosen query.

Example 6-31 Stored Procedure to Open Cursor Variables with Different Queries

CREATE OR REPLACE 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 OR REPLACE 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;
/

For more flexibility, a stored subprogram can run queries with different return types, shown in Example 6-32.

Example 6-32 Cursor Variable with Different Return Types

CREATE OR REPLACE PACKAGE admin_data AS
  TYPE gencurtyp IS REF CURSOR;
  PROCEDURE open_cv (generic_cv IN OUT gencurtyp, choice INT);
END admin_data;
/
CREATE OR REPLACE 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;
/

Fetching from Cursor Variables

The FETCH statement retrieves rows from the result set of a multiple-row query. It works the same with cursor variables as with explicit cursors. Example 6-33 fetches rows one at a time from a cursor variable into a record.

Example 6-33 Fetching from Cursor Variable into Record

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

Result:

Name = Steven King
Name = Neena Kochhar
Name = Lex De Haan
...
Name = Karen Colmenares

Using the BULK COLLECT clause, you can bulk fetch rows from a cursor variable into one or more collections as shown in Example 6-34.

Example 6-34 Fetching from Cursor Variable into Collections

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

Result:

Name = Tucker, salary = 10000
Name = Bernstein, salary = 9500
Name = Hall, salary = 9000
...
Name = Johnson, salary = 6200

Any variables in the associated query are evaluated only when the cursor variable is opened. To change the result set or the values of variables in the query, reopen the cursor variable with the variables set to new values. You can use a different INTO clause on separate fetches with the same cursor variable. Each fetch retrieves another row from the same result set.

PL/SQL makes sure the return type of the cursor variable is compatible with the INTO clause of the FETCH statement. If there is a mismatch, an error occurs at compile time if the cursor variable is strongly typed, or at run time if it is weakly typed. At run time, PL/SQL raises the predefined exception ROWTYPE_MISMATCH before the first fetch. If you trap the exception and run the FETCH statement using a different (compatible) INTO clause, no rows are lost.

When declaring a cursor variable as the formal parameter of a subprogram that fetches from or closes the cursor variable, you must specify the IN or IN OUT mode. If the subprogram opens the cursor variable, you must specify the IN OUT mode.

If you try to fetch from a closed or never-opened cursor variable, PL/SQL raises the predefined exception INVALID_CURSOR.

Closing Cursor Variables

The CLOSE statement disables a cursor variable and makes the associated result set undefined. Close the cursor variable after the last row is processed.

When declaring a cursor variable as the formal parameter of a subprogram that closes the cursor variable, you must specify the IN or IN OUT mode. If you try to close an already-closed or never-opened cursor variable, PL/SQL raises the predefined exception INVALID_CURSOR.

INVALID_CURSOR Exception

If you try to fetch from, close, or refer to 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 cursor subprogram 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 open host cursor variable or open 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.

Cursor Variables as Host Variables

You can declare a cursor variable in a PL/SQL host environment such as an OCI or Pro*C program. To use the cursor variable, you must pass it as a host variable to PL/SQL.

Example 6-35, a Pro*C program passes a host cursor variable and selector to a PL/SQL block, which opens the cursor variable for the chosen query.

Example 6-35 Cursor Variable as Host Variable

EXEC SQL BEGIN DECLARE SECTION;
  SQL_CURSOR  generic_cv;  -- host cursor variable
  int         choice;
EXEC SQL END DECLARE SECTION;
EXEC SQL ALLOCATE :generic_cv;  -- Initialize host cursor variable.
-- 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;

Host cursor variables are compatible with any query return type. They act like weakly typed PL/SQL cursor variables.

When passing host cursor variables to PL/SQL, you can reduce network traffic by grouping OPEN FOR statements, as in Example 6-36, where the PL/SQL block opens multiple cursor variables in a single round trip.

Example 6-36 Reducing Network Traffic When Passing Host Variables

/* 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 can be useful when you want to populate a multiblock form, as in in Oracle Forms. 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;
/

Cursor Variable Restrictions

Cursor variables are subject to these restrictions:

  • You cannot declare cursor variables in a package specification, as Example 6-37 shows.

  • If you bind a host cursor variable into PL/SQL from an Oracle Call Interface (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 a collection.

  • Cursors and cursor variables are not interchangeable (you cannot use one where the other is expected). For example, you cannot reference a cursor variable in a cursor FOR LOOP.

  • A cursor variable is permitted in a server-to-server RPC only if the remote database is not an Oracle Database accessed through a Procedural Gateway.

Note:

LOB parameters are not permitted in a server-to-server RPC.

Example 6-37 Declaration of Cursor Variables in a Package

CREATE OR REPLACE 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 OR REPLACE 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;
/

Cursor Expressions

A cursor expression returns a nested cursor. Each row in the result set can contain values, as usual, and cursors produced by subqueries involving the other values in the row. A single query can return a large set of related values retrieved from multiple tables. You can process the result set with nested loops that fetch first from the rows of the result set, and then from any nested cursors in those rows.

A cursor expression has this syntax:

CURSOR(subquery)

A nested cursor opens when the containing row is fetched from the parent cursor.

A nested cursor closes only when one of the following happens:

In Example 6-38, the cursor c1 is associated with a query that includes a cursor expression. For each department in the departments table, the nested cursor returns the last name of each employee in that department (which it retrieves from the employees table).

Example 6-38 Cursor Expression

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_id,
      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  -- Process each row of query result set
    FETCH c1 INTO dept_name, emp_cur;
    EXIT WHEN c1%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE('Department: ' || dept_name);

    LOOP -- Process each row of subquery result set
      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;
/

Result:

Department: 10
-- Employee: Whalen
Department: 110
-- Employee: Higgins
-- Employee: Gietz

You can pass a cursor expression to a function that has a formal parameter of the type REF CURSOR. Using a cursor expression as an actual parameter lets you pass the function a set of rows. Cursor expressions are often passed as parameters to pipelined table functions, as explained in "Passing Data with Cursor Variables".

Cursor Expression Restrictions

Transaction Processing and Control

Transaction processing is an Oracle Database feature that enables multiple users to work on the database concurrently, and ensures that each user sees a consistent version of data and that all changes are applied in the right order. Different users can write to the same data structures without harming each other's data or coordinating with each other, because Oracle Database locks data structures automatically. To maximize data availability, Oracle Database locks the minimum amount of data for the minimum amount of time.

You rarely must write extra code to prevent problems with multiple users accessing data concurrently. However, if you do need this level of control, you can request locks on tables or rows.

You can control transactions with the SQL transaction control statements COMMIT, SAVEPOINT, ROLLBACK, and SET TRANSACTION.

Explicitly end every transaction, using either the COMMIT or ROLLBACK statement. Otherwise, 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 run a data definition, data control, or COMMIT statement or if you issue the EXIT, DISCONNECT, or QUIT statement, the database commits the transaction. If you run a ROLLBACK statement or stop the SQL*Plus session, the database rolls back the transaction.

Whether you end the transaction in your PL/SQL program or from a client program depends on the application logic.

Oracle Database uses transactions to ensure data integrity. A transaction is a series of DML 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. A transaction can span multiple blocks, and a block can contain multiple transactions.

At the end of a transaction, the database either commits (makes permanent) or rolls back (undoes) all changes in the transaction. If your program fails in the middle of a transaction, the database detects the failure and rolls back the transaction, restoring the database to its former state.

The SQL transaction control statements are COMMIT, ROLLBACK, SAVEPOINT, and SET TRANSACTION statements. 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 "Transaction Processing and Control".

Topics:

See Also:

COMMIT Statement

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.

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.

In Example 6-39, a transaction transfers money from one bank account to another. It is important that the money both leaves one account and enters the other, hence the COMMIT WRITE IMMEDIATE NOWAIT statement.

Example 6-39 COMMIT Statement with COMMENT and WRITE Clauses

DROP TABLE accounts;
CREATE TABLE accounts (
  account_id  NUMBER(6),
  balance     NUMBER (10,2)
);
 
INSERT INTO accounts (account_id, balance)
VALUES (7715, 6350.00);
 
INSERT INTO accounts (account_id, balance)
VALUES (7720, 5100.50);
 
CREATE OR REPLACE PROCEDURE transfer (
  from_acct  NUMBER,
  to_acct    NUMBER,
  amount     NUMBER
) AUTHID DEFINER AS
BEGIN
  UPDATE accounts
  SET balance = balance - amount
  WHERE account_id = from_acct;
 
  UPDATE accounts
  SET balance = balance + amount
  WHERE account_id = to_acct;
 
  COMMIT WRITE IMMEDIATE NOWAIT;
END;
/

Query before transfer:

SELECT * FROM accounts;

Result:

ACCOUNT_ID    BALANCE
---------- ----------
      7715       6350
      7720     5100.5
 
BEGIN
  transfer(7715, 7720, 250);
END;
/
 

Query after transfer:

SELECT * FROM accounts;

Result:

ACCOUNT_ID    BALANCE
---------- ----------
      7715       6100
      7720     5350.5

Note:

The default PL/SQL commit behavior for nondistributed transactions is BATCH NOWAIT if the COMMIT_LOGGING and COMMIT_WAIT database initialization parameters have not been set.

See Also:

ROLLBACK Statement

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-40 inserts information about an employee into three different tables. If an INSERT statement tries to store a duplicate employee number, the predefined exception DUP_VAL_ON_INDEX is raised. To ensure that changes to all three tables are undone, the exception handler runs a ROLLBACK.

Example 6-40 ROLLBACK Statement

DROP TABLE emp_name;
CREATE TABLE emp_name AS 
  SELECT employee_id, last_name
  FROM employees;
 
CREATE UNIQUE INDEX empname_ix
ON emp_name (employee_id);
 
 
DROP TABLE emp_sal;
CREATE TABLE emp_sal AS
  SELECT employee_id, salary
  FROM employees;
 
CREATE UNIQUE INDEX empsal_ix
ON emp_sal (employee_id);
 
 
DROP TABLE emp_job;
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 (employee_id, last_name)
  VALUES (emp_id, emp_lastname);
 
  INSERT INTO emp_sal (employee_id, salary) 
  VALUES (emp_id, emp_salary);
 
  INSERT INTO emp_job (employee_id, job_id)
  VALUES (emp_id, emp_jobid);
 
EXCEPTION
  WHEN DUP_VAL_ON_INDEX THEN
    ROLLBACK;
    DBMS_OUTPUT.PUT_LINE('Inserts were rolled back');
END;
/

See Also:

Oracle Database SQL Language Reference for more information about the ROLLBACK statement

SAVEPOINT Statement

The SAVEPOINT statement 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-41 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 and the transaction rolls back to the savepoint, undoing only the INSERT statement.

Example 6-41 SAVEPOINT and ROLLBACK Statements

DROP TABLE emp_name;
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 (employee_id, last_name, salary)
  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 in a recursive subprogram, new instances of the SAVEPOINT statement run 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 in a transaction moves the savepoint from its old position to the current point in the transaction, which means that a rollback to the savepoint affects only the current part of the transaction.

Example 6-42 Reusing a SAVEPOINT with ROLLBACK

DROP TABLE emp_name;
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;
 
  SAVEPOINT my_savepoint;
 
  INSERT INTO emp_name (employee_id, last_name, salary)
  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;
/

See Also:

Oracle Database SQL Language Reference for more information about the SET TRANSACTION SQL statement

Implicit Rollbacks

Before running an INSERT, UPDATE, or DELETE statement, the database marks an implicit savepoint (unavailable to you). If the statement fails, the database 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.

The database can also roll back single SQL statements to break deadlocks. The database signals an error to a participating transaction and rolls back the current statement in that transaction.

Before running a SQL statement, the database must parse it, that is, examine it to ensure it follows syntax rules and refers to valid schema objects. Errors detected while running 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.

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-43 a read-only transaction 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. The orders table is in the sample schema OE.

Example 6-43 SET TRANSACTION Statement in Read-Only Transaction

DECLARE
  daily_order_total    NUMBER(12,2);
  weekly_order_total   NUMBER(12,2); 
  monthly_order_total  NUMBER(12,2);
BEGIN
   COMMIT; -- end 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.

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

See Also:

Oracle Database SQL Language Reference for more information about the SQL statement SET TRANSACTION

Overriding Default Locking

By default, Oracle Database locks data structures automatically, which enables different applications to write to the same data structures without harming each other's data or coordinating with each other.

If you must have exclusive access to data during a transaction, you can override default locking with these SQL statements:

  • LOCK TABLE, which explicitly locks entire tables.

  • SELECT with the FOR UPDATE clause (SELECT FOR UPDATE) , which explicitly locks specific rows of a table.

Topics:

LOCK TABLE Statement

The LOCK TABLE statement explicitly locks one or more tables in a specified lock mode so that you can share or deny access to them.

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.

A table lock never prevents 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 does one transaction wait for the other to complete. The LOCK TABLE statement lets you specify how long to wait for another transaction to complete.

Table locks are released when the transaction that acquired them is either committed or rolled back.

See Also:

SELECT FOR UPDATE and FOR UPDATE Cursors

The SELECT statement with the FOR UPDATE clause (SELECT FOR UPDATE) selects the rows of the result set and locks them. SELECT FOR UPDATE enables you to base an update on the existing values in the rows, because it ensures that no other user can change those values before you update them. You can also use SELECT FOR UPDATE to lock rows that you do not want to update, as in Example 9-20.

By default, the SELECT FOR UPDATE statement waits until the requested row lock is acquired. To change this behavior, use the NOWAIT, WAIT, or SKIP LOCKED clause of the SELECT FOR UPDATE statement. For information about these clauses, see Oracle Database SQL Language Reference.

When SELECT FOR UPDATE is associated with an explicit cursor, the cursor is called a FOR UPDATE cursor. Only a FOR UPDATE cursor can appear in the CURRENT OF clause of an UPDATE or DELETE statement. (The CURRENT OF clause, a PL/SQL extension to the WHERE clause of the SQL statements UPDATE and DELETE, restricts the statement to the current row of the cursor.)

In Example 6-44, a FOR UPDATE cursor appears in the CURRENT OF clause of an UPDATE statement.

Example 6-44 FOR UPDATE Cursor in CURRENT OF Clause of UPDATE Statement

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

When SELECT FOR UPDATE queries multiple tables, it locks only rows whose columns appear in the FOR UPDATE clause.

In Example 6-45, SELECT FOR UPDATE queries the tables EMPLOYEES and DEPARTMENTS, but only SALARY appears in the FOR UPDATE clause. SALARY is a column of EMPLOYEES, but not of DEPARTMENTS; therefore, SELECT FOR UPDATE locks only rows of EMPLOYEES. If the FOR UPDATE clause included DEPARTMENT_ID or MANAGER_ID, which are columns of both EMPLOYEES and DEPARTMENTS, SELECT FOR UPDATE would lock rows of both tables.

Example 6-45 SELECT FOR UPDATE with Multiple Tables

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;
BEGIN
  NULL;
END;
/

Simulating CURRENT OF Clause with ROWID Pseudocolumn

The rows of the result set are locked when you open a FOR UPDATE cursor, not as they are fetched. The rows are unlocked when you commit or roll back the transaction. After the rows are unlocked, you cannot fetch from the FOR UPDATE cursor, as Example 6-46 shows (the result is the same if you substitute ROLLBACK for COMMIT).

Example 6-46 Trying to Fetch with FOR UPDATE Cursor After COMMIT Statement

DECLARE
  CURSOR c1 IS
    SELECT * FROM employees
    FOR UPDATE OF salary;
  emp_rec  employees%ROWTYPE;
BEGIN
  OPEN c1;
  LOOP
    FETCH c1 INTO emp_rec;  -- fails on second iteration
    EXIT WHEN c1%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE (
      'emp_rec.employee_id = ' ||
      TO_CHAR(emp_rec.employee_id)
    );
    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;
/

Result:

emp_rec.employee_id = 100
DECLARE
*
ERROR at line 1:
ORA-01002: fetch out of sequence
ORA-06512: at line 9

The workaround is to simulate the CURRENT OF clause with the ROWID pseudocolumn. Select the rowid of each row into a UROWID variable and use the rowid to identify the current row during subsequent updates and deletes, as in Example 6-47.

Caution:

Because no FOR UPDATE clause locks the fetched rows, other users might unintentionally overwrite your changes.

Note:

The extra space needed for read consistency is not released until the cursor is closed, which can slow down processing for large updates.

Example 6-47 Simulating CURRENT OF Clause with ROWID Pseudocolumn

DECLARE
  CURSOR c1 IS
    SELECT last_name, job_id, rowid
    FROM employees;  -- no FOR UPDATE clause
  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;  -- simulates WHERE CURRENT OF c1

    COMMIT;
  END LOOP;
  CLOSE c1;
END;
/

Autonomous Transactions

An autonomous transaction is an independent transaction started by another transaction, the main transaction. Autonomous transactions do SQL operations and commit or roll back, without committing or rolling back the main transaction. For example, if you write auditing data to a log table, you want to commit the audit data even if the operation you are auditing later fails; if something goes wrong recording the audit data, you do not want the main operation to be rolled back.

Figure 6-1 shows how control flows from the main transaction (MT) to an autonomous transaction (AT) and back again.

Figure 6-1 Transaction Control Flow

Transaction Control Flow
Description of "Figure 6-1 Transaction Control Flow"

Note:

Although an autonomous transaction is started by another transaction, it is not a nested transaction, because:
  • It does not share transactional resources (such as locks) with the main transaction.

  • It does not depend on the main transaction.

    For example, if the main transaction rolls back, nested transactions roll back, but autonomous transactions do not.

  • Its committed changes are visible to other transactions immediately.

    A nested transaction's committed changes are not visible to other transactions until the main transaction commits.

  • Exceptions raised in an autonomous transaction cause a transaction-level rollback, not a statement-level rollback.

Topics:

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.

Autonomous transactions help you build modular, reusable software components. You can encapsulate autonomous transactions in stored subprograms. A calling application needs not know whether operations done by that stored subprogram succeeded or failed.

Transaction Context

The main transaction shares its context with nested routines, but not with autonomous transactions. When one autonomous routine invokes another (or itself, recursively), the routines share no transaction context. When an autonomous routine invokes a nonautonomous routine, the routines share the same transaction context.

Transaction Visibility

Changes made by an autonomous transaction become visible to other transactions when the autonomous transaction commits. These changes become visible to the main transaction when it resumes, if its isolation level is set to READ COMMITTED (the default).

If you set the isolation level of the main transaction to SERIALIZABLE, changes made by its autonomous transactions are not visible to the main transaction when it resumes:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

Note:

  • Transaction properties apply only to the transaction in which they are set.

  • Cursor attributes are not affected by autonomous transactions.

Declaring Autonomous Transactions

To declare an autonomous transaction, use the AUTONOMOUS_TRANSACTION pragma. For information about this pragma, see "AUTONOMOUS_TRANSACTION Pragma".

Tip:

For readability, put the AUTONOMOUS_TRANSACTION pragma at the top of the declarative section. (The pragma is allowed anywhere in the declarative section.)

You cannot apply the AUTONOMOUS_TRANSACTION pragma to an entire package, but you can apply it to each subprogram in a package.

Example 6-48 marks a packaged function as autonomous.

Example 6-48 Declaring an Autonomous Function in a Package

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-49 marks a standalone stored subprogram as autonomous.

Example 6-49 Declaring an Autonomous Standalone Procedure

CREATE OR REPLACE 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-50 marks a schema-level PL/SQL block as autonomous. (A nested PL/SQL block cannot be autonomous.)

Example 6-50 Declaring an Autonomous PL/SQL Block

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

  COMMIT;
END;
/

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 run since the last commit or rollback comprise the current transaction. To control autonomous transactions, use these statements, which apply only to the current (active) transaction:

  • COMMIT

  • ROLLBACK [TO savepoint_name]

  • SAVEPOINT savepoint_name

  • SET TRANSACTION

Topics:

Entering and Exiting

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 invoked by it) has pending transactions, an exception is raised, and the pending transactions are rolled back.

Committing and Rolling 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 can contain several autonomous transactions, if it issues several COMMIT statements.

Savepoints

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.

Avoiding Errors with Autonomous Transactions

To avoid some common errors, remember:

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

  • The database 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, the database raises an exception. If the exception goes unhandled, the transaction is rolled back.

Autonomous Triggers

A database trigger can do the following only if it is autonomous:

  • Run Transaction Control Language (TCL) statements

  • Use native dynamic SQL to run Data Definition Language (DDL) statements

    For information about native dynamic SQL, see "Native Dynamic SQL".

One use of database triggers is to log events transparently—for example, to log all inserts into a table, even those that roll back. In Example 6-51, whenever a row is inserted into the EMPLOYEES table, a trigger inserts the same row into an audit table. Because the trigger is autonomous, it can commit changes to the audit table regardless of whether they are committed to the main table.

Example 6-51 Autonomous Trigger the Logs INSERT Statements

-- Audit table:

DROP 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)
);

-- Autonomous trigger on 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 (
    emp_audit_id,
    up_date,
    new_sal,
    old_sal
  )
  VALUES (
    :old.employee_id,
    SYSDATE,
    :new.salary,
    :old.salary
  );
  COMMIT;
END;
/
UPDATE employees
SET salary = salary * 1.05
WHERE employee_id = 115;

COMMIT;

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;

Result:

EMP_AUDIT_ID UP_DATE      NEW_SAL    OLD_SAL
------------ --------- ---------- ----------
         115 06-MAR-09     3320.1       3162
         116 06-MAR-09     3105.9       2958
 
2 rows selected.

In Example 6-52, an autonomous trigger uses native dynamic SQL (an EXECUTE IMMEDIATE statement) to drop a temporary table after a row is inserted into the table emp_audit.

Example 6-52 Autonomous Trigger Using Native Dynamic SQL for DDL

DROP 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)
);
DROP TABLE temp_audit;
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;
/
-- Show how trigger works
SELECT * FROM temp_audit;
 

Result:

no rows selected

INSERT INTO emp_audit (emp_audit_id, up_date, new_sal, old_sal)
VALUES (999, SYSDATE, 5000, 4500);
 
SELECT * FROM temp_audit;

Result:

SELECT * FROM temp_audit
              *
ERROR at line 1:
ORA-00942: table or view does not exist

For general information about triggers, see Chapter 9, "PL/SQL Triggers."

Invoking Autonomous Functions from SQL

A function invoked from SQL statements must obey rules meant to control side effects (for details, see "Subprogram Side Effects"). To check for violations of the rules, use the RESTRICT_REFERENCES pragma. This pragma asserts that a function does not read or write database tables or package variables. For more information about this pragma, see Oracle Database Advanced Application Developer's Guide.

By definition, autonomous routines never violate the rules read no database state (RNDS) and write no database state (WNDS).

The packaged function log_msg in Example 6-53 is autonomous. Therefore, when the query invokes the function, the function inserts a message into database table debug_output without violating the rule write no database state.

Example 6-53 Invoking an Autonomous Function

DROP TABLE debug_output;
CREATE TABLE debug_output (message VARCHAR2(200));
 
CREATE OR REPLACE PACKAGE debugging AS
  FUNCTION log_msg (msg VARCHAR2) RETURN VARCHAR2;
  PRAGMA RESTRICT_REFERENCES(log_msg, WNDS, RNDS);
END debugging;
/
CREATE OR REPLACE PACKAGE BODY debugging AS
  FUNCTION log_msg (msg VARCHAR2) RETURN VARCHAR2 IS
    PRAGMA AUTONOMOUS_TRANSACTION;
  BEGIN
    /* This insert does not violate the constraint WNDS
       because this is an autonomous routine */
  
  INSERT INTO debug_output (message)
  VALUES (msg);
  
  COMMIT;
  RETURN msg;
  END;
END debugging;
/
-- Invoke packaged function from 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;
/