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