• OUT—The program can return values to the calling PL/SQL block.
• IN OUT—The program can read or write to the variable.

When you declare a cursor variable as a parameter of a procedure or function that fetches from the cursor variable, you must specify the IN or IN OUT mode. Likewise, if the procedure or function also opens the cursor variable, you must specify the IN OUT mode. Finally, the IN OUT mode must be used when you want the procedure or function to pass an open cursor back to the calling program.

The Current Row of Parameter and Cursors

The current row of a cursor always refers to the latest row retrieved by the FETCH statement. Oracle allows you to delete and update this current row. In order to delete or update the fetched row, the cursor must be declared using the FOR UPDATE clause and must be open. The following example illustrates the update of the current row for the employee cursor:

DECLARE
CURSOR emp_cur IS                            -- cursor declared for update
SELECT emp_name, pay_type, pay_rate
FROM employee
WHERE pay_rate > 5000.00
FOR UPDATE of pay_type;                             -- FOR UPDATE clause

emp_rec  emp_cur%rowtype;

BEGIN
OPEN emp_cur;                                        -- cursor is opened

LOOP
FETCH emp_cur INTO emp_rec;
UPDATE employee                                    -- updates current row
SET pay_type = `H'
WHERE CURRENT OF emp_cur;
EXIT WHEN emp_cur%notfound;
END LOOP;
END;

Cursor Scoping

The scope of a cursor variable follows these rules:

• The cursor variable is available to the local module in which it is declared.
• The cursor variable is global in nature when declared in a package.
• The cursor variable can exist outside its original scope, as shown in Figure 12.1.

Figure 12.1.
Cursor variable scope.

In the top of Figure 12.1, cursor variable B is assigned to cursor variable A. Cursor variable A is assigned to the cursor object 123. Both cursor variables are within the scope of cursor object 123. Cursor object 123 remains accessible to cursor variable B even when cursor variable A is deleted—as shown in the bottom half of Figure 12.1.

Remember that any variable, cursor or otherwise, is valid only in the scope of the loop. You cannot make any external reference to the cursor variable declared in a loop because it does not exist. Any cursor variable declared in an inner PL/SQL block is not accessible to the outer PL/SQL block. Likewise, you cannot refer to a cursor outside the declaring block unless the cursor is declared in a package specification.

Cursor Alias

As shown in Figure 12.1, you can have two cursor variables assigned to a single cursor object. Each of the variables is an alias to the cursor object. They both share the result set of the cursor object 123 query. Anything that one alias, or cursor variable, does that affects the cursor object will be seen immediately in the other cursor variable. This can be easily illustrated in the following cursor pseudocode:

PROCEDURE obtain_stock_quotes (stock_cv_1  in out  stock_cur_type,
                                        stock_cv_2 in out stock_cur_type);
stock_rec    stocks%rowtype;

BEGIN
OPEN stock_cv_1 FOR SELECT * FROM stocks;      -- open the cursor

stock_cv_2 := stock_cv_1;                    -- cursor variable assignment
FETCH stock_cv_1 INTO stock_rec;         -- fetch first record from cursor
FETCH stock_cv_2 INTO stock_rec;        -- fetch second record from cursor
FETCH stock_cv_2 INTO stock_rec;         -- fetch third record from cursor

CLOSE stock_cv_1;                        -- closes cursor for both aliases

Because the cursor is closed at this point, you cannot fetch from the cursor using either of the stock_cv_1 or stock_cv_2 variables. If you do attempt a fetch at this point, an error will occur stating that the cursor is closed.

Summary

This chapter presents you with several more applications and uses for cursors, including the concept of passing parameters into the cursor declaration. Cursor parameters allow your code to become more modular and maintainable. Likewise, you learned about declaring a cursor in a package specification, whereas the body of the cursors is in the cursor body. This gives the programmer an opportunity to know only the input and output return values of a cursor and not necessarily all the mechanics of exactly what the cursor is doing. The chapter also discusses the cursor variable. This cursor variable is like a pointer to a cursor. It is not a cursor itself but merely points to the same result set that the cursor created.

Q&A

Q Why should I use default values when I declare a cursor parameter?

A Defining default values for a cursor parameter gives the programmer two advantages. First, if no value is explicitly defined for the parameter, you know that processing will continue with a value you have set previously. Second, you can override or change the value of a parameter by passing the cursor a replacement value for the default value.

Q What is the purpose of declaring a cursor in the package specification and not in the package body?

A This makes programming cleaner in that the programmer will not have to handle the specification but only the cursor body. You can change the cursor body without having to alter the specification.

Q Cursor variables provide what advantages to the programmer?

A Included in the many advantages of cursor variables is the opportunity Oracle provides in that you can pass cursor variables as parameters to procedures and functions. Likewise, you can pass cursor variables from a host environment through Pro*C (or similar program) to your PL/SQL block.

Workshop

Use the following workshop to test your comprehension of this chapter and put what you've learned into practice. You'll find the answers to the quiz and exercise in Appendix A, "Answers."

Quiz

1. Name the different cursor variable parameter modes and their purposes.
2. What is the scope of a cursor parameter?

Exercise

Write a brief package declaration block and the beginning portion of the package body where you declare a cursor variable, and then use this variable in a procedure to open a cursor with a query.