Examples of usage of middle-_mysql in Mysql stored procedures

This article illustrates the use of the middle stream of the MySQL stored procedure. Share to everyone for your reference. Specifically as follows:

1. BULK Insert Merchant Routing association data:

DELIMITER $$ use ' Mmm_mac ' $$ DROP PROCEDURE IF EXISTS ' BULK insert Merchant Route Association data ' $$ CREATE definer= ' root ' @ '% ' PROCEDURE ' BULK insert Merchant Route Association Data '
 () 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;

2. Update Merchant table:

DELIMITER $$ use
' Mmm_partner ' $$
DROP PROCEDURE IF EXISTS ' update merchant table ' $$
CREATE definer= ' root ' @ '% ' PROCEDURE ' Update Merchant table ' ()
BEGIN
  DECLARE v_partner_no VARCHAR ();
  DECLARE vpartner_no VARCHAR ();
  DECLARE v_partner_id VARCHAR ();
  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;

I hope this article is helpful to the design of MySQL database.