Categories
Sql Server

What Is A Cursor?

What Is A Cursor?

In this post we will discuss about What Is A Cursor?.A cursor keeps track of the position in the result set, and allows you to perform multiple operations row by row against a result set.We can also fetch cursor rows and perform operations on them in a loop just like using any looping mechanism found in any other programming language. Before you can use a cursor, you need to declare it.

Cursor Life Cycle:

1. Declare Cursor: A cursor is declared by defining the SQL statement that returns a result set.
2. Open: A Cursor is opened and populated by executing the SQL statement defined by the cursor.
3. Fetch: When cursor is opened, rows can be fetched from the cursor one by one or in a block to do data manipulation.
4. Close: After data manipulation, we should close the cursor explicitly.
5. Deallocate: Finally, we need to delete the cursor definition and released all the system resources associated with the cursor.

Cursor Syntax:

DECLARE CURSOR_HIGHTECH CURSOR
[LOCAL | GLOBAL] --define cursor scope
[FORWARD_ONLY | SCROLL] --define cursor movements (forward/backward)
[STATIC | KEYSET | DYNAMIC | FAST_FORWARD] --basic type of cursor
[READ_ONLY | SCROLL_LOCKS | OPTIMISTIC] --define locks
FOR select_statement --define SQL Select statement
FOR UPDATE [col,col] --define columns that need to be updated

----------------------------------OPEN CURSOR----------------------------------

OPEN [GLOBAL] CURSOR_HIGHTECH

----------------------------------CLOSE CURSOR--------------------------------

CLOSE CURSOR_HIGHTECH

-------------------------DEALLOCATE CURSOR--------------------------------

DEALLOCATE CURSOR_HIGHTECH

Cursor Example:

DECLARE @fName varchar(50), @lName varchar(50)

DECLARE CURSOR_HIGHTECH CURSOR -- Declare cursor

LOCAL SCROLL STATIC

FOR

Select firstName, lastName FROM hr.emp

OPEN CURSOR_HIGHTECH -- Open the cursor

FETCH NEXT FROM CURSOR_HIGHTECH INTO @fName, @lName

PRINT @fName + ' ' + @lName -- Print the name

WHILE @@FETCH_STATUS = 0

BEGIN

FETCH NEXT FROM CURSOR_HIGHTECH INTO @fName, @lName

PRINT @fName + ' ' + @lName -- Print the name

END

CLOSE CURSOR_HIGHTECH -- Close the cursor

DEALLOCATE CURSOR_HIGHTECH -- Deallocate the cursor