CURSORS IN SQL

A cursor in SQL is a temporary work area created in system memory when a SQL statement is executed. A SQL cursor is a set of rows together with a pointer that identifies a current row. It is a database object to retrieve data from a result set one row at a time. It is useful when we want to manipulate the record of a table in a singleton method, in other words one row at a time. In other words, a cursor can hold more than one row, but can process only one row at a time. The set of rows the cursor holds is called the active set. nn  Types of Cursors in SQL

There are the following two types of cursors in SQL:

Implicit Cursor         Explicit Cursor 

Implicit Cursor 

These types of cursors are generated and used by the system during the manipulation of a DML query (INSERT, UPDATE and DELETE). An implicit cursor is also generated by the system when a single row is selected by a SELECT command.

Explicit Cursor

This type of cursor is generated by the user using a SELECT command. An explicit cursor contains more than one row, but only one row can be processed at a time. An explicit cursor moves one by one over the records. An explicit cursor uses a pointer that holds the record of a row. After fetching a row, the cursor pointer moves to the next row.

Main components of Cursors

Each cursor contains the followings 5 parts

Declare Cursor: 

In this part we declare variables and return a set of values.

Open: 

This is the entering part of the cursor.

Fetch: 

Used to retrieve the data row by row from a cursor.

Close: 

This is an exit part of the cursor and used to close a cursor.

Deallocate: 

In this part we delete the cursor definition and release all the system resources associated with the cursor.