Mysql存储过程中游标使用

根据一定条件，批量插入和更新mysql数据库中的数据 无 DELIMITER $$USE `mmm_mac`$$DROP PROCEDURE IF EXISTS `批量插入商户路由关联数据`$$CREATE DEFINER=`root`@`%` PROCEDURE `批量插入商户路由关联数据`()BEGINDECLARE v_partner_no VARCHAR(32); DECLAR

根据一定条件，批量插入和更新mysql数据库中的数据

DELIMITER $$

USE `mmm_mac`$$

DROP PROCEDURE IF EXISTS `批量插入商户路由关联数据`$$

CREATE DEFINER=`root`@`%` PROCEDURE `批量插入商户路由关联数据`()
BEGIN
    DECLARE 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 CURSOR
    FOR
    SELECT comp_id, partner_no, sc_pid FROM mmm_partner.anl_partner;
    SET n_mac_no = 100000000;
  SET n_mac_addr = 1000000000;

    OPEN cur_partnerlist;

    REPEAT
        FETCH 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 ;

DELIMITER $$

USE `mmm_partner`$$

DROP PROCEDURE IF EXISTS `更新商户表`$$

CREATE DEFINER=`root`@`%` PROCEDURE `更新商户表`()
BEGIN
    DECLARE v_partner_no VARCHAR(32);
    DECLARE vpartner_no VARCHAR(32);
    DECLARE v_partner_id VARCHAR(32);
    DECLARE n BIGINT;
    DECLARE partnerid_list CURSOR
    FOR
    SELECT comp_id FROM 100msh_partner.anl_partner WHERE TRIM(partner_no) = '';

    SET vpartner_no = '2015415parno';
    SET n = 10000000;
    OPEN partnerid_list;

    REPEAT
        FETCH 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 ;