Introduction to Cursors - Oracle

This concept is the heart of PL/SQL programming. First of all, this concept is very easy; it is not at all complicated in any sense. Those who are familiar with Visual Basic 6 can just imagine a cursor as a recordset, or "datatable" in .NET (or resultset in java). A cursor can be understood as a pointer (or reference) to a memory area which holds the result of any SQL query. Actually, we already used the concept of cursor (unknowingly) in the section "A Powerful FOR loop in PL/SQL" of part five in this series.

Basically, there exist two types of cursors, IMPLICIT and EXPLICIT cursors. IMPLICIT cursors do not have any declarations. They will be automatically created and lost within the memory. We will not have any control over the IMPLICIT cursors. A FOR loop with a SELECT statement, internally maintains an IMPLICIT cursor in memory which is removed from memory once the loop gets completed (an example is available in part five of this series).

We can identify the status of any cursor by using a set of cursor ATTRIBUTES. These ATTRIBUTES can be used with named cursors only (we will look into this later). These ATTRIBUTES play a very important role in dealing with cursors. The following is the list of ATTRIBUTES used with cursors.

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

The above are just some basic attributes. Oracle 9i and 10g introduced a few more. But I will look into them only in upcoming articles, as they will be used in different contexts, other than just a simple cursor. I would like to explain each of the above using examples rather than just messy paragraphs.