根据一定条件，批量插入和更新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 ;