A Beginner's Guide to an SQL Cursor (In Many Databases)

calendar Jul 6, 2020 · 21 min read  ·
Share on: linkedin copy

Cursors are a feature in many SQL databases that allow you to work with data.

They exist in SQL Server, Oracle, MySQL, PostgreSQL, and likely many other databases.

But what is an SQL cursor and how do you use them?

Let's learn all about them in this guide.

What is an SQL Cursor?

A cursor in SQL is an object in database code that lets you process rows individually, or "traverse" over the rows.

Why is this needed?

Many programming languages, such as those used for application development or web development like JavaScript and C#, process sets of data individually using a concept called a loop.

Loops look like this:

  • For each record in the set of data:
  • Do something with the record
  • Repeat until it has reached the end of the set of data

It looks at each record inside a set individually.

However, in SQL, data is processed in a set. You perform actions on the whole set:

  • Get a set of data
  • Do something with the entire set

This can be a tough change in mindset to make for developers who are new to SQL or looking to improve their SQL.

However, many database vendors include the ability to process rows individually. This is what a cursor does.

Cursors allow you to store a result set in a variable, and loop through it to perform logic and actions on each row.

 

When to Use an SQL Cursor

Using a cursor in SQL to iterate through a set of data to perform logic often has performance issues. It goes against how data sets work in SQL.

Normally, SQL cursors should be avoided. Most of the time, when code is written using an SQL cursor, it is:

  • Performing data update operations which can be done using normal SQL
  • Selecting data from multiple tables which can be done using joins
  • Complex logic which could be done using a CASE statement

However, there are some situations where a cursor is useful:

  • Calculating a running total (which may be faster)
  • Executing stored procedures in some situations
  • Processing batches of records to reduce the impact of locks on tables

So, before you use a cursor for all of your code, consider if there is another way to do it.

With that said, let's look into how cursors work and see some examples.

 

The Four Steps in an SQL Cursor

There are four steps in the lifecycle of a cursor:

Declare

The Declare step of a cursor is where you specify the name of the cursor and the SQL statement that is used to populate it.

Open

The next step is Open, which processes and runs the SQL statement that is mentioned in the Declare section.

Fetch

The third step is Fetch, which reads a single row from the set of rows stored in the cursor and stores this single row into another variable.

When you fetch the row, you can perform actions and logic on the data in the row. You can modify other variables, run SQL commands, perform IF statements, and more.

The Fetch step is usually run on each row in the overall result.

Close

Finally, once all of the results have been processed and the Fetch stage is finished, the Close step will release the cursor from memory and allow you to continue with the application.

Now we've had a look at some of the theory of cursors and what they are, let's see some examples!

 

SQL Server Cursor Example (T-SQL)

Let's take a look at a simple example of a cursor in SQL Server. This cursor will select some data from a table.

First, we'll create a sample table and insert some data into it.

 1CREATE TABLE product (
 2  id INT,
 3  product_name VARCHAR(100),
 4  product_category_id INT,
 5  price INT
 6);
 7
 8INSERT INTO product (id, product_name, product_category_id, price)
 9VALUES (1, 'Toaster', 1, 20);
10INSERT INTO product (id, product_name, product_category_id, price)
11VALUES (2, 'TV', 2, 600);
12INSERT INTO product (id, product_name, product_category_id, price)
13VALUES (3, 'Couch', 2, 450);
14INSERT INTO product (id, product_name, product_category_id, price)
15VALUES (4, 'Fridge', 1, 800);
16INSERT INTO product (id, product_name, product_category_id, price)
17VALUES (5, 'Kettle', 1, 25);
18INSERT INTO product (id, product_name, product_category_id, price)
19VALUES (6, 'Microwave', 1, 40);

