MySql 存储过程 动态sql

将从表中查出的数据作为表名，对其进行操作

• 根据查出来的表名，将该表的数据全部插入到一个总表

CREATE DEFINER=`root`@`%` PROCEDURE `InsertIntoBrief`()
BEGIN

      DECLARE  table_name varchar(64); 
    
    -- 遍历数据结束标志
    DECLARE done INT DEFAULT FALSE;
    
    DECLARE  cur_table CURSOR FOR select table_id from yp_table_mapping;
   
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    
    truncate yp_brief;
    OPEN  cur_table;     
    -- 开始遍历
    read_loop: LOOP
            -- 赋值
            FETCH  NEXT from cur_table INTO table_name;
            IF done THEN
                LEAVE read_loop;
             END IF;
             set @s = concat("insert into yp_brief select * from ",table_name);
             PREPARE s from @s;
             EXECUTE s;
    END LOOP;
 
 
    CLOSE cur_table;


END

• 遍历所有表，判断是否存在某个字段，存在则修改该字段属性

CREATE DEFINER=`root`@`%` PROCEDURE `changeColumnName`()
BEGIN

      DECLARE  table_name varchar(64); 
    
    -- 遍历数据结束标志
    DECLARE done INT DEFAULT FALSE;
    -- 游标
    DECLARE  cur_table CURSOR FOR select table_id from yp_table_mapping;

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    
        TRUNCATE yp_brief;
    -- 打开游标
    OPEN  cur_table;     

    read_loop: LOOP
     
            FETCH  NEXT from cur_table INTO table_name;
            IF done THEN
                LEAVE read_loop;
             END IF;
             if EXISTS(SELECT * FROM information_schema.columns WHERE table_schema = 'database_name'  AND table_name = table_name AND column_name = 'a') then
                     -- 修改字段名称
                set @s = concat("ALTER TABLE ",table_name," CHANGE a b varchar(255)");
                PREPARE s from @s;
                EXECUTE s;
                     
              END IF;
    END LOOP;
 
 
    CLOSE cur_table;


END