mysql 动态存储过程

##⚠️ DELIMITER // 是定义多个sql的分隔符。DELIMITER , 就是分隔符是，

##oracle 加字段
ALTER table T_NEW_HIS_KDDELIVEREXP add ISSUCCESS VARCHAR2(30);

##建表语句  his

DELIMITER //
drop procedure if EXISTS pro_createTable_his;//
    create PROCEDURE pro_createTable_his()
    begin
        DECLARE var_tname varchar(50);
        DECLARE sql_create varchar(1000);
        declare done int default false;
        DECLARE cur CURSOR FOR SELECT tablename from t_his_table_city; 
        declare continue HANDLER for not found set done = true;

        open cur;
        
        fetch cur into var_tname;
        while (not done) do
            SET sql_create= CONCAT("CREATE TABLE if not EXISTS ",var_tname,"(
    ID VARCHAR(40) NOT NULL COMMENT '唯一ID',
    APPNUM VARCHAR(30) COMMENT '运单号', 
    STATIONCODE VARCHAR(40) COMMENT '站点编码', 
    PROVINCE VARCHAR(45) COMMENT '省份code', 
    CITY VARCHAR(45) COMMENT '城市code', 
    COUNTY VARCHAR(45) COMMENT '区县code', 
    ADDRESSDETAILS VARCHAR(500) COMMENT '地址其他信息', 
    CREATETIME datetime not null Default CURRENT_TIMESTAMP  COMMENT '创建时间', 
    MODIFYTIME datetime not null Default CURRENT_TIMESTAMP COMMENT '修改时间', 
    CUSTNAME VARCHAR(300) COMMENT '客户名称', 
    PHONE VARCHAR(120) COMMENT '手机号码', 
    TEL VARCHAR(120) COMMENT '电话号码', 
    APPCREATETIME datetime COMMENT '开单时间', 
    FREQUENCY int(2) DEFAULT 0 COMMENT '次数', 
    ISSUCCESS VARCHAR(30) COMMENT '是否成功同步到es,初始时为空，同步后改为Y',  
    PRIMARY KEY (ID),
    INDEX IDX_ADDRESSDETAILS(ADDRESSDETAILS),
    INDEX IDX_STATIONCODE (STATIONCODE),
    INDEX IDX_PROVINCE_CITY_COUNTY_ADDRESSDETAILS (PROVINCE, CITY, COUNTY, ADDRESSDETAILS)
    )ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;");
     ##select sql_create;
            set @sql = sql_create;
      prepare s1 from @sql;
      execute s1;
            FETCH cur into var_tname;
        end WHILE;

        CLOSE cur;
    end;//
        
        call pro_createTable_his();
        
        
##建表语句  seg

DELIMITER //
drop procedure  if EXISTS pro_createTable_seg;//
    create PROCEDURE pro_createTable_seg()
    begin
        DECLARE var_tname varchar(50);
        DECLARE sql_create varchar(1000);
        declare done int default false;
        DECLARE cur CURSOR FOR SELECT tablename from t_seg_table_city; 
        declare continue HANDLER for not found set done = true;

        open cur;
        
        fetch cur into var_tname;
        while (not done) do
            SET sql_create= CONCAT("CREATE TABLE if not EXISTS ",var_tname,"(
    ID VARCHAR(50) NOT NULL  COMMENT '唯一ID',
    PROVINCE VARCHAR(50) COMMENT '省份',
    PROVINCECODE VARCHAR(50) COMMENT '省份code',    
    CITY VARCHAR(50) COMMENT '城市', 
    CITYCODE VARCHAR(50) COMMENT '城市code', 
    COUNTY VARCHAR(50) COMMENT '区县', 
    COUNTYCODE VARCHAR(50) COMMENT '区县code', 
    TOWN VARCHAR(50) COMMENT '镇名称', 
    TOWNCODE VARCHAR(50) COMMENT '镇code', 
    KEYCODE         VARCHAR(500) not null COMMENT '查询用Key数据，唯一索引，例： 区编码|路|号 或 区编码|路|地标 等',
  BIZCODE      VARCHAR(100) not null COMMENT '业务部门编码',
  KEYTYPE      VARCHAR(50) not null COMMENT 'key数据类型， 例： 路+号 或 路+地标',
  MATCHTYPE    VARCHAR(50) not null COMMENT '历史经验匹配类型,与地址匹配类型一致',
  CREATEDATE   datetime not null Default CURRENT_TIMESTAMP  COMMENT '创建时间',
  MODIFYDATE   datetime not null Default CURRENT_TIMESTAMP  COMMENT '修改时间',
    ISSUCCESS VARCHAR(30) COMMENT '是否成功同步到es,初始时为空，同步后改为Y',  
    PRIMARY KEY (ID)
    )ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;");
     ##select sql_create;
            set @sql = sql_create;
      prepare s1 from @sql;
      execute s1;
            FETCH cur into var_tname;
        end WHILE;

        CLOSE cur;
    end;//
        
        call pro_createTable_seg();
        
        
