MySQL - Stored Procedure Language

Part:   1  2  3  

(Continued from previous part...)

Cursors

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) 2004 by Dr. Herong Yang
--
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

Part:   1  2  3