what is sql cursor and its types of cursor

 what is sql cursor and its types of cursor

Cursor is a Temporary Memory or Temporary Work Station. It is Allocated by Database Server at the Time of Performing DML(Data Manipulation Language) operations on Table by User. Cursors are used to store Database Tables. There are 2 types of Cursors: Implicit Cursors, and Explicit Cursors.

A cursor in SQL is a database object stored in temp memory and used to work with datasets. You can use cursors to manipulate data in a database, one row at a time. A cursor uses a SQL SELECT statement to fetch a rowset from a database and then can read and manipulate one row at a time.

There are the following two types of cursors in SQL:

a. Implicit Cursor
b. Explicit Cursor

a. Implicit Cursor

The system generates and uses these types of cursors to manipulate a DML query (INSERT, UPDATE, and DELETE). In addition, a system also generates an implicit cursor when a SELECT command selects a single row.

b. Explicit Cursor

This type of cursor is generated by the user using a SELECT command. An explicit cursor contains more than one row, but only one row can be processed at a time. An explicit cursor moves one by one over the records. An explicit cursor uses a pointer that holds the record of a row. After fetching a row, the cursor pointer moves to the next row.

Syntax of a Cursor

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.