MySQL 存储过程 实现遍历指针
从某张表中查出结果集赋值给游标 再通过REPEAT 遍历游标插入另外一张表
DROP PROCEDURE IF EXISTS PROC_CARD_ROSTRA;
DELIMITER $$
CREATE PROCEDURE proc_card_rostra(IN outId VARCHAR(50),IN authType int)
BEGIN
#卡挂失、补卡、解挂 向智慧终端授权记录表中添加授权数据(rostra_auth_record)
#声明结束标识
DECLARE end_flag int DEFAULT 0;
DECLARE terminaId VARCHAR(50);
#声明游标 terminalIds
DECLARE terminalIds CURSOR FOR SELECT TERMINAL_ID FROM rostra_auth_info WHERE rostra_auth_info.OUTID = outId;
#设置终止标志
DECLARE CONTINUE HANDLER FOR NOT FOUND SET end_flag=1;
#打开游标
OPEN terminalIds;
#遍历游标
REPEAT
#获取当前游标指针记录,取出值赋给自定义的变量
FETCH terminalIds INTO terminaId;
IF end_flag <> 1 THEN
#利用取到的值进行数据库的操作
INSERT INTO rostra_auth_record (OUTID,AUTH_TYPE,TERMINAL_ID,START_DATE,END_DATE,CREATE_DATE,ISSUE_TYPE,RECORD_TYPE)
VALUES (outId,authType,terminaId,DATE_FORMAT(NOW(),'%Y-%m-%d'),'2099_12-31',DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i:%s'),0,1);
END IF;
# 根据 end_flag 判断是否结束
UNTIL end_flag END REPEAT;
-- 关闭游标
close terminalIds;
END$$
DELIMITER ;