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.
CREATE TABLE product (
id INT,
product_name VARCHAR(100),
product_category_id INT,
price INT
);
INSERT INTO product (id, product_name, product_category_id, price)
VALUES (1, 'Toaster', 1, 20);
INSERT INTO product (id, product_name, product_category_id, price)
VALUES (2, 'TV', 2, 600);
INSERT INTO product (id, product_name, product_category_id, price)
VALUES (3, 'Couch', 2, 450);
INSERT INTO product (id, product_name, product_category_id, price)
VALUES (4, 'Fridge', 1, 800);
INSERT INTO product (id, product_name, product_category_id, price)
VALUES (5, 'Kettle', 1, 25);
INSERT INTO product (id, product_name, product_category_id, price)
VALUES (6, 'Microwave', 1, 40);Now, we’ll create our cursor code.
DECLARE @currentID INT, @currentProductName VARCHAR(100), @currentPrice INT;
DECLARE test_cursor CURSOR FOR
SELECT id, product_name, price
FROM product;
OPEN test_cursor;
FETCH NEXT FROM test_cursor INTO @currentID, @currentProductName, @currentPrice;
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @currentProductName + ' ' + CAST(@currentPrice AS VARCHAR) + ' (ID: '+ CAST(@currentID AS VARCHAR) +')';
FETCH NEXT FROM test_cursor INTO @currentID, @currentProductName, @currentPrice;
END;
CLOSE test_cursor;
DEALLOCATE 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:
Started executing query at Line 2 Toaster 20 (ID: 1) TV 600 (ID: 2) Couch 450 (ID: 3) Fridge 800 (ID: 4) Kettle 25 (ID: 5) Microwave 40 (ID: 6) Total 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:
CREATE TABLE product (
id NUMBER,
product_name VARCHAR2(100),
product_category_id NUMBER,
price NUMBER
);
INSERT INTO product (id, product_name, product_category_id, price)
VALUES (1, 'Toaster', 1, 20);
INSERT INTO product (id, product_name, product_category_id, price)
VALUES (2, 'TV', 2, 600);
INSERT INTO product (id, product_name, product_category_id, price)
VALUES (3, 'Couch', 2, 450);
INSERT INTO product (id, product_name, product_category_id, price)
VALUES (4, 'Fridge', 1, 800);
INSERT INTO product (id, product_name, product_category_id, price)
VALUES (5, 'Kettle', 1, 25);
INSERT INTO product (id, product_name, product_category_id, price)
VALUES (6, 'Microwave', 1, 40);Now here’s the cursor example:
DECLARE
currentID NUMBER;
currentProductName VARCHAR2(100);
currentPrice NUMBER;
CURSOR test_cursor IS
SELECT id, product_name, price
FROM product;
BEGIN
OPEN test_cursor;
LOOP
FETCH test_cursor INTO currentID, currentProductName, currentPrice;
EXIT WHEN test_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(currentProductName || ' ' || currentPrice || ' (ID: ' || currentID || ')');
END LOOP;
CLOSE test_cursor;
END;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:
Statement processed. Toaster 20 (ID: 1) TV 600 (ID: 2) Couch 450 (ID: 3) Fridge 800 (ID: 4) Kettle 25 (ID: 5) Microwave 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:
CREATE TABLE product (
id INT,
product_name VARCHAR(100),
product_category_id INT,
price INT
);
INSERT INTO product (id, product_name, product_category_id, price)
VALUES (1, 'Toaster', 1, 20);
INSERT INTO product (id, product_name, product_category_id, price)
VALUES (2, 'TV', 2, 600);
INSERT INTO product (id, product_name, product_category_id, price)
VALUES (3, 'Couch', 2, 450);
INSERT INTO product (id, product_name, product_category_id, price)
VALUES (4, 'Fridge', 1, 800);
INSERT INTO product (id, product_name, product_category_id, price)
VALUES (5, 'Kettle', 1, 25);
INSERT INTO product (id, product_name, product_category_id, price)
VALUES (6, 'Microwave', 1, 40);Now, here’s the cursor, contained within a stored procedure:
DELIMITER $$
CREATE PROCEDURE cursor_demo()
BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE currentID INT;
DECLARE currentProductName VARCHAR(100);
DECLARE currentPrice INT;
DECLARE test_cursor CURSOR FOR
SELECT id, product_name, price
FROM product;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = true;
OPEN test_cursor;
getData: LOOP
FETCH test_cursor INTO currentID, currentProductName, currentPrice;
IF done = TRUE THEN
LEAVE getData;
END IF;
SELECT CONCAT(currentProductName, ' ', currentPrice, ' (ID: ', currentID, ')');
END LOOP getData;
CLOSE test_cursor;
END$$
DELIMITER ;We can then call the stored procedure using the CALL command:
CALL 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:
# CONCAT(currentProductName, ' ', currentPrice, ' (ID: ', currentID, ')') Toaster 20 (ID: 1) # CONCAT(currentProductName, ' ', currentPrice, ' (ID: ', currentID, ')') TV 600 (ID: 2) # CONCAT(currentProductName, ' ', currentPrice, ' (ID: ', currentID, ')') Couch 450 (ID: 3) # CONCAT(currentProductName, ' ', currentPrice, ' (ID: ', currentID, ')') Fridge 800 (ID: 4) # CONCAT(currentProductName, ' ', currentPrice, ' (ID: ', currentID, ')') Kettle 25 (ID: 5) # CONCAT(currentProductName, ' ', currentPrice, ' (ID: ', currentID, ')') Microwave 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:
CREATE TABLE product (
id INT,
product_name VARCHAR(100),
product_category_id INT,
price INT
);
INSERT INTO product (id, product_name, product_category_id, price)
VALUES (1, 'Toaster', 1, 20);
INSERT INTO product (id, product_name, product_category_id, price)
VALUES (2, 'TV', 2, 600);
INSERT INTO product (id, product_name, product_category_id, price)
VALUES (3, 'Couch', 2, 450);
INSERT INTO product (id, product_name, product_category_id, price)
VALUES (4, 'Fridge', 1, 800);
INSERT INTO product (id, product_name, product_category_id, price)
VALUES (5, 'Kettle', 1, 25);
INSERT INTO product (id, product_name, product_category_id, price)
VALUES (6, 'Microwave', 1, 40);Now here’s the code to use the cursor:
CREATE OR REPLACE FUNCTION fn_test_cursor() RETURNS text
language plpgsql AS $$
DECLARE
test_cursor CURSOR FOR
SELECT id, product_name, price
FROM product;
currentID INT;
currentProductName VARCHAR(100);
currentPrice INT;
BEGIN
OPEN test_cursor;
LOOP
FETCH test_cursor INTO currentID, currentProductName, currentPrice;
EXIT WHEN NOT FOUND;
RAISE NOTICE '% % (ID: %)', currentProductName, currentPrice, currentID;
END LOOP;
CLOSE test_cursor;
RETURN 'Done';
END $$;
SELECT * 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:
Toaster 20 (ID: 1) TV 600 (ID: 2) Couch 450 (ID: 3) Fridge 800 (ID: 4) Kettle 25 (ID: 5) Microwave 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.
can u give some task ?
What happens if the database is modified via another connection while looping through the rows via a cursor? That is, if someone else inserts a new row, might the cursor return it? If someone else deletes a row, will we get it or not?
(I’m trying to figure out how to best scroll through results in a mobile app, grabbing a few dozen rows at a time as the user scrolls)
Good question. Assuming the other connection commits the changes, then the cursor may retrieve the data. I think it depends on the “isolation level” of the session that the cursor is running in. It may be set to “read committed” which means it will get the changes. I think there’s another setting that will only get data that the session could see at the start, so it would ignore the committed changes.