##建表语句 seg 添加index
 
DELIMITER //
drop procedure pro_createTable_index;//
    create PROCEDURE pro_createTable_index()
    begin
        DECLARE var_tname varchar(50);
                DECLARE sql_index varchar(1000);
                DECLARE sql_index2 varchar(1000);
                DECLARE sql_index3 varchar(1000);
                DECLARE sql_index4 varchar(1000);
                DECLARE sql_index5 varchar(1000);
                DECLARE sql_index6 varchar(1000);
        declare done int default false;
        DECLARE cur CURSOR FOR SELECT tablename from t_seg_table_city;  
        declare continue HANDLER for not found set done = true;

        open cur;
        
        fetch cur into var_tname;
        while (not done) do
        SET sql_index= CONCAT("ALTER  TABLE  ",var_tname,"  ADD  INDEX IDX_PROVINCECODE_CITYCODE_COUNTYCODE (PROVINCECODE, CITYCODE, COUNTYCODE);"
        
    );
     ##select sql_index;
        
        set @sql = sql_index;
   prepare s1 from @sql;
   execute s1;
     
     SET sql_index2= CONCAT(
        "ALTER  TABLE  ",var_tname,"  ADD  INDEX IDX_PROVINCE_CITY_COUNTY (PROVINCE, CITY, COUNTY);"
    );
    ## select sql_index2;
        
     set @sql = sql_index2;
   prepare s2 from @sql;
   execute s2;
     
     SET sql_index3= CONCAT(
        "ALTER  TABLE  ",var_tname,"  ADD  INDEX IDX_BIZCODE_KEY (BIZCODE, KEYCODE,CREATEDATE,CITYCODE);"
    );
     ##select sql_index3;
        
     set @sql = sql_index3;
   prepare s2 from @sql;
   execute s2;
     
      SET sql_index4= CONCAT(
        "ALTER  TABLE  ",var_tname,"  ADD  INDEX IDX_COUNTYCODE (COUNTYCODE);"
    );
    ## select sql_index4;
        
     set @sql = sql_index4;
   prepare s2 from @sql;
   execute s2;

 SET sql_index5= CONCAT(
        "ALTER  TABLE  ",var_tname,"  ADD  INDEX IDX_KEY_MATCHTYPE (KEYCODE, MATCHTYPE);"
    );
    ## select sql_index5;
        
     set @sql = sql_index5;
   prepare s2 from @sql;
   execute s2;
     
     SET sql_index6= CONCAT(
        "ALTER  TABLE  ",var_tname,"  ADD  INDEX IDX_MODIFYDATE (MODIFYDATE);"
    );
     ##select sql_index6;
        
     set @sql = sql_index6;
   prepare s2 from @sql;
   execute s2;

     
     
            FETCH cur into var_tname;
        end WHILE;

        CLOSE cur;
    end;//
        
        call pro_createTable_index();
        
