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.

Advertisements

About oracledocuments

Zekeriya Beşiroğlu, It joined the Bilginc IT Academy in July 2000. In the meantime, the Oracle Education, Oracle Data Base Management System and Oracle Internet Technologies, Oracle development technologies such as the training of Oracle products is responsible for training as consultants. During this task, since 1 April 2008 with 293 Oracle training in total, in 2150 Oracle customers successfully submitted. Oracle 10g New York in November 2002 have received training and education in Turkey was the first time the consultants. 20 April 2008 on education in Turkey 11g is the first who was a consultant. Oracle Real Application Cluster Expert advisor is certified is the first . Http://zekeriyabesiroglu.blogspot.com and Http://www.oracleforum.info owner and manager of the site. Its own has more than one hundred articles. In addition, creation and improvement of training materials are also related to work. Oracle products are used, database management and reporting Academy eruditely It also is working on. From the date of 1 January 2009 will continue to work as the Technical Director
This entry was posted in Uncategorized and tagged . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s