PL/SQL User's Guide and Reference

Managing Cursors

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

Explicit Cursors

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

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

Declaring a Cursor

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

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

where parameter stands for the following syntax:

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

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

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

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

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

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

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

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

Opening a Cursor

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

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

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

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

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

any of the following statements opens the cursor:

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

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

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

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

Fetching with a Cursor

The FETCH statement retrieves the rows in the result set one at a time. After each fetch, the cursor advances to the next row in the result set. An example of the FETCH statement follows:

FETCH c1 INTO my_empno, my_ename, my_deptno;

For each column value returned by the query associated with the cursor, there must be a corresponding variable in the INTO list. Also, their datatypes must be compatible. Typically, you use the FETCH statement as follows:

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

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

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

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

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

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

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

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

Closing a Cursor

The CLOSE statement disables the cursor, and the result set becomes undefined. An example of the CLOSE statement follows:

CLOSE c1;

Once a cursor is closed, you can reopen it. Any other operation on a closed cursor raises the predefined exception INVALID_CURSOR.

Using Subqueries

In this context, a subquery is a query that appears in another query. When evaluated, the subquery provides a value or set of values to the query. Subqueries are most often used in the WHERE clause. For example, the following query returns employees not located in Chicago:

DECLARE
   CURSOR c1 IS SELECT empno, ename FROM emp
      WHERE deptno IN (SELECT deptno FROM dept 
         WHERE loc <> 'CHICAGO');
         FROM emp GROUP BY deptno) t2
      WHERE t1.deptno = t2.deptno AND "STAFF" => 5;

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

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

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

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

Implicit Cursors

Oracle implicitly opens a cursor to process each SQL statement not associated with an explicitly declared cursor. PL/SQL lets you refer to the most recent implicit cursor as the "SQL" cursor.

You cannot use the OPEN, FETCH, and CLOSE statements to control the SQL cursor. But, you can use cursor attributes to get information about the most recently executed SQL statement. See "Using Cursor Attributes" .