Figure 14-2. Referencing a cursor variable across two programs

The code you write to take advantage of cursor variables is very similar to that for explicit cursors. The following example declares a cursor type (called a REF CURSOR type) for the company table, then opens, fetches from, and closes the cursor:

DECLARE /* Create the cursor type. */ TYPE company_curtype IS REF CURSOR RETURN company%ROWTYPE; /* Declare a cursor variable of that type. */ company_curvar company_curtype; /* Declare a record with same structure as cursor variable. */ company_rec company%ROWTYPE;BEGIN /* Open the cursor variable, associating with it a SQL statement. */ OPEN company_curvar FOR SELECT * FROM company; /* Fetch from the cursor variable. */ FETCH company_curvar INTO company_rec; /* Close the cursor object associated with variable. */ CLOSE company_curvar;END;

That looks an awful lot like explicit cursor operations, except for the following:

· The REF CURSOR type declaration

· The OPEN FOR syntax that specified the query at the time of the open

Although the syntax is similar, the very fact that the cursor variable is a variable opens up many new opportunities in your programs. These are explored in the following sections.

14.6.1 Why Cursor Variables?

Cursor variables allow you to do the following:

· Associate a cursor variable with different queries at different times in your program execution. In other words, a single cursor variable can be used to fetch from different result sets.

· Pass a cursor variable as an argument to aprocedure or function. You can, in essence, share the results of a cursor by passing the reference to that result set.

· Employ the full functionality of static PL/SQL cursors for cursor variables. You can OPEN, CLOSE, and FETCH with cursor variables within your PL/SQL programs. You can also reference the standardcursor attributes—%ISOPEN, %FOUND, %NOTFOUND, and %ROWCOUNT—for cursor variables.

· Assign the contents of one cursor (and its result set) to another cursor variable. Because the cursor variable is a variable, it can be used in assignment operations. There are restrictions on referencing this kind of variable, however, as we'll discuss later in this chapter.