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

本文实例讲述了mysql存储过程中游标的用法。分享给大家供大家参考。具体如下：

1. 批量插入商户路由关联数据：

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 ;

2. 更新商户表：

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 ;

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