"示例一 DROP procedure IF EXISTS moveCardprofileOld2CardPersondataInfo; DELIMITER $$ create procedure moveCardprofileOld2CardPersondataInfo() begin DECLAR ...."

曙光大自然教育程序社会进销存游戏最新问答优选

登录注册

qiankunpingtai
荆棘从死亡中来，死亡又包含着新生。
待分类 • 0 回帖 • 196 浏览 • 1 年前

mysql 存储过程示例

示例一

DROP procedure IF EXISTS moveCardprofileOld2CardPersondataInfo;
DELIMITER $$
create procedure moveCardprofileOld2CardPersondataInfo()
    begin
        DECLARE _eid varchar(50) DEFAULT NULL;
        DECLARE _iccid varchar(50) DEFAULT NULL;
        DECLARE done INT DEFAULT 0;
        DECLARE cur CURSOR FOR SELECT eid,iccid FROM cardprofile_old;
        DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
        OPEN cur;
        -- 数据重复性校验eid imsi
        read_loop: LOOP
            FETCH cur INTO _eid,_iccid;
            IF done=1 THEN
                LEAVE read_loop;
            END IF;
            update card_persondata_info cpi set cpi.iccid=_iccid where cpi.eid=_eid;
            update card_persondata_info cpi set cpi.imsi=(select cio.imsi from card_info_old cio where cio.iccid=_iccid) where cpi.iccid=_iccid;
        END LOOP read_loop;
        CLOSE cur;
    end $$
DELIMITER ;

示例二

-- 批量导入的处理函数
-- 修改存储过程data_procedure
DROP procedure IF EXISTS data_procedure;
DELIMITER $$
CREATE DEFINER=`root`@`%` PROCEDURE data_procedure()
  BEGIN
    declare mycount INT default 0;
    DECLARE flag VARCHAR(255) DEFAULT NULL;
    DECLARE done INT DEFAULT 0;
    DECLARE meid varchar(255) DEFAULT NULL;
    DECLARE mimsi varchar(255) DEFAULT NULL;
    DECLARE miccid varchar(255) DEFAULT NULL;
    DECLARE mtask_id int(255) DEFAULT NULL;

    DECLARE lid int(255) DEFAULT NULL;
    DECLARE lmsisdn varchar(255) DEFAULT NULL;
    DECLARE lmsisdn_status varchar(255) DEFAULT NULL;
    DECLARE limsi varchar(255) DEFAULT NULL;
    DECLARE lprovince varchar(255) DEFAULT NULL;

    -- basedata_card_info
    DECLARE leid varchar(255) DEFAULT NULL;
    DECLARE lkic varchar(255) DEFAULT NULL;
    DECLARE lkid varchar(255) DEFAULT NULL;
    DECLARE lkik varchar(255) DEFAULT NULL;
    -- imsi
    DECLARE lversion varchar(255) DEFAULT NULL;
    DECLARE lcreate_time datetime DEFAULT NULL;
    DECLARE lcreator varchar(255) DEFAULT NULL;
    DECLARE lmodify_time datetime DEFAULT NULL;
    DECLARE lmodifier varchar(255) DEFAULT NULL;
    DECLARE lcompany_id int(255) DEFAULT NULL;  -- 无
    DECLARE lcntr varchar(255) DEFAULT NULL;
    DECLARE lwc_count int(255) DEFAULT NULL;
    DECLARE lwc_max_count int(255) DEFAULT NULL;

    DECLARE ldata_status varchar(255) DEFAULT NULL;
    DECLARE liccid varchar(255) DEFAULT NULL;
    DECLARE lk varchar(255) DEFAULT NULL;
    DECLARE lopc varchar(255) DEFAULT NULL;
    DECLARE lpin1 varchar(255) DEFAULT NULL;
    DECLARE lpin2 varchar(255) DEFAULT NULL;
    DECLARE lpuk1 varchar(255) DEFAULT NULL;
    DECLARE lpuk2 varchar(255) DEFAULT NULL;
    DECLARE lsmsp varchar(255) DEFAULT NULL;
    DECLARE lacc varchar(255) DEFAULT NULL;
    DECLARE ladm varchar(255) DEFAULT NULL;

    DECLARE lcard_state varchar(255) DEFAULT NULL;
    DECLARE lcard_status varchar(255) DEFAULT NULL;
    DECLARE limport_id varchar(255) DEFAULT NULL;
    DECLARE ltask_id int(255) DEFAULT NULL;
    DECLARE companyName varchar(255) DEFAULT NULL;
    DECLARE companyId int(11);
    DECLARE cardId bigint(20);
    DECLARE cardCompanyId int(11);
    DECLARE persondataId bigint(20);
    DECLARE persondataCompanyId int(11);

    DECLARE cur CURSOR FOR SELECT bci.eid,dit.task_id FROM basedata_card_info bci,data_import_temporary dit WHERE bci.eid is not null AND bci.eid = dit.eid ;
    DECLARE cur2 CURSOR FOR SELECT bpi.iccid,bpi.imsi,dit.task_id FROM basedata_persondata_info bpi,data_import_temporary dit WHERE (bpi.iccid is not null and bpi.iccid = dit.iccid) or (bpi.imsi is not null and bpi.imsi = dit.imsi);
    DECLARE cur3 CURSOR FOR select id, data_status, iccid, eid, kic, kid, kik, imsi, k, opc, pin1, pin2, puk1, puk2,smsp, msisdn, msisdn_status, province, acc, adm, version, create_time, creator, update_time, updater, cntr, wc_count,wc_max_count, task_id, company_name from data_import_temporary;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

    OPEN cur;
    -- 数据重复性校验eid imsi
    read_loop1: LOOP
      FETCH cur INTO meid,mtask_id;
      IF done=1 THEN
        LEAVE read_loop1;
      END IF;
      INSERT INTO data_import_error(eid,task_id,error_reason,create_time) VALUES (meid,mtask_id,'basedata_card_info表与data_import_temporary表eid重复',(select now()));
      DELETE FROM data_import_temporary WHERE eid = meid;
    END LOOP read_loop1;
    CLOSE cur;
    set done = 0;
    OPEN cur2;
    -- 数据重复性校验iccid
    read_loop2: LOOP
      FETCH cur2 INTO miccid,mimsi,mtask_id;
      IF done=1 THEN
        LEAVE read_loop2;
      END IF;
      INSERT INTO data_import_error(iccid,imsi,task_id,error_reason,create_time) VALUES (miccid,mimsi,mtask_id,'basedata_persondata_info表与data_import_temporary表iccid/imsi重复',(select now()));
      DELETE FROM data_import_temporary WHERE iccid = miccid or imsi=mimsi;
    END LOOP read_loop2;
    CLOSE cur2;
    set done = 0;
    OPEN cur3;
    -- 数据准确性校验(长度)
    read_loop3: LOOP
      FETCH cur3 INTO lid,ldata_status,liccid,leid,lkic,lkid,lkik,limsi,lk,lopc,lpin1,lpin2,lpuk1,lpuk2,lsmsp,lmsisdn,lmsisdn_status,lprovince,lacc,ladm,lversion,lcreate_time,lcreator,lmodify_time,lmodifier,lcntr,lwc_count,lwc_max_count,ltask_id,companyName;
      IF done=1 THEN
        LEAVE read_loop3;
      END IF;
      IF (ldata_status = 0) THEN
        -- 制卡(制卡允许只有eid和三把key，imsi和iccid可以没有)
        IF (LENGTH(leid) != 20) THEN
          INSERT INTO data_import_error(imsi,eid,task_id,error_reason,create_time) VALUES (limsi,leid,ltask_id,'eid数据异常',(select now()));
          DELETE FROM data_import_temporary WHERE eid = leid;
        ELSEIF (LENGTH(lkic) != 32) THEN
          INSERT INTO data_import_error(imsi,kic,task_id,error_reason,create_time) VALUES (limsi,lkic,ltask_id,'kic数据异常',(select now()));
          DELETE FROM data_import_temporary WHERE kic = lkic;
        ELSEIF (LENGTH(lkid) != 32) THEN
          INSERT INTO data_import_error(imsi,kid,task_id,error_reason,create_time) VALUES (limsi,lkid,ltask_id,'kid数据异常',(select now()));
          DELETE FROM data_import_temporary WHERE kid = lkid;
        ELSEIF (LENGTH(lkik) != 32) THEN
          INSERT INTO data_import_error(imsi,kik,task_id,error_reason,create_time) VALUES (limsi,lkik,ltask_id,'kik数据异常',(select now()));
          DELETE FROM data_import_temporary WHERE kik = lkik;
        -- ELSE
        -- INSERT INTO basedata_number_info(msisdn,msisdn_status,imsi,province) SELECT msisdn,msisdn_status,imsi,province FROM card_info_new WHERE data_status = 0;
        -- INSERT INTO basedata_card_info(id,eid,kic,kid,kik,imsi,version,create_time,creator,modify_time,modifier,cntr) SELECT id,eid,kic,kid,kik,imsi,version,create_time,creator,modify_time,modifier,cntr FROM card_info_new WHERE data_status = 0;
        -- INSERT INTO basedata_persondata_info(iccid,imsi,k,opc,pin1,pin2,puk1,puk2,smsp,acc,adm,create_time,data_status) SELECT iccid,imsi,k,opc,pin1,pin2,puk1,puk2,smsp,acc,adm,create_time,data_status FROM card_info_new WHERE data_status = 0;
        END IF;
      ELSEIF (ldata_status = 1) THEN
        -- 写卡
        IF (LENGTH(liccid) != 20) THEN
          INSERT INTO data_import_error(iccid,eid,imsi,task_id,error_reason,create_time) VALUES (liccid,leid,limsi,ltask_id,'iccid数据异常',(select now()));
          DELETE FROM data_import_temporary WHERE iccid = liccid;
        ELSEIF (LENGTH(lmsisdn) != 0 && LENGTH(lmsisdn) != 11 && LENGTH(lmsisdn) != 13) THEN
          INSERT INTO data_import_error(imsi,msisdn,task_id,error_reason,create_time) VALUES (limsi,lmsisdn,ltask_id,'msisdn数据异常',(select now()));
          DELETE FROM data_import_temporary WHERE msisdn = lmsisdn;
        ELSEIF (LENGTH(limsi) != 15) THEN
          INSERT INTO data_import_error(msisdn,msisdn_status,imsi,province,task_id,error_reason,create_time) VALUES (lmsisdn,lmsisdn_status,limsi,lprovince,ltask_id,'imsi数据异常',(select now()));
          DELETE FROM data_import_temporary WHERE imsi = limsi;
        ELSEIF (LENGTH(lk) != 32) THEN
          INSERT INTO data_import_error(imsi,k,task_id,error_reason,create_time) VALUES (limsi,lk,ltask_id,'k数据异常',(select now()));
          DELETE FROM data_import_temporary WHERE k = lk;
        ELSEIF (LENGTH(lopc) != 32) THEN
          INSERT INTO data_import_error(imsi,opc,task_id,error_reason,create_time) VALUES (limsi,lopc,ltask_id,'opc数据异常',(select now()));
          DELETE FROM data_import_temporary WHERE opc = lopc;
        ELSEIF (LENGTH(lpin1) != 4) THEN
          INSERT INTO data_import_error(imsi,pin1,task_id,error_reason,create_time) VALUES (limsi,lpin1,ltask_id,'pin1数据异常',(select now()));
          DELETE FROM data_import_temporary WHERE pin1 = lpin1;
        ELSEIF (LENGTH(lpin2) != 4) THEN
          INSERT INTO data_import_error(imsi,pin2,task_id,error_reason,create_time) VALUES (limsi,lpin2,ltask_id,'pin2数据异常',(select now()));
          DELETE FROM data_import_temporary WHERE pin2 = lpin2;
        ELSEIF (LENGTH(lpuk1) != 8) THEN
          INSERT INTO data_import_error(imsi,puk1,task_id,error_reason,create_time) VALUES (limsi,lpuk1,ltask_id,'puk1数据异常',(select now()));
          DELETE FROM data_import_temporary WHERE puk1 = lpuk1;
        ELSEIF (LENGTH(lpuk2) != 8) THEN
          INSERT INTO data_import_error(imsi,puk2,task_id,error_reason,create_time) VALUES (limsi,lpuk2,ltask_id,'puk2数据异常',(select now()));
          DELETE FROM data_import_temporary WHERE puk2 = lpuk2;
        ELSEIF ((LENGTH(lsmsp) != 11) && (LENGTH(lsmsp) != 13)) THEN
          INSERT INTO data_import_error(imsi,smsp,task_id,error_reason,create_time) VALUES (limsi,lsmsp,ltask_id,'smsp数据异常',(select now()));
          DELETE FROM data_import_temporary WHERE smsp = lsmsp;
        -- ELSE
        -- INSERT INTO basedata_number_info(msisdn,msisdn_status,imsi,province) SELECT msisdn,msisdn_status,imsi,province FROM card_info_new WHERE data_status = 1;
        -- INSERT INTO basedata_card_info(id,eid,kic,kid,kik,imsi,version,create_time,creator,modify_time,modifier,cntr) SELECT id,eid,kic,kid,kik,imsi,version,create_time,creator,modify_time,modifier,cntr FROM card_info_new WHERE data_status = 1;
        -- INSERT INTO basedata_persondata_info(iccid,imsi,k,opc,pin1,pin2,puk1,puk2,smsp,acc,adm,create_time,data_status) SELECT iccid,imsi,k,opc,pin1,pin2,puk1,puk2,smsp,acc,adm,create_time,data_status FROM card_info_new WHERE data_status = 1;
        END IF;
      END IF;
      IF (companyName is not null) THEN
        -- 公司名称不为空，校验公司名称是否存在
        set companyId=null;
        IF EXISTS (select 1  from company_info where company_name=companyName and delete_flag='0') THEN
          select id into companyId from company_info where company_name=companyName and delete_flag='0';
        END IF;
        IF (companyId is null) THEN
          INSERT INTO data_import_error(imsi,eid,task_id,error_reason,company_name,create_time) VALUES (limsi,leid,ltask_id,'公司名称不存在',companyName,(select now()));
        ELSE
          set cardId = null;
          set cardCompanyId = null;
          IF(LENGTH(leid) = 20)THEN
            -- company_card_info 没有eid不插入
            -- 查询是否已存在对应数据
            IF EXISTS (select 1 from company_card_info where eid=leid and delete_flag='0') THEN
              select id ,company_id into cardId,cardCompanyId from company_card_info where eid=leid and delete_flag='0';
            END IF;
            IF (cardId is null) THEN
              -- 创建公司和卡数据的关联关系
              INSERT INTO company_card_info(eid, company_id, create_time, update_time, delete_flag) VALUES (leid, companyId, (select now()), (select now()), '0');
            ELSEIF (cardCompanyId!=companyId) THEN
              update company_card_info set company_id=companyId,update_time=(select now()) where 1=1 and id=cardId;
            END IF;
          END IF;
          IF (LENGTH(liccid) = 20 OR LENGTH(limsi) = 15)THEN
            -- company_persondata_info 没有iccid和imsi不插入
            set persondataId=null;
            set persondataCompanyId=null;
            IF EXISTS (select 1 from company_persondata_info where iccid=liccid and imsi=limsi and delete_flag='0') THEN
              select id ,company_id into persondataId,persondataCompanyId from company_persondata_info where iccid=liccid and imsi=limsi and delete_flag='0';
            END IF;
            IF (persondataId is null) THEN
              -- 创建公司和卡数据的关联关系
              INSERT INTO company_persondata_info(iccid, imsi, company_id, create_time, update_time, delete_flag) VALUES ( liccid, limsi, companyId, (select now()), (select now()), '0');
            ELSEIF (persondataCompanyId!=companyId) THEN
              update company_persondata_info set company_id=companyId,update_time=(select now()) where 1=1 and id=persondataId;
            END IF;
          END IF;
        END IF;
      END IF;
    END LOOP read_loop3;
    CLOSE cur3;
    -- basedata_card_info 没有eid不插入
    INSERT INTO basedata_card_info (eid, kic, kid, kik, version, create_time, update_time)
      SELECT eid,kic,kid,kik,version,(select now()),(select now()) FROM data_import_temporary where 1=1 and  LENGTH(eid)=20 ;
    -- basedata_persondata_info 没有iccid和imsi不插入
    INSERT INTO basedata_persondata_info(iccid, imsi, k, opc, pin1, pin2, puk1, puk2, smsp, acc, adm, create_time, update_time)
      SELECT iccid,imsi,k,opc,pin1,pin2,puk1,puk2,smsp,acc,adm,(select now()),(select now()) FROM data_import_temporary where 1=1 and (LENGTH(iccid) = 20 OR LENGTH(imsi) = 15);
    -- card_persondata_info 没有eid不插入
    INSERT INTO card_persondata_info(eid, iccid, imsi, cntr, wc_count, wc_max_count, create_time, update_time, delete_flag)
      SELECT eid,iccid,imsi,cntr,wc_count,wc_max_count,(select now()),(select now()),'0' FROM data_import_temporary where 1=1 and  LENGTH(eid)=20;
    -- number_persondata_info 没有iccid和imsi不插入
    INSERT INTO number_persondata_info(msisdn, msisdn_status, iccid, imsi, imsi_status, province, create_time, update_time,delete_flag)
      SELECT msisdn,msisdn_status,iccid,imsi,'0',province,(select now()),(select now()),'0' FROM data_import_temporary where 1=1 and (LENGTH(iccid) = 20 OR LENGTH(imsi) = 15);
    -- persondata_status 没有iccid和imsi不插入
    INSERT INTO persondata_status(iccid, imsi, card_state, card_status, data_status, data_status_now, create_time,update_time, delete_flag)
      SELECT iccid,imsi,card_state,card_status,data_status,data_status,(select now()),(select now()),'0' FROM data_import_temporary where 1=1 and (LENGTH(iccid) = 20 OR LENGTH(imsi) = 15);
    -- 上传任务完成后清空临时表 每次 平时测试时候可以先关闭 上传完成后手动删除
    DELETE FROM data_import_temporary;
  END $$
DELIMITER ;

0 回帖

相关帖子

随便看看

赞助商我要投放

垃圾广告

低俗色情

违法违规

涉嫌侵权

人身攻击

其他

这里是一个活跃的小众网站，大家相互信任，以平等 • 自由 • 奔放的价值观进行分享交流

Feel easy about trust.

京ICP备19012706号-1 • © 2012-present B3log 开源 • Sym 3.6.2

mysql 存储过程示例

0 0 0 0

回帖