drop PROCEDURE P_BACKUP;
create PROCEDURE P_BACKUP()
BEGIN 
DECLARE i INT;  # 申明变量
SET i = 0;  # 变量赋值
WHILE i<5 DO # 结束循环的条件: 当i大于5时跳出while循环
select @Sql:=concat('create table vendor_moodle_hs.',table_name,'_hs as select *,now() as etl_date from vendor_moodle.',table_name,';') from information_schema.tables where table_schema='vendor_moodle' and table_type='base table' and table_name  like 'mdl_%';
prepare stmt from @Sql;
execute stmt;
deallocate prepare stmt;
SET i = i+1;    # 循环一次,i加1
END WHILE;  # 结束while循环
END;
call P_BACKUP()

复制