Working with Cursors

The central purpose of the Oracle PL/SQL language is to make it as easy and efficient as possible to query and change the contents of tables in a database. You must, of course, use the SQL language to access tables, and each time you do so, you use a cursor to get the job done. A cursor is a pointer to a private SQL area that stores information about the processing of a SELECT or data manipulation language (DML) statement (INSERT, UPDATE, DELETE, or MERGE). Cursor management of DML statements is handled by Oracle Database, but PL/SQL offers several ways to define and manipulate cursors to execute SELECT statements. This article focuses on the most-common ways programmers execute SELECT statements in PL/SQL, namely 

• Using the SELECT-INTO statement
• Fetching from an explicit cursor
• Using a cursor FOR loop
• Using EXECUTE IMMEDIATE INTO for dynamic queries
• Using cursor variables 

At the end of the article, I offer some quick tips to help you figure out which of these techniques you should use for different scenarios.

SELECT-INTO

SELECT-INTO offers the fastest and simplest way to fetch a single row from a SELECT statement. The syntax of this statement is

SELECT select_list INTO variable_list FROM remainder_of_query;  

where remainder_of_query contains the list of tables or views, the WHERE clause, and other clauses of the query. The number and types of elements in the variable_list must match those of the select_list.

If the SELECT statement identifies more than one row to be fetched, Oracle Database will raise the TOO_MANY_ROWS exception. If the statement doesn’t identify any rows to be fetched, Oracle Database will raise the NO_DATA_FOUND exception.

Here are some examples of using SELECT-INTO:

Get the last name for a specific employee ID (the primary key in the employees table): 

DECLARE
  l_last_name  employees.last_name%TYPE;
BEGIN
  SELECT last_name
    INTO l_last_name
    FROM employees
   WHERE employee_id = 138;

  DBMS_OUTPUT.put_line (
     l_last_name);
END; 

If there is a row in the employees table with ID 138, this block will display the last name of that employee. If there is no such row, the block will fail with an unhandled NO_DATA_FOUND exception. Assuming that a unique index is defined on the employee_id column, this block will never raise the TOO_MANY_ROWS exception.

Fetch an entire row from the employees table for a specific employee ID: 
DECLARE
   l_employee   employees%ROWTYPE;
BEGIN
   SELECT *
     INTO l_employee
     FROM employees
    WHERE employee_id = 138;

   DBMS_OUTPUT.put_line (l_employee.last_name);
END; 



Again, if an employee exists for that ID, the last name will be displayed. In this case, I declare a record based on the employees table and fetch all columns (with a SELECT *) into that record for the specified row.

Fetch columns from different tables: 
DECLARE
  l_last_name         
     employees.last_name%TYPE;
  l_department_name   
     departments.department_name%TYPE;
BEGIN
  SELECT last_name, department_name
    INTO l_last_name, l_department_name
    FROM employees e, departments d
   WHERE e.department_id=d.department_id
         AND e.employee_id=138;

  DBMS_OUTPUT.put_line ( l_last_name || ' in ' || l_department_name);
END;



In this case, I need more than one column value but not all the column values in either or both of the tables. So I declare two variables and fetch the two column values into those variables.

What happens if the list of variables in the INTO clause does not match the SELECT list of the query? You will see one of the error messages shown in Table 1.

ORA-00947: not enough values
The INTO list contains fewer variables than the SELECT list.
ORA-00913: too many values
The INTO list contains more variables than the SELECT list.
ORA-06502: PL/SQL: numeric or value error
The number of variables in the INTO and SELECT lists matches, but the datatypes do not match and Oracle Database was unable to convert implicitly from one type to the other.

Table 1: Possible error messages if INTO and SELECT lists do not match

Fetching from Explicit Cursors

A SELECT-INTO is also referred to as an implicit query, because Oracle Database implicitly opens a cursor for the SELECT statement, fetches the row, and then closes the cursor when it finishes doing that (or when an exception is raised).

You can, alternatively, explicitly declare a cursor and then perform the open, fetch, and close operations yourself.

Suppose I need to write a block that fetches employees in ascending salary order and gives them a bonus from a total pool of funds by calling the assign_bonus procedure, whose header is 

