Search This Blog

Thursday, October 31, 2013

PL/SQL MERGE Statement

The MERGE statement inserts some rows and updates others in a single operation. The decision about whether to update or insert into the target table is based upon a join condition: rows already in the target table that match the join condition are updated; otherwise, a row is inserted using values from a separate subquery.
    
PROCEDURE PROC_MERGE
AS

BEGIN
  DBMS_OUTPUT.PUT_LINE('PROC_MERGE');
  MERGE INTO COPY_EMP C
        USING EMPLOYEES E
        ON(C.EMPLOYEE_ID = C.EMPLOYEE_ID)
    WHEN MATCHED THEN
        UPDATE SET 
            C.FIRST_NAME = E.FIRST_NAME,
            C.LAST_NAME = E.LAST_NAME,
            C.EMAIL = E.EMAIL,
            C.PHONE_NUMBER = E.PHONE_NUMBER,
            C.HIRE_DATE = E.HIRE_DATE,
            C.JOB_ID = E.JOB_ID,
            C.SALARY = E.SALARY,
            C.COMMISSION_PCT = E.COMMISSION_PCT,
            C.MANAGER_ID = E.MANAGER_ID,
            C.DEPARTMENT_ID = E.DEPARTMENT_ID
             
    WHEN NOT MATCHED THEN
        insert values (e.EMPLOYEE_ID,e.FIRST_NAME,e.LAST_NAME,e.EMAIL,e.PHONE_NUMBER,e.HIRE_DATE,e.JOB_ID,e.SALARY,e.COMMISSION_PCT,e.MANAGER_ID,e.DEPARTMENT_ID);
END PROC_MERGE;

No comments: