How to create a database cursor in SQL
In SQL, cursors are used to iterate row-wise over a result set. This iteration works the same way as the iterators in lists and other data structures. It acts as a pointer and is used to retrieve or update data of each row of a query.
Types of cursors
SQL supports different types of cursors – they are:
1. Static: This cursor creates a copy of data. In order to see the effect of data operations and changes, the cursor must be closed and then reopened.
2. Dynamic: Unlike static cursors, dynamic cursors allow us to see the effect of data operations and changes in real-time.
3. Fast_forward: This is the fastest cursor used to traverse through the data in the forwarding direction only. It does not allow data modifications.
4. Keyset: It creates a temporary table to store unique identifiers of records in record set. It also reflects on the changes made to non-key values in the records.
The lifecycle of a cursor
The complete lifecycle of an SQL cursor is:
1. Declare
First, we DECLARE a cursor object by assigning it a name and a SELECT statement.
Syntax:
DECLARE name_of_cursor CURSOR[LOCAL | GLOBAL] -- define scope of the cursor[FORWARD_ONLY | SCROLL] -- define movement type of the cursor[STATIC | DYNAMIC | FAST_FORWARD | KEYSET] --define type of cursor[SCROLL_LOCKS | OPTIMISTIC |READ_ONLY] -- define locks if anyFOR SELECT * from name_of_table
2. Open
Then, we OPEN a cursor by allocating it a memory to fetch and storing the data retrieved from the result set.
Syntax:
OPEN name_of_cursor
3. Fetch
After we open the cursor, we can FETCH rows from the cursor by accessing one row at a time.
SQL has the following two movement options to fetch data:
-
FORWARD_ONLY: The cursor can only access data in a forward direction from the first to the last row. It only allows the use of theFETCH NEXTstatement. -
SCROLL: The cursor can move in any of the following six directions to access data:NEXT: Fetches the next row of the cursor table.PRIOR: Fetches the previous row of the cursor table.FIRST: Fetches the first row of the cursor table.LAST: Fetches the last row of the cursor table.ABSOLUTE n: Fetches the nth row of the cursor table.RELATIVE n: Fetches an nth row from the current position of the cursor table.
Syntax:
FETCH NEXT FROM name_of_cursorFETCH PRIOR FROM name_of_cursorFETCH FIRST FROM name_of_cursorFETCH LAST FROM name_of_cursorFETCH ABSOLUTE number_of_element FROM name_of_cursorFETCH RELATIVE number_of_element FROM name_of_cursor-- negative number_of_element means moving backward
4. Close
After fetching the data, we explicitly CLOSE the cursor. The cursor can be reopened after closing.
Syntax:
CLOSE name_of_cursor
5. Deallocate
In the end, we DEALLOCATE the cursor to deallocate all associated system resources and cursor definition. After deallocating, the cursor cannot be reopened.
Syntax:
DEALLOCATE name_of_cursor
The following is an example of the process:
CREATE TABLE students(student_id int NOT NULL PRIMARY KEY,student_name varchar(50) NOT NULL)DECLARE student_cursor CURSOR SCROLLFOR SELECT student_id, student_nameFROM studentsOPEN student_cursorFETCH LAST FROM student_cursorWHILE @@FETCH_STATUS = 0FETCH PRIOR FROM student_cursorCLOSE student_cursorDEALLOCATE student_cursor