PROCEDURE assign_bonus (

employee_id_in IN     

employees.employee_id%TYPE,

bonus_pool_io  IN OUT INTEGER)


Each time assign_bonus is called, the procedure subtracts the bonus given from the total and returns that reduced total. When that bonus pool is exhausted, it stops fetching and commits all changes.

Listing 1 includes a block that uses an explicit cursor to implement this logic, and it describes the operations in the block at specified line numbers.

Code Listing 1: Block and description of explicit cursor implementation 
 1  DECLARE
 2     l_total       INTEGER := 10000;
 3
 4     CURSOR employee_id_cur
 5     IS
 6          SELECT employee_id
 7            FROM plch_employees
 8        ORDER BY salary ASC;
 9
10     l_employee_id   employee_id_cur%ROWTYPE;
11  BEGIN
12     OPEN employee_id_cur;
13
14     LOOP
15        FETCH employee_id_cur INTO l_employee_id;
16        EXIT WHEN employee_id_cur%NOTFOUND;
17
18        assign_bonus (l_employee_id, l_total);
19        EXIT WHEN l_total <= 0;
20     END LOOP;
21
22     CLOSE employees_cur;
23  END; 

Line(s) Description
4–8
The explicit cursor declaration. Move the query from the executable section (where the SELECT-INTO must reside), and use the CURSOR keyword to declare (give a name to) that query.
10
Declare a record based on the row of data returned by the query. In this case, there is just a single column value, so you could just as easily have declared l_employee_id as employees.employee_id%TYPE. But whenever you use an explicit cursor, it is best to declare a record by using %ROWTYPE, so that if the SELECT list of the cursor ever changes, that variable will change with it.
12
Open the cursor, so that rows can now be fetched from the query.
Note: This is a step Oracle Database performs with the SELECT-INTO statement.
14
Start a loop to fetch rows.
15
Fetch the next row for the cursor, and deposit that row’s information into the record specified in the INTO clause. 
Note: This is a step Oracle Database performs with the SELECT-INTO statement.
16
If the FETCH does not find a row, exit the loop.
18
Call assign_bonus, which applies the bonus and also decrements the value of the l_total variable by that bonus amount.
19
Exit the loop if all the bonus funds have been exhausted.
22
Close the cursor. 
Note: This is a step Oracle Database performs with the SELECT-INTO statement.














































Here are some things to keep in mind when working with explicit cursors:


If the query does not identify any rows, Oracle Database will not raise NO_DATA_FOUND. Instead, the cursor_name%NOTFOUND attribute will return TRUE.


Your query can return more than one row, and Oracle Database will not raise TOO_MANY_ROWS.


When you declare a cursor in a package (that is, not inside a subprogram of the package) and the cursor is opened, it will stay open until you explicitly close it or your session is terminated.


When the cursor is declared in a declaration section (and not in a package), Oracle Database will also automatically close it when the block in which it is declared terminates. It is still, however, a good idea to explicitly close the cursor yourself. If the cursor is moved to a package, you will have the now necessary CLOSE already in place. And if it is local, then including a CLOSE statement will also show other developers and your manager that you are paying attention. 



Using the Cursor FOR Loop

The cursor FOR loop is an elegant and natural extension of the numeric FOR loop in PL/SQL. With a numeric FOR loop, the body of the loop executes once for every integer value between the low and high values specified in the range. With a cursor FOR loop, the body of the loop is executed for each row returned by the query.

The following block uses a cursor FOR loop to display the last names of all employees in department 10: 
BEGIN
   FOR employee_rec IN (
        SELECT *
          FROM employees
         WHERE department_id = 10)
   LOOP
      DBMS_OUTPUT.put_line (
         employee_rec.last_name);
   END LOOP;
END;
 


You can also use a cursor FOR loop with an explicitly declared cursor:

DECLARE
   CURSOR employees_in_10_cur
   IS
      SELECT *
        FROM employees
       WHERE department_id = 10;
BEGIN
   FOR employee_rec 
   IN employees_in_10_cur
   LOOP
      DBMS_OUTPUT.put_line (
         employee_rec.last_name);
   END LOOP;
END;

