DECLARE done BOOLEAN DEFAULT 0;
DECLARE _devtype VARCHAR(56);
DECLARE t_index CURSOR FOR SELECT title FROM t_devtype; -- 定义游标
-- 写法一:DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;
-- DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
-- 写法二:DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
-- 第一步:查询需要返回的数据,support默认为0
DROP TEMPORARY TABLE IF EXISTS tmp_alarm;
create temporary table if not exists tmp_alarm(aid INT, grpid int, nid int, title VARCHAR(56), devtype VARCHAR(56), support int);
OPEN t_index; -- 打开游标
REPEAT
FETCH t_index INTO _devtype;
IF done!=1 THEN
INSERT into tmp_alarm(aid, grpid, nid, title, devtype, support)
SELECT aid, -grpid AS grpid, nid, title, _devtype, 0 FROM t_alarmplatform;
END IF;
UNTIL DONE END REPEAT;
DECLARE _devtype VARCHAR(56);
DECLARE t_index CURSOR FOR SELECT title FROM t_devtype; -- 定义游标
-- 写法一:DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;
-- DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
-- 写法二:DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
-- 第一步:查询需要返回的数据,support默认为0
DROP TEMPORARY TABLE IF EXISTS tmp_alarm;
create temporary table if not exists tmp_alarm(aid INT, grpid int, nid int, title VARCHAR(56), devtype VARCHAR(56), support int);
OPEN t_index; -- 打开游标
REPEAT
FETCH t_index INTO _devtype;
IF done!=1 THEN
INSERT into tmp_alarm(aid, grpid, nid, title, devtype, support)
SELECT aid, -grpid AS grpid, nid, title, _devtype, 0 FROM t_alarmplatform;
END IF;
UNTIL DONE END REPEAT;
CLOSE t_index; -- 关闭游标
链接:http://blog.csdn.net/u010003835/article/details/50716610