将多个表结构相同的表插入到一张表

示例如下：

create table dept1 as SELECT * FROM dept ;
create table dept2 as SELECT * FROM dept ;
create table dept3 as SELECT * FROM dept ;
create table temp_dept as SELECT * FROM dept where 1=2;


将dept 开头的表循环插入到 temp_dept 表中

delare SQL_STR VARCHAR2(500);
BEGIN
FOR I IN(SELECT TABLE_NAME FROM USER_TABLES WHERE TABLE_NAME LIKE 'DEPT%')
  LOOP 
     SQL_STR:='insert into dept_temp select * from '||I.TABLE_NAME;
  DBMS_OUTPUT.put_line(SQL_STR);
  EXECUTE IMMEDIATE SQL_STR;
  END LOOP;
END;