The nice thing about the cursor FOR loop is that Oracle Database opens the cursor, declares a record by using %ROWTYPE against the cursor, fetches each row into a record, and then closes the loop when all the rows have been fetched (or the loop terminates for any other reason).

Best of all, Oracle Database automatically optimizes cursor FOR loops to perform similarly to BULK COLLECT queries. So even though your code looks as if you are fetching one row at a time, Oracle Database will actually fetch 100 rows at a time—and enable you to work with each row individually.

Dynamic Queries with EXECUTE IMMEDIATE

Dynamic SQL means that at the time you write (and then compile) your code, you do not have all the information you need for parsing a SQL statement. Instead, you must wait for runtime to complete the SQL statement and then parse and execute it.

Oracle Database makes it easy to execute SQL statements (and PL/SQL blocks) dynamically with the EXECUTE IMMEDIATE statement. And querying data is the easiest dynamic SQL operation of all!

You can fetch a single row or multiple rows. Here is a generic function that fetches the value of a numeric column in any table, for the specified WHERE clause:

CREATE OR REPLACE FUNCTION 
single_number_value (
   table_in    IN VARCHAR2,
   column_in   IN VARCHAR2,
   where_in    IN VARCHAR2)
   RETURN NUMBER
IS
   l_return   NUMBER;
BEGIN
   EXECUTE IMMEDIATE
         'SELECT '
      || column_in
      || ' FROM '
      || table_in
      || ' WHERE '
      || where_in
      INTO l_return;
   RETURN l_return;
END; 

As you can see, instead of SELECT-INTO, I use EXECUTE IMMEDIATE-INTO and construct the SELECT statement from the arguments passed to the function. Here’s an example of calling the function:

BEGIN
   DBMS_OUTPUT.put_line (
      single_number_value (
                'employees',
                'salary',
                'employee_id=138'));
END; 

As with SELECT-INTO, EXECUTE IMMEDIATE-INTO will raise NO_DATA_FOUND if no rows are found and TOO_MANY_ROWS if more than one row is found.

You can also use EXECUTE IMMEDIATE to fetch multiple rows of data, which means that you will populate a collection, so you must use BULK COLLECT. The following is a procedure that will display the values of any numeric column for all rows specified in the WHERE clause: 

CREATE OR REPLACE PROCEDURE 
show_number_values (
   table_in    IN VARCHAR2,
   column_in   IN VARCHAR2,
   where_in    IN VARCHAR2)
IS
   TYPE values_t IS TABLE OF NUMBER;

   l_values   values_t;
BEGIN
   EXECUTE IMMEDIATE
         'SELECT '
      || column_in
      || ' FROM '
      || table_in
      || ' WHERE '
      || where_in
      BULK COLLECT INTO l_values;

   FOR indx IN 1 .. l_values.COUNT
   LOOP
      DBMS_OUTPUT.put_line(l_values (indx));
   END LOOP;
END; 

