Mysql存儲過程中游標的用法實例

2024-07-24 13:07:38

字體：大中小

來源：轉載

供稿：網友

這篇文章主要介紹了Mysql存儲過程中游標的用法,以商戶關聯數據的插入及更新為例分析了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 ;