1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 | 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 ; |