有1000多个表结构相同的表,现在想把这1000多个表的数据插入到一个临时表中,
我的想法是:
1. 定义游标取得表名称
CURSOR A IS select table_name
from user_tables
where table_name like 'ABC_%'
order by table_name;
2. 遍历游标得到表名称,然后操作数据
insert into select * from 动态表名;
DECLARE CURSOR A IS select table_name
from user_tables
where table_name like 'ABC_%'
order by table_name;
CURSOR B(v_t varchar2) IS
select gsmc,SUM(cl)
from v_t
group by gsmc
BEGIN
FOR RA IN A LOOP
FOR RB IN B(RA.table_name) LOOP
EXECUTE IMMEDIATE 'insert into temp values(RB.a,RB.b,RB.c)';
end loop;
commit;
END LOOP;
END; 现在遇到错误,提示表名称不存在, 查了一下说表名称不能绑定变量,谁有好办法实现这个功能?
我的想法是:
1. 定义游标取得表名称
CURSOR A IS select table_name
from user_tables
where table_name like 'ABC_%'
order by table_name;
2. 遍历游标得到表名称,然后操作数据
insert into select * from 动态表名;
DECLARE CURSOR A IS select table_name
from user_tables
where table_name like 'ABC_%'
order by table_name;
CURSOR B(v_t varchar2) IS
select gsmc,SUM(cl)
from v_t
group by gsmc
BEGIN
FOR RA IN A LOOP
FOR RB IN B(RA.table_name) LOOP
EXECUTE IMMEDIATE 'insert into temp values(RB.a,RB.b,RB.c)';
end loop;
commit;
END LOOP;
END; 现在遇到错误,提示表名称不存在, 查了一下说表名称不能绑定变量,谁有好办法实现这个功能?
EXECUTE IMMEDIATE 'insert into temp select gsmc,SUM(cl) from '||RA.table_name||' group by gsmc';
end loop;cursor b不需要