MySQL Tutorials - Herong's Tutorial Examples - Version 4.20, by Dr. Herong Yang
DECLARE ... CURSOR FOR Select Statements
This section describes cursors used in stored procedures to represent a connection to the output table of a select statement.
A cursor is a data type that represents a connection handle to the output table of a select statement. A cursor is very similar to a file handle in many other programming languages. There are several statements related to the use of a cursor:
DECLARE statement - To declare a cursor variable and associate it to a select statement:
DECLARE cursor_variable CURSOR FOR select_statement;
OPEN statement - To execute the associated select statement and set the cursor to point to the first row of the output table:
OPEN cursor_variable;
FETCH statement - To fetch data from the current row into receiving variables, and set the cursor to point to the next row of the output table:
FETCH cursor_variable INTO variable, variable, ...;
CLOSE statement - To close the cursor:
CLOSE cursor_variable;
When a cursor reaches the end of the output table, the FETCH statement will result in an execution error: 02000. But this error can be converted into a logical condition by a "continue handler":
DECLARE EndOfData INTEGER; DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET EndOfData = 1;
Here is sample code that uses a cursor to loop through each row of a table and calculate the minimum value and maximum value:
-- ProcedureCursor.sql -- Copyright (c) 2015, HerongYang.com, All Rights Reserved. -- DROP DATABASE IF EXISTS HyTest; CREATE DATABASE HyTest; USE HyTest; -- DROP PROCEDURE IF EXISTS InitTable; DELIMITER '/'; CREATE PROCEDURE InitTable(IN N INTEGER) BEGIN DECLARE I INTEGER; SET I = 0; WHILE I < N DO INSERT INTO MyTable VALUES (I, RAND()*N); SET I = I + 1; END WHILE; END/ DELIMITER ';'/ -- DROP PROCEDURE IF EXISTS CheckTable; DELIMITER '/'; CREATE PROCEDURE CheckTable(OUT Size INTEGER, OUT Min INTEGER, OUT Max INTEGER) BEGIN DECLARE K, V INTEGER; DECLARE HasData INTEGER; DECLARE Result CURSOR FOR SELECT ID, Value FROM MyTable; DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET HasData = 0; OPEN Result; SET Size = 0; SET Min = 999999; SET Max = -999999; SET HasData = 1; FETCH Result INTO K, V; WHILE HasData = 1 DO SET Size = Size + 1; IF V < Min THEN SET Min = V; END IF; IF V > Max THEN SET Max = V; END IF; FETCH Result INTO K, V; END WHILE; CLOSE Result; END/ DELIMITER ';'/ -- DROP TABLE IF EXISTS MyTable; CREATE TABLE MyTable (ID INTEGER, Value INTEGER); CALL InitTable(20); CALL CheckTable(@Size, @Min, @Max); -- SELECT 'Table summary:' AS '---'; SELECT @Size AS 'Size', @Min AS 'Minimum', @Max AS 'Maximum'; SELECT 'Table detail :' AS '---'; SELECT * FROM MyTable WHERE ID < 20;
Output:
--- Table summary: Size Minimum Maximum 20 6 19 --- Table detail : ID Value 0 11 1 6 2 15 3 19 4 10 5 12 6 10 7 16 8 8 9 14 10 7 11 12 12 18 13 14 14 15 15 13 16 19 17 19 18 15 19 18
Last update: 2015.
Table of Contents
MySQL Introduction and Installation
Introduction of MySQL Programs
Perl Programs and MySQL Servers
PHP Programs and MySQL Servers
Java Programs and MySQL Servers
Character Strings and Bit Strings
Table Column Types for Different Types of Values
Using DDL to Create Tables and Indexes
Using DML to Insert, Update and Delete Records
Using SELECT to Query Database
Transaction Management and Isolation Levels
Defining and Calling Stored Procedures
►Variables, Loops and Cursors Used in Stored Procedures
Local Variables in Stored Procedures
Execution Flow Control Statements
ITERATE and LEAVE - Statements to Break Loops