如果两个表的结构相同:
create or replace PROCEDURE P_INSERT_PRO
(
p_DATE IN DATE
)
AS
BEGIN
INSERT INTO TABLE1
SELECT * FROM TABLE2 WHERE TO_CHAR(TABLE2.TIME_FIELD,'YYYYMMDD') = TO_CHAR(NVL(p_DATE,SYSDATE-1),'YYYYMMDD');
EXECPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SUBSTR('ERROR='||SQLERRM,1,255));
END P_INSERT_PRO;
create or replace PROCEDURE P_INSERT_PRO
(
p_DATE IN DATE
)
AS
BEGIN
INSERT INTO TABLE1
SELECT * FROM TABLE2 WHERE TO_CHAR(TABLE2.TIME_FIELD,'YYYYMMDD') = TO_CHAR(NVL(p_DATE,SYSDATE-1),'YYYYMMDD');
EXECPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SUBSTR('ERROR='||SQLERRM,1,255));
END P_INSERT_PRO;
p_time in date default null
)
as
l_time date;
begin
if p_time is null then
l_time:=sysdate-1;
else
l_time:=p_time;
end if;
insert into b value
select * from a where trunc(a.day)=trunc(l_time);
commit;
end;
/
原表a(f1,f2),b(f1,f2),c(f1,f2),d(f1,f2)
目标表t(f1,f2,f3,f4,f5,f6,f7,f8)create or replace procedure p(
p_time in date default null
)
as
l_time date;
begin
if p_time is null then
l_time:=sysdate-1;
else
l_time:=p_time;
end if;
insert into t(f1,f2) select f1,f2 from a where trunc(time)=trunc(l_time);
insert into t(f3,f4) select f1,f2 from b where trunc(time)=trunc(l_time);
insert into t(f5,f6) select f1,f2 from c where trunc(time)=trunc(l_time);
insert into t(f7,f8) select f1,f2 from d where trunc(time)=trunc(l_time);
commit;
end;
/
你写应该不行,因为INSERT是在表的末尾插入一行数据。你那样做将产生4行数据,每行数据只有2列。
应该第一次有INSERT,而后面有UPDATE.
....
insert into t(f1,f2,f3,f4,f5,f6,f7,f8)
select a.f1,a.f2,b.f1,b.f2,c.f1,c.f2,d.f1,d.f2 from a,b,c,d
where trunc(a.time)=trunc(l_time)
and trunc(b.time)=trunc(l_time)
and trunc(c.time)=trunc(l_time)
and trunc(d.time)=trunc(l_time);
....
先找出4个小表中在统计日期里记录最多的表名,
select count(1) into acnt from a where trunc(time)=trunc(l_time);
select count(1) into bcnt from b where trunc(time)=trunc(l_time);
select count(1) into ccnt from c where trunc(time)=trunc(l_time);
select count(1) into dcnt from d where trunc(time)=trunc(l_time);
....如:a
然后组装sql:
....
insert into t(f1,f2,f3,f4,f5,f6,f7,f8)
select aa.f1,aa.f2,bb.f1,bb.f2,cc.f1,cc.f2,dd.f1,dd.f2 from
(select rownum n,a.f1,a.f2 from a where trunc(a.time)=trunc(l_time)) aa,
(select rownum n,b.f1,b.f2 from b where trunc(b.time)=trunc(l_time)) bb,
(select rownum n,c.f1,c.f2 from c where trunc(c.time)=trunc(l_time)) cc,
(select rownum n,d.f1,d.f2 from a where trunc(d.time)=trunc(l_time)) dd
where aa.n=bb.n(+) and aa.n=cc.n(+) and aa.n=dd.n(+)
....如果是b 表则最后的条件改为:
where bb.n=aa.n(+) and bb.n=cc.n(+) and bb.n=dd.n(+)
....当然可以组织成动态sql,但不推荐这么做,动态sql每次都要重新解析,性能不好...