What is Cursor? How to use a Cursor?

Operations in a relational database act on a complete set of rows. The set of rows returned by a SELECT statement consists of all the rows that satisfy the conditions in the WHERE clause of the statement. This complete set of rows returned by the statement is known as the result set. Applications, especially those that are interactive and online, cannot always work effectively with the entire result set as a unit. These applications need a mechanism to work with one row or a small block of rows at a time. Cursors are an extension to result sets that provide that mechanism.

A cursor is implemented by a cursor library. A cursor library is software, often implemented as a part of a database system or a data access API, that is used to manage attributes of data returned from a data source (a result set). These attributes include concurrency management, position in the result set, number of rows returned, and whether you can move forward or backward (or both) through the result set (scrollability).

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, with or without returning to the original table. In other words, cursors conceptually return a result set based on tables within the databases. The cursor is so named because it indicates the current position in the result set, just as the cursor on a computer screen indicates current position.

It is important to become familiar with the concept of cursors before moving on to learn the specifics of their usage in ADO.

Using cursors, you can:

• Specify positioning at specific rows in the result set.

• Retrieve one row or a block of rows based on the current result set position.

• Modify data in the rows at the current position in the result set.

• Define different levels of sensitivity to data changes made by other users.

For example, consider an application that displays a list of available products to a potential buyer. The buyer scrolls through the list to see product details and cost, and finally selects a product for purchase. Additional scrolling and selection occurs for the remainder of the list. As far as the buyer is concerned, the products appear one at a time, but the application uses a scrollable cursor to browse up and down through the result set.

You can use cursors in a variety of ways:

• With no rows at all.

• With some or all of the rows in a single table.

• With some or all of the rows from logically joined tables.

• As read-only or updateable at the cursor or field level.

• As forward-only or fully scrollable.

• With the cursor keyset located on the server.

• Sensitive to underlying table changes caused by other applications (such as membership, sort, inserts, updates, and deletes).

• Existing on either the server or the client.

Read-only cursors help users browse through the result set, and read/write cursors can implement individual row updates. Complex cursors can be defined with keysets that point back to base table rows. Although some cursors are read-only in a forward direction, others can move back and forth and provide a dynamic refresh of the result set based on changes that other applications are making to the database.

Not all applications need to use cursors to access or update data. Some queries simply do not require direct row updating by using a cursor. Cursors should be one of the last techniques you choose to retrieve data-and then you should choose the lowest impact cursor possible. When you create a result set by using a stored procedure, the result set is not updateable using cursor edit or update methods.

A cursor in SQL is a temporary work area created in system memory when a SQL statement is executed. A SQL cursor is a set of rows together with a pointer that identifies a current row. It is a database object to retrieve data from a result set one row at a time. It is useful when we want to manipulate the record of a table in a singleton method, in other words, one row at a time. In other words, a cursor can hold more than one row but can process only one row at a time. The set of rows the cursor holds is called the active set.

Below is example of using cursor in sql.

DECLARE @AccountID INT  
DECLARE @getAccountID CURSOR  
SET @getAccountID = CURSOR FOR  
SELECT Account_ID  
FROM Accounts  
OPEN @getAccountID  
FETCH NEXT  
FROM @getAccountID INTO @AccountID  
WHILE @@FETCH_STATUS = 0  
BEGIN  
PRINT @AccountID  
FETCH NEXT  
FROM @getAccountID INTO @AccountID  
END  
CLOSE @getAccountID  
DEALLOCATE @getAccountID

Cursor is a database object to retrieve data from a result set one row at a time, instead of the T-SQL commands that operate on all the rows in the result set at one time. We use a cursor when we need to update records in a database table in a singleton fashion means row by row.

Please refer to the below links for a better understanding. 

https://www.dotnettricks.com/learn/sqlserver/sql-server-basics-of-cursors

https://www.sqlservertutorial.net/sql-server-stored-procedures/sql-server-cursor/

https://www.c-sharpcorner.com/article/cursors-in-sql-server/

A database cursor is an object that enables traversal over the rows of a result set. It allows you to process individual row returned by a query.

 

For more details check the below articles.

https://www.sqlservertutorial.net/sql-server-stored-procedures/sql-server-cursor/

https://www.c-sharpcorner.com/article/cursors-in-sql-server/

https://www.geeksforgeeks.org/what-is-cursor-in-sql/

https://www.ibm.com/docs/en/db2/11.5?topic=procedures-cursors