Declare a Cursor

This MariaDB tutorial explains how to declare a cursor in MariaDB with syntax and examples.

Description

A cursor is a SELECT statement that is defined within the declaration section of your stored program in MariaDB.

Syntax

The syntax to declare a cursor in MariaDB is:

DECLARE cursor_name CURSOR FOR

  select_statement;

Parameters or Arguments

cursor_name

The name to assign to the cursor.

select_statement

The SELECT statement associated with the cursor.

Example

Let's look at how to declare a cursor in MariaDB.

For example:

DECLARE c1 CURSOR FOR

  SELECT SUM(file_size)

  FROM pages

  WHERE site_name = name_in;

The result set of this cursor is all site_id values where the site_name matches the name_in variable.

Below is a function that uses this cursor.

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 ;

You could then call your new function (that contains the cursor) as follows:

SELECT FindSize ('Fastread.aitechtonic.com');