Day 12

Using Cursors for
Complex Processing

by Tom Luers

Today's lesson discusses using parameters to pass information into a cursor as well as using cursors as variables to other PL/SQL constructs such as procedures and functions. Using parameters to pass information provides the flexibility to reuse and modularize your cursor routines. This chapter covers using parameters with cursors as well as several other more advanced cursor topics.

Passing Parameters to Cursors

In PL/SQL, you can pass parameters into cursors just as you would for functions and procedures. For example, you can establish the value of a parameter depending on your logic and then pass this parameter into the cursor for processing. Cursors cannot pass parameters out of the cursors. These parameters

allow your code to become more modular and maintainable. This lends itself to increased usability because you no longer have to hard-code values in the query statement.

Declaring Cursor Parameters

To declare cursor parameters, place the parameters in the cursor definition statement enclosed in parentheses. In Oracle, you can use as many parameters as you need.

The Syntax for Declaring a Cursor

CURSOR name (parameter_1 datatype, parameter_2 datatype...)
IS SELECT statement...

In this syntax, name is the name you assign to the cursor. parameter_1 and parameter_2 are the parameters that are passed into the cursor. The datatypes correspond to the parameters. Finally, SELECT statement is the statement that defines the cursor contents.

The following is an example of a cursor that is to receive two parameters. One of the parameters is used in the SELECT statement:

DECLARE
CURSOR emp_cur
(emp_nbr  number,  emp_name varchar2(32))
IS SELECT pay_rate FROM employee WHERE emp_id = emp_nbr;
                               -- parameter is used here

You can also initialize cursor parameters in the declaration statement. This is a very convenient method to pass default values to the cursor. Likewise, you can override these default values with different, explicitly defined values. The following example passes two different parameters to the cursor in a stock-purchasing scenario:

DECLARE
CURSOR stock_cur
(buy_price    number  default 23.50,
sell_price     number  default 38.33) IS SELECT ...

Using the preceding declaration, you can pass actual values to the two parameters in the OPEN cursor statement, thereby overriding the specified default values. The following example will cause the cursor to be declared with a buy_price of $24.25 and a sell_price of $44.67:

OPEN stock_cur (24.25, 44.67);

If you pass no values to the cursor in the OPEN statement, the default values take effect. Hence, the following two OPEN statements are equivalent:

OPEN stock_cur;

OPEN stock_cur (23.50,38.33);

Using Cursor Parameters

Carrying the stock-purchasing cursor example further, suppose that you want to see all the stocks that are in a particular category of stocks. Also, your desired category will change from time to time. The following declaration of the cursor would be helpful:

DECLARE
CURSOR stock_listing_cur  (stock_category  varchar2)  IS
SELECT stock_name, current_price from stocks
WHERE category = stock_category;

stock record  stock_listing_cur%rowtype;

This example allows you to specify any category of stock. You can use this cursor as shown in the next example. In an example like this, you can set the value of stock_type by using a substitution variable, Oracle Forms field, or other means:

DECLARE
CURSOR stock_listing_cur  (stock_category  varchar2)  IS
SELECT stock_name, current_price from stocks
WHERE category = stock_category;

stock record  stock_listing_cur%rowtype;

BEGIN
OPEN stock_listing_cur (:stock_type);    --use a bind variable set earlier

Scope of Cursor Parameters

Cursor parameters are visible only to that cursor. You cannot reference a cursor parameter outside the context of the cursor. If you try to refer to a cursor parameter outside the cursor, Oracle will return an error indicating that the variable is undefined.

Cursor Return Clause and Packages

As you learned on Day 9, "Using SQL: INSERT, SELECT, Advanced Declarations, and Tables," you can declare and use cursors inside packages. Oracle does allow you to place the cursor declaration in the package specification and the cursor body in the package body. This separation of declaration and body provides the developer some level of design and programming flexibility. The programmer can alter the cursor body without having to alter the specification. Or better yet, the programmer only needs to know what the cursor returns and not how it is accomplished. The following example illustrates the separation of the cursor body from its declaration.

The package specification is

CREATE PACKAGE  stock_purchase as
CURSOR stock_cur RETURN stock%rowtype;
END stock_purchase;

The package body is

CREATE PACKAGE BODY stock_purchase as
CURSOR stock_cur return stock%rowtype
SELECT stock_type, stock_name, curr_prics
FROM stocks
WHERE stock_type = :stock_type;

Note that in the preceding example, the return clause is used. The return clause is mandatory when you elect to separate the cursor components. This clause creates the bridge between the two just as if they appear together in a typical cursor construct.

Cursor Variables

As you recall from the previous chapter, the PL/SQL cursor is a named area in the database. The cursor variable, by definition, is a reference to that named area. A cursor variable is like a pointer that you would use in a programming language such as C. Cursor variables point to a query's work area in which the query's result set is stored. A cursor variable is also dynamic in nature because it is not tied to a specific query. Oracle retains this work area as long as a cursor pointer is pointing to it. You can use a cursor variable for any type-compatible query.

One of the most significant features of the cursor variable is that Oracle allows you to pass a cursor variable as an argument to a procedure or function call. The cursor variable cannot accept variables to itself.

Cursor variables can also be declared using programs such as Pro*C or OCI. After the cursor variable is declared there, you can pass it as a bind variable to your PL/SQL block. Likewise, these variables can be declared in other Oracle products such as Forms. Additionally, you can pass cursor variables back and forth between servers and applications through Oracle's remote procedure calls.

The Cursor Variable Declaration

To create a cursor variable, you must first create a referenced cursor type and then declare a cursor variable on that type.

The Syntax for Creating the Cursor Type

TYPE cursor_type_name IS REF CURSOR RETURN return_type;

In this syntax, REF stands for reference, cursor_type_name is the name of the type of cursor, and return_type is the data specification for the return cursor type. The return clause is optional.

Oracle makes a subtle distinction in cursor variables based upon whether or not a return clause is included in the cursor variable. If the return clause is present, then the cursor variable is known as a strong cursor variable. If the return clause is not present, the cursor variable is a weak cursor variable. Do not confuse the RETURN statement with the return clause, which specifies the datatype of the result value in a stored program.