本文共 2675 字，大约阅读时间需要 8 分钟。

Oracle MERGE???????????????

??????????????????????????????????????????Oracle???MERGE???????????????????????????????Oracle 9i?Oracle 10g???????????MERGE???????????????

1. MERGE???????

?Oracle??MERGE??????????

MERGE INTO products pUSING newproducts npON (p.product_id = np.product_id)WHEN MATCHED THEN    UPDATE SET p.product_name = np.product_name,              p.category = np.categoryWHEN NOT MATCHED THEN    INSERT VALUES (np.product_id, np.product_name, np.category)

????????????????????????????product_id????????????????????????????MERGE?????????????????????????????

2. ??????

2.1 ????

?????????MERGE??????????

-- ?????????MERGE INTO products pUSING newproducts npON (p.product_id = np.product_id)WHEN NOT MATCHED THEN    INSERT VALUES (np.product_id, np.product_name, np.category)

??????newproducts???????????products?????product_id?????????????

2.2 ????

?????????MERGE???????????????

-- ?????????MERGE INTO products pUSING newproducts npON (p.product_id = np.product_id)WHEN MATCHED THEN    UPDATE SET p.product_name = np.product_name,              p.category = np.category

????newproducts??????products?????????????????product_name?category???

3. ??????????

??????????????MERGE???????????

3.1 ?????????

-- ?????????????MERGE INTO products pUSING newproducts npON (p.product_id = np.product_id)WHEN MATCHED THEN    UPDATE SET p.product_name = np.product_name,              p.category = np.category              WHERE p.category = 'DVD'WHEN NOT MATCHED THEN    INSERT VALUES (np.product_id, np.product_name, np.category)              WHERE np.category != 'BOOKS'

??????????category?'DVD'????????????category?'BOOKS'????

3.2 ????????

????????????????????

-- ????????MERGE INTO products pUSING newproducts npON (1 = 0)  -- ??????WHEN NOT MATCHED THEN    INSERT VALUES (np.product_id, np.product_name, np.category)              WHERE np.category = 'BOOKS'

ON (1 = 0)? Oracle ?????????????????????????????????????

4. DELETE???MERGE

???????MERGE?????DELETE??????????

-- ????????MERGE INTO products pUSING newproducts npON (p.product_id = np.product_id)WHEN MATCHED THEN    UPDATE SET p.product_name = np.product_name,              p.category = np.category              WHERE p.category = 'ELECTRNCS'WHEN NOT MATCHED THEN    INSERT VALUES (np.product_id, np.product_name, np.category)WHEN OTHERS THEN    DELETE WHERE (p.category = 'ELECTRNCS')

???WHEN OTHERS???????????????????????newproducts?????

5. ????

???MERGE?????????????

6. ??

??????MERGE?????????????????????????????????????Oracle ???????????????MERGE????????????????