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:
Post a Comment