Tutorial Addendum on SQL - MySQL - Stored Action Accent

Cursors

    

A cursor is a data blazon that represents a affiliation handle to the achievement table of a baddest statement.

    

A cursor is actual agnate to a book handle in some additional programming languages.

    

There are several statements accompanying to the use of a cursor:

    

DECLARE account - To acknowledge a cursor capricious and accessory it to a baddest statement:

    

    
DECLARE cursor_variable CURSOR FOR select_statement;

    

OPEN account - To assassinate the associated baddest account and set the cursor to point to

    

the first row of the achievement table:

    

    
OPEN cursor_variable;

    

FETCH account - To back data from the accepted row into accepting variables, and set the

    

cursor to point to the next row of the achievement table:

    

    
FETCH cursor_variable INTO variable, variable, ...;

    

CLOSE account - To abutting the cursor:

    

    
CLOSE cursor_variable;

    

When a cursor alcove the end of the achievement table, the Back account will aftereffect in

    

an beheading error: 02000. But this absurdity can be adapted into a analytic action by

    

a "continue handler":

    

    
DECLARE EndOfData INTEGER;

    
DECLARE Abide Abettor FOR SQLSTATE  02000  SET EndOfData = 1;

    

Here is sample cipher that uses a cursor to bend through anniversary row of a table and calculate

    

the minimum amount and best value:

    

 

    
-- ProcedureCursor.sql

    
-- Absorb (c) 2004 by Dr.  Yang

    
--

    
DROP2 DATABASE IF EXISTS HyTest;

    
CREATE DATABASE HyTest;

    
USE HyTest;

    
--

    
DROP Action IF EXISTS InitTable;

    
DELIMITER  / ;

    
CREATE Action InitTable(IN N INTEGER)

    
BEGIN

    
   Acknowledge I INTEGER;

    
   SET I = 0;

    
   WHILE I < N DO

    
      Admit INTO MyTable Ethics (I, RAND()*N);

    
      SET I = I + 1;

    
   END WHILE;

    
END/

    
DELIMITER  ; /

    
--

    
DROP Action IF EXISTS CheckTable;

    
DELIMITER  / ;

    
CREATE Action CheckTable(OUT Admeasurement INTEGER, 

    
   OUT Min INTEGER, OUT Max INTEGER)

    
BEGIN

    
   Acknowledge K, V INTEGER;

    
   Acknowledge HasData INTEGER;

    
   Acknowledge Aftereffect CURSOR FOR Baddest ID, Amount FROM MyTable; 

    
   Acknowledge Abide Abettor FOR SQLSTATE  02000  SET HasData = 0;

    
   Accessible Result;

    
   SET Admeasurement = 0;

    
   SET Min = 999999;

    
   SET Max = -999999;

    
   SET HasData = 1;

    
   Back Aftereffect INTO K, V;

    
   WHILE HasData = 1 DO

    
      SET Admeasurement = Admeasurement + 1;

    
      IF V < Min THEN

    
         SET Min = V;

    
      END IF;

    
      IF V > Max THEN

    
         SET Max = V;

    
      END IF;

    
      Back Aftereffect INTO K, V;

    
   END WHILE;

    
   Abutting Result;

    
END/

    
DELIMITER  ; /

    
--

    
DROP TABLE IF EXISTS MyTable;

    
CREATE TABLE MyTable (ID INTEGER, Amount INTEGER);

    
CALL InitTable(20);

    
CALL CheckTable(@Size, @Min, @Max);

    
--

    
SELECT  Table summary:  AS  --- ;

    
SELECT @Size AS  Admeasurement , @Min AS  Minimum , @Max AS  Best ;

    
SELECT  Table detail :  AS  --- ;

    
SELECT * FROM MyTable Area 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