intro
Let’s see what the SQL FETCH statement is and how it works to master data retrieval within database cursors.
In SQL, cursors are a powerful tool for sequential access to query results. Have you ever wondered how to retrieve data in cursors for sequential manipulation? The answer is SQL FETCH! This statement allows you to retrieve data in database cursors, opening the door to row-by-row processing of result sets.
In this guide, you will find out what the FETCH SQL statement is, where you can use it, and how to use it in popular database technologies.
Let’s dive in!
What is the FETCH SQL Statement?
When it comes to cursors, the FETCH SQL statement supports the retrieval of rows from the result set of a query, one at a time. If you are not familiar with cursor, this is a database object that allows you to iterate over the rows returned by a query. Typically, cursors are used within stored procedures, SQL triggers, and functions to process individual rows one at a time.
You can think of a cursor as a virtual magnifying glass hovering over a table of data. With each movement, it zooms in on one row at a time, providing the ability to inspect the details closely before moving on to the next row.
Thus, the SQL FETCH statement retrieves rows using a cursor created previously. While FETCH is part of the SQL standard, each database implements it with its own extensions and variations. For more information, refer to the official documentation page of your database.
Time to explore the syntax of this useful clause!
How to Use FETCH in SQL Cursors: Syntax and Initial Examples
Explore how to use the SQL FETCH clause within cursors in MySQL, SQL Server, PostgreSQL, and Oracle.
MySQL
This is what the syntax of the MySQL FETCH clause in cursors looks like:
1
FETCH [[NEXT] FROM] cursor_name INTO var_name_1, ..., var_name_n
The above statement fetches the next row for the SELECT statement associated with the specified cursor and advances the cursor pointer. Note that the cursor must be OPEN for FETCH to work. If a row exists, the fetched columns are stored in the specified named variables.
The number of columns retrieved by the SELECT statement must match the number of output variables specified in the FETCH statement, as in the example below:
1
-- stored procedure/trigger/function definition ...
2
DECLARE book_cursor CURSOR FOR
3
SELECT title, author, price
4
FROM books;
5
6
OPEN book_cursor;
7
8
FETCH NEXT FROM book_cursor INTO @title, @author, @price;
9
10
WHILE @@FETCH_STATUS = 0 DO
11
-- Process the fetched data, maybe update the price
12
-- For example, you might increase the price of books by 10%
13
UPDATE books SET price = @price * 1.1 WHERE title = @title;
14
15
-- Fetch the next book
16
FETCH NEXT FROM book_cursor INTO @title, @author, @price;
17
END WHILE;
18
19
CLOSE book_cursor;
PostgreSQL
The PostgreSQL FETCH statement supports several directions:
1
FETCH [ direction ] [ FROM | IN ] cursor_name
Where direction can be one of:
Here is what a SQL FETCH sample statement looks like in PostgreSQL:
1
BEGIN WORK;
2
3
-- define a cursor
4
DECLARE movie_cursor SCROLL CURSOR FOR
5
SELECT * FROM movies;
6
7
-- fetch the first 5 rows in the cursor
8
FETCH FORWARD 5 FROM movie_cursor;
9
10
-- ...
11
12
-- fetch the previous row
13
FETCH PRIOR FROM movie_cursor;
14
15
-- ...
16
17
-- close the cursor and end the transaction:
18
CLOSE movie_cursor;
19
COMMIT WORK;
SQL Server
The SQL Server FETCH clause has a complex with several options:
1
FETCH
2
[ [ NEXT | PRIOR | FIRST | LAST
3
| ABSOLUTE { n | @nvar }
4
| RELATIVE { n | @nvar }
5
]
6
FROM
7
]
8
{ { [ GLOBAL ] cursor_name } | @cursor_variable_name }
9
[ INTO @variable_name [ ,...n ] ]
Where:
Note that if SCROLL option is not specified in the DECLARE CURSOR statement, SQL Server's FETCH only supports the NEXT option.
See FETCH in action in the following example:
1
USE ECommerceDB;
2
GO
3
-- declare the variables to store the values returned by FETCH.
4
DECLARE @LastName VARCHAR(50), @FirstName VARCHAR(50);
5
6
DECLARE customer_cursor CURSOR FOR
7
SELECT LastName, FirstName FROM Customers
8
WHERE LastName LIKE 'S%'
9
ORDER BY LastName, FirstName;
10
11
OPEN customer_cursor;
12
13
-- perform the first fetch and store the values in variables
14
FETCH NEXT FROM contact_cursor
15
INTO @LastName, @FirstName;
16
17
-- check @@FETCH_STATUS to see if there are any more rows to fetch
18
WHILE @@FETCH_STATUS = 0
19
BEGIN
20
-- concatenate and display the current values in the variables
21
PRINT 'Customer Name: ' + @FirstName + ' ' + @LastName
22
23
-- fetch new data
24
FETCH NEXT FROM contact_cursor
25
INTO @LastName, @FirstName;
26
END
27
28
CLOSE contact_cursor;
29
DEALLOCATE contact_cursor;
30
GO
This combines the data read from a Customers table into a single string and returns them to the client.
Oracle
The SQL FETCH statement in Oracle retrieves rows of data from the result set of a multi-row query. In detail, it can fetch rows one at a time, several at a time, or all at once. The data is stored in variables or fields that correspond to the columns selected by the query.
For the syntax GIF and simple examples, explore the official documentation.
SQL FETCH: Use Cases
Here are some of the three most popular use cases for FETCH in SQL:
Conclusion
In this article, you understood what the FETCH SQL clause is, which databases support it, and how it works. Now you know that it helps you retrieve a chunk of rows at a time in SQL cursors. Thanks to the use cases explored here, you also understood when to use FETCH in SQL.
Complex data involving cursors becomes easier with a database client. DbVisualizer is a powerful database client that supports many DBMS technologies, comes with advanced query optimization functionality, and can generate ERD-like schemas with a single click. Try DbVisualizer for free today!
FAQ
What is a cursor in SQL?
In SQL, a cursor is a database object used to traverse the result set of a query one row at a time. It allows sequential access to the data retrieved from a SELECT statement, enabling row-level processing. Cursors are particularly useful in scenarios where you need to perform specific tasks on individual rows, such as data manipulation, validation, or calculation, before moving to the next row.
Where can the FETCH SQL statement be used?
The FETCH SQL statement can generally be used in stored procedures, triggers, and other database routines. Explore the official documentation of your DBMS for more details.
What is the SQL OFFSET-FETCH clause?
The SQL OFFSET-FETCH clause is used to implement pagination in queries. In particular, it is a standardized way of retrieving a subset of rows from a result set:
1
SELECT column_1, column_2, ... column_n
2
FROM table_name
3
OFFSET {number_of_rows} ROWS
4
FETCH {FIRST | NEXT} {number_of_rows} ROWS ONLY;
OFFSET specifies the number of rows to skip from the beginning of the result set, while FETCH specifies the number of rows to return after skipping the offset. This feature is similar to the LIMIT-OFFSET syntax supported by some databases.
What is the difference between FETCH and LIMIT in SQL?
The FETCH OFFSET and LIMIT OFFSET SQL statements share the same goal of retrieving paginated data but differ in terms of syntax and database support. FETCH OFFSET comes from standard SQL, while LIMIT is available only in some DBMS technologies like MySQL and PostgreSQL.
What are some best practices for FETCH in SQL?
Here are some SQL FETCH best practices:
