FETCH Statement

This MariaDB tutorial explains how to use the FETCH statement to fetch the next row for a cursor in MariaDB with syntax and examples.

Description

The purpose of using a cursor, in most cases, is to retrieve the rows from your cursor so that some type of operation can be performed on the data. After declaring and opening your cursor, the next step is to use the FETCH statement to fetch rows from your cursor.

Syntax

The syntax for the FETCH statement in MariaDB is:

FETCH [ NEXT [ FROM ] ] cursor_name INTO variable_list;

Parameters or Arguments

cursor_name

The name of the cursor that you wish to fetch rows.

variable_list

The list of variables, comma separated, that you wish to store the cursor result set in.

Example

Let's look at how to fetch the next row for a cursor using the FETCH statement in MariaDB.

For example, you could have a cursor defined in MariaDB as follows:

DECLARE c1 CURSOR FOR

  SELECT SUM(file_size)

  FROM pages

  WHERE site_name = name_in;

The command that would be used to fetch the data from this cursor is:

FETCH c1 INTO TotalSize;

This would fetch the SUM(file_size) value into the variable called TotalSize.

Below is a function that demonstrates how to use the FETCH statement.

DELIMITER //

 

CREATE FUNCTION FindSize ( name_in VARCHAR(50) )

RETURNS INT READS SQL DATA

 

BEGIN

 

   DECLARE done INT DEFAULT FALSE;

   DECLARE TotalSize INT DEFAULT 0;

 

   DECLARE c1 CURSOR FOR

     SELECT SUM(file_size)

     FROM pages

     WHERE site_name = name_in;

 

   DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

 

   OPEN c1;

   FETCH c1 INTO TotalSize;

 

   CLOSE c1;

 

   RETURN TotalSize;

 

END; //

 

DELIMITER ;