Cursor

Cursor

• When user executes any query, block of memory will be provided in database.
• This block of memory is provided for its internal processing and its private to database engine.
• It is called cursor.
• Microsoft SQL Server statements produce a complete result set, but there are times when the results are best processed one row at a time. Opening a cursor on a result set allows processing the result set one row at a time. You can assign a cursor to a variable or parameter with a cursor data type
• When user defines a block of memory to execute a query through programming it is said to be explicit cursor.
• To define explicit cursor, user have to follow below steps.
1. Declare cursor
2. Open cursor
3. Fatch cursor
4. Close Cursor
5. Deallocate Cursor

Example

DECLARE @fname varchar(50)
DECLARE cur_emp CURSOR
static FOR
SELECT fname from emp
OPEN cur_emp
IF @@CURSOR_ROWS > 0
BEGIN
FETCH NEXT FROM cur_emp INTO @fname
WHILE @@Fetch_status = 0
BEGIN
PRINT ' Name : '+@fname
FETCH NEXT FROM cur_emp INTO @fname
END
END
CLOSE cur_emp
DEALLOCATE cur_emp

Types of Cursors

• Static Cursors - A static cursor populates the result set at the time of cursor creation and query result is cached for the lifetime of the cursor.
• Dynamic Cursors - A dynamic cursor allows you to see the data updation, deletion and insertion in the data source while the cursor is open. Hence a dynamic cursor is sensitive to any changes to the data source and supports update, delete operations.