A Cursor is essentially a temporary work area created in the system memory that comes into play during the execution of DML statements like INSERT, UPDATE, and DELETE. It enables the processing of multiple rows of data, one row at a time, ensuring careful manipulation of database records. Cursors are invaluable tools in the context of databases, providing a structured approach to data handling and modification.
In SQL, there are two different types of cursors
- Implicit cursors- Provide the results about the execution of insert update and delete.
- Explicit cursors- Defined by programmers to have more control area over context area.
Detailed Answer
A cursor is a temporary workspace that a database server allocates during the execution of a statement. This database object allows us to access data of one row at a time. This concept is useful when the user wants to update the rows of a table one by one. Cursors are very helpful in all kinds of databases like MySQL, Oracle, SQL Server etc. They are useful for Data Manipulation Language (DML) statements like Update, Insert and Delete. The collection of tuples held by cursor is called active set.
Life Cycle of a Cursor
- Declare a cursor
- Open Cursor
- Fetch data from Cursor
- Close Cursor Connection
- Deallocate Cursor
1. Declare a cursor
The first step is to declare the cursor
Syntax: DECLARE Cursor_name Cursor for Select_Statement;
2. Open Cursor
The second step is to open the cursor for storing the data that is returned from the result set.
Syntax: OPEN Cursor_name;
3. Fetch data from cursor
The third step fetches the rows for performing operations like insertion, update and deletion on currently active tuple in the cursor.
There are six options that can be used to fetch data from the cursor.
1). FIRST- Allows the system to access only the first record from the cursor table.
Syntax: FETCH FIRST FROM Cursor_name;
2). LAST – Allows the system to access only the last record from the cursor table.
Syntax: FETCH LAST FROM Cursor_name;
3). NEXT – Allows the system to access the data in the forward direction from the cursor table.
Syntax: FETCH NEXT FROM Cursor_name;
4). PRIOR – Allows the system to access the data in the backward direction from the cursor table.
Syntax: FETCH PRIOR FROM Cursor_name;
5). ABSOLUTE n – Allows the system to access the data of the exact nth row from the cursor table.
Syntax: FETCH ABSOLUTE n FROM Cursor_name;
6). RELATIVE n – Allows the system to access the data in both incremental and detrimental processes from the cursor table.
Syntax: FETCH RELATIVE n FROM Cursor_name;
4. Close cursor
The fourth step is to close the cursor when the work is completed.
Syntax: CLOSE Cursor_name:
5. Deallocate cursor
It is the fifth and last step of the life circle of cursor. this step erases the definition of the cursor and discharge all the system resources combined with the cursor.
Types of cursor
There are two types of cursor:
- Implicit Cursor
- Explicit Cursor
1. Implicit Cursor
This cursor is the default cursor in SQL, so there is no need to declare the cursor. Implicit cursors are generated and allocated by the SQL server when the system performs operations like insert, update and delete. Implicit cursors are available even for queries that return one row. We can track the information about the execution of an implicit cursor with the cursor attributes.
Attributes of implicit Cursors
- %FOUND
- %ISOPEN
- %FOUND
- %ROWCOUNT
2. Explicit Cursor
Programmers define explicit cursors to have more control are on a context area. These cursors are created using select query. It holds multiple records but can process a single row at a time. It uses pointer, which after reading one row, moves to another row.
Steps to create explicit cursors
1). Cursor declaration to initialize the memory
Syntax: CURSOR cursor_name IS
SELECT Field_name FROM table_name;
2). Cursor opening to allocate the memory
Syntax: OPEN Cursor_name;
3). Cursor Fetching to retrieve the data
Syntax: FETCH Cursor_name INTO Respective_columns;
4). Cursor closing to release allocated memory
Syntax: CLOSE Cursor_name;
Difference Between Implicit Cursors and Explicit Cursors
| Implicit Cursors | Explicit Cursors |
| Automatically created when select statements are executed. | Needs to be defined explicitly by the user or programmer by providing a name |
| Capable of fetching a single row at a time | Can fetch multiple rows |
| Closes automatically after execution | Needs to be closed after execution |
| More vulnerable to errors | Less vulnerable to errors |
| User has less control | User has entire control |
| Requires anonymous buffer memory for storage purpose | User defined memory for storage purpose |