[原创]写了一个简单的对多游标控制的存储过程

写了一个简单的对多游标控制的存储过程。
此例是示范游标的具体使用方法。
不过生产过程中不推荐使用游标。因为完全可以用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