MySQL 常用的 procedure

ゞ浴缸里的玫瑰 2022-11-30 01:23 128阅读 0赞

## 循环执行 ##

delimiter ;;
    create procedure idata()
    begin
      declare i int;
      set i=1;
      while(i<=100000)do
        insert into t values(i, i, i);
        set i=i+1;
      end while;
    end;;
    delimiter ;
    call idata();

## 删除索引 ##

-- drop index 的存储过程：当存在时删除，因为 drop index 不支持 if exists
    DROP PROCEDURE IF EXISTS drop_index_if_exists;
    DELIMITER //
    create procedure drop_index_if_exists(IN p_db varchar(200),IN p_tablename varchar(200), IN p_idxname VARCHAR(200))
    begin
      DECLARE str VARCHAR(250);
      set @str=concat(' drop index ',p_idxname,' on ',p_tablename); 
      select count(*) into @cnt from information_schema.statistics where table_name=p_tablename and index_name=p_idxname and TABLE_SCHEMA = p_db;
      if @cnt >0 then 
        PREPARE stmt FROM @str;
        EXECUTE stmt ;
      end if;
    END
    //
    DELIMITER ;

## 删除列 ##

-- drop column 的存储过程：当存在时删除，因为 drop column 不支持 if exists
    DROP PROCEDURE IF EXISTS drop_column_if_exists;
    DELIMITER //
    create procedure drop_column_if_exists(IN p_db varchar(200),IN p_tablename varchar(200), IN p_columnname VARCHAR(200))
    begin
      DECLARE str VARCHAR(250);
      set @str=concat(' alter table ',p_tablename,' drop column ',p_columnname); 
      select count(*) into @cnt from information_schema.columns where table_name=p_tablename and column_name=p_columnname and TABLE_SCHEMA = p_db;
      if @cnt >0 then 
        PREPARE stmt FROM @str;
        EXECUTE stmt ;
      end if;
    END
    //
    DELIMITER ;

## 条件不满足立刻返回 ##

DELIMITER //
    create procedure check_duplicate()
    label:begin
      select count(*) into @cnt from (select count(*) from dc_fb_form_tables group by `season_uuid`, `team_uuid`, `round_uuid`, `group_uuid` having count(*) > 1 order by null) t; 
      select concat('check dc_fb_form_tables count is ', @cnt,',') ;
      if @cnt > 0 then
        select 'fail';
        LEAVE label;
      end if;
      
      select count(*) into @cnt from (select count(*) from dc_fb_injuries group by `person_uuid`, `start_date` having count(*) > 1 order by null) t; 
      select concat('check dc_fb_injuries count is ', @cnt,',') ;
      if @cnt > 0 then
        select 'fail';
        LEAVE label;
      end if;
      
      select count(*) into @cnt from (select count(*) from dc_fb_match_event group by `match_uuid`, `event_type`, `code`, `person_uuid`, `team_uuid`, `minute` having count(*) > 1 order by null) t; 
      select concat('check dc_fb_match_event count is ', @cnt,',') ;
      if @cnt > 0 then
        select 'fail';
        LEAVE label;
      end if;
      
    END
    //
    DELIMITER ;