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.
