Here is a sample sql cursor code created for looping through a list of records as a result of a select query, which enables the sql developer to execute a stored procedure for each row in the cursor which use the values fetched by the cursor as the input arguments. The sample cursor is developed on a SQL Server and is a sample for sql server cursor. The sql codes may use t-sql codes so the sample may have differences than a typical pl sql cursor or an oracle cursor.The sample sql cursor codes below illustrates a process of merging dublicate customer records kept in an application database. Assume that the dublicate customers list and relation among the duclicate customer records are inserted into and kept in a table named DublicateCustomers which is simply formed of columns MasterCustomerId, DublicateCustomerId and some other columns like MergeDate, IsMerged, MergedByUserId, InsertedDate, InsertedByUserId, etc which are used during processing some details and useful in the reporting of the dublicate record merge process results.The list of the original customer records and the dublicate customer records can be selected by the sql select query below:SELECT MasterCustomerId, DublicateCustomerIdFROM DublicateCustomers WHERE IsMerged = 0
You can either create a temporary table to keep the result set in order to use your initial set of records in the next steps of your process or you can just use the above sql query to supply your records set to feed the cursor we will create.
Here with the variable declarations we will set column values we fetch with the cursor to the variables.DECLARE @MergeDate Datetime
DECLARE @MasterId Int
DECLARE @DublicateId Int
Then the sql cursor definition or the cursor declaration code takes place.
DECLARE merge_cursor CURSOR FAST_FORWARD FOR
SELECT MasterCustomerId, DublicateCustomerId
FROM DublicateCustomers WHERE IsMerged = 0
During the cursor declararion you can set the cursor properties or the attributes of the cursor. Note that the sample cursor declaration uses the FAST_FORWARD key attribute in order to create a cursor with a high performance. Since FAST_FORWARD states that the cursor is FORWARD_ONLY and READ_ONLY the performance of the cursor is optimized.
The t-sql syntax of cursor declaration command DECLARE CURSOR is stated as below :
DECLARE cursor_name CURSOR [ LOCAL GLOBAL ] [ FORWARD_ONLY SCROLL ] [ STATIC KEYSET DYNAMIC FAST_FORWARD ] [ READ_ONLY SCROLL_LOCKS OPTIMISTIC ] [ TYPE_WARNING ] FOR select_statement [ FOR UPDATE [ OF column_name [ ,...n ] ] ]
You can find more on how to declare a cursor and cursor attributes in Books Online
With the call of the OPEN command the t-sql server cursor is opened and the cursor is populated with the data by the execution of the select query of the DECLARE CURSOR command.
OPEN merge_cursor
So the OPEN command runs or executes the "SELECT MasterCustomerId, DublicateCustomerId FROM DublicateCustomers WHERE IsMerged = 0" select query defined in the DECLARE CURSOR definition command which is set after FOR key. With the execution of this select query the cursor is populated with the rows or the data returned as a result set of the query.
The next step in using a cursor is fetching the rows from the populated cursor one by one.FETCH NEXT FROM merge_cursor INTO @MasterId, @DublicateId
The syntax of the FETCH command is as follows
FETCH [ [ NEXT PRIOR FIRST LAST ABSOLUTE { n @nvar } RELATIVE { n @nvar } ] FROM ] { { [ GLOBAL ] cursor_name } @cursor_variable_name } [ INTO @variable_name [ ,...n ] ]
With the use of the NEXT, the FETCH NEXT command returns the row following the current row. If FETCH NEXT is called for the first time for a cursor, or we can say if it is called after the OPEN CURSOR command, then the first row in the returned result set is fetched or returned. The column values in the returned row can be set into variables with the INTO key and by giving the names of the variables as a comma seperated list after the INTO key.
So for our sample the first row in the return result set of the cursor is set into two variables named @MasterId and @DublicateId. Here one important point is the first column of the result set (column named MasterCustomerId) is set to first variable defined in the list which is the @MasterId variable. And the secod column named DublicateCustomerId is set to the second variable @DublicateId.
So the variable types must be carefully declared according to the column types of the selected rows.
After the FETCH command, you should always control the value of the @@FETCH_STATUS. This variable returns the status of the last cursor FETCH command in the current connection.
The possible return values of @@FETCH_STATUS are;
0
FETCH statement was successful
-1
FETCH statement failed or the row was beyond the result set
-2
Row fetched is missing
By always checking the @@FETCH_STATUS and controlling that it is value is equal to "0" we will have a new row fetched. When the fetched status is different than the "0" we can say that we have no more records are fetched. In short, the value of @@FETCH_STATUS variable is the controlling parameter of the loop we will use during processing all records or rows in the cursor.
In the body part of the WHILE statement the codes to process each row returned by the cursor takes place. This code block changes according to your reason to create and use a cursor. I placed an EXEC call for a sql stored procedure and an UPDATE sql statement here in order to show as a sample.
The most important thing to care for the inside codes of the WHILE code block is the last code statement FETCH NEXT command is recalled to get the next row from the return cursor data set.
After all the records are processed the @@FETCH_STATUS parameter returns -1, so the cursor can be now closed with the CLOSE CURSOR command. CLOSE CURSOR releases the current result set. And the DEALLOCATE CURSOR command releases the last cursor reference.
Here you can find the full sample sql cursor code used in this article for explaining the cursors in SQL Server.
DECLARE @MergeDate Datetime
DECLARE @MasterId Int
DECLARE @DublicateId Int
SELECT @MergeDate = GetDate()
DECLARE merge_cursor CURSOR FAST_FORWARD FOR
SELECT MasterCustomerId, DublicateCustomerId FROM DublicateCustomers WHERE IsMerged = 0
OPEN merge_cursor FETCH NEXT FROM merge_cursor
INTO @MasterId, @DublicateId
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC MergeDuplicateCustomers @MasterId, @DublicateId
UPDATE DublicateCustomersSETIsMerged = 1,MergeDate = @MergeDate
WHERE
MasterCustomerId = @MasterId ANDDublicateCustomerId = @DublicateId
FETCH NEXT FROM merge_cursor
INTO @MasterId, @DublicateId
END
CLOSE merge_cursor
DEALLOCATE merge_cursor
