PLSQL bilinmesi gerekenler-7

13- merge uygulamaları

Bilindiği üzere merge komutu iki recordu karşılaştırıp varsa update yoksa insert şeklinde davranır.

bir örnek senaryo ile açıklayalım


SELECT employee_id, title, salary
FROM adp_employees;
EMPLOYEE_ID TITLE SALARY
----------- ------------------------- ----------
1 PRESIDENT and COO 5000
2 VP, OPERATIONS 1450
3 VP, SALES 1400
4 VP, FINANCE 1450
5 VP, ADMINISTRATION 1550
SELECT employee_id, title, salary
FROM employees;
EMPLOYEE_ID TITLE SALARY
----------- ------------------------- ----------
1 PRESIDENT 2500
2 VP, OPERATIONS 1450
3 VP, SALES 1400

Yukarıda görüldüpü üzere iki tablomuz bulunmaktadır. Bunlardan biri employees bir diğeride adpemp tablosudur.

oracle 9i öncesinde


DECLARE
lv_adp_employee adp_employees.employee_id%TYPE;
CURSOR cur_adp_employee IS
SELECT employee_id, title, salary
FROM adp_employees;
CURSOR cur_employee IS
SELECT employee_id, title, salary
FROM employees
where employee_id = lv_adp_employee;
lv_cur_employee cur_employee%ROWTYPE;
lv_emp_insert PLS_INTEGER := 0;
lv_emp_update PLS_INTEGER := 0;
BEGIN
FOR lv_cur_adp_employee_rec IN cur_adp_employee LOOP
lv_adp_employee := lv_cur_adp_employee_rec.employee_id;
OPEN cur_employee; FETCH cur_employee INTO lv_cur_employee;

IF cur_employee%FOUND THEN
UPDATE employees
SET title = lv_cur_adp_employee_rec.title,
salary = lv_cur_adp_employee_rec.salary
WHERE employee_id =
lv_cur_adp_employee_rec.employee_id;
lv_emp_update := lv_emp_update + 1;
ELSE
INSERT INTO employees
(employee_id, title, salary)
VALUES
(lv_cur_adp_employee_rec.employee_id,
lv_cur_adp_employee_rec.title,
lv_cur_adp_employee_rec.salary);
lv_emp_insert := lv_emp_insert + 1;
END IF;
CLOSE cur_employee;
END LOOP;
DBMS_OUTPUT.PUT_LINE('Records Inserted: ' lv_emp_insert);
DBMS_OUTPUT.PUT_LINE('Records Updated: ' lv_emp_update);
END;
/

şeklinde yapmamız gerekirken

merge statementı ile birlikte


MERGE INTO employees dest
USING
(SELECT employee_id, title, salary
FROM adp_employees) orig
ON (dest.employee_id = orig.employee_id)
WHEN MATCHED THEN
UPDATE SET
dest.title = orig.title,
dest.salary = orig.salary
WHEN NOT MATCHED THEN
INSERT (dest.employee_id, dest.title, dest.salary)
VALUES
(orig.employee_id, orig.title, orig.salary);
5 rows merged.

bu işlem çok daha kolay ve perfomanslı bir hal almıştır.