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:

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.