MySQL
ORACLE
POSTGRESQL
SQL
SQL SERVER

SQL FETCH: Retrieving Data In Database Cursors

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:

Copy
        
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:

Copy
        
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:

Copy
        
1 FETCH [ direction ] [ FROM | IN ] cursor_name

Where direction can be one of:

  • NEXT: Fetch the next row to the current one. This is the default value if direction is omitted.
  • PRIOR: Fetch the prior row to the current one.
  • FIRST: Fetch the first row of the query. This is the same as ABSOLUTE 1.
  • LAST: Fetch the last row of the query. This is the same as ABSOLUTE -1.
  • ABSOLUTE count: Fetch the countth row of the query or the abs(count)th row from the end if count is negative. ABSOLUTE 0 positions before the first row of the query.
  • RELATIVE count: Fetch the countth succeeding row or the abs(count)th prior row if count is negative. RELATIVE 0 re-fetches the current row, if any.
  • count: Fetch the next count rows. This is the same as FORWARD count.
  • ALL: Fetch all remaining rows. This is the same as FORWARD ALL.
  • FORWARD: Fetch the next row. This is the same as NEXT.
  • FORWARD count: Fetch the next count rows. FORWARD 0 re-fetches the current row.
  • FORWARD ALL: Fetch all remaining rows.
  • BACKWARD: Fetch the prior row. This is the same as PRIOR.
  • BACKWARD count: Fetch the prior count rows, scanning backward. BACKWARD 0 re-fetches the current row.
  • BACKWARD ALL: Fetch all prior rows, scanning backward.

Here is what a SQL FETCH sample statement looks like in PostgreSQL:

Copy
        
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:

Copy
        
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:

  • NEXT retrieves the row immediately following the current row. If it is the first fetch, it returns the first row in the result set.
  • PRIOR retrieves the row immediately preceding the current row. If it is the first fetch, no row is returned.
  • FIRST retrieves the first row in the cursor and makes it the current row.
  • LAST retrieves the last row in the cursor and makes it the current row.
  • ABSOLUTE {n|@nvar} retrieves the row n rows from the front or end of the cursor based on the sign of n. If n is 0, no rows are returned.
  • RELATIVE {n|@nvar} retrieves the row n rows beyond or prior to the current row based on the sign of n. If n is 0, returns the current row.
  • GLOBAL specifies that the cursor_name refers to a global cursor.
  • cursor_name is the name of the open cursor from which the fetch should be made.
  • @cursor_variable_name is the name of a cursor variable referencing the open cursor for fetching data.
  • INTO @variable_name[,...n] allows data from the columns of a fetch to be placed into local variables. Each variable is associated with a corresponding column in the cursor result set. The number of variables must match the number of columns in the cursor select list.

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:

Copy
        
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:

  1. Data processing in iterative operations: Operations where you need to process each row of a result set sequentially, like updating values or performing row-level operations in stored procedures or triggers.
  2. Data pagination: Retrieving a chunk of data at a time with specific FETCH SQL statements, helping manage large result sets efficiently.
  3. Cursor-based row navigation: Navigating through the result set in various ways, such as moving to the next or previous row, jumping to the first or last row, or selecting a specific row based on its position.

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:

Copy
        
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:

  • Employ cursors judiciously, as they can impact performance negatively for large result sets.
  • Reduce FETCH operations by fetching multiple rows at once.
  • Ensure efficient indexing and SQL query optimization techniques to minimize the time spent fetching data.
  • Always close cursors and query connections after use to release database resources promptly.
Dbvis download link img
About the author
Antonello Zanini

Antonello is a software engineer, and often refers to himself as a technology bishop. His mission is to spread knowledge through writing.

The Table Icon
Sign up to receive The Table's roundup
More from the table
Title Author Tags Length Published
title

Counter in MySQL: Counting Rows with Ease

author Lukas Vileikis tags MySQL SQL 8 min 2024-10-03
title

PostgreSQL Subquery - A Guide

author Leslie S. Gyamfi tags POSTGRESQL SUBQUERY 3 min 2024-10-02
title

A Complete Guide to the SQL Server FOR XML PATH Clause

author Antonello Zanini tags SQL SERVER XML 8 min 2024-10-01
title

SQL OFFSET: Skipping Rows in a Query

author Antonello Zanini tags MySQL ORACLE POSTGRESQL SQL SQL SERVER 7 min 2024-09-30
title

The MySQL RENAME COLUMN Statement: How, What, and Why

author Lukas Vileikis tags MySQL SQL 6 min 2024-09-26
title

A Guide to the Postgres Not Null Constraint

author Leslie S. Gyamfi tags POSTGRESQL 3 min 2024-09-25
title

A Complete Guide to the SQL Server COALESCE Function

author Antonello Zanini tags SQL SERVER 6 min 2024-09-23
title

SQL DATEPART: Get a Part of a Date in SQL Server

author Antonello Zanini tags SQL SERVER 8 min 2024-09-19
title

MySQL Binary Logs – Walkthrough

author Lukas Vileikis tags Binary Log MySQL 6 min 2024-09-18
title

MySQL SHOW TABLES Statement: What it is, How It Works, What It Means for You

author Lukas Vileikis tags MySQL 5 min 2024-09-16

The content provided on dbvis.com/thetable, including but not limited to code and examples, is intended for educational and informational purposes only. We do not make any warranties or representations of any kind. Read more here.

Cookie policy

We use cookies to ensure that we give you the best experience on our website. However you can change your cookie settings at any time in your browser settings. Please find our cookie policy here ↗