T-SQL - Cursor - Description and Use-Case (Example)

Cursor in t-sql is very useful feature.  In SQL, it is difficult to set a pointer to a set of data and evaluate other tables or data while in the loop.  Simply, cursors are temporary storage to a row data or some column data in a row you set in a table so that they can be used to evaluate other data while going through each row in the table.  Because cursors use server resources, you must know how to use it correctly, especially when you are running it in a large loop.  
 
I will go over the concept using the example below.
 

반응형

---

1. DEFINE

First you must define a cursor as below.

-- VARIABLES DEFINITION
DECLARE @city_name VARCHAR(128);
DECLARE @country_name VARCHAR(128);
DECLARE @city_id INT;
 
-- CURSOR DEFINITION
DECLARE cursor_city_country CURSOR FOR
  SELECT city.id, TRIM(city.city_name), TRIM(country.country_name)
  FROM city
  INNER JOIN country ON city.country_id = country.id;

cursor_city_country is the name of the cursor.  There are three variables to store from the cursor, @city_name, @country_name and @city_id.  The cursor will get the row data from the query defined under "CURSOR DEFINITION" section in the example above.

---

2. OPEN

Then you are opening a cursor as below:

-- OPEN CURSOR
OPEN cursor_city_country;

By doing this, the cursor will start consuming SQL server's resources because the cursor for the first row data from the query is now "open".

---

3.  FETCH & EVALUATE

The third step is that you will be "fetching" row data to the variables using the cursor.

FETCH NEXT FROM cursor_city_country INTO @city_id, @city_name, @country_name;

Now the variables will be set with the data (in the order of SELECT query you defined above).  Once you have these variables set, you can do any evaluation you want.  In a real world, you will want to include this in the loop.  
 

728x90

---

4. REPEAT with a LOOP

Once the cursor is opened and data is fetched to variables, you will want to do an evaluation in a loop using the next row data.  Below is an example:

WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT CONCAT('city id: ', @city_id, ' / city name: ', @city_name, ' / country name: ', @country_name);
    FETCH NEXT FROM cursor_city_country INTO @city_id, @city_name, @country_name;
END;

In the example above, the evaluation is just PRINT, but you willl be doing more complex evaluation in a real world.  The important concept is that you want to set the loop condition as @@FETCH_STATUS = 0.  If there is no more data to be fetched, then @@FETCH_STATUS will be set to 0.  Therefore, you are running a WHILE loop until it is set to 0 (end of the query data).

---

5.  GARBAGE COLLECTION (DEALLOCATION)

This is the most important part you must remember.  When you are done with the cursor, you must deallocate resources that the cursor has consumed.  Otherwise, the resources will not be released back to the server, potential for future crashes.  
 
Use the following code to deallocate the resources:

-- CLOSE AND DEALLOCATE
CLOSE cursor_city_country;
DEALLOCATE cursor_city_country;

---

The code below is the complete version of the example:
 

-- VARIABLES DEFINITIONS
DECLARE @city_name VARCHAR(128);
DECLARE @country_name VARCHAR(128);
DECLARE @city_id INT;
 
-- CURSOR DEFINITION
DECLARE cursor_city_country CURSOR FOR
  SELECT city.id, TRIM(city.city_name), TRIM(country.country_name)
  FROM city
  INNER JOIN country ON city.country_id = country.id;
 
-- OPEN CURSOR
OPEN cursor_city_country;
 
-- EVALUATION VIA WHILE LOOP (UNTIL THE END OF THE QUERY)
FETCH NEXT FROM cursor_city_country INTO @city_id, @city_name, @country_name;
WHILE @@FETCH_STATUS = 0
    BEGIN
    PRINT CONCAT('city id: ', @city_id, ' / city name: ', @city_name, ' / country name: ', @country_name);
    FETCH NEXT FROM cursor_city_country INTO @city_id, @city_name, @country_name;
    END;
 
-- CLOSE CURSOR AND DEALLOCATE RESOURCES
CLOSE cursor_city_country;
DEALLOCATE cursor_city_country;

---

Hope this helped!  Thank you