mysql read loop_mysql数据库游标的使用

Mysql在5.0版本支持在存储过程中使用游标。

游标声明必须出现在处理程序声明变量和条件的声明后。

游标的使用如下：

CREATE PROCEDURE curdemo()

BEGIN

DECLARE done INT DEFAULT FALSE;

DECLARE a CHAR(16);

DECLARE b, c INT;

DECLARE cur1 CURSOR FOR SELECT id,data FROM test.t1;

DECLARE cur2 CURSOR FOR SELECT i FROM test.t2;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

OPEN cur1;

OPEN cur2;

read_loop: LOOP

FETCH cur1 INTO a, b;

FETCH cur2 INTO c;

IF done THEN

LEAVE read_loop;

END IF;

IF b < c THEN

INSERT INTO test.t3 VALUES (a,b);

ELSE

INSERT INTO test.t3 VALUES (a,c);

END IF;

END LOOP;

CLOSE cur1;

CLOSE cur2;

END;

上面的例子是从官方提供的文档上找到的.

CREATE TABLE test(

id INT AUTO_INCREMENT PRIMARY KEY,

product_name VARCHAR(50),

product_type INT

);

INSERT INTO test(product_name,product_type)VALUES('mobile',1);

INSERT INTO test(product_name,product_type)VALUES('computer',1);

INSERT INTO test(product_name,product_type)VALUES('apple',2);

INSERT INTO test(product_name,product_type)VALUES('Orange',2);

CREATE TABLE test_temp(

id INT,

product_name VARCHAR(50)

);

下面的是有游标的存储过程

DELIMITER $$

CREATE PROCEDURE curdemo()

BEGIN

DECLARE done BOOLEAN DEFAULT FALSE;

DECLARE _id INT;

DECLARE _product_name VARCHAR(50);

DECLARE cur CURSOR FOR SELECT id,product_name FROM test WHERE product_type=1;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

OPEN cur;

read_loop: LOOP

FETCH cur INTO _id,_product_name;

IF done THEN

LEAVE read_loop;

END IF;

INSERT INTO test_temp (id,product_name)VALUES(_id,_product_name);

END LOOP;

CLOSE cur;

END$$

DELIMITER;

调用存储过程

CALL curdemo;

查看运行结果

SELECT * FROM test_temp;

运行结果为:

idproduct_name

1mobile

2computer

此存储过程是可以运行的,结果也是正确的,下面稍微修改一下存储过程.

DELIMITER $$

CREATE PROCEDURE curdemo()

BEGIN

DECLARE done BOOLEAN DEFAULT FALSE;

DECLARE _id INT;

DECLARE _product_name VARCHAR(50);

DECLARE cur CURSOR FOR SELECT id,product_name FROM test WHERE product_type=1;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

OPEN cur;

read_loop: LOOP

FETCH cur INTO _id,_product_name;

IF done THEN

LEAVE read_loop;

END IF;

SELECT product_type INTO @product_type FROM test WHERE product_type = 3;

INSERT INTO test_temp (id,product_name)VALUES(_id,_product_name);

END LOOP;

CLOSE cur;

END$$

DELIMITER;

清空test_temp表数据

TRUNCATE TABLE test_temp;

再次调用存储过程后,

CALL curdemo;

SELECT * FROM test_temp;

运行结果为:

idproduct_name

1mobile

为什么少了一行数据,不就是多加了一条select 语句嘛

原因很简单,就是SELECT product_type INTO @product_type FROM test WHERE product_type = 3;

抛出了一个not found的异常,从而使得游标的循环终止了.

很多时候在游标循环的过程体中,调用了其他存储过程,但是我们不知道其他存储过程会不会抛出not found,我们的目的是循环完游标,怎么解决?

一种解决方法如下:

DELIMITER $$

CREATE PROCEDURE curdemo()

BEGIN

DECLARE done BOOLEAN DEFAULT FALSE;

DECLARE _id INT;

DECLARE _product_name VARCHAR(50);

DECLARE cur CURSOR FOR SELECT id,product_name FROM test WHERE product_type=1;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

OPEN cur;

read_loop: LOOP

SET done = FALSE;

FETCH cur INTO _id,_product_name;

IF done THEN

LEAVE read_loop;

END IF;

SELECT product_type INTO @product_type FROM test WHERE product_type = 3;

INSERT INTO test_temp (id,product_name)VALUES(_id,_product_name);

END LOOP;

CLOSE cur;

END$$

DELIMITER;

运行结果正常.