And when I call the procedure for the standard employees table 
BEGIN
   show_number_values (
      'employees',
      'salary',
      'department_id = 10 
       order by salary desc');
END; 

I see the following two rows of output: 
4400
3200

A general note of caution regarding dynamic SQL and the preceding examples in particular: whenever you concatenate text to execute a dynamically executed statement, you run the risk ofSQL injection. This occurs when a malicious user “injects,” or inserts into the statement, code that changes the behavior of that SQL statement.

Cursor Variables

A cursor variable is, as you might guess from its name, a variable that points to a cursor or a result set. Unlike with an explicit cursor, you can pass a cursor variable as an argument to a procedure or a function. There are several excellent use cases for cursor variables, including the following: 


Pass a cursor variable back to the host environment that called the program unit—the result set can be “consumed” for display or other processing.


Construct a result set inside a function, and return a cursor variable to that set. This is especially handy when you need to use PL/SQL, in addition to SQL, to build the result set.


Pass a cursor variable to a pipelined table function—a powerful but quite advanced optimization technique. A full explanation of cursor variables, including the differences between strong and weak REF CURSOR types, is beyond the scope of this article.


Instead, I will show the basic syntax for working with cursor variables and identify situations in which you might consider using this feature.



Cursor variables can be used with either embedded (static) or dynamic SQL. Listing 2 includes the names_for function, which returns a cursor variable that fetches either employee or department names, depending on the argument passed to the function.

Code Listing 2: Block and description of the names_for function, which returns a cursor variable 

1  CREATE OR REPLACE FUNCTION names_for (
 2        name_type_in IN VARCHAR2)
 3     RETURN SYS_REFCURSOR
 4  IS
 5     l_return   SYS_REFCURSOR;
 6  BEGIN
 7     CASE name_type_in
 8        WHEN 'EMP'
 9        THEN
10           OPEN l_return FOR
11                SELECT last_name
12                  FROM employees
13              ORDER BY employee_id;
14        WHEN 'DEPT'
15        THEN
16           OPEN l_return FOR
17                SELECT department_name
18                  FROM departments
19              ORDER BY department_id;
20     END CASE;
21
22     RETURN l_return;
23  END names_for;



Line(s) Description

3
Return a piece of data whose type is SYS_REFCURSOR.
5
Declare a cursor variable to be returned by the function.
7
Use a CASE statement driven by the value of name_type_in to determine which query should be opened.
10–13
Open a cursor variable for a query from the employees table.
16–19
Open a cursor variable for a query from the departments table.


Here is a block that uses the names_for function to display all the names in the departments table: 

DECLARE

l_names   SYS_REFCURSOR;

l_name    VARCHAR2 (32767);
BEGIN

l_names := names_for('DEPT');
LOOP
FETCH l_names INTO l_name;

DBMS_OUTPUT.put_line (l_name);

EXIT WHEN l_names%NOTFOUND;    
   END LOOP;
   CLOSE l_names;


END;




As you can see, all the information about the query being opened is “hidden” behind the function header. You simply ask to get the “names for” a given table. The function picks the appropriate SELECT statement, opens the cursor variable for that statement, and then returns the variable pointing to that result set.

Once the cursor variable has been opened and passed back to the block, I use the same code with a cursor variable that I would use with an explicit cursor: 



FETCH from the cursor (variable) INTO one or more variables (I can even FETCH-BULK COLLECT INTO with a cursor variable, populating a collection with multiple rows)


Check the %NOTFOUND attribute of the cursor variable to see if I am done fetching all rows


CLOSE the cursor variable when done 




The OPEN-FOR statement is unique to cursor variables and enables me to specify at runtime, without having to switch to dynamic SQL, which data set will be fetched through the cursor variable.

Nevertheless, you can use OPEN-FOR with a dynamic SELECT statement. Here is a very simple example: 
CREATE OR REPLACE FUNCTION 
numbers_from (
      query_in IN VARCHAR2)
   RETURN SYS_REFCURSOR
IS
   l_return   SYS_REFCURSOR;
BEGIN
   OPEN l_return FOR query_in;
   RETURN l_return;
END numbers_from; 

And here is a block—virtually identical to the one that calls names_for, above—that displays all the salaries for employees in department 10: 
DECLARE
  l_salaries   SYS_REFCURSOR;
  l_salary     NUMBER;
BEGIN
  l_salaries :=
    numbers_from (
      'select salary 
        from employees 
       where department_id = 10');
  LOOP
    FETCH l_salaries INTO l_salary;
    EXIT WHEN l_salaries%NOTFOUND;
    DBMS_OUTPUT.put_line (l_salary);
  END LOOP;
  CLOSE l_salaries;
END;


Choosing the Right Way to Query

This article has shown that the PL/SQL language offers many different ways, ranging from the simplest SELECT-INTO implicit query to the much more complicated cursor variable, to use cursors to fetch data from relational tables into local variables.

Here are some guidelines to help you decide which technique to use: 


When fetching a single row, use SELECT-INTO or EXECUTE IMMEDIATE-INTO (if your query is dynamic). Do not use an explicit cursor or a cursor FOR loop.


When fetching all the rows from a query, use a cursor FOR loop unless the body of the loop executes one or more DML statements (INSERT, UPDATE, DELETE, or MERGE). In such a case, you will want to switch to BULK COLLECT and FORALL.


Use an explicit cursor when you need to fetch with BULK COLLECT, but limit the number of rows returned with each fetch.


Use an explicit cursor when you are fetching multiple rows but might conditionally exit before all rows are fetched.


Use a cursor variable when the query you are fetching from varies at runtime (but isn’t necessarily dynamic) and especially when you need to pass a result back to a non-PL/SQL host environment.


Use EXECUTE IMMEDIATE to query data only when you cannot fully construct the SELECT statement while writing your code. 

Cursor Variable with Dynamic SQL

OPEN-FOR-USING Statement
The OPEN-FOR-USING statement associates a cursor variable with a query, executes the query, identifies the result set, positions the cursor before the first row in the result set, then zeroes the rows-processed count kept by %ROWCOUNT.

Because this statement can use bind variables to make the SQL processing more efficient, use the OPEN-FOR-USING statement when building a query where you know the WHERE clauses in advance. Use the OPEN-FOR statement when you need the flexibility to process a dynamic query with an unknown number of WHERE clauses.

Syntax:

cursor_variable_name

A weakly typed cursor variable (one without a return type) previously declared within the current scope.

bind_argument

An expression whose value is passed to the dynamic SELECT statement.

dynamic_string

A string literal, variable, or expression that represents a multi-row SELECT statement.

host_cursor_variable_name

A cursor variable declared in a PL/SQL host environment and passed to PL/SQL as a bind variable. The datatype of the host cursor variable is compatible with the return type of any PL/SQL cursor variable. Host variables must be prefixed with a colon.

USING ...

This optional clause specifies a list of bind arguments. At run time, bind arguments in the USING clause replace corresponding placeholders in the dynamic SELECT statement.

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

The dynamic string can contain any multi-row SELECT statement (without the terminator). The string can also contain placeholders for bind arguments. However, you cannot use bind arguments to pass the names of schema objects to a dynamic SQL statement.

Every placeholder in the dynamic string must be associated with a bind argument in the USING clause. Numeric, character, and string literals are allowed in the USING clause, but Boolean literals (TRUE, FALSE, NULL) are not.

Any bind arguments in the query are evaluated only when the cursor variable is opened. To fetch from the cursor using different bind values, you must reopen the cursor variable with the bind arguments set to their new values.

Dynamic SQL supports all the SQL datatypes. For example, bind arguments can be collections, LOBs, instances of an object type, and refs. As a rule, dynamic SQL does not support PL/SQL-specific types. For instance, bind arguments cannot be Booleans or index-by tables.

Example
The following example declares a cursor variable, then associates it with a dynamic SELECT statement:
DECLARE
TYPE EmpCurTyp IS REF CURSOR; -- define weak REF CURSOR type
emp_cv EmpCurTyp; -- declare cursor variable
my_ename VARCHAR2(15);
my_sal NUMBER := 1000;
BEGIN
OPEN emp_cv FOR -- open cursor variable
'SELECT ename, sal FROM emp WHERE sal > :s' USING my_sal;
...
END;

Example  Dynamic SQL Fetching into a Record

As the following example shows, you can fetch rows from the result set of a dynamic multi-row query into a record:

DECLARE
TYPE EmpCurTyp IS REF CURSOR;
emp_cv EmpCurTyp;
emp_rec emp%ROWTYPE;
sql_stmt VARCHAR2(200);
my_job VARCHAR2(15) := 'CLERK';
BEGIN
sql_stmt := 'SELECT * FROM emp WHERE job = :j';
OPEN emp_cv FOR sql_stmt USING my_job;
LOOP
FETCH emp_cv INTO emp_rec;
EXIT WHEN emp_cv%NOTFOUND;
-- process record
END LOOP;
CLOSE emp_cv;
END;

/

Example Dynamic SQL with BULK COLLECT INTO Clause

You can bind define variables in a dynamic query using the BULK COLLECT INTO clause. As the following example shows, you can use that clause in a bulk FETCH or bulk EXECUTE IMMEDIATE statement:

DECLARE
TYPE EmpCurTyp IS REF CURSOR;
TYPE NumList IS TABLE OF NUMBER;
TYPE NameList IS TABLE OF VARCHAR2(15);
emp_cv EmpCurTyp;
empnos NumList;
enames NameList;
sals NumList;
BEGIN
OPEN emp_cv FOR 'SELECT empno, ename FROM emp';
FETCH emp_cv BULK COLLECT INTO empnos, enames;
CLOSE emp_cv;
EXECUTE IMMEDIATE 'SELECT sal FROM emp'
BULK COLLECT INTO sals;
END;

/

Using Cursor Variables (REF CURSORs)

Like a cursor, a cursor variable points to the current row in the result set of a multi-row query. A cursor variable is more flexible because it is not tied to a specific query. You can open a cursor variable for any query that returns the right set of columns. 

You pass a cursor variable as a parameter to local and stored 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 multi-language applications, where a PL/SQL subprogram might return a result set to a subprogram written in a different language.

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 and Oracle Reports, 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 procedure calls.

What Are Cursor Variables (REF CURSORs)?

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

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

Why Use Cursor Variables?

You use cursor variables to pass query result sets between PL/SQL stored subprograms and various clients. PL/SQL and its clients share a pointer to the query work area in which the result set is stored. For example, an OCI client, Oracle Forms application, and Oracle database server can all refer to the same work area.

A query work area remains accessible as long as any cursor variable points to it, as you pass the value of a cursor variable from one scope to another. For example, if you pass a host cursor variable to a PL/SQL block embedded in a Pro*C program, the work area to which the cursor variable points remains accessible after the block completes.

If you have a PL/SQL engine on the client side, calls from client to server impose no restrictions. For example, you can declare a cursor variable on the client side, open and fetch from it on the server side, then continue to fetch from it back on the client side. You can also reduce network traffic by having a PL/SQL block open or close several

host cursor variables in a single round trip.

Declaring REF CURSOR Types and Cursor Variables

To create cursor variables, you define a REF CURSOR type, then declare cursor variables of that type. You can define REF CURSOR types in any PL/SQL block, ubprogram, or package. In the following example, you declare a REF CURSOR type that represents a result set from the DEPARTMENTS table:

DECLARE

TYPE DeptCurTyp IS REF CURSOR RETURN departments%ROWTYPE;

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

Strong REF CURSOR types are less error prone because the PL/SQL compiler lets you associate a strongly typed cursor variable only with queries that return the right set of

columns. Weak REF CURSOR types are more flexible because the compiler lets you associate a weakly typed cursor variable with any query.

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

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

DECLARE

TYPE EmpCurTyp IS REF CURSOR RETURN emp%ROWTYPE; -- strong

TYPE GenericCurTyp IS REF CURSOR; -- weak

cursor1 EmpCurTyp;

cursor2 GenericCurTyp;

my_cursor SYS_REFCURSOR; -- didn't need to declare a new type above

The following example declares the cursor variable dept_cv:

DECLARE

TYPE DeptCurTyp IS REF CURSOR RETURN dept%ROWTYPE;

dept_cv DeptCurTyp; -- declare cursor variable

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

Example 1 Cursor Variable Returning %ROWTYPE

In the RETURN clause of a REF CURSOR type definition, you can use %ROWTYPE to refer

to a strongly typed cursor variable:

DECLARE

TYPE TmpCurTyp IS REF CURSOR RETURN employees%ROWTYPE;

tmp_cv TmpCurTyp; -- declare cursor variable

TYPE EmpCurTyp IS REF CURSOR RETURN tmp_cv%ROWTYPE;

emp_cv EmpCurTyp; -- declare cursor variable

BEGIN

NULL;

END;

/

Example 2 Cursor Variable Returning %TYPE

You can also use %TYPE to provide the datatype of a record variable:

DECLARE

dept_rec departments%ROWTYPE; -- declare record variable

TYPE DeptCurTyp IS REF CURSOR RETURN dept_rec%TYPE;

dept_cv DeptCurTyp; -- declare cursor variable

BEGIN

NULL;

END;

/

Example 3 Cursor Variable Returning Record Type

This example specifies a user-defined RECORD type in the RETURN clause:

DECLARE

TYPE EmpRecTyp IS RECORD (

employee_id NUMBER,

last_name VARCHAR2(30),

salary NUMBER(7,2));

TYPE EmpCurTyp IS REF CURSOR RETURN EmpRecTyp;

emp_cv EmpCurTyp; -- declare cursor variable

BEGIN

NULL;

END;

/

Passing Cursor Variables As Parameters

You can declare cursor variables as the formal parameters of functions and procedures.

The following example defines a REF CURSOR type, then declares a cursor variable of that type as a formal parameter:

DECLARE

TYPE EmpCurTyp IS REF CURSOR RETURN employees%ROWTYPE;

emp EmpCurTyp;

-- Once we have a result set, we can process all the rows

-- inside a single procedure rather than calling a procedure

-- for each row.

PROCEDURE process_emp_cv (emp_cv IN EmpCurTyp) IS

person employees%ROWTYPE;

BEGIN

dbms_output.put_line('-----');

dbms_output.put_line('Here are the names from the result set:');

LOOP

FETCH emp_cv INTO person;

EXIT WHEN emp_cv%NOTFOUND;

dbms_output.put_line('Name = ' || person.first_name ||

' ' || person.last_name);

END LOOP;

END;

/

BEGIN

-- First find 10 arbitrary employees.

OPEN emp FOR SELECT * FROM employees WHERE ROWNUM < 11;

process_emp_cv(emp);

CLOSE emp;

-- Then find employees matching a condition.

OPEN emp FOR SELECT * FROM employees WHERE last_name LIKE 'R%';

process_emp_cv(emp);

CLOSE emp;

END;

/

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

You use three statements to control a cursor variable: OPEN-FOR, FETCH, and CLOSE.

First, you OPEN a cursor variable FOR a multi-row query. Then, you FETCH rows from

the result set. When all the rows are processed, you CLOSE the cursor variable.

Opening a Cursor Variable

The OPEN-FOR statement associates a cursor variable with a multi-row query, executes

the query, and identifies the result set.

OPEN {cursor_variable | :host_cursor_variable} FOR

{ select_statement

| dynamic_string [USING bind_argument[, bind_argument]...] };

The cursor variable can be declared directly in PL/SQL, or in a PL/SQL host environment such as an OCI program.

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.

Note: This section discusses the static SQL case, in which select_statement is

used. 

Unlike cursors, cursor variables take no parameters. Instead, you can pass whole queries (not just parameters) to a cursor variable. The query can reference host variables and PL/SQL variables, parameters, and functions.

The example below opens a cursor variable. Notice that you can apply cursor attributes (%FOUND, %NOTFOUND, %ISOPEN, and %ROWCOUNT) to a cursor variable.

DECLARE

TYPE EmpCurTyp IS REF CURSOR RETURN employees%ROWTYPE;

emp_cv EmpCurTyp;

BEGIN

IF NOT emp_cv%ISOPEN THEN

/* Open cursor variable. */

OPEN emp_cv FOR SELECT * FROM employees;

END IF;

CLOSE emp_cv;

END;

/

Other OPEN-FOR statements can open the same cursor variable for different queries. You need not close a cursor variable before reopening it. (Recall that consecutive OPENs of a static cursor raise the predefined exception CURSOR_ALREADY_OPEN.)

When you reopen a cursor variable for a different query, the previous query is lost.

Example 4 Stored Procedure to Open a Ref Cursor

Typically, you open a cursor variable by passing it to a stored procedure that declares an IN OUT parameter that is a cursor variable. For example, the following procedure opens a cursor variable:

CREATE PACKAGE emp_data AS

TYPE EmpCurTyp IS REF CURSOR RETURN employees%ROWTYPE;

PROCEDURE open_emp_cv (emp_cv IN OUT EmpCurTyp);

END emp_data;

/

CREATE PACKAGE BODY emp_data AS

PROCEDURE open_emp_cv (emp_cv IN OUT EmpCurTyp) IS

BEGIN

OPEN emp_cv FOR SELECT * FROM employees;

END open_emp_cv;

END emp_data;

/

DROP PACKAGE emp_data;

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

Example 4 Stored Procedure to Open Ref Cursors with Different Queries

To centralize data retrieval, you can group type-compatible queries in a stored procedure. In the example below, the packaged procedure declares a selector as one of its formal parameters. When called, the procedure opens the cursor variable emp_cv for the chosen query.

CREATE PACKAGE emp_data AS

TYPE EmpCurTyp IS REF CURSOR RETURN emp%ROWTYPE;

PROCEDURE open_emp_cv (emp_cv IN OUT EmpCurTyp, choice INT);

END emp_data;

CREATE PACKAGE BODY emp_data AS

PROCEDURE open_emp_cv (emp_cv IN OUT EmpCurTyp, choice INT) IS

BEGIN

IF choice = 1 THEN

OPEN emp_cv FOR SELECT * FROM emp WHERE comm IS NOT NULL;

ELSIF choice = 2 THEN

OPEN emp_cv FOR SELECT * FROM emp WHERE sal > 2500;

ELSIF choice = 3 THEN

OPEN emp_cv FOR SELECT * FROM emp WHERE deptno = 20;

END IF;

END;

END emp_data;

Example 5 Cursor Variable with Different Return Types

For more flexibility, a stored procedure can execute queries with different return types:

CREATE 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 countries;

END IF;

END;

END admin_data;

/

Fetching from a Cursor Variable

The FETCH statement retrieves rows from the result set of a multi-row query. It works the same with cursor variables as with explicit cursors.

Example 6 Fetching from a Cursor Variable into a Record

The following example fetches rows one at a time from a cursor variable into a 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 salary < 3000;

LOOP

/* Fetch from cursor variable. */

FETCH emp_cv INTO emp_rec;

EXIT WHEN emp_cv%NOTFOUND; -- exit when last row is fetched

-- process data record

dbms_output.put_line('Name = ' || emp_rec.first_name || ' ' ||

emp_rec.last_name);

END LOOP;

CLOSE emp_cv;

END;

/

Example 7 Fetching from a Cursor Variable into Collections

Using the BULK COLLECT clause, you can bulk fetch rows from a cursor variable into

one or more 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 salary < 3000;

FETCH emp_cv BULK COLLECT INTO names, sals;

CLOSE emp_cv;

-- Now loop through the NAMES and SALS collections.

FOR i IN names.FIRST .. names.LAST

LOOP

dbms_output.put_line('Name = ' || names(i) || ', salary = ' ||

sals(i));

END LOOP;

END;

/

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 error and execute the FETCH statement using a different (compatible)

INTO clause, no rows are lost.

When you declare a cursor variable as the formal parameter of a subprogram that fetches from the cursor variable, you must specify the IN or IN OUT mode. If the subprogram also opens the cursor variable, you must specify the IN OUT mode. If you try to fetch from a closed or never-opened cursor variable, PL/SQL raises the predefined exception INVALID_CURSOR.

Closing a Cursor Variable

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.

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

When passing host cursor variables to PL/SQL, you can reduce network traffic by grouping OPEN-FOR statements. For example, the following PL/SQL block opens multiple cursor variables in a single round trip:

/* anonymous PL/SQL block in host environment */

BEGIN

OPEN :emp_cv FOR SELECT * FROM employees;

OPEN :dept_cv FOR SELECT * FROM departments;

OPEN :loc_cv FOR SELECT * FROM locations;

END;

This technique might be useful in Oracle Forms, for instance, when you want to populate a multi-block form.

When you pass host cursor variables to a PL/SQL block for opening, the query work areas to which they point remain accessible after the block completes, so your OCI or Pro*C program can use these work areas for ordinary cursor operations. In the following example, you open several such work areas in a single round trip:

BEGIN

OPEN :c1 FOR SELECT 1 FROM dual;

OPEN :c2 FOR SELECT 1 FROM dual;

OPEN :c3 FOR SELECT 1 FROM dual;

END;

/

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

BEGIN

CLOSE :c1;

CLOSE :c2;

CLOSE :c3;

END;

Avoiding Errors with Cursor Variables

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

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

■ OPEN the cursor variable FOR the query.

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

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

Be careful when passing cursor variables as parameters. At run time, PL/SQL raises ROWTYPE_MISMATCH if the return types of the actual and formal parameters are

incompatible.

Restrictions on Cursor Variables

Currently, cursor variables are subject to the following restrictions:

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

CREATE PACKAGE emp_stuff AS

TYPE EmpCurTyp IS REF CURSOR RETURN emp%ROWTYPE;

emp_cv EmpCurTyp; -- not allowed

END emp_stuff;

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

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

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

■ You cannot assign nulls to a cursor variable.

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

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

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