A cursor is a database object that can be created at runtime. It is used to get data  from a result set line by line(or row by row) rather than executing  all the rows in the result set at a single time like an SQL-transaction. Generally, we use it when we want to update a database record in line by line fashion.

We have a two types of cursors:
1. Forward only:- This is a default cursor type. This cursor is used when we need to go in a forward direction from its starting point.

2. Scroll cursor:- This cursor is used  when we want to move forward, come backward, go to a particular line or position in the result set. This cursor will have different fetching options like FIRST, LAST, NEXT, PRIOR, RELATIVE, ABSOLUTE to retrieve the data.  

 Cursor scope

We have a two types of scope in which cursor can be define:
1. Local:- when the cursor can be accessed within the stored procedure or trigger in which it is declared.

2. Global:- When the cursor can be accessed by any outside stored procedure or trigger.

Cursor behaviour

Cursor can behave in two ways:
1. Static:- In this behavior, we have  to create a temporary copy of a data on which the cursor work so that any change made in the data value will not reflect on the original data value.

2. Dynamic:- In this behavior cursor will work on the original value of data so that any change made in the data value will reflect on the original value of data.

Syntax for declaring a cursor:

DECLARE cursor_name CURSOR [ LOCAL | GLOBAL ] 
     [ FORWARD_ONLY | SCROLL ] 
     [ STATIC | DYNAMIC | FAST_FORWARD ] 
     [ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ] 
     [ TYPE_WARNING ] 
     FOR select_statement 
OPEN cursor_name
FETCH NEXT FROM cursor_name
[;]

Steps to create a cursor are as follows:
1. Define a cursor
2. Open the cursor
3. Fetch rows from the result set
4. Close the cursor
5. Deallocate the cursor

Cursor Example:

DECLARE @UserId int
DECLARE @UserFullName varchar(100)
DECLARE UserDetailsCursor CURSOR FOR
SELECT UserId , FirstName + SPACE(1)+ LastName FROM User
WHERE UserId > 4;
OPEN UserDetailsCursor 
FETCH NEXT FROM UserDetailsCursor INTO @UserId , @UserFullName
WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT CAST(@UserId as varchar(20))+ ' :- ' + @UserFullName
    FETCH NEXT FROM UserDetailsCursor INTO @UserId , @UserFullName
END
CLOSE UserDetailsCursor 
DEALLOCATE UserDetailsCursor