2007-04-03

oracle merge into 10G增强

关键字: sql
http://www.oracle-base.com/articles/10g/MergeEnhancements10g.php
如果在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;
评论
发表评论

您还没有登录，请登录后发表评论

eyejava
搜索本博客
我的相册
5882c820-4b57-4dfd-b8d1-046e88098b0f-thumb
Gmail
共 8 张
存档
最新评论