Page 115 - CITS - Computer Software Application -TT

COMPUTER SOFTWARE APPLICATION - CITS

UPDATE customers
SET salary = salary + 500
WHERE id = 2;

When a record is updated in the CUSTOMERS table, the above create trigger, display_salary_changes will be
fired and it will display the following result −
Old salary: 1500
New salary: 2000
Salary difference: 500

Creating Cursor

A cursor in SQL Server is a database object that allows us to retrieve each row at a time and manipulate its
data. A cursor is nothing more than a pointer to a row. It’s always used in conjunction with a SELECT statement.
It is usually a collection of SQL logic that loops through a predetermined number of rows one by one. A simple
illustration of the cursor is when we have an extensive database of worker’s records and want to calculate each
worker’s salary after deducting taxes and leaves.
The SQL Server cursor’s purpose is to update the data row by row, change it, or perform calculations that are not
possible when we retrieve all records at once. It’s also useful for performing administrative tasks like SQL Server
database backups in sequential order. Cursors are mainly used in the development, DBA, and ETL processes.
This article explains everything about SQL Server cursor, such as cursor life cycle, why and when the cursor is
used, how to implement cursors, its limitations, and how we can replace a cursor.
Life Cycle of the cursor
We can describe the life cycle of a cursor into the five different sections as follows:

1 Declare Cursor

The first step is to declare the cursor using the below SQL statement:
• DECLARE cursor_name CURSOR
• FOR select_statement;
We can declare a cursor by specifying its name with the data type CURSOR after the DECLARE keyword.
Then, we will write the SELECT statement that defines the output for the cursor.
2 Open Cursor
It’s a second step in which we open the cursor to store data retrieved from the result set. We can do this by
using the below SQL statement:
1 OPEN cursor_name;
3 Fetch Cursor
It’s a third step in which rows can be fetched one by one or in a block to do data manipulation like insert,

102

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