mysql 存储过程 游标_mysql中游标在存储过程中的详细用法

BEGIN

DECLARE done INT default 0;

DECLARE channel_id,bank_id INT(11);

/*定义游标*/

DECLARE bankcur CURSOR FOR SELECT id FROM dolphin_pay_bank;

/*游标循环到末尾时给定义的常量赋值*/

DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;

/*开游标*/

OPEN bankcur;

REPEAT

/*游标赋值*/

FETCH bankcur INTO bank_id;

/* 循环体 */

IF NOT done THEN

-- 查入支付渠道为1，启动；1，单笔代扣；1，优先级；1

INSERT INTO `dolphin`.`dolphin_pay_bank_channel` (`id`,`channel_id`,`bank_id`,`rel_type`,`pay_type`,`priority`,`maintenance_start`,`maintenance_end`,`cdt`,`udt`)

VALUES(NULL,'2',bank_id,'1','1','1',NULL,NULL,'2017-10-26 16:34:24','2017-10-26 16:34:24');

-- 查入支付渠道为1，启动；1，单笔代付；2，优先级；1

INSERT INTO `dolphin`.`dolphin_pay_bank_channel` (`id`,`channel_id`,`bank_id`,`rel_type`,`pay_type`,`priority`,`maintenance_start`,`maintenance_end`,`cdt`,`udt`)

VALUES(NULL,'2',bank_id,'1','2','1',NULL,NULL,'2017-10-26 16:34:24','2017-10-26 16:34:24');

end IF;

UNTIL done END REPEAT;

/*关闭游标*/

CLOSE bankcur;

END