-
SQL code
-
-- 使用Merge来判断是插入还修改 CREATE TABLE T124 ( ID NUMBER ( 4 ), F1 VARCHAR2 ( 20 ), F2 VARCHAR2 ( 20 ) ); INSERT INTO T124 VALUES ( 1 , ' A ' , ' AA ' ); INSERT INTO T124 VALUES ( 2 , ' B ' , ' BB ' ); INSERT INTO T124 VALUES ( 3 , ' C ' , ' CC ' );
实测结果:
=====================================================
CREATE TABLE Tbl123( ID1 NUMBER(4), ID2 VARCHAR2(20), ID3 VARCHAR2(20) );INSERT INTO Tbl123 VALUES(1, 'A','123');INSERT INTO Tbl123 VALUES(2, 'B','1234');INSERT INTO Tbl123 VALUES(3, 'C','123123');commit; declare sSql varchar2(3000); iCount number; v_ID1 number; v_ID2 varchar2(20); v_ID3 varchar2(20);begin iCount := 0; v_ID1 := 1; v_ID3 := 'wee'; sSql := 'begin select count(*) into :1 from Tbl123 where Id1 = :2; end;'; execute immediate sSql using out iCount,in v_ID1; if iCount > 0 then sSql := 'begin select distinct Id2 into :1 from Tbl123 where Id1 = :2 and rownum = 1; end;'; execute immediate sSql using out v_ID2,in v_ID1; sSql := 'begin update Tbl123 set Id3 = :1 where Id1 = :2 and Id2 = :3; end;'; execute immediate sSql using in v_ID3,in v_ID1,in v_ID2; dbms_output.put_line('update'); else sSql := 'begin insert into Tbl123(Id1,ID2,Id3) values (:1,:2,:3); end;'; execute immediate sSql using in v_ID1,in v_ID2,in v_ID3; dbms_output.put_line('insert'); end if; commit;exception when others then dbms_output.put_line('Error');end;select * from Tbl123;
===================================================
Merge into dept_2 d using dept de on(d.id=de.id)when matched thenupdate setd.name=de.namewhen not matched theninsert (id,name)values (de.id,de.name);-- 解释-- *1). Merge into dept_2 d using dept de -- 从源表 dept (别名 de) 往 dept_2 表(别名d)中插入或更新数据-- *2). on(d.id=de.id) :源表(dept)与目标表(dept_2)的连接条件是 (dept_2.id=dept.id)-- *3). when matched then update set d.name=de.name-- 当目标表找到与源表匹配的记录行(匹配条件:dept_2.id=dept.id ),-- 则更新目标表(dept_2) 的 name字段值为 源表 (dept) 的name字段值-- *4). when not matched then insert (id,name) values (de.id,de.name)-- 如果没有找到匹配记录(即:源表记录的id字段值,在目标表中不存在),-- 则将源表中这样的记录插入目标表-- 简单理解为一句话:有:则更新;无:则插入!