写了一个简单的对多游标控制的存储过程。
此例是示范游标的具体使用方法。
不过生产过程中不推荐使用游标。因为完全可以用INTO 变量语句和循环来代替游标。
1、SP 代码：
|
DELIMITER $$
DROP PROCEDURE IF EXISTS `test2`.`sp_cur_demo`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_cur_demo`()
BEGIN
-- Variants declare
DECLARE i INT DEFAULT 0;
DECLARE cnt INT DEFAULT 0;
DECLARE a,b INT;
DECLARE c CHAR(1);
-- Define cursor
-- Notice:Cursor's declaration must be after ordinary variant.
DECLARE cur1 CURSOR FOR SELECT a1 from t1;
DECLARE cur2 CURSOR FOR SELECT a2 from t1;
DECLARE cur3 CURSOR FOR SELECT a3 from t1;
-- Fetch total number of the table.
SELECT count(1) FROM t1 INTO cnt;
DROP TABLE tmp;
-- Create a temporary table to store the results.
CREATE TEMPORARY TABLE IF NOT EXISTS tmp(cur_c1 int, cur_c2 int,cur_c3 char(1));
OPEN cur1;
OPEN cur2;
OPEN cur3;
-- Begin fetch data into a new temporary table.
WHILE i < cnt DO
FETCH cur1 INTO a;
FETCH cur2 INTO b;
FETCH cur3 INTO c;
INSERT INTO tmp SELECT a,b,c;
SET i = i + 1;
END WHILE;
CLOSE cur1;
CLOSE cur2;
CLOSE cur3;
END$$
DELIMITER ;
|
2、测试结果：
|
select * from t1;
call sp_cur_demo();
select * from tmp;
query result(7 records)
| a1 |
a2 |
a3 |
| 1 |
1 |
A |
| 2 |
2 |
B |
| 3 |
2 |
C |
| 4 |
2 |
D |
| 5 |
3 |
E |
| 6 |
3 |
F |
| 7 |
4 |
G |
query result(7 records)
| cur_c1 |
cur_c2 |
cur_c3 |
| 1 |
1 |
A |
| 2 |
2 |
B |
| 3 |
2 |
C |
| 4 |
2 |
D |
| 5 |
3 |
E |
| 6 |
3 |
F |
| 7 |
4 |
G |
|
本文转自 david_yeung 51CTO博客，原文链接：http://blog.51cto.com/yueliangdao0608/81279，如需转载请自行联系原作者
