Cursor in SQL Server
Introduction
• A cursor is a database object that allows us to retrieve each row at a
time and manipulate its data.
• It is a temporary memory that is allocated by the database server at the
time of performing the Data Manipulation Language operations on a
table, such as INSERT, UPDATE and DELETE etc.
• A cursor is a pointer which is pointing to this area.T he data contained in
this memory area is also known as Active Set.
• It is used to retrieve and manipulate data stored in the SQL table.
Life Cycle of the cursor
Declare Cursor Statement
• Syntax
DECLARE cursor_name CURSOR
FOR select_statement;
Open Cursor Statement
• Syntax
OPEN cursor_name;
Fetch Cursor Statement
• In this step rows can be fetched one by one or in a block to do data
manipulation like insert, update, and delete operations on the currently
active row in the cursor.
• We can do this by using the below SQL statement:
• Syntax
FETCH NEXT FROM cursor INTO variable list;
Close Cursor Statement
• In this step the cursor should be closed after we finished work with a
cursor. We can do this by using the below SQL statement:
CLOSE cursor_name;
types of Cursors:
1. Implicit Cursors,
2. Explicit Cursors.
1.Implicit Cursors: Implicit Cursors are also known as Default
Cursors of SQL SERVER. These Cursors are allocated by SQL
SERVER when the user performs DML operations.
2.Explicit Cursors: Explicit Cursors are Created by Users whenever
the user requires them.
Explicit Cursors are used for Fetching data from Table in Row-By-Row
Manner.
Implicit Cursors Explicit Cursors
Implicit cursors are automatically created when Explicit cursors needs to be defined explicitly by the
select statements are executed. user by providing a name.
They are capable of fetching a single row at a time. Explicit cursors can fetch multiple rows.
Closes automatically after execution. Need to close after execution.
They are more vulnerable to errors such as Data
They are less vulnerable to errors(Data errors etc.)
errors, etc.
Provides less programmatic control to the users User/Programmer has the entire control.
Comparative to Implicit cursors, explicit cursors are
Implicit cursors are less efficient.
more efficient.
Implicit Cursors are defined as: Explicit cursors are defined as:
BEGIN SELECT attr_name from table_name where DECLARE CURSOR cur_name IS SELECT attr_name
CONDITION; END from table_name where CONDITION; BEGIN ...