Now, we'll create our cursor code.

 1DECLARE @currentID INT, @currentProductName VARCHAR(100), @currentPrice INT;
 2DECLARE test_cursor CURSOR FOR
 3  SELECT id, product_name, price
 4  FROM product;
 5OPEN test_cursor;
 6FETCH NEXT FROM test_cursor INTO @currentID, @currentProductName, @currentPrice;
 7WHILE @@FETCH_STATUS = 0
 8  BEGIN
 9    PRINT @currentProductName + ' ' + CAST(@currentPrice AS VARCHAR) + ' (ID: '+ CAST(@currentID AS VARCHAR) +')';
10    FETCH NEXT FROM test_cursor INTO @currentID, @currentProductName, @currentPrice;
11  END;
12CLOSE test_cursor;
13DEALLOCATE test_cursor;

 

What does this code do?

Declare variables. The first line declares a few variables (@currentID, @currentProductName, @currentPrice). These are used to store the id, product_name, and price, for each row returned. We only need one set of variables as we're only fetching one row of data at a time.

Declare the cursor. Next, we declare the cursor. It's called "test_cursor" and we specify the SELECT statement to use. In this example, we're selecting a few records from the product table.

Open the cursor. After declaring the cursor, we open it. We need to do this to proceed. This will run the SQL statement.

Fetch the first record. This is done after we open the cursor, and it's done using the FETCH NEXT command. We fetch the first record into the variables we have declared.

Check if a record is found. The WHILE @@FETCH_STATUS = 0 part checks if the FETCH_STATUS is 0. This is set to 0 if the fetch was successful. The code is saying "while there are rows to be fetched, keep running this code".

Begin a block. The next line just says BEGIN. This indicates the start of the code to be run as part of the WHILE loop.

Print a line of text. The PRINT statement will print a line of text to the IDE's output section. In this example, we are printing each of the three variables, separated by spaces. We print the product_name, then the price, then the ID. We have to use the CAST function on the price and ID as they are INT values and the PRINT statement only accepts VARCHAR.

Fetch the next row. For the WHILE statement to continue to look at our cursor, it needs to fetch the next row. We do this by running the same FETCH NEXT statement as earlier. The variables now store the details for the second row found.

End the block. This signals the end of the block of code that is looped as part of the WHILE statement. The execution of the code then returns to the WHILE statement above.

Close the cursor. Once the WHILE statement is no longer true (in this case, there are no more records in the cursor), then the cursor is closed.

Deallocate cursor. The second part of closing a cursor is running this DEALLOCATE command.

The code is now completed.

This is a simple cursor that outputs the values from a table in lines of text. If you run this code, after creating and populating the table, you should see output like this:

1Started executing query at Line 2
2Toaster 20 (ID: 1)
3TV 600 (ID: 2)
4Couch 450 (ID: 3)
5Fridge 800 (ID: 4)
6Kettle 25 (ID: 5)
7Microwave 40 (ID: 6)
8Total execution time: 00:00:00.024

So that's a simple example of a cursor in SQL Server.

Oracle Cursor Example (PL/SQL)

Let's take a look at an example of a cursor in Oracle PL/SQL.

Oracle actually has two different types of cursors: implicit cursors and explicit cursors.

An implicit cursor is a type of cursor automatically created by Oracle when an SQL statement is run. It’s called an implicit cursor because it’s created automatically – you don’t need to do anything for it to get created.

An explicit cursor is one that is declared by the PL/SQL code. You have more control over it and how the data is handled.

In this guide, we'll be looking at an example of an explicit cursor. Implicit cursors are a little simpler and are covered in my PL/SQL tutorial.

Here's our sample table and data:

 1CREATE TABLE product (
 2  id NUMBER,
 3  product_name VARCHAR2(100),
 4  product_category_id NUMBER,
 5  price NUMBER
 6);
 7
 8INSERT INTO product (id, product_name, product_category_id, price)
 9VALUES (1, 'Toaster', 1, 20);
10INSERT INTO product (id, product_name, product_category_id, price)
11VALUES (2, 'TV', 2, 600);
12INSERT INTO product (id, product_name, product_category_id, price)
13VALUES (3, 'Couch', 2, 450);
14INSERT INTO product (id, product_name, product_category_id, price)
15VALUES (4, 'Fridge', 1, 800);
16INSERT INTO product (id, product_name, product_category_id, price)
17VALUES (5, 'Kettle', 1, 25);
18INSERT INTO product (id, product_name, product_category_id, price)
19VALUES (6, 'Microwave', 1, 40);

