Chinaunix首页 | 论坛 | 博客
  • 博客访问: 746311
  • 博文数量: 180
  • 博客积分: 4447
  • 博客等级: 上校
  • 技术积分: 1582
  • 用 户 组: 普通用户
  • 注册时间: 2006-04-03 14:51
文章分类

全部博文(180)

文章存档

2014年(6)

2013年(8)

2011年(125)

2009年(35)

2008年(1)

2007年(5)

分类: Oracle

2011-01-06 19:31:12

  1. MERGE语法
  2. 创建时间:2008年12月23日(星期二) 下午05:04 | 分类:日记 | 天气:南京 晴
  3. MERGE语法
  4. ===========================================================
  5. 作者: xsb(http://xsb.itpub.net)
  6. 发表于:2006.03.01 14:37
  7. 分类: Oracle
  8. 出处:http://xsb.itpub.net/post/419/30766
  9. ---------------------------------------------------------------

  10. 24/05/2005 14:37 FP 在数据仓库中的转换和装载过程中,经常会使用MERGE语句,这里简单总结一下。




  11. MERGE语句是Oracle9i新增的语法,用来合并UPDATE和INSERT语句。通过MERGE语句,根据一张表或子查询的连接条件对另外一张表进行查询,连接条件匹配上的进行UPDATE,无法匹配的执行INSERT。这个语法仅需要一次全表扫描就完成了全部工作,执行效率要高于INSERT+UPDATE。
  12. 下面看个具体的例子:
  13. SQL> CREATE TABLE T AS SELECT ROWNUM ID, A.* FROM DBA_OBJECTS A;
  14. 表已创建。
  15. SQL> CREATE TABLE T1 AS
  16. 2 SELECT ROWNUM ID, OWNER, TABLE_NAME, CAST('TABLE' AS VARCHAR2(100)) OBJECT_TYPE
  17. 3 FROM DBA_TABLES;
  18. 表已创建。
  19. SQL> MERGE INTO T1 USING T
  20. 2 ON (T.OWNER = T1.OWNER AND T.OBJECT_NAME = T1.TABLE_NAME AND T.OBJECT_TYPE = T1.OBJECT_TYPE)
  21. 3 WHEN MATCHED THEN UPDATE SET T1.ID = T.ID
  22. 4 WHEN NOT MATCHED THEN INSERT VALUES (T.ID, T.OWNER, T.OBJECT_NAME, T.OBJECT_TYPE);
  23. 6165 行已合并。
  24. SQL> SELECT ID, OWNER, OBJECT_NAME, OBJECT_TYPE FROM T
  25. 2 MINUS
  26. 3 SELECT * FROM T1;
  27. 未选定行
  28. MERGE语法其实很简单,下面稍微修改一下例子。
  29. SQL> DROP TABLE T;
  30. 表已丢弃。
  31. SQL> DROP TABLE T1;
  32. 表已丢弃。
  33. SQL> CREATE TABLE T AS SELECT ROWNUM ID, A.* FROM DBA_OBJECTS A;
  34. 表已创建。
  35. SQL> CREATE TABLE T1 AS SELECT ROWNUM ID, OWNER, TABLE_NAME FROM DBA_TABLES;
  36. 表已创建。
  37. SQL> MERGE INTO T1 USING T
  38. 2 ON (T.OWNER = T1.OWNER AND T.OBJECT_NAME = T1.TABLE_NAME)
  39. 3 WHEN MATCHED THEN UPDATE SET T1.ID = T.ID
  40. 4 WHEN NOT MATCHED THEN INSERT VALUES (T.ID, T.OWNER, T.OBJECT_NAME);
  41. MERGE INTO T1 USING T
  42. *
  43. ERROR 位于第 1 行:
  44. ORA-30926: 无法在源表中获得一组稳定的行
  45. 这个错误是使用MERGE最常见的错误,造成这个错误的原因是由于通过连接条件得到的T的记录不唯一。最简单的解决方法类似:
  46. SQL> MERGE INTO T1
  47. 2 USING (SELECT OWNER, OBJECT_NAME, MAX(ID) ID FROM T GROUP BY OWNER, OBJECT_NAME) T
  48. 3 ON (T.OWNER = T1.OWNER AND T.OBJECT_NAME = T1.TABLE_NAME)
  49. 4 WHEN MATCHED THEN UPDATE SET T1.ID = T.ID
  50. 5 WHEN NOT MATCHED THEN INSERT VALUES (T.ID, T.OWNER, T.OBJECT_NAME);
  51. 5775 行已合并。
  52. 另外,MERGE语句的UPDATE不能修改用于连接的列,否则会报错,详细信息可以参考:http://blog.itpub.net/post/468/14844

  53. ===============================================================

  54. ref: http://tomszrp.itpub.net/post/11835/263865

  55. 在Oracle 10g之前,merge语句支持匹配更新和不匹配插入2种简单的用法,在10g中Oracle对merge语句做了增强,增加了条件选项和DELETE操作。下面我通过一个demo来简单介绍一下10g中merge的增强和10g前merge的用法。

  56.  

  57. 参考Oracle 的SQL Reference,大家可以看到Merge Statement的语法如下:
  58. MERGE [hint] INTO [schema .] table [t_alias] USING [schema .]
  59. { table | view | subquery } [t_alias] ON ( condition )
  60. WHEN MATCHED THEN merge_update_clause
  61. WHEN NOT MATCHED THEN merge_insert_clause;

  62. 下面我在windows xp 下10.2.0.1版本上做一个测试看看


  63. SQL> select * from v$version;

  64. BANNER
  65. ----------------------------------------------------------------
  66. Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
  67. PL/SQL Release 10.2.0.1.0 - Production
  68. CORE 10.2.0.1.0 Production
  69. TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
  70. NLSRTL Version 10.2.0.1.0 - Production

  71. SQL>
  72. 一、创建测试用的表
  73. SQL> create table subs(msid number(9),
  74.   2 ms_type char(1),
  75.   3 areacode number(3)
  76.   4 );

  77. 表已创建。

  78. SQL> create table acct(msid number(9),
  79.   2 bill_month number(6),
  80.   3 areacode number(3),
  81.   4 fee number(8,2) default 0.00);

  82. 表已创建。

  83. SQL>
  84. SQL> insert into subs values(905310001,0,531);

  85. 已创建 1 行。

  86. SQL> insert into subs values(905320001,1,532);

  87. 已创建 1 行。

  88. SQL> insert into subs values(905330001,2,533);

  89. 已创建 1 行。

  90. SQL> commit;

  91. 提交完成。

  92. SQL>
  93.  

  94. 二、下面先演示一下merge的基本功能

  95. 1) matched 和not matched clauses 同时使用
  96.    merge into acct a
  97.      using subs b on (a.msid=b.msid)
  98.    when MATCHED then
  99.         update set a.areacode=b.areacode
  100.    when NOT MATCHED then
  101.         insert(msid,bill_month,areacode)
  102.         values(b.msid,'200702',b.areacode);
  103. 2) 只有not matched clause,也就是只插入不更新
  104.    merge into acct a
  105.      using subs b on (a.msid=b.msid)
  106.    when NOT MATCHED then
  107.         insert(msid,bill_month,areacode)
  108.         values(b.msid,'200702',b.areacode);

  109. 3) 只有matched clause, 也就是只更新不插入
  110.    merge into acct a
  111.      using subs b on (a.msid=b.msid)
  112.    when MATCHED then
  113.         update set a.areacode=b.areacode
  114.         
  115. Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
  116. Connected as study

  117. SQL> select * from subs;

  118.       MSID MS_TYPE AREACODE
  119. ---------- ------- --------
  120.  905310001 0 531
  121.  905320001 1 532
  122.  905330001 2 533

  123. SQL> select * from acct;

  124.       MSID BILL_MONTH AREACODE FEE
  125. ---------- ---------- -------- ----------

  126. SQL>
  127. SQL> merge into acct a
  128.   2 using subs b on (a.msid=b.msid)
  129.   3 when MATCHED then
  130.   4 update set a.areacode=b.areacode
  131.   5 when NOT MATCHED then
  132.   6 insert(msid,bill_month,areacode)
  133.   7 values(b.msid,'200702',b.areacode);

  134. Done

  135. SQL> select * from acct;

  136.       MSID BILL_MONTH AREACODE FEE
  137. ---------- ---------- -------- ----------
  138.  905320001 200702 532 0.00
  139.  905330001 200702 533 0.00
  140.  905310001 200702 531 0.00

  141. SQL> insert into subs values(905340001,3,534);

  142. 1 row inserted

  143. SQL> select * from subs;

  144.       MSID MS_TYPE AREACODE
  145. ---------- ------- --------
  146.  905340001 3 534
  147.  905310001 0 531
  148.  905320001 1 532
  149.  905330001 2 533

  150. SQL>
  151. SQL> merge into acct a
  152.   2 using subs b on (a.msid=b.msid)
  153.   3 when NOT MATCHED then
  154.   4 insert(msid,bill_month,areacode)
  155.   5 values(b.msid,'200702',b.areacode);

  156. Done

  157. SQL> select * from acct;

  158.       MSID BILL_MONTH AREACODE FEE
  159. ---------- ---------- -------- ----------
  160.  905320001 200702 532 0.00
  161.  905330001 200702 533 0.00
  162.  905310001 200702 531 0.00
  163.  905340001 200702 534 0.00

  164. SQL> update subs set areacode=999;

  165. 4 rows updated

  166. SQL> select * from subs;

  167.       MSID MS_TYPE AREACODE
  168. ---------- ------- --------
  169.  905340001 3 999
  170.  905310001 0 999
  171.  905320001 1 999
  172.  905330001 2 999

  173. SQL> select * from acct;

  174.       MSID BILL_MONTH AREACODE FEE
  175. ---------- ---------- -------- ----------
  176.  905320001 200702 532 0.00
  177.  905330001 200702 533 0.00
  178.  905310001 200702 531 0.00
  179.  905340001 200702 534 0.00

  180. SQL>
  181. SQL> merge into acct a
  182.   2 using subs b on (a.msid=b.msid)
  183.   3 when MATCHED then
  184.   4 update set a.areacode=b.areacode;

  185. Done

  186. SQL> select * from acct;

  187.       MSID BILL_MONTH AREACODE FEE
  188. ---------- ---------- -------- ----------
  189.  905320001 200702 999 0.00
  190.  905330001 200702 999 0.00
  191.  905310001 200702 999 0.00
  192.  905340001 200702 999 0.00

  193. SQL>
  194.  
  195. 三、10g中增强一:条件操作

  196. 1) matched 和not matched clauses 同时使用
  197.    merge into acct a
  198.      using subs b on (a.msid=b.msid)
  199.    when MATCHED then
  200.         update set a.areacode=b.areacode
  201.         where b.ms_type=0
  202.    when NOT MATCHED then
  203.         insert(msid,bill_month,areacode)
  204.         values(b.msid,'200702',b.areacode)
  205.         where b.ms_type=0;
  206. 2) 只有not matched clause,也就是只插入不更新
  207.    merge into acct a
  208.      using subs b on (a.msid=b.msid)
  209.    when NOT MATCHED then
  210.         insert(msid,bill_month,areacode)
  211.         values(b.msid,'200702',b.areacode)
  212.         where b.ms_type=0;

  213. 3) 只有matched clause, 也就是只更新不插入
  214.    merge into acct a
  215.      using subs b on (a.msid=b.msid)
  216.    when MATCHED then
  217.         update set a.areacode=b.areacode
  218.         where b.ms_type=0;
  219.         
  220.         
  221. Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
  222. Connected as study

  223. SQL> select * from subs;

  224.       MSID MS_TYPE AREACODE
  225. ---------- ------- --------
  226.  905310001 0 531
  227.  905320001 1 532
  228.  905330001 2 533

  229. SQL> select * from acct;

  230.       MSID BILL_MONTH AREACODE FEE
  231. ---------- ---------- -------- ----------

  232. SQL>
  233. SQL> merge into acct a
  234.   2 using subs b on (a.msid=b.msid)
  235.   3 when MATCHED then
  236.   4 update set a.areacode=b.areacode
  237.   5 where b.ms_type=0
  238.   6 when NOT MATCHED then
  239.   7 insert(msid,bill_month,areacode)
  240.   8 values(b.msid,'200702',b.areacode)
  241.   9 where b.ms_type=0;

  242. Done

  243. SQL> select * from acct;

  244.       MSID BILL_MONTH AREACODE FEE
  245. ---------- ---------- -------- ----------
  246.  905310001 200702 531 0.00

  247. SQL> insert into subs values(905360001,0,536);

  248. 1 row inserted

  249. SQL> select * from subs;

  250.       MSID MS_TYPE AREACODE
  251. ---------- ------- --------
  252.  905360001 0 536
  253.  905310001 0 531
  254.  905320001 1 532
  255.  905330001 2 533

  256. SQL>
  257. SQL> merge into acct a
  258.   2 using subs b on (a.msid=b.msid)
  259.   3 when NOT MATCHED then
  260.   4 insert(msid,bill_month,areacode)
  261.   5 values(b.msid,'200702',b.areacode)
  262.   6 where b.ms_type=0;

  263. Done

  264. SQL> select * from acct;

  265.       MSID BILL_MONTH AREACODE FEE
  266. ---------- ---------- -------- ----------
  267.  905310001 200702 531 0.00
  268.  905360001 200702 536 0.00

  269. SQL> update subs set areacode=888 where ms_type=0;

  270. 2 rows updated

  271. SQL> select * from subs;

  272.       MSID MS_TYPE AREACODE
  273. ---------- ------- --------
  274.  905360001 0 888
  275.  905310001 0 888
  276.  905320001 1 532
  277.  905330001 2 533

  278. SQL> select * from acct;

  279.       MSID BILL_MONTH AREACODE FEE
  280. ---------- ---------- -------- ----------
  281.  905310001 200702 531 0.00
  282.  905360001 200702 536 0.00

  283. SQL>
  284. SQL> merge into acct a
  285.   2 using subs b on (a.msid=b.msid)
  286.   3 when MATCHED then
  287.   4 update set a.areacode=b.areacode
  288.   5 where b.ms_type=0;

  289. Done

  290. SQL> select * from acct;

  291.       MSID BILL_MONTH AREACODE FEE
  292. ---------- ---------- -------- ----------
  293.  905310001 200702 888 0.00
  294.  905360001 200702 888 0.00

  295. SQL>
  296. 四、10g中增强二:删除操作
  297. An optional DELETE WHERE clause can be used to clean up after a
  298. merge operation. Only those rows which match both the ON clause
  299. and the DELETE WHERE clause are deleted.

  300.    merge into acct a
  301.      using subs b on (a.msid=b.msid)
  302.    when MATCHED then
  303.         update set a.areacode=b.areacode
  304.         delete where (b.ms_type!=0);

  305. SQL> select * from subs;

  306.       MSID MS_TYPE AREACODE
  307. ---------- ------- --------
  308.  905310001 0 531
  309.  905320001 1 532
  310.  905330001 2 533

  311. SQL> select * from acct;

  312.       MSID MS_TYPE AREACODE
  313. ---------- ------- --------
  314.  905310001 0 531
  315.  905320001 1 532
  316.  905330001 2 533

  317. SQL>
  318. SQL> merge into acct a
  319.   2 using subs b on (a.msid=b.msid)
  320.   3 when MATCHED then
  321.   4 update set a.areacode=b.areacode
  322.   5 delete where (b.ms_type!=0);

  323. Done

  324. SQL> select * from acct;

  325.       MSID MS_TYPE AREACODE
  326. ---------- ------- --------
  327.  905310001 0 531

  328. SQL>

  329. 更为详尽的语法,请参考Oracle SQL Reference手册!

  330. xsb 发表于:2006.03.01 14:37 ::分类: ( Oracle ) ::阅读:(5854次) :: 评论 (6) :: 引用 (0)
  331.  re: MERGE语法 [回复]
  332. merge 语法中,T1可以不是oracle 实际存在的表吗 ?
  333. 比如
  334. TYPE my_type_array IS TABLE OF
  335. my_type INDEX BY BINARY_INTEGER;

  336. T1 my_type_array

  337. 谢谢

  338. 大狗 评论于:2006.03.21 21:10
  339.  re: MERGE语法 [回复]
  340. T1必须是oracle实际存在的表!

  341. xsb 评论于:2006.03.22 09:02
  342.  re: MERGE语法 [回复]
  343. 我也查了相关资料,的确是要实际存在的表,呵呵,谢谢你的分享精神!

  344. 大狗 评论于:2006.04.02 13:27
  345.  re: MERGE语法 [回复]
  346. -- Created on 2006-3-22 by xsb
  347. declare
  348. --T1必须是oracle实际存在的表或视图!
  349. --create table t (c1 number,c2 number);
  350. --insert into t values (1,2);
  351. type TYPE_REC_RESULT is record(
  352. C1 number,
  353. C2 number);
  354. type TYPE_TAB_RESULT is table of TYPE_REC_RESULT index by binary_integer;
  355. T1 TYPE_TAB_RESULT;
  356. begin
  357. merge into T1
  358. using T
  359. on (T1.C1 = T.C1)
  360. when matched then
  361. update
  362. set T1.C2 = T.C2
  363. when not matched then
  364. insert
  365. values
  366. (T.C1, T.C2);
  367. end;
  368. /
  369. PL/SQL: ORA-00942: 表或视图不存在
阅读(2753) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~