使用MERGE语句,这里简单总结一下 (一个语句解决update 和 insert 功能)
来源:互联网 发布:时间管理工具 知乎 编辑:程序博客网 时间:2024/06/02 20:57
在数据仓库中的转换和装载过程中,经常会使用MERGE语句,这里简单总结一下。
MERGE语句是Oracle9i新增的语法,用来合并UPDATE和INSERT语句。通过MERGE语句,根据一张表或子查询的连接条件对另外一张表进行查询,连接条件匹配上的进行UPDATE,无法匹配的执行INSERT。这个语法仅需要一次全表扫描就完成了全部工作,执行效率要高于INSERT+UPDATE。
下面看个具体的例子:
SQL> CREATE TABLE T AS SELECT ROWNUM ID, A.* FROM DBA_OBJECTS A;
表已创建。
SQL> CREATE TABLE T1 AS
2 SELECT ROWNUM ID, OWNER, TABLE_NAME, CAST('TABLE' AS VARCHAR2(100)) OBJECT_TYPE
3 FROM DBA_TABLES;
表已创建。
SQL> MERGE INTO T1 USING T
2 ON (T.OWNER = T1.OWNER AND T.OBJECT_NAME = T1.TABLE_NAME AND T.OBJECT_TYPE = T1.OBJECT_TYPE)
3 WHEN MATCHED THEN UPDATE SET T1.ID = T.ID
4 WHEN NOT MATCHED THEN INSERT VALUES (T.ID, T.OWNER, T.OBJECT_NAME, T.OBJECT_TYPE);
6165 行已合并。
SQL> SELECT ID, OWNER, OBJECT_NAME, OBJECT_TYPE FROM T
2 MINUS
3 SELECT * FROM T1;
未选定行
MERGE语法其实很简单,下面稍微修改一下例子。
SQL> DROP TABLE T;
表已丢弃。
SQL> DROP TABLE T1;
表已丢弃。
SQL> CREATE TABLE T AS SELECT ROWNUM ID, A.* FROM DBA_OBJECTS A;
表已创建。
SQL> CREATE TABLE T1 AS SELECT ROWNUM ID, OWNER, TABLE_NAME FROM DBA_TABLES;
表已创建。
SQL> MERGE INTO T1 USING T
2 ON (T.OWNER = T1.OWNER AND T.OBJECT_NAME = T1.TABLE_NAME)
3 WHEN MATCHED THEN UPDATE SET T1.ID = T.ID
4 WHEN NOT MATCHED THEN INSERT VALUES (T.ID, T.OWNER, T.OBJECT_NAME);
MERGE INTO T1 USING T
*
ERROR 位于第 1 行:
ORA-30926: 无法在源表中获得一组稳定的行
这个错误是使用MERGE最常见的错误,造成这个错误的原因是由于通过连接条件得到的T的记录不唯一。最简单的解决方法类似:
SQL> MERGE INTO T1
2 USING (SELECT OWNER, OBJECT_NAME, MAX(ID) ID FROM T GROUP BY OWNER, OBJECT_NAME) T
3 ON (T.OWNER = T1.OWNER AND T.OBJECT_NAME = T1.TABLE_NAME)
4 WHEN MATCHED THEN UPDATE SET T1.ID = T.ID
5 WHEN NOT MATCHED THEN INSERT VALUES (T.ID, T.OWNER, T.OBJECT_NAME);
5775 行已合并。
另外,MERGE语句的UPDATE不能修改用于连接的列,否则会报错,详细信息可以参考:http://blog.itpub.net/post/468/14844
===============================================================
ref: http://tomszrp.itpub.net/post/11835/263865
在Oracle 10g之前,merge语句支持匹配更新和不匹配插入2种简单的用法,在10g中Oracle对merge语句做了增强,增加了条件选项和DELETE操作。下面我通过一个demo来简单介绍一下10g中merge的增强和10g前merge的用法。
参考Oracle 的SQL Reference,大家可以看到Merge Statement的语法如下:
MERGE [hint] INTO [schema .] table [t_alias] USING [schema .]
{ table | view | subquery } [t_alias] ON ( condition )
WHEN MATCHED THEN merge_update_clause
WHEN NOT MATCHED THEN merge_insert_clause;
下面我在windows xp 下10.2.0.1版本上做一个测试看看
SQL> select * from v$version;BANNER----------------------------------------------------------------Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - ProdPL/SQL Release 10.2.0.1.0 - ProductionCORE 10.2.0.1.0 ProductionTNS for 32-bit Windows: Version 10.2.0.1.0 - ProductionNLSRTL Version 10.2.0.1.0 - ProductionSQL> 一、创建测试用的表SQL> create table subs(msid number(9), 2 ms_type char(1), 3 areacode number(3) 4 );表已创建。SQL> create table acct(msid number(9), 2 bill_month number(6), 3 areacode number(3), 4 fee number(8,2) default 0.00);表已创建。SQL> SQL> insert into subs values(905310001,0,531);已创建 1 行。SQL> insert into subs values(905320001,1,532);已创建 1 行。SQL> insert into subs values(905330001,2,533);已创建 1 行。SQL> commit;提交完成。SQL> 二、下面先演示一下merge的基本功能1) matched 和not matched clauses 同时使用 merge into acct a using subs b on (a.msid=b.msid) when MATCHED then update set a.areacode=b.areacode when NOT MATCHED then insert(msid,bill_month,areacode) values(b.msid,'200702',b.areacode);2) 只有not matched clause,也就是只插入不更新 merge into acct a using subs b on (a.msid=b.msid) when NOT MATCHED then insert(msid,bill_month,areacode) values(b.msid,'200702',b.areacode);3) 只有matched clause, 也就是只更新不插入 merge into acct a using subs b on (a.msid=b.msid) when MATCHED then update set a.areacode=b.areacode Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 Connected as studySQL> select * from subs; MSID MS_TYPE AREACODE---------- ------- -------- 905310001 0 531 905320001 1 532 905330001 2 533SQL> select * from acct; MSID BILL_MONTH AREACODE FEE---------- ---------- -------- ----------SQL> SQL> merge into acct a 2 using subs b on (a.msid=b.msid) 3 when MATCHED then 4 update set a.areacode=b.areacode 5 when NOT MATCHED then 6 insert(msid,bill_month,areacode) 7 values(b.msid,'200702',b.areacode);DoneSQL> select * from acct; MSID BILL_MONTH AREACODE FEE---------- ---------- -------- ---------- 905320001 200702 532 0.00 905330001 200702 533 0.00 905310001 200702 531 0.00SQL> insert into subs values(905340001,3,534);1 row insertedSQL> select * from subs; MSID MS_TYPE AREACODE---------- ------- -------- 905340001 3 534 905310001 0 531 905320001 1 532 905330001 2 533SQL> SQL> merge into acct a 2 using subs b on (a.msid=b.msid) 3 when NOT MATCHED then 4 insert(msid,bill_month,areacode) 5 values(b.msid,'200702',b.areacode);DoneSQL> select * from acct; MSID BILL_MONTH AREACODE FEE---------- ---------- -------- ---------- 905320001 200702 532 0.00 905330001 200702 533 0.00 905310001 200702 531 0.00 905340001 200702 534 0.00SQL> update subs set areacode=999;4 rows updatedSQL> select * from subs; MSID MS_TYPE AREACODE---------- ------- -------- 905340001 3 999 905310001 0 999 905320001 1 999 905330001 2 999SQL> select * from acct; MSID BILL_MONTH AREACODE FEE---------- ---------- -------- ---------- 905320001 200702 532 0.00 905330001 200702 533 0.00 905310001 200702 531 0.00 905340001 200702 534 0.00SQL> SQL> merge into acct a 2 using subs b on (a.msid=b.msid) 3 when MATCHED then 4 update set a.areacode=b.areacode;DoneSQL> select * from acct; MSID BILL_MONTH AREACODE FEE---------- ---------- -------- ---------- 905320001 200702 999 0.00 905330001 200702 999 0.00 905310001 200702 999 0.00 905340001 200702 999 0.00SQL> 三、10g中增强一:条件操作1) matched 和not matched clauses 同时使用 merge into acct a using subs b on (a.msid=b.msid) when MATCHED then update set a.areacode=b.areacode where b.ms_type=0 when NOT MATCHED then insert(msid,bill_month,areacode) values(b.msid,'200702',b.areacode) where b.ms_type=0;2) 只有not matched clause,也就是只插入不更新 merge into acct a using subs b on (a.msid=b.msid) when NOT MATCHED then insert(msid,bill_month,areacode) values(b.msid,'200702',b.areacode) where b.ms_type=0;3) 只有matched clause, 也就是只更新不插入 merge into acct a using subs b on (a.msid=b.msid) when MATCHED then update set a.areacode=b.areacode where b.ms_type=0; Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 Connected as studySQL> select * from subs; MSID MS_TYPE AREACODE---------- ------- -------- 905310001 0 531 905320001 1 532 905330001 2 533SQL> select * from acct; MSID BILL_MONTH AREACODE FEE---------- ---------- -------- ----------SQL> SQL> merge into acct a 2 using subs b on (a.msid=b.msid) 3 when MATCHED then 4 update set a.areacode=b.areacode 5 where b.ms_type=0 6 when NOT MATCHED then 7 insert(msid,bill_month,areacode) 8 values(b.msid,'200702',b.areacode) 9 where b.ms_type=0;DoneSQL> select * from acct; MSID BILL_MONTH AREACODE FEE---------- ---------- -------- ---------- 905310001 200702 531 0.00SQL> insert into subs values(905360001,0,536);1 row insertedSQL> select * from subs; MSID MS_TYPE AREACODE---------- ------- -------- 905360001 0 536 905310001 0 531 905320001 1 532 905330001 2 533SQL> SQL> merge into acct a 2 using subs b on (a.msid=b.msid) 3 when NOT MATCHED then 4 insert(msid,bill_month,areacode) 5 values(b.msid,'200702',b.areacode) 6 where b.ms_type=0;DoneSQL> select * from acct; MSID BILL_MONTH AREACODE FEE---------- ---------- -------- ---------- 905310001 200702 531 0.00 905360001 200702 536 0.00SQL> update subs set areacode=888 where ms_type=0;2 rows updatedSQL> select * from subs; MSID MS_TYPE AREACODE---------- ------- -------- 905360001 0 888 905310001 0 888 905320001 1 532 905330001 2 533SQL> select * from acct; MSID BILL_MONTH AREACODE FEE---------- ---------- -------- ---------- 905310001 200702 531 0.00 905360001 200702 536 0.00SQL> SQL> merge into acct a 2 using subs b on (a.msid=b.msid) 3 when MATCHED then 4 update set a.areacode=b.areacode 5 where b.ms_type=0;DoneSQL> select * from acct; MSID BILL_MONTH AREACODE FEE---------- ---------- -------- ---------- 905310001 200702 888 0.00 905360001 200702 888 0.00SQL> 四、10g中增强二:删除操作An optional DELETE WHERE clause can be used to clean up after a merge operation. Only those rows which match both the ON clause and the DELETE WHERE clause are deleted. merge into acct a using subs b on (a.msid=b.msid) when MATCHED then update set a.areacode=b.areacode delete where (b.ms_type!=0); SQL> select * from subs; MSID MS_TYPE AREACODE---------- ------- -------- 905310001 0 531 905320001 1 532 905330001 2 533SQL> select * from acct; MSID MS_TYPE AREACODE---------- ------- -------- 905310001 0 531 905320001 1 532 905330001 2 533SQL> SQL> merge into acct a 2 using subs b on (a.msid=b.msid) 3 when MATCHED then 4 update set a.areacode=b.areacode 5 delete where (b.ms_type!=0);DoneSQL> select * from acct; MSID MS_TYPE AREACODE---------- ------- -------- 905310001 0 531SQL> 更为详尽的语法,请参考Oracle SQL Reference手册!
T1必须是oracle实际存在的表!
declare
--T1必须是oracle实际存在的表或视图!
--create table t (c1 number,c2 number);
--insert into t values (1,2);
type TYPE_REC_RESULT is record(
C1 number,
C2 number);
type TYPE_TAB_RESULT is table of TYPE_REC_RESULT index by binary_integer;
T1 TYPE_TAB_RESULT;
begin
merge into T1
using T
on (T1.C1 = T.C1)
when matched then
update
set T1.C2 = T.C2
when not matched then
insert
values
(T.C1, T.C2);
end;
/
PL/SQL: ORA-00942: 表或视图不存在
- 使用MERGE语句,这里简单总结一下 (一个语句解决update 和 insert 功能)
- 使用Merge Into 语句实现 Insert/Update
- 使用Merge Into 语句实现 Insert/Update
- ORACLE MERGE 介绍(合并UPDATE和INSERT语句)
- 使用Merge优化改写一个update语句
- Merge Into 语句代替Insert/Update
- oracle中merge into..using..on..when..when..用法 使用一条语句实现update/insert 语句
- insert 复合语句和 update 复合语句
- update select 语句和merge into语句
- 一个从T-SQL MERGE语句中获取insert, update, delete 行数的脚本
- mysql数据库insert、delete、update语句总结
- Oracle中用一条SQL语句直接进行Insert/Update的操作--merge into的使用
- Oracle中5个核心Sql语句的基本构造:Select、Insert、Update、Delete和Merge
- Merge Into 语句代替Insert/Update在Oracle中的应用
- Oracle的INSERT ALL和MERGE语句
- Oracle的INSERT ALL和MERGE语句
- 使用Java生成insert,select,update语句
- mysql insert update语句
- 软件开发项目计划书编写说明
- 如何在 SQL Server 的存储过程和函数里进行错误处理
- 手动打包
- 测试中,使用的linux指令
- 解决 IE7下图片上传预览
- 使用MERGE语句,这里简单总结一下 (一个语句解决update 和 insert 功能)
- 理解I/O Completion Port(完成端口)
- 又做了一点东西.
- 开发日志(2008)
- linux 脚本-学习记录
- FTPClient下载目录(递归方法)
- Open Cascade 重新编译
- VC++下对匿名管道的编程实现
- Ubuntu 安装WordNet3