MariaDB: 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 ('TechOnTheNet.com');
Advertisements