##建中间表 seg----

CREATE TABLE if not EXISTS t_seg_table_city (
  id int(50) NOT NULL auto_increment COMMENT '唯一ID',
  tablename varchar(50) NOT NULL COMMENT '表名',
  city varchar(50) NOT NULL COMMENT '市',
    active int(3) NOT NULL COMMENT '有效',
    CREATETIME datetime not null Default CURRENT_TIMESTAMP COMMENT '创建时间',
    MODIFYTIME datetime not null Default CURRENT_TIMESTAMP COMMENT '修改时间',
  PRIMARY KEY (id),
  INDEX index_city(city),
  INDEX index_city_tablename(city,tablename)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

##建中间表  his----

CREATE TABLE if not EXISTS t_his_table_city (
  id int(50) NOT NULL auto_increment COMMENT '唯一ID',
  tablename varchar(50) NOT NULL COMMENT '表名',
  city varchar(50) NOT NULL COMMENT '市',
    active int(3) NOT NULL COMMENT '有效',
    CREATETIME datetime not null Default CURRENT_TIMESTAMP COMMENT '创建时间',
    MODIFYTIME datetime not null Default CURRENT_TIMESTAMP COMMENT '修改时间',
  PRIMARY KEY (id),
  INDEX index_city(city),
  INDEX index_city_tablename(city,tablename)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

        
        
#建删除表 -----
CREATE TABLE if not EXISTS t_delete_deptcode(
  id INT(50) NOT NULL auto_increment COMMENT '唯一ID',
  deletecode varchar(50) NOT NULL COMMENT '部门code',
  tablehis varchar(50) NOT NULL COMMENT '对应历史经验表名',
    tableseg varchar(50) NOT NULL COMMENT '对应分词表名',
    CREATETIME datetime not null Default CURRENT_TIMESTAMP COMMENT '创建时间',
    MODIFYTIME datetime not null Default CURRENT_TIMESTAMP COMMENT '修改时间',
    isdelete int(10) not null DEFAULT 0,
    DELETE_HIS_COUNT VARCHAR(10) DEFAULT 0 COMMENT '本次已删除记录数', 
  DELETE_SEG_COUNT VARCHAR(10) DEFAULT 0 COMMENT '本次已删除分词记录数',  
  PRIMARY KEY (id),
 INDEX idx_deletecode_tablehis(deletecode,tablehis),
 INDEX idx_deletecode_tableseg(deletecode,tableseg)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

#建营业部与市、表名对照表  java用 
CREATE TABLE if not EXISTS t_deptcode_city(
  id INT(50) NOT NULL auto_increment COMMENT '唯一ID',
  deptcode varchar(50) NOT NULL COMMENT '部门code',
    citycode varchar(50) NOT NULL COMMENT '市code',
 ## tablehis varchar(50) NOT NULL COMMENT '对应表名',
    ##tableseg varchar(50) NOT NULL COMMENT '对应表名',
    CREATETIME datetime not null Default CURRENT_TIMESTAMP COMMENT '创建时间',
    MODIFYTIME datetime not null Default CURRENT_TIMESTAMP COMMENT '修改时间',
    isdelete int(10) not null DEFAULT 0 COMMENT '记录有效性',
  PRIMARY KEY (id),
  ##INDEX idx_deletecode_tablehis(deletecode,tablehis),STATIONCODE
    ##INDEX idx_deletecode_tableseg(deletecode,tableseg)
    INDEX idx_deptcode_modifytime(deptcode,MODIFYTIME)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

##删除存储过程 — - 加isdelete 字段   可用java替换执行
DELIMITER //
DROP PROCEDURE IF EXISTS pro_execute_4;//
create PROCEDURE pro_execute_4() 
    begin
                DECLARE var_id varchar(50);
        DECLARE var_namehis varchar(50);
                 DECLARE var_nameseg varchar(50);
                DECLARE var_code varchar(50);
        DECLARE sql_deletehis varchar(1000);
                DECLARE sql_deleteseg varchar(1000);
                DECLARE sql_updatehis varchar(1000);
                DECLARE sql_updateseg varchar(1000);
                DECLARE counthis INT(20) DEFAULT 0;
                DECLARE countseg INT(20) DEFAULT 0;
        declare done int default false;
                ##查询删除中间表需删除deptCode+tableName
            DECLARE cur CURSOR FOR SELECT tablehis,tableseg,deletecode,id FROM t_delete_deptcode WHERE isdelete=0;
        declare continue HANDLER for not found set done = true;
        open cur;
        fetch cur into var_namehis,var_nameseg,var_code,var_id;
        while (not done) do
                        ##删除历经经验表数据
            SET sql_deletehis= CONCAT("DELETE FROM ",var_namehis," WHERE STATIONCODE = '",var_code,"';");
                        ##SELECT sql_deletehis;
                        set @sql = sql_deletehis;
                         prepare s1 from @sql;
                        start transaction;
                        execute s1;
                        ##一定要在commit之前!!!
                        set @cnthis  = ROW_COUNT(); 
                        ##SELECT @cnthis;
                        SELECT @cnthis   into counthis;
                        commit;
                        ##释放预处理字段
                        DEALLOCATE PREPARE s1;
                        
                     ##修改中间表isdelete字段
                     SET sql_updatehis= CONCAT("UPDATE t_delete_deptcode set isdelete = 1,DELETE_HIS_COUNT = '",counthis,"' WHERE id =  '",var_id,"';");
                     ##SELECT sql_updatehis;
                     set @sql = sql_updatehis;
                         prepare s2 from @sql;
                     start transaction;
                     execute s2;
                     commit;
                     DEALLOCATE PREPARE s2;
            
                         ##删除分词数据
           SET sql_deleteseg= CONCAT("DELETE FROM ",var_nameseg," WHERE BIZCODE =  '",var_code,"';");
                 ##SELECT sql_deleteseg;
                 set @sql = sql_deleteseg;
                         prepare s3 from @sql;
                     start transaction;
                     execute s3;
                    set @cntseg  = ROW_COUNT() ; 
                     ##SELECT @cntseg;
                     commit;
                    ##SELECT countseg;
                     ##释放预处理字段
                     DEALLOCATE PREPARE s3;
                     ##受影响值为0！！！
                ##    set @cntseg  = ROW_COUNT() ; 
                ##    SELECT @cntseg;
                ##        SELECT @cntseg  into countseg;
                    ##在释放预处理sql前后都可 DEALLOCATE PREPARE s3;无关
                      SELECT @cntseg  into countseg;
                     ##修改中间表isdelete字段
                     SET sql_updateseg= CONCAT("UPDATE t_delete_deptcode set isdelete = 1,DELETE_SEG_COUNT = '",countseg,"' WHERE id =  '",var_id,"';");
                  ## SELECT sql_updateseg;
                     set @sql = sql_updateseg;
                         prepare s4 from @sql;
                     start transaction;
                     execute s4;
                     commit;
                     DEALLOCATE PREPARE s4;
                
            fetch cur into var_namehis,var_nameseg,var_code,var_id;
        end WHILE;
        CLOSE cur;
    end;//
        
    #执行
    call pro_execute_4();
    
    
##删除存储过程 — - 加isdelete 字段  定时---          可用java替换执行
SET GLOBAL event_scheduler = 1;

DROP EVENT IF EXISTS  e_test;//
CREATE EVENT e_test
on schedule EVERY 1 DAY STARTS date_add(date( ADDDATE(curdate(),1)),interval 3 hour) 
##ON SCHEDULE EVERY 1 DAY  STARTS TIMESTAMP date_add(date( ADDDATE(curdate(),1)),interval 3 hour) 
ON COMPLETION PRESERVE
DO
BEGIN
call pro_execute_4();
END;
    
    

##同步语句  his 按市同步

DELIMITER //
drop procedure if EXISTS pro_sync_his;//
    create PROCEDURE pro_sync_his()
    begin
        DECLARE var_tablename varchar(50);
                DECLARE var_city varchar(50);
        DECLARE sql_sync varchar(1000);
        declare done int default false;
        DECLARE cur CURSOR FOR SELECT city,tablename from t_his_table_city; 
        declare continue HANDLER for not found set done = true;
        open cur;
        fetch cur into var_city,var_tablename;
        while (not done) do
           SET sql_sync= CONCAT("INSERT into ",var_tablename,"(ID,APPNUM,STATIONCODE,PROVINCE,CITY,COUNTY,ADDRESSDETAILS,CREATETIME,MODIFYTIME,CUSTNAME,PHONE,TEL,APPCREATETIME,FREQUENCY,ISSUCCESS)
        SELECT 
        ID,APPNUM,STATIONCODE,PROVINCE,CITY,COUNTY,ADDRESSDETAILS,CREATETIME,MODIFYTIME,CUSTNAME,PHONE,TEL,APPCREATETIME,FREQUENCY,ISSUCCESS  
                FROM T_NEW_HIS_KDDELIVEREXP
             WHERE city = '",var_city,"';");
     select sql_sync;
          set @sql = sql_sync;
      prepare s1 from @sql;
      execute s1;
            FETCH cur into var_city,var_tablename;
        end WHILE;

        CLOSE cur;
    end;//
        
        call pro_sync_his();
        
        
##同步语句  seg 按市同步

DELIMITER //
drop procedure if EXISTS pro_sync_seg;//
    create PROCEDURE pro_sync_seg()
    begin
        DECLARE var_tablename varchar(50);
                DECLARE var_city varchar(50);
        DECLARE sql_sync varchar(1000);
        declare done int default false;
        DECLARE cur CURSOR FOR SELECT city,tablename from t_seg_table_city; 
        declare continue HANDLER for not found set done = true;
        open cur;
        fetch cur into var_city,var_tablename;
        while (not done) do
                INSERT into t_seg_1 
        (ID,PROVINCE,PROVINCECODE,CITY,CITYCODE,COUNTY,COUNTYCODE,TOWN,TOWNCODE,KEYCODE,BIZCODE,KEYTYPE,MATCHTYPE,CREATEDATE,MODIFYDATE,ISSUCCESS)
     SELECT 
      ID,PROVINCE,PROVINCECODE,CITY,CITYCODE,COUNTY,COUNTYCODE,TOWN,TOWNCODE,KEYCODE,BIZCODE,KEYTYPE,MATCHTYPE,CREATEDATE,MODIFYDATE,ISSUCCESS
            FROM t_new_seg_hismatch
            WHERE city = '110000-1';    
           SET sql_sync= CONCAT("INSERT into ",var_tablename,"(ID,PROVINCE,PROVINCECODE,CITY,CITYCODE,COUNTY,COUNTYCODE,TOWN,TOWNCODE,KEYCODE,BIZCODE,KEYTYPE,MATCHTYPE,CREATEDATE,MODIFYDATE,ISSUCCESS)
        SELECT 
      ID,PROVINCE,PROVINCECODE,CITY,CITYCODE,COUNTY,COUNTYCODE,TOWN,TOWNCODE,KEYCODE,BIZCODE,KEYTYPE,MATCHTYPE,CREATEDATE,MODIFYDATE,ISSUCCESS
            FROM t_new_seg_hismatch
            WHERE CITYCODE = '",var_city,"';");
     select sql_sync;
          set @sql = sql_sync;
      prepare s1 from @sql;
      execute s1;
            FETCH cur into var_city,var_tablename;
        end WHILE;

        CLOSE cur;
    end;//
        
        call pro_sync_seg();