What is a Database Cursor in sql?
In this post we will show you What is a Database Cursor in sql, hear for What is a Database Cursor in sql we will give how to Avoid it.
What is a Database Cursor?
A database cursor can be thought of as a pointer to a specific row within a query result. The pointer can be moved from one row to the next. Depending on the type of cursor, you may be even able to move it to the previous row.
Think of it this way: a SQL result is like a bag, you get to hold a whole bunch of rows at once, but not any of them individually; whereas, a cursor is like a pair of tweezers. With it, you can reach into the bag and grab a row, and then move onto the next.
Types of Cursors
The type of cursors you can define are broken in two main categories: scrolling capabilities and ability to detect changes made to the database.
Let’s first talk about scrolling capabilities. Cursors can be defined with two main scrolling capabilities, FORWARD_ONLY or SCROLL.
- FORWARD_ONLY – The cursor starts on the first row and end on the last. The cursor can only move to the next row in the result.
- SCROLL – the cursor can use operations, such as FIRST, LAST, PRIOR, NEXT, RELATIVE, ABSOLUTE to navigate the results.
When we talk about data sensitivity we mean what happens when the same row is changed by someone else? Is that change seen in the result of the cursor?
- STATIC – any changes made aren’t reflected in the cursor’s results. Also, any change made to the cursor’s data, aren’t permanent. They aren’t stored to the underlying database tables.
- KEYSET – A keyset cursor can see changes made to rows that are originally in to cursor, since these rows unique identifiers (keys) are used to fetch rows during cursor operations. A keyset cursor cannot see rows added to the table.
- DYNAMIC – changes made are reflected in the cursors. Likewise, changes made within the cursor are made to the underlying database.
Why Database Cursors are Used
The reason you may need to use a database cursor is that you need to perform actions on individual rows.
For example, consider this update statement:
UPDATE esqlSalesPerson SET City = 'Saline' WHERE SalesPersonID < 10035
It updates every row in the table esqlSalesPerson where the SalesPersonID is less than 10035. If, during the update operation, there is an error, then no rows are updated. The entire update is treated as a transaction.
Now by using a cursor, we can iterate or move from one row to the next and updating rows as we go. If we encounter an error, try something else, or skip the operation. The difference is, that when you use cursors, you can act on each row.
Also, if the cursor is defined as SCROLLABLE we can even move back to the previous row.
Example Database Cursors in SQL
The purpose of this example isn’t to go full detail on how to build a cursor, we’ll do that in a later article, rather, it’s to show you an example so you’ll be able to recognize them.
Consider the following select statement. We’ll use this for the basis of our cursor.
SELECT BusinessEntityID, FirstName, LastName FROM Person.Person
Here are the general steps we do to set up a cursor:
- Declare Variables
- Declare Cursor
- Fetch values into variables
- Test Status and Loop
- Close Cursor
- Deallocate Cursor
Here is the code for the cursor:
DECLARE @businessEntityID as INT; DECLARE @firstName as NVARCHAR(45), @lastName as NVARCHAR(45); DECLARE @personCursor as CURSOR; SET @personCursor = CURSOR FOR SELECT BusinessEntityID, FirstName, LastName FROM Person.Person OPEN @personCursor FETCH NEXT FROM @personCursor INTO @businessEntityID, @firstName, @lastName WHILE @@FETCH_STATUS = 0 BEGIN PRINT cast(@BusinessEntityID as VARCHAR (45)) + ' - ' + @firstName + ' ' + @lastName; FETCH NEXT FROM @personCursor INTO @businessEntityID, @firstName, @lastName END CLOSE @personCursor; DEALLOCATE @personCursor;
Notice that we used the PRINT statement. This is a handy statement. It will send output to the SQL Server Management Studio Message Window! This make is a good tool for general debugging and playing around with stored procedures:
Hope this code and post will helped you for implement What is a Database Cursor. if you need any help or any feedback give it in comment section or you have good idea about this post you can give it comment section. Your comment will help us for help you more and improve feeverr.