Cursors in Oracle Explanation with Example

A cursor is a handle (pointer) in memory for a DML operation (Select Update).

There are mainly 2 types of cursors .
1) Implicit Cursor.
2) Explicit Cursor.

Implicit cursor: Oracle will implicitly creates an area for the DML operations. Programmer will not have control on implicit cursors. The only useful attribute on this implicit cursor is SQL ROWCOUNT it will give the number of rows affected by the recent DML operation.

The only Implicit cursor is SQL.

Explicit Cursor:
Explicit cursors are created by the programmer and programmer have control on it
Programmer can

1) Open
2) Fetch
3) Close

and do some manipulations on the values

Explicit Cursors are classified into

1) Normal cursor

2) Parameterized cursor

3) Cursor For Loops and

4) REF cursors

REF Cursors:

Normally when we create a normal cursor we cant change the select query associated to that query (the query which is given at the time of definition)

But using REF cursors we can change the cursor statement also.

These REF cursors are useful when we are sending data from one environment to another environment.

Oracle Cursors and Types of Cursors

The Cursor is a handle (name or a pointer) for the memory associated with a specific statement.  A cursor is basically an Area alocated by Oracle for executing the Sql Statements.  Oracle Uses an Implicit Cursor statement for a single row query and Explicit Cursor for a multi row query.

Types of Cursor :

I) Implicit
2) Expicit

Flow in Explicit Cursor
------------------------------
 I. Declaring a cursor :- Involves assign a name to cursor and associating a query with it..
 II. Open the cursor   :-  Executes the query and identify the result set.
 III. Fetch the cursor :-  gets the result set and Loops through to process them
 IV. Close the cursor :- Releases the cursor

Types of Cursors in Oracle

Cursors are of two types
1. Implicit Cursors: -  Whenever we execute sql statements oracle server assigns a work area called private sql area to store precessed infomation. The most recently used work are can be accessed using SQL%. In implicit cursors open, fetch , close operations are automatically performed by the server implicitly.

2.Explicit cursors:-   In explicit cursor  you can explicitly assign a name to process information stored in private sql areas.  This  process involves four steps

II) Explicit (Explicit cursor has three sub-types)

1)  Simple Cursor

2)  Parameterised Cursor

3) Ref Cursor