All About Cursors

I have been studying my "Oracle Advanced PL/SQL Professional Guide" to prepare for a technical interview. There was a lot of information on cursors in the book. That is good since I use cursor a lot. But I normally only use a specific style of cursor.

To start with, you can define a cursor in the declaration section. If it needs any parameters, you pass it a list in parentheses almost like you do a function. Then you OPEN the cursor, passing in any parameters in parentheses. You can then FETCH the cursor into your variables. And before doing a CLOSE on the cursor, you might be checking any of the cursor attributes:

• %ROWCOUNT
• %ISOPEN
• %FOUND
• %NOTFOUND

Normally you will check %NOTFOUND to see if you are at the end of the result set. You can always access the %ISOPEN attribute on a cursor. However the others will throw an exception unless you are accessing them between the OPEN and CLOSE of the cursor.

When using a FORALL cursor, there are the additional attributes %BULK_ROWCOUNT and %BULK_EXCEPTIONS. Most of the time when you define an explicit cursor, you have the cursor name that you prepend to the attributes. However you could do an implicit cursor by executing a SELECT statement. In that scenario, you can still check the attribute, but you use the term SQL instead of the cursor name. For example, you can check SQL%ROWCOUNT.

Then there is my good friend the ref cursor. I actually don't use these very frequently. My cursors are created and used in a procedure or function. I don't pass cursors around to other procedures. But if you had to, you could use a ref cursor. It is, as the name implies, a reference to a cursor. There are two types: strong and weak. Strong ref cursors have a fixed return type in the prototype. Weak ref cursors can be used for any SELECT statement. There is also a generic SYS_REFCURSOR which is a weak ref cursor.

The book goes on at length about collections. However that warrants its own separate post.