Oracle Cursor Basics

Why do we need the Cursors?

• The select statement should return only one row at a time in previous PL/SQL programs. This is too restrictive in many applications.
• We use the idea of Cursor to handle the above problem. The cursor is the mechanism that allows the program to step through the rows one at a time.
 Cursor contains two parts

•  Header
•  Body

The header includes cursor name, any parameters and the type of data being loaded.

The body includes the select statement.

Ex:

Cursor c(dno in number) return dept%rowtype is select *from dept;

           In the above

                        Header – cursor c (dno in number) return dept%rowtype

                        Body – select *from dept

CURSOR TYPES

• Implicit (SQL)
• Explicit
• Parameterized cursors
• REF cursors

CURSOR STAGES

• Open
• Fetch
• Close

CURSOR ATTRIBUTES

• %found
• %notfound
• %rowcount
• %isopen
• %bulk_rowcount
• %bulk_exceptions

CURSOR DECLARATION

Syntax:

     Cursor <cursor_name> is select statement;

Ex:

     Cursor c is select *from dept;