Cursor in Database

Cursor:-

A cursor is a database object that helps in accessing and manipulating data in a given result set. The main advantage of cursors is that you can process data row-by-row.

A result set is defined as a collection of rows obtained from a SELECT statement that meet the criteria specified in the WHERE clause.

Cursors, Serve as a mechanism for applications to operate on a single row or a set of rows.

Cursors enable the processing of rows in the given result set in the following ways:

1) Allow specific rows to be retrieved from the result set.

2) Allow the current row in the result set to be modified.

3) Help navigate from the current row in the result set to a different row.

4) Allow data modified by other users to be visible in the result set.

Structure of Cursors:

The following tasks need to be performed while using a cursor in SQL Server:

1) The cursor needs to be defined and its attributes need to be set.
2) The cursor needs to be opened.
3) The required rows need to be fetched from the cursor. Fetch refers to the process of retrieving a row from the result set.
4) The data in the current row of the cursor can be modified, if required.
5) The cursor needs to be closed.
6) The cursor should be deallocated.

This is a good practice as resources used by the cursor are released.

 

Result:-  

Note:-

 Cursor is used to read a record on row by row basis, so that above cursor stored procedure shows only one record at a time.

 So we apply the “while” condition and get all the records row by row.

Result:- 

Conclusion:-

(I)    It is returned by only one row at a time. So it gives less performance.
(II)    If the result set are less than 50 or 100 records it is better to go for cursors.