Now here's the cursor example:

 1DECLARE
 2  currentID NUMBER;
 3  currentProductName VARCHAR2(100);
 4  currentPrice NUMBER;
 5  CURSOR test_cursor IS
 6  SELECT id, product_name, price
 7  FROM product;
 8BEGIN
 9  OPEN test_cursor;
10  LOOP
11    FETCH test_cursor INTO currentID, currentProductName, currentPrice;
12    EXIT WHEN test_cursor%NOTFOUND;
13    DBMS_OUTPUT.PUT_LINE(currentProductName || ' ' || currentPrice || ' (ID: ' || currentID || ')');
14  END LOOP;
15  CLOSE test_cursor;
16END;

What does this code do?

Declare section. The keyword DECLARE starts the declare section, which is where variables are declared.

Declare variables. The first three lines after DECLARE will declare a few variables (currentID, currentProductName, currentPrice). These are used to store the id, product_name, and price, for each row returned. We only need one set of variables as we're only fetching one row of data at a time.

Declare the cursor. Next, we declare the cursor. It's called "test_cursor" and we specify the SELECT statement to use. In this example, we're selecting a few records from the product table.

Begin block. This is where the code execution starts.

Open the cursor. After declaring the cursor, we open it. We need to do this to proceed. This will run the SQL statement.

Start loop. The LOOP command starts a loop, and all of the code between this command and the upcoming END LOOP command are run until a condition is reached (which is specified later).

Fetch the first record. This is done after we open the cursor, and it's done using the FETCH command. We fetch the first record into the variables we have declared.

Exit if a condition is met. The EXIT WHEN command will instruct the code to exit the loop when test_cursor%NOTFOUND, which is an attribute of the cursor that is set when the next record is not found. It's saying that the loop will exit when all rows have been processed.

Print a line of text. The DBMS_OUTPUT.PUT_LINE statement will print a line of text to the IDE's output section. In this example we are printing each of the three variables, separated by spaces. We print the product_name, then the price, then the ID.

End loop. This indicates the end of the code that is being looped.

Close the cursor. Once the LOOP statement is no longer true (in this case, there are no more records in the cursor), then the cursor is closed.

End block. This final END statement relates to the earlier BEGIN statement, and is the end of the code.

The code is now completed.

This is a simple cursor that outputs the values from a table in lines of text. If you run this code, after creating and populating the table, you should see output like this:

1Statement processed.
2Toaster 20 (ID: 1)
3TV 600 (ID: 2)
4Couch 450 (ID: 3)
5Fridge 800 (ID: 4)
6Kettle 25 (ID: 5)
7Microwave 40 (ID: 6)

So that's a simple example of a cursor in Oracle SQL.

 

MySQL Cursor Example

Let's take a look at a cursor example in MySQL. Cursors are a little more restrictive in MySQL compared to Oracle and SQL Server:

  • Read-only: you can't update the data in the underlying table through the cursor
  • You can use cursors in stored procedures, functions, and triggers. You can't write an anonymous block of code.

Here's the sample data that we will use:

 1CREATE TABLE product (
 2  id INT,
 3  product_name VARCHAR(100),
 4  product_category_id INT,
 5  price INT
 6);
 7
 8INSERT INTO product (id, product_name, product_category_id, price)
 9VALUES (1, 'Toaster', 1, 20);
10INSERT INTO product (id, product_name, product_category_id, price)
11VALUES (2, 'TV', 2, 600);
12INSERT INTO product (id, product_name, product_category_id, price)
13VALUES (3, 'Couch', 2, 450);
14INSERT INTO product (id, product_name, product_category_id, price)
15VALUES (4, 'Fridge', 1, 800);
16INSERT INTO product (id, product_name, product_category_id, price)
17VALUES (5, 'Kettle', 1, 25);
18INSERT INTO product (id, product_name, product_category_id, price)
19VALUES (6, 'Microwave', 1, 40);

