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

 About This Book

 Introduction of SQL

 MySQL Introduction and Installation

 Introduction of MySQL Programs

 Perl Programs and MySQL Servers

 PHP Programs and MySQL Servers

 Java Programs and MySQL Servers

 Datatypes and Data Literals

 Operations and Expressions

 Character Strings and Bit Strings

 Commonly Used Functions

 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

 Locks Used in MySQL

 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

DECLARE ... CURSOR FOR Select Statements

 Outdated Tutorials

 References

 PDF Printing Version