Oracle merge into 的用法详解实例作用:merge into 解决用B表跟新A表数据,如果A表中没有,则把B表的数据插入A表;语法:MERGE INTO [your table-name] [rename your table here]USING ( [write your query here] )[rename your query-sql and using just like a table]ON ([conditional expression here] AND [...]...)WHEN MATHED THEN [here you can execute some update sql or something else ]WHEN NOT MATHED THEN [execute something else here ! ]-------实例------- merge into a using (select id,name from b ) c on(a.id=c.id ) when matched then update set a.name=c.name when not matched then insert (a.id,a.name) values (c.id,c.name); 作用:利用表 b 跟新表a ,条件是a.id=b.id,如果a表中没有该条件的数据就插入。如果你的数据量很大,此sql效率非常高。 本文来自CSDN博客,转载请标明出处:http://blog.csdn.net/tianlesoftware/archive/2009/10/23/4714921.aspx
触发器 create or replace trigger tg_a before insert or update or delete on a for each row begin if inserting then insert into b values (:new.col1,:new.col2...);end if; if updating then update b set (col1,col2,col3..)=(:new.col1,:new.col2,:new.col3...)where col1(主键)=:old.col1; end if; if deleting then delete from b where col1=:old.col1; end if; end;
时时同步: 触发器 create or replace trigger tg_a before insert or update or delete on a for each row begin if inserting then insert into b values (:new.col1,:new.col2...);end if; if updating then update b set (col1,col2,col3..)=(:new.col1,:new.col2,:new.col3...)where col1(主键)=:old.col1; end if; if deleting then delete from b where col1=:old.col1; end if; end;每天同步: merge into a using (select id,name from b ) c on(a.id=c.id ) when matched then update set a.name=c.name when not matched then insert (a.id,a.name) values (c.id,c.name); 触发器用多了,不好。 建立表B的时候就关联好表A,让表B永远自动跟着表A变化! --------------------------------------------- 如果A,B表一样,就不要浪费表空间了。建个view就行了。
create table ta(id int primary key,name varchar2(10)); create table tb(id int primary key,name varchar2(10));create or replace trigger trig_ta before insert or update or delete on ta for each row begin if inserting then insert into tb(id,name) select :new.id,:new.name from dual; end if; if updating then update tb set name= :new.name where id =:old.id; end if; if deleting then delete from tb where id =:old.id; end if;end;insert into ta select 1,'aa' from dual; insert into ta select 2,'bb' from dual;select * from tb;update ta set name ='cc'; select * from tb;delete from ta where id =1 ;select * from tb;
create table ta(id int primary key,name varchar2(10)); create table tb(id int primary key,name varchar2(10));merge into tb a using (select id,name from ta) b on (a.id=b.id) when matched then update set a.name=b.name when not matched then insert (a.id,a.name) values(b.id,b.name)
Oracle merge into 的用法详解实例作用:merge into 解决用B表跟新A表数据,如果A表中没有,则把B表的数据插入A表;语法:MERGE INTO [your table-name] [rename your table here]USING ( [write your query here] )[rename your query-sql and using just like a table]ON ([conditional expression here] AND [...]...)WHEN MATHED THEN [here you can execute some update sql or something else ]WHEN NOT MATHED THEN [execute something else here ! ]-------实例-------
merge into a
using (select id,name from b ) c
on(a.id=c.id )
when matched then update set a.name=c.name
when not matched then insert (a.id,a.name) values (c.id,c.name);
作用:利用表 b 跟新表a ,条件是a.id=b.id,如果a表中没有该条件的数据就插入。如果你的数据量很大,此sql效率非常高。
本文来自CSDN博客,转载请标明出处:http://blog.csdn.net/tianlesoftware/archive/2009/10/23/4714921.aspx
create or replace trigger tg_a
before insert or update or delete on a
for each row
begin
if inserting then insert into b values (:new.col1,:new.col2...);end if;
if updating then update b set (col1,col2,col3..)=(:new.col1,:new.col2,:new.col3...)where col1(主键)=:old.col1; end if;
if deleting then delete from b where col1=:old.col1; end if;
end;
---------------------------------------------
那b表有什么用?建个view不就行了。
触发器
create or replace trigger tg_a
before insert or update or delete on a
for each row
begin
if inserting then insert into b values (:new.col1,:new.col2...);end if;
if updating then update b set (col1,col2,col3..)=(:new.col1,:new.col2,:new.col3...)where col1(主键)=:old.col1; end if;
if deleting then delete from b where col1=:old.col1; end if;
end;每天同步:
merge into a
using (select id,name from b ) c
on(a.id=c.id )
when matched then update set a.name=c.name
when not matched then insert (a.id,a.name) values (c.id,c.name); 触发器用多了,不好。
建立表B的时候就关联好表A,让表B永远自动跟着表A变化!
---------------------------------------------
如果A,B表一样,就不要浪费表空间了。建个view就行了。
你想实现类似级联的效果。在oracle里面实现不了,除了写触发器外
create table ta(id int primary key,name varchar2(10));
create table tb(id int primary key,name varchar2(10));create or replace trigger trig_ta
before insert or update or delete
on ta
for each row
begin
if inserting then
insert into tb(id,name) select :new.id,:new.name from dual;
end if;
if updating then
update tb set name= :new.name
where id =:old.id;
end if;
if deleting then
delete from tb where id =:old.id;
end if;end;insert into ta select 1,'aa' from dual;
insert into ta select 2,'bb' from dual;select * from tb;update ta set name ='cc';
select * from tb;delete from ta where id =1 ;select * from tb;
create table ta(id int primary key,name varchar2(10));
create table tb(id int primary key,name varchar2(10));merge into tb a
using (select id,name from ta) b
on (a.id=b.id)
when matched then update set a.name=b.name
when not matched then insert (a.id,a.name) values(b.id,b.name)