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;
运行结果正常.