Now, here's the cursor, contained within a stored procedure:

 1DELIMITER $$
 2CREATE PROCEDURE cursor_demo()
 3BEGIN
 4DECLARE done INT DEFAULT FALSE;
 5    DECLARE currentID INT;
 6    DECLARE currentProductName VARCHAR(100);
 7    DECLARE currentPrice INT;
 8    DECLARE test_cursor CURSOR FOR
 9SELECT id, product_name, price
10FROM product;
11DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = true;
12OPEN test_cursor;    
13    getData: LOOP
14FETCH test_cursor INTO currentID, currentProductName, currentPrice;
15        IF done = TRUE THEN
16LEAVE getData;
17END IF;
18        SELECT CONCAT(currentProductName, ' ', currentPrice, ' (ID: ', currentID, ')');
19END LOOP getData;
20CLOSE test_cursor;
21END$$
22DELIMITER ;

We can then call the stored procedure using the CALL command:

1CALL cursor_demo();

So what does this code do?

Change the delimiter. In MySQL, the default delimiter between SQL statements is a semicolon. When running a stored procedure, MySQL will think that statements within the stored procedure are individual statements and not part of a stored procedure, and won't run them correctly.

So, to create a stored procedure, we change the delimiter to something that we won't use: in this case, a double dollar sign $$.

Create a procedure to contain the cursor. We create a new procedure called cursor_demo to create and run the code for our cursor.

Begin the procedure. The procedure begins with the BEGIN statement.

Declare a value for done. This is a variable to store whether or not the cursor has finished processing all records. There's code further down that applies this.

Declare variables for the data. These three variables (currentID, currentProductName, currentPrice) are used to store the id, product_name, and price, for each row returned. We only need one set of variables as we're only fetching one row of data at a time.

Declare the cursor. Next, we declare the cursor. It's called "test_cursor" and we specify the SELECT statement to use. In this example, we're selecting a few records from the product table.

Declare a Not Found handler. In MySQL, you need to declare a variable to handle the situation when the cursor does not find a row. This is called a Not Found Handler, and it's done using the line starting with DECLARE CONTINUE HANDLER. It says when there are no records found, set the done variable (which we declared earlier) to true.

Open the cursor. After declaring the cursor, we open it. We need to do this to proceed. This will run the SQL statement.

Start loop. The LOOP command starts a loop, and all of the code between this command and the upcoming END LOOP command is run until a condition is reached (which is specified later). The loop has a name called getData. This is named so that when we want to exit the loop, we can specify we want to exit the loop and not the entire procedure.

Fetch a record. This is done after we open the cursor, and it's done using the FETCH command. We fetch the first record into the variables we have declared.

Check if a condition is met. We run an IF statement to see if done is true. The variable of done will be set to true if no record is found.

Exit if a condition is met. If the IF statement is true, then there are no more records. The LEAVE getData statement is run which instructs the code to exit the loop.

Display some data. The SELECT statement is used to print a line of text to the IDE. In this example, we are printing each of the three variables, separated by spaces. We print the product_name, then the price, then the ID. MySQL doesn't have a console log output like Oracle and SQL Server have.

End loop. This indicates the end of the code that is being looped.

Close the cursor. Once the LOOP statement is no longer true (in this case, there are no more records in the cursor), then the cursor is closed.

End block. This final END statement relates to the earlier BEGIN statement, and is the end of the code. It uses the delimiter we specified, which is $$, to indicate that it's the end of the overall Create Procedure statement that needs to be run.

Change the delimiter to the original value. Now we have finished creating the procedure, we can change the delimiter back to the default value of a semicolon ;.

The code is now completed.

We can run the code using the CALL cursor_demo() statement as shown above.

This is a simple cursor that outputs the values from a table. If you run this code, after creating and populating the table, you should see output like this:

 1# CONCAT(currentProductName, ' ', currentPrice, ' (ID: ', currentID, ')')
 2Toaster 20 (ID: 1)
 3
 4# CONCAT(currentProductName, ' ', currentPrice, ' (ID: ', currentID, ')')
 5TV 600 (ID: 2)
 6
 7# CONCAT(currentProductName, ' ', currentPrice, ' (ID: ', currentID, ')')
 8Couch 450 (ID: 3)
 9
10# CONCAT(currentProductName, ' ', currentPrice, ' (ID: ', currentID, ')')
11Fridge 800 (ID: 4)
12
13# CONCAT(currentProductName, ' ', currentPrice, ' (ID: ', currentID, ')')
14Kettle 25 (ID: 5)
15
16# CONCAT(currentProductName, ' ', currentPrice, ' (ID: ', currentID, ')')
17Microwave 40 (ID: 6)

In MySQL Workbench, these are displayed as separate tabs in the output section, as they are separate SQL statements. But if you use cursors in your real code, you probably won't be writing separate SELECT statements like this demo.

So that's a simple example of a cursor in MySQL.

 

PostgreSQL Cursor Example (PL/pgSQL)

PostgreSQL also supports cursors. Let's take a look at an example.

Here's the sample data for the cursor:

 1CREATE TABLE product (
 2  id INT,
 3  product_name VARCHAR(100),
 4  product_category_id INT,
 5  price INT
 6);
 7
 8INSERT INTO product (id, product_name, product_category_id, price)
 9VALUES (1, 'Toaster', 1, 20);
10INSERT INTO product (id, product_name, product_category_id, price)
11VALUES (2, 'TV', 2, 600);
12INSERT INTO product (id, product_name, product_category_id, price)
13VALUES (3, 'Couch', 2, 450);
14INSERT INTO product (id, product_name, product_category_id, price)
15VALUES (4, 'Fridge', 1, 800);
16INSERT INTO product (id, product_name, product_category_id, price)
17VALUES (5, 'Kettle', 1, 25);
18INSERT INTO product (id, product_name, product_category_id, price)
19VALUES (6, 'Microwave', 1, 40);

Now here's the code to use the cursor:

 1CREATE OR REPLACE FUNCTION fn_test_cursor() RETURNS text
 2language plpgsql AS $$
 3DECLARE
 4  test_cursor CURSOR FOR
 5  SELECT id, product_name, price
 6  FROM product;
 7  currentID INT;
 8  currentProductName VARCHAR(100);
 9  currentPrice INT;
10BEGIN
11  OPEN test_cursor;
12  LOOP
13    FETCH test_cursor INTO currentID, currentProductName, currentPrice;
14    EXIT WHEN NOT FOUND;
15RAISE NOTICE '% % (ID: %)', currentProductName, currentPrice, currentID;
16  END LOOP;
17  CLOSE test_cursor;
18  RETURN 'Done';
19END $$;
20
21SELECT * FROM fn_test_cursor();

The code for a cursor in PostgreSQL works a little differently to other databases. Here's what the code is doing:

Create a function. We start by creating a function to contain the code for our cursor. This is an easy way to get the cursor created and able to call it.

Declare the cursor. We then declare the cursor, calling it test_cursor, and defining the SELECT query that's used.

Declare the variables to store the row data. We declare three variables here (currentID, currentProductName, and currentPrice) to store the row data. This could be stored in a single variable with a type that matches the table row, as an alternative.

Begin the execution code. The BEGIN statement starts the code execution.

Open the cursor. The next step is to open the cursor, which runs the SELECT statement.

Begin the loop. This will start a loop and execute all code between here and the END LOOP statement, until it exits.

Fetch a row of data into the variables. The FETCH command will get the first row of data from test_cursor and place the values into the three variables mentioned. For each loop, it will get the next row.

Exit the loop when no row is found. If no row is found, then the loop exits. This is to ensure the loop does not run indefinitely.

Output the data using RAISE NOTICE. In PostgreSQL, you can use the RAISE NOTICE command to output a message to the output panel. The % symbols indicate placeholders for variables, and there are three which match the number of variables mentioned.

End the loop. The END LOOP command will end the loop code and rerun the loop.

Close the cursor. The cursor will be closed and memory released to the application.

Return a text value. Because a text value needs to be returned from the function we return one here.

End the function. This is done by using the END statement.

Call the function to run it. Finally, we call the function by running it inside a SELECT command to see the output.

The output of this code is:

1Toaster 20 (ID: 1)
2TV 600 (ID: 2)
3Couch 450 (ID: 3)
4Fridge 800 (ID: 4)
5Kettle 25 (ID: 5)
6Microwave 40 (ID: 6)

These are the records from the table, written using the RAISE NOTICE command.

 

Other Possibilities for Cursors

The basic structure of a block of code that uses a cursor is the same. The code inside the loop of the cursor is where it changes.

Inside the loop, you can do many things, such as:

  • Check the value of some column using an IF statement and perform actions accordingly
  • Update another table with the values in this table
  • Modify the value of a variable (e.g. a running total, or a count of a specific type of value)
  • Insert records into another table
  • Call other procedures

These all use procedural code to perform the actions. You can find out more about the procedural code to do this in other posts or other resources online for your database:

  • Oracle: PL/SQL
  • SQL Server: T-SQL
  • MySQL: procedural code
  • PostgreSQL: PL/pgSQL

 

Cursor Advantages and Disadvantages

Cursors are a divisive topic in the world of SQL development. Many people suggest not using them at all, and others say they are good for a few reasons.

Personally, I avoid cursors where I can. I haven't come across a situation where I needed to use a cursor in quite a few years. Having said that, we all work in different environments, so let's take a look at some of the advantages and disadvantages of SQL cursors.

Advantages of SQL Cursors

Some advantages of SQL cursors are:

  • They are sometimes faster for calculating variables, such as a running total.
  • They are helpful for running stored procedures multiple times based on table data (e.g. space used for all tables).
  • They can help you control the size of the batch when you're working with a large number of rows.

Disadvantages of SQL Cursors

The disadvantages of using SQL cursors are:

  • They process results of a query row by row, making them much slower than processing the entire result set at once
  • They involve a lot of code for what they do
  • Alternatives can often be done in SQL or in a programming language, so are often misused
  • They encourage procedural or row by row programming, rather than set-based queries which is what SQL was designed for

 

FAQs about SQL Cursors

Here are some frequently asked questions about cursors in SQL.

Are cursors bad in SQL?

Cursors aren't bad, generally. But they are often misused by many developers, causing them to perform a task much slower than using alternative methods such as plain SQL.

How do I create a cursor in SQL?

You create a cursor by using the DECLARE statement, followed by the name of the cursor, then the SELECT statement. The syntax is slightly different in each database, but that's the general pattern.

How do I run a cursor?

A cursor is run by opening it with the OPEN command, and then you load the data using the FETCH command. This is generally done inside a loop so you can get all records.

What's the difference between a while loop and a cursor?

A cursor is a type of variable that stores a query and the results of that query. A while loop is a programming construct that allows you to repeat the same piece of code many times. They are often used together: use a while loop to analyse each row of data in a cursor.

Which is faster, a cursor or a while loop?

It's hard to tell, as it depends on your code, and they are often used together. There are ways you can test it though.

How can I speed up my cursor?

First, try to avoid using a cursor and use plain SQL instead. If you must use a cursor, try to avoid running SQL inside the cursor each time, and run it at the end.

What can be used instead of a cursor in SQL?

You can use plain SQL statements (Select, Insert, Update, Delete) in many cases to do the same thing. Combine them with joins, grouping, CASE statements, and other keywords to get what you want.

 

Conclusion

SQL cursors are a feature in many major database vendors. They are often misused, causing slow performance compared to the alternatives. However, they can be used well in some cases, and this article explains when they could be used and how to create them in Oracle, SQL Server, MySQL, and PostgreSQL.

comments powered by Disqus