表1
create table TB1
(
QUEUE_ID NVARCHAR2(50),
SIGINCOUNT INTEGER,
REALBUSY INTEGER,
PREBUSY INTEGER,
REALBUSYIN04A06A100 INTEGER,
ENABLERESTCOUNT INTEGER
)
表2create table TB2
(
QUEUE_ID NVARCHAR2(50),
SIGINCOUNT INTEGER,
REALBUSY INTEGER,
PREBUSY INTEGER,
REALBUSYIN04A06A100 INTEGER
)将 TB2表中的数据插入TB1中,如果TB1中存在QUEUE_ID与TB2中QUEUE_ID相同的记录,则更新另外四个字段,没有则插入,谢谢!
create table TB1
(
QUEUE_ID NVARCHAR2(50),
SIGINCOUNT INTEGER,
REALBUSY INTEGER,
PREBUSY INTEGER,
REALBUSYIN04A06A100 INTEGER,
ENABLERESTCOUNT INTEGER
)
表2create table TB2
(
QUEUE_ID NVARCHAR2(50),
SIGINCOUNT INTEGER,
REALBUSY INTEGER,
PREBUSY INTEGER,
REALBUSYIN04A06A100 INTEGER
)将 TB2表中的数据插入TB1中,如果TB1中存在QUEUE_ID与TB2中QUEUE_ID相同的记录,则更新另外四个字段,没有则插入,谢谢!
--1,
update TB1 set ... where exists (select 1 from TB2 where tb2.queue_id =tb1.queue_id)
--2,
insert into tb1 select ..from tb2
注意,2个表字段数不一致。
update TB1 set ... where exists (select 1 from TB2 where tb2.queue_id =tb1.queue_id)
--2,
insert into tb1 select ..from tb2
where not exists (select 1 from TB2 where tb2.queue_id =tb1.queue_id)
exec_point varchar2(100);
dec_cnt number(2) := 0;
cursor cur_tb2 is
select * from tb2;
row_cur_tb2 cur_tb2%rowtype;
begin
exec_point := 'P_INS_UP begin';
for row_cur_tb2 in cur_tb2
loop
select count(1) into dec_cnt from tb1 where tb1.queue_id = row_cur_tb2.queue_id;
if (dec_cnt = 0) then
exec_point := 'insert' || row_cur_tb2.queue_id;
insert into tb1
(QUEUE_ID, SIGINCOUNT, REALBUSY, PREBUSY, REALBUSYIN04A06A100)
values
(row_cur_tb2.queue_id,
row_cur_tb2.sigincount,
row_cur_tb2.realbusy,
row_cur_tb2.prebusy,
row_cur_tb2.realbusyin04a06a100);
else
exec_point := 'update' || row_cur_tb2.queue_id;
update tb1
set SIGINCOUNT = row_cur_tb2.sigincount,
REALBUSY = row_cur_tb2.realbusy,
PREBUSY = row_cur_tb2.prebusy,
REALBUSYIN04A06A100 = row_cur_tb2.realbusyin04a06a100
where QUEUE_ID = row_cur_tb2.queue_id;
end if;
end loop;
commit;
exception
when others then
dbms_output.put_line(exec_point);
rollback;
end P_INS_UP;
using view_v8_restcomput t2
on (t1.queue_id = t2.queue_id)
when matched then
update set
t1.Sigincount=t2.Sigincount,
t1.realbusy=t2.RealBusy,
t1.prebusy=t2.PreBusy,
t1.realbusyin04a06a100=t2.RealBusyIn04A06A100
when not matched then
insert values(t2.queue_id,t2.Sigincount,t2.RealBusy,t2.PreBusy,t2.RealBusyIn04A06A100)我在网上找的一段用merge的,我修改了一下,不能编译通过,错在哪里,谢谢大家
如
insert(t1.queue_id,t1......) values(t2.queue_id,t2.Sigincount,t2.RealBusy,t2.PreBusy,t2.RealBusyIn04A06A100)
using TB2 t2
on (t1.QUEUE_ID=t2.QUEUE_ID)
when matched then update set t1.SIGINCOUNT=t2.SIGINCOUNT AND t1.REALBUSY=t2.REALBUSY AND t1.PREBUSY=t2.PREBUSY AND
t1.REALBUSYIN04A06A100=t2.REALBUSYIN04A06A100
when not matched then insert (QUEUE_ID, SIGINCOUNT, REALBUSY,PREBUSY,REALBUSYIN04A06A100)
values (t2.QUEUE_ID, t2.SIGINCOUNT, t2.REALBUSY, t2.REALBUSYIN04A06A100)
using view_v8_restcomput t2
on (t1.queue_id = t2.queue_id)
when matched then
update set
t1.Sigincount=t2.Sigincount,
t1.realbusy=t2.RealBusy,
t1.prebusy=t2.PreBusy,
t1.realbusyin04a06a100=t2.RealBusyIn04A06A100
when not matched then
insert values(t2.queue_id,t2.Sigincount,t2.RealBusy,t2.PreBusy,t2.RealBusyIn04A06A100,null)