Types of Cursor in SQL

Cursor

A cursor allows you to name these work areas and access their stored information. It uses a SQL SELECT statement to fetch a row-set from a database and then can read and manipulate one row at a time.

Types of Cursor

There are mainly two types of cursors. These are:

i. Implicit Cursors

ii. Explicit Cursors

Implicit Cursors

Implicit Cursors are the cursors declared automatically by the Query Processing Software. Cursors are declared implicitly for all Data Manipulation Language (DML) statements.

Explicit Cursors

Explicit Cursors are user-defined cursors declared in EXEC…END-EXEC block. These cursors can be named and manipulated through other statements within the block. It allows multiple rows to be processed from the query result.

How to use a cursor in SQL?

To use a cursor, you must declare and execute a cursor. The process includes the following five steps.

1. Declare Cursor: In this part, we declare variables and return a set of values.
2. Open: This is the entering part of the cursor.
3. Fetch: It Used to retrieve the data row by row from a cursor.
4. Close: This is an exit part of the cursor and is used to close a cursor.
5. De-allocate: In this part, we delete the cursor definition and release all the system resources associated with the cursor.

Syntax:

DECLARE @Variable nvarchar(50); -- Declare all required variables
DECLARE Cursor_Name CURSOR -- Declare Cursor Name
[LOCAL | GLOBAL] -- Define cursor scope
[FORWARD_ONLY | SCROLL] -- Define movement direction of cursor
[KEYSET | DYNAMIC | STATIC | FAST_FORWARD] -- Define basic type of cursor
[SCROLL_LOCKS | OPTIMISTIC | READ_ONLY] -- Define locks

OPEN Cursor_Name; -- Open cursor
FETCH NEXT FROM Cursor_Name; -- Fetch data from cursor
-- Implement SQL query
CLOSE Cursor_Name; -- Close the cursor
DEALLOCATE Cursor_Name; -- Deallocate all resources and memory.