用trigger
before insert
create trigger trgname
...
reference new as new old as old
declarev_col1 %t.col1;
v_col2 %t.col2;
v_col3 %t.col3;
v_count number;select count (*) into v_count from t where col1=v_col1 and col2=v_col2 and col3=v_col3;
if v_count=0 then
insert into t(...) values(v_col1,v_col2,v_col3)
else
update t set col4=... where col1=v_col1 and col2=v_col2 and col3=v_col3;
endif;
before insert
create trigger trgname
...
reference new as new old as old
declarev_col1 %t.col1;
v_col2 %t.col2;
v_col3 %t.col3;
v_count number;select count (*) into v_count from t where col1=v_col1 and col2=v_col2 and col3=v_col3;
if v_count=0 then
insert into t(...) values(v_col1,v_col2,v_col3)
else
update t set col4=... where col1=v_col1 and col2=v_col2 and col3=v_col3;
endif;
----
merge into a
using b
on (a.col1=b.col1
and a.col2=b.col2
and a.col3=b.col3)
when matched then update set a.col4=b.col4
when not matched then insert (col1
,col2
,col3
,col4
)
values (b.col1
,b.col2
,b.col3
,b.col4
);