Mysql存储过程中游标的用法实例

本文实例讲述了Mysql存储过程中游标的用法。分享给大家供大家参考。具体如下：
1. 批量插入商户路由关联数据：

DELIMITER $$USE `mmm_mac`$$DROP PROCEDURE IF EXISTS `批量插入商户路由关联数据`$$CREATE DEFINER=`root`@`%` PROCEDURE `批量插入商户路由关联数据`（）BEGINDECLARE v_partner_no VARCHAR（32）; DECLARE v_partner_id INT（11）; DECLARE v_sc_pid INT（11）;DECLARE v_mac_no VARCHAR（32）; DECLARE v_mac_addr VARCHAR（32）;DECLARE n_mac_no BIGINT;DECLARE n_mac_addr BIGINT;DECLARE n_mac_addr_str VARCHAR（32）;DECLARE done INT;#取得商户数据DECLARE cur_partnerlist CURSORFORSELECT comp_id, partner_no, sc_pid FROM mmm_partner.anl_partner; SET n_mac_no = 100000000; SET n_mac_addr = 1000000000;OPEN cur_partnerlist;REPEATFETCH cur_partnerlist INTO v_partner_id,v_partner_no,v_sc_pid;SET v_mac_no = CONCAT（"MAC",v_sc_pid,n_mac_no）;SET n_mac_addr_str = CONCAT（SUBSTR（n_mac_addr,1,2）,":",SUBSTR（n_mac_addr,3,2）,":",SUBSTR（n_mac_addr,5,2）,":",SUBSTR（n_mac_addr,7,2）,":",SUBSTR（n_mac_addr,9,2））;SET v_mac_addr = CONCAT（"CC:",n_mac_addr_str）;SET n_mac_no = n_mac_no + 1;SET n_mac_addr = n_mac_addr + 1;#向t_machine_sc_config表中插入商户关联路由的数据#insert into t_machine_sc_config（mac_no, partner_no, partner_id, sc_pid, mac_addr, comp_id, is_lock） values（"MAC2016000000001","44060430603381",1,4403,"C8:87:18:AB:79:66",1,1）;INSERT INTO t_machine_sc_config（mac_no, partner_no, partner_id, sc_pid, mac_addr, comp_id, is_lock） VALUES（v_mac_no,v_partner_no,v_partner_id,v_sc_pid,v_mac_addr,1,1）;UNTIL 0 END REPEAT;CLOSE cur_partnerlist;END$$DELIMITER ;

2. 更新商户表：

DELIMITER $$USE `mmm_partner`$$DROP PROCEDURE IF EXISTS `更新商户表`$$CREATE DEFINER=`root`@`%` PROCEDURE `更新商户表`（）BEGINDECLARE v_partner_no VARCHAR（32）;DECLARE vpartner_no VARCHAR（32）;DECLARE v_partner_id VARCHAR（32）;DECLARE n BIGINT;DECLARE partnerid_list CURSORFORSELECT comp_id FROM 100msh_partner.anl_partner WHERE TRIM（partner_no） = ""; SET vpartner_no = "2015415parno";SET n = 10000000;OPEN partnerid_list;REPEATFETCH partnerid_list INTO v_partner_id;SET v_partner_no = CONCAT（vpartner_no,n）;SET n = n + 1;UPDATE mmm_partner.anl_partner SET partner_no = v_partner_no WHERE comp_id = v_partner_id;UNTIL 0 END REPEAT;CLOSE partnerid_list;END$$DELIMITER ;

希望本文所述对大家的mysql数据库程序设计有所帮助。