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 any
FOR 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:

  1. 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 the FETCH NEXT statement.

  2. SCROLL: The cursor can move in any of the following six directions to access data:

    1. NEXT: Fetches the next row of the cursor table.
    2. PRIOR: Fetches the previous row of the cursor table.
    3. FIRST: Fetches the first row of the cursor table.
    4. LAST: Fetches the last row of the cursor table.
    5. ABSOLUTE n: Fetches the nth row of the cursor table.
    6. RELATIVE n: Fetches an nth row from the current position of the cursor table.

Syntax:

FETCH NEXT FROM name_of_cursor
FETCH PRIOR FROM name_of_cursor
FETCH FIRST FROM name_of_cursor
FETCH LAST FROM name_of_cursor
FETCH ABSOLUTE number_of_element FROM name_of_cursor
FETCH 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 SCROLL
FOR SELECT student_id, student_name
FROM students
OPEN student_cursor
FETCH LAST FROM student_cursor
WHILE @@FETCH_STATUS = 0
  FETCH PRIOR FROM student_cursor
CLOSE student_cursor
DEALLOCATE student_cursor

Free Resources

License: Creative Commons-Attribution-ShareAlike 4.0 (CC-BY-SA 4.0)