What is Cursor ?

Cursor is a pointer in memory area called context area.

What is context area ?

Context area is a memory area inside the Process Global Area (PGA) which helps Oracle server in processing an SQL statement by holding the important information about the statement.

This information include:

1. Rows returned by a query.
2. Number of rows processes by a query.
3. A pointer to the parsed query in the shared pool.

Using cursor we can control the context area as it is a pointer to the same.

---

Types of cursors:

1. Implicit cursors
2. Explicit cursors

1. Implicit cursors:

1. Automatically gets created by Oracle server when DML SQL statement gets executed.
2. Users can not control the behavior of these cursors.
3. Created in background for any PL/SQL block which executes an SQL statement. 

2. Explicit cursors:

1. User defined cursors which means user has to create this cursor for any statement.
2. Unlike implicit cursor user has full control over explicit cursor.

---

Create an explicit cursor:

This can be done in 4 steps-

1. Declare
2. Open
3. Fetch
4. Close

In case of implicit cursor Oracle performs all these steps automatically for us.

1. Declare cursor: 

Declaring a cursor means initializing a cursor into memory.

Syntax,

CURSOR cursor_name IS select_statement;

2. Open cursor:

• In order to put the cursor at work we have to open it first.
• When we open the cursor the memory will be allotted to it., and it set put to the next step which is fetching the data from it.

Syntax,

OPEN cursor_name;

3. Fetch cursor:

The process of retrieving the data from the cursor is known as fetching the cursor.

Syntax,

FETCH cursor_name INTO PL/SQL variable;OrFETCH cursor_name INTO PL/SQL record;

4. Close cursor:

Closing statement of a cursor will releases all the resources associated with it.

Syntax,

CLOSE cursor_name;

---

Example of a Cursor:

DECLARE
CURSOR cursor_name IS select_statement;
BEGIN
OPEN cursor_name;
FETCH cursor_name INTO PL/SQL variable;
CLOSE cursor_name;
END;