我要评论本文实例讲述了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数据库程序设计有所帮助。
您可能感兴趣的文章:
如您对本文有疑问或者有任何想说的，请点击进行留言回复，万千网友为您解惑！
网友评论