Oracle Cursor Basics

CURSORS

Cursors are one of the most common and fundamental terms in the database terminology. It is one of the core database programming concepts, which forms a basic unit of execution of SQL statement.

A cursor is a pointer, which points towards a pre allocated memory location in the SGA(System Global Area). For transparent understanding, it is a handle or gateway adopted by Oracle to execute a SQL query. The memory location to which it points is known as Context area. Oracle associates every SELECT statement with a cursor to hold the query information in this context area.

---

Why do we need the Cursors?

• 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. Cursor is the mechanism that allows the program to step through the rows one at a time.
 

Cursor contains two parts

ü  Header

ü  Body

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

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

CCURSOR ATTRIBUTES

Ø  %found

Ø  %notfound

Ø  %rowcount

Ø  %isopen

Ø  %bulk_rowcount

Ø  %bulk_exceptions

CURSOR DECLERATION

Syntax:

     Cursor <cursor_name> is select statement;

Ex:

     Cursor c is select *from dept;

Share on Google Plus

About sekhar

This is a short description in the author block about the author. You edit it by entering text in the "Biographical Info" field in the user admin panel.