Cursor is an data object which is used when we need to manipulate date on row by row basis.ie, when we need to go thro’ each row in the table and then do some manipulations and save it in a temporary table.
Disadvantages of cursor:
1.Each time we fetch a row from the cursor, it result a network round trip, where as a normal select statement query make only one round trip.
2.Require more resources and temporary storage
Code Snippet
Cursor Components
* DECLARE statements – Declare variables used in the code block
* SET\SELECT statements – Initialize the variables to a specific value
* DECLARE CURSOR statement – Populate the cursor with values that will be evaluated
o NOTE – There are an equal number of variables in the DECLARE CURSOR FOR statement as there are in the SELECT statement. This could be 1 or many variables and associated columns.
* OPEN statement – Open the cursor to begin data processing
* FETCH NEXT statements – Assign the specific values from the cursor to the variables
o NOTE – This logic is used for the initial population before the WHILE statement and then again during each loop in the process as a portion of the WHILE statement
* WHILE statement – Condition to begin and continue data processing
* BEGIN…END statement – Start and end of the code block
o NOTE – Based on the data processing multiple BEGIN…END statements can be used
* Data processing – In this example, this logic is to backup a database to a specific path and file name, but this could be just about any DML or administrative logic
* CLOSE statement – Releases the current data and associated locks, but permits the cursor to be re-opened
* DEALLOCATE statement – Destroys the cursor
HappyCoding ! 😉
Geek Girl ! 