Page 115 - CITS - Computer Software Application -TT
P. 115

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
   110   111   112   113   114   115   116   117   118   119   120