CREATE PROCEDURE 存储过程名称()
BEGIN
DECLARE column 数据类型 DEFAULT 默认值;
DECLARE column1 数据类型 DEFAULT 默认值;
DECLARE sum INT DEFAULT 0;
DECLARE count INT DEFAULT 0;
DECLARE done INT DEFAULT 0;
DECLARE cur1 CURSOR FOR (SELECT * FROM a );
DECLARE cur2 CURSOR FOR (SELECT * FROM b);
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = null;
SELECT count(*) FROM b INTO count;
OPEN cur1;
FETCH cur1 INTO culumn[,column1,column2];
WHILE (done IS NOT NULL) DO
IF count > 0 THEN
OPEN cur2;
#赋值给全局变量
FETCH cur2 INTO culumn[,column1,column2];
WHILE (sum < count)DO
#业务逻辑
#赋值给全局变量
FETCH cur2 INTO culumn[,column1,column2];
SET sum = sum+1;
END WHILE;
SET done = 0;
SET sum = 0;
CLOSE cur2;
END IF;
FETCH cur1 INTO culumn[,column1,column2];
END WHILE;
CLOSE cur1;
END
第二个游标cur2遍历完毕时会给done赋值null所以要重新赋值。第一个游标cur1在第一遍遍历完成时,全局变量sum的值为count,也需要重新赋值。
这里主要描述循环的逻辑,具体的代码没必要贴上来,业务逻辑不一样。
多游标原理同双游标。