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 %ROWCOUNTfor 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.
