oracle merge into 10G增强

最新推荐文章于 2025-04-17 23:56:43 发布

最新推荐文章于 2025-04-17 23:56:43 发布 · 163 阅读

文章标签：

#Oracle #SQL #PHP

DB 专栏收录该内容

24 篇文章

订阅专栏

本文介绍Oracle 10g中Merge语句的增强功能，包括MATCHED和NOT MATCHED子句变为可选，允许仅执行更新或插入操作。通过示例说明了不同Merge语句的应用场景。

[url]http://www.oracle-base.com/articles/10g/MergeEnhancements10g.php[/url]
如果在9i中执行以下sql将报错：ora-00905:missing keyword

Merge Into card a
Using etl_cardinfo b On (a.card_number = b.card_no)
WHEN MATCHED Then
Update Set a.open_date=b.issue_date,a.card_limit=b.card_limit
;

在10G 中：The MATCHED and NOT MATCHED clauses are now optional making all of the following examples valid.


-- Both clauses present.
MERGE INTO test1 a
  USING all_objects b
    ON (a.object_id = b.object_id)
  WHEN MATCHED THEN
    UPDATE SET a.status = b.status
  WHEN NOT MATCHED THEN
    INSERT (object_id, status)
    VALUES (b.object_id, b.status);

-- No matched clause, insert only.
MERGE INTO test1 a
  USING all_objects b
    ON (a.object_id = b.object_id)
  WHEN NOT MATCHED THEN
    INSERT (object_id, status)
    VALUES (b.object_id, b.status);

-- No not-matched clause, update only.
MERGE INTO test1 a
  USING all_objects b
    ON (a.object_id = b.object_id)
  WHEN MATCHED THEN
    UPDATE SET a.status = b.status;