create or replace
procedure ups(xa number)
as
begin
merge into mergetest m using dual on (a = xa)
when not matched then insert (a,b) values (xa,1)
when matched then update set b = b+1;
end ups;
/
drop table mergetest;
create table mergetest(a number, b number);
call ups(10);
call ups(10);
call ups(20);
select * from mergetest;
A B
---------------------- ----------------------
10 2
20 1
MERGE INTO Employee USING dual ON ( "id"=2097153 )
WHEN MATCHED THEN UPDATE SET "last"="smith" , "name"="john"
WHEN NOT MATCHED THEN INSERT ("id","last","name")
VALUES ( 2097153,"smith", "john" )
UPDATE tablename
SET val1 = in_val1,
val2 = in_val2
WHERE val3 = in_val3;
IF ( sql%rowcount = 0 )
THEN
INSERT INTO tablename
VALUES (in_val1, in_val2, in_val3);
END IF;
-6
如果不存在则插入
更新:
INSERT INTO mytable (id1, t1)
SELECT 11, 'x1' FROM DUAL
WHERE NOT EXISTS (SELECT id1 FROM mytble WHERE id1 = 11);
UPDATE mytable SET t1 = 'x1' WHERE id1 = 11;
PROCEDURE MyProc (
...
) IS
BEGIN
LOOP
BEGIN
MERGE INTO Employee USING dual ON ( "id"=2097153 )
WHEN MATCHED THEN UPDATE SET "last"="smith" , "name"="john"
WHEN NOT MATCHED THEN INSERT ("id","last","name")
VALUES ( 2097153,"smith", "john" );
EXIT; -- success? -> exit loop
EXCEPTION
WHEN NO_DATA_FOUND THEN -- the entry was concurrently deleted
NULL; -- exception? -> no op, i.e. continue looping
WHEN DUP_VAL_ON_INDEX THEN -- an entry was concurrently inserted
NULL; -- exception? -> no op, i.e. continue looping
END;
END LOOP;
END;
MERGE INTO KBS.NUFUS_MUHTARLIK B
USING (
SELECT '028-01' CILT, '25' SAYFA, '6' KUTUK, '46603404838' MERNIS_NO
FROM DUAL
) E
ON (B.MERNIS_NO = E.MERNIS_NO)
WHEN MATCHED THEN
UPDATE SET B.CILT = E.CILT, B.SAYFA = E.SAYFA, B.KUTUK = E.KUTUK
WHEN NOT MATCHED THEN
INSERT ( CILT, SAYFA, KUTUK, MERNIS_NO)
VALUES (E.CILT, E.SAYFA, E.KUTUK, E.MERNIS_NO);
UPDATE tablename SET val1 = in_val1, val2 = in_val2
WHERE val3 = in_val3;
IF ( sql%notfound ) THEN
INSERT INTO tablename
VALUES (in_val1, in_val2, in_val3);
END IF;
下面的代码是可能新的和改进的代码
MERGE INTO tablename USING dual ON ( val3 = in_val3 )
WHEN MATCHED THEN UPDATE SET val1 = in_val1, val2 = in_val2
WHEN NOT MATCHED THEN INSERT
VALUES (in_val1, in_val2, in_val3)
CREATE GLOBAL TEMPORARY TABLE t1
(id VARCHAR2(5) ,
value VARCHAR2(5),
value2 VARCHAR2(5)
)
ON COMMIT DELETE ROWS;
CREATE GLOBAL TEMPORARY TABLE t2
(id VARCHAR2(5) ,
value VARCHAR2(5),
value2 VARCHAR2(5))
ON COMMIT DELETE ROWS;
ALTER TABLE t2 ADD CONSTRAINT PK_LKP_MIGRATION_INFO PRIMARY KEY (id);
insert into t1 values ('a','1','1');
insert into t1 values ('b','4','5');
insert into t2 values ('b','2','2');
insert into t2 values ('c','3','3');
merge into t2
using t1
on (t1.id = t2.id)
when matched then
update set t2.value = t1.value,
t2.value2 = t1.value2
when not matched then
insert (t2.id, t2.value, t2.value2)
values(t1.id, t1.value, t1.value2);
select * from t2
结果:
b 4 5
c 3 3
a 1 1
-3
试试这个,
insert into b_building_property (
select
'AREA_IN_COMMON_USE_DOUBLE','Area in Common Use','DOUBLE', null, 9000, 9
from dual
)
minus
(
select * from b_building_property where id = 9
)
;
INSERT
FIRST WHEN
credit_limit >=100000
THEN INTO
rich_customers
VALUES(cust_id,cust_credit_limit)
INTO customers
ELSE
INTO customers SELECT * FROM new_customers;
12 回答
MERGE的替代品(“老式方式”):
MERGE statement合并两个表之间的数据 . 使用DUAL允许我们使用此命令 . 请注意,这不受并发访问的保护 .
上面的PL / SQL中的双重例子很棒,因为我想做类似的事情,但我想要客户端...所以这里是我用来直接从一些C#发送类似语句的SQL
但是,从C#的角度来看,这提供的速度比执行更新要慢,并查看受影响的行是否为0,如果是,则执行插入操作 .
没有例外检查的另一种选择:
如果不存在则插入
更新:
到目前为止给出的答案都没有,正如蒂姆西尔维斯特的评论所指出的那样,并且在比赛的情况下会引发例外 . 要解决这个问题,插入/更新组合必须包含在某种循环语句中,以便在异常情况下重试整个事件 .
作为一个例子,这里是Grommit的代码如何被包装在一个循环中,以便在并发运行时使其安全:
注:在交易模式
SERIALIZABLE
,我不推荐btw,你可能会遇到ORA-08177: can't serialize access for this transaction例外 .我想要Grommit回答,除非它需要重复值 . 我找到了可能出现一次的解决方案:http://forums.devshed.com/showpost.php?p=1182653&postcount=2
关于两种解决方案的说明:
1)插入,如果异常则更新,
要么
2)更新,如果sql%rowcount = 0则插入
是否首先插入或更新的问题也取决于应用程序 . 您是否期望更多插入或更多更新?最有可能成功的那个应该先行 .
如果你选错了,你将获得一堆不必要的索引读取 . 这不是一个大问题,但仍有待考虑 .
多年来我一直在使用第一个代码示例 . 注意不要发现而不是计数 .
下面的代码是可能新的和改进的代码
在第一个示例中,更新执行索引查找 . 它必须,以更新正确的行 . Oracle打开一个隐式游标,我们使用它来包装相应的插入,这样我们就知道插入只会在键不存在时发生 . 但插入是一个独立的命令,它必须进行第二次查找 . 我不知道merge命令的内部工作原理,但由于命令是一个单元,因此Oracle可以使用单个索引查找执行正确的插入或更新 .
我认为合并更好,当你有一些处理要做,这意味着从一些表中获取数据并更新表,可能插入或删除行 . 但对于单行情况,您可能会考虑第一种情况,因为语法更常见 .
使用MERGE将一个表复制并粘贴到另一个表的示例:
结果:
b 4 5
c 3 3
a 1 1
试试这个,
来自http://www.praetoriate.com/oracle_tips_upserts.htm:
“在Oracle9i中,UPSERT可以在一个语句中完成此任务:”