Page 116 - CITS - Computer Software Application -TT

COMPUTER SOFTWARE APPLICATION - CITS

update, and delete operations on the currently active row in the cursor. We can do this by using the below SQL
statement:
1 FETCH NEXT FROM cursor INTO variable_list;

We can also use the @@FETCHSTATUS function in SQL Server to get the status of the most recent
FETCH statement cursor that was executed against the cursor. The FETCH statement was successful
when the @@FETCHSTATUS gives zero output. The WHILE statement can be used to retrieve all records
from the cursor. The following code explains it more clearly:
1 WHILE @@FETCH_STATUS = 0

2 BEGIN
3 FETCH NEXT FROM cursor_name;
4 END;
4 Close Cursor
It’s a fourth step in which the cursor should be closed after we finished work with a cursor. We can do this by
using the below SQL statement:
1. CLOSE cursor_name;
5 Deallocate Cursor
It is the fifth and final step in which we will erase the cursor definition and release all the system resources
associated with the cursor. We can do this by using the below SQL statement:
1 DEALLOCATE cursor_name;
Uses of SQL Server Cursor

We know that relational database management systems, including SQL Server, are excellent in handling
data on a set of rows called result sets. For example, we have a table product_table that contains the product
descriptions. If we want to update the price of the product, then the below ‘UPDATE’ query will update all
records that match the condition in the ‘WHERE’ clause:
1 UPDATE product_table SET unit_price = 100 WHERE product_id = 105;

Sometimes the application needs to process the rows in a singleton fashion, i.e., on row by row basis rather than
the entire result set at once. We can do this process by using cursors in SQL Server. Before using the cursor, we
must know that cursors are very bad in performance, so it should always use only when there is no option except
the cursor.
The cursor uses the same technique as we use loops like FOREACH, FOR, WHILE, DO WHILE to iterate one
object at a time in all programming languages. Hence, it could be chosen because it applies the same logic as the
programming language’s looping process.

Types of Cursors in SQL Server
The following are the different types of cursors in SQL Server listed below:
• Static Cursors
• Dynamic Cursors
• Forward-Only Cursors
• Keyset Cursors

Static Cursors
The result set shown by the static cursor is always the same as when the cursor was first opened. Since the
static cursor will store the result in tempdb, they are always read-only. We can use the static cursor to move both
forward and backward. In contrast to other cursors, it is slower and consumes more memory. As a result, we can
use it only when scrolling is necessary, and other cursors aren’t suitable.

103

CITS : IT&ITES - Computer software application - Lesson 18 - 36