دستور MERGE در اوراکل

موارد استفاده معمول:

• برای کانورت اطلاعات در اوراکل.

• استفاده در Job ها در اوراکل.

• عملیات های (DML (INSERT,UPDATE,DELETE بصورت مجموعه ای از دیتا.

• کارایی Performance در اوراکل: تاثیرگذار می باشد زیرا باعث بهینه شدن ادغام مجموعه ای از دیتا می باشد.


MERGE <hint> INTO <table_name>
USING <table_view_or_query>
ON (<condition>)
WHEN MATCHED THEN <update_clause>
DELETE <where_clause>
WHEN NOT MATCHED THEN <insert_clause>
[LOG ERRORS <log_errors_clause> <reject limit <integer | unlimited>];

 برای درک بهتر مثالی زده می شود: در این مثال می خواهیم که مقادیر فیلد employee_id رکوردهای اطلاعاتی جدول employee را به جدول bonuses انتقال دهیم.

CREATE TABLE employee (
employee_id NUMBER(5),
first_name VARCHAR2(20),
last_name VARCHAR2(20),
dept_no NUMBER(2),
salary NUMBER(10));

INSERT INTO employee VALUES (1, 'Dan', 'Morgan', 10, 100000);
INSERT INTO employee VALUES (2, 'Helen', 'Lofstrom', 20, 100000);
INSERT INTO employee VALUES (3, 'Akiko', 'Toyota', 20, 50000);
INSERT INTO employee VALUES (4, 'Jackie', 'Stough', 20, 40000);
INSERT INTO employee VALUES (5, 'Richard', 'Foote', 20, 70000);
INSERT INTO employee VALUES (6, 'Joe', 'Johnson', 20, 30000);
INSERT INTO employee VALUES (7, 'Clark', 'Urling', 20, 90000);

CREATE TABLE bonuses (
employee_id NUMBER, bonus NUMBER DEFAULT 100);

INSERT INTO bonuses (employee_id) VALUES (1);
INSERT INTO bonuses (employee_id) VALUES (2);
INSERT INTO bonuses (employee_id) VALUES (4);
INSERT INTO bonuses (employee_id) VALUES (6);
INSERT INTO bonuses (employee_id) VALUES (7);
COMMIT;

SELECT * FROM employee;

SELECT * FROM bonuses;

اکنون می خواهیم دیتای جدول bonuses را ایجاد / بروزرسانی / حذف کنیم. در ابتدا نشان می هد که عملیات بر روی جدول bonuses می باشد {MERGE INTO bonuses} سپس در خط های پایین تر کوئری جدول یا جداول مورد نظر گفته می شود {…SELECT employee_id, salary, dept_no} و در قسمت بعد شرط Join بین دو جدول employee و bonuses آورده می شود.

{ON (b.employee_id = e.employee_id)}

از این مرحله به بعد عملیات های DELETE , UPDATE, INSERT بسته به نیازی که داریم را اضافه می کنیم.

بدین معنی می باشد که اگر رکوردی را برابر با Join آورده شده پیدا کرد، در جدول bonuses مقدار فیلد bonus را مقدار دهی می کند و اگر مقدار فید salary در جدول employee کوچکتر از ۴۰۰۰ بود آن رکورد را در جدول bonuses حذف می کند.

در مرحله بعد اگر هیچ رکوردی متناظر با جدول employee در جدول bonuses وجود نداشت، آن رکورد را در جدول bonuses ایجاد می کند.

MERGE INTO bonuses b
USING (
SELECT employee_id, salary, dept_no
FROM employee
WHERE dept_no =20) e
ON (b.employee_id = e.employee_id)
WHEN MATCHED THEN
UPDATE SET b.bonus = e.salary * 0.1
DELETE WHERE (e.salary < 40000)
WHEN NOT MATCHED THEN
INSERT (b.employee_id, b.bonus)
VALUES (e.employee_id, e.salary * 0.05)
WHERE (e.salary > 40000);

SELECT * FROM bonuses;

Hint برای افزایش کارایی و بالا بردن سرعت اجرای کوئری روی merge

استفاده از APPEND hint با پارالل . مثال:


۱)alter session enable parallel dml;

۲)
merge /*+ parallel(contract_dim,10) append */
into contract_dim d
using TABLE(trx.go(CURSOR(select /*+ parallel(contracts_\
file,10) full (contracts_file) */ *
from contracts_file ))) f
on d.contract_id = f.contract_id
when matched then
update set desc = f.desc,
init_val_loc_curr = f.init_val_loc_curr,
init_val_adj_amt = f.init_val_adj_amt
when not matched then
insert values ( f.contract_id,f.desc,f.init_val_loc_curr,
f.init_val_adj_amt);