Understanding the PL/SQL MERGE Statement

The PL/SQL MERGE statement allows you to insert and update operations in a single query. It is particularly useful for upsert operations where you need to update rows that exist in the target table or insert new rows if they do not exist.

Key Features of the MERGE Statement

1. Target Table and Source Data:
   • The MERGE INTO clause specifies the target table.
   • The USING clause specifies the source data, which can be a table or subquery.
2. Conditional Logic:
   • The WHEN MATCHED clause defines the update logic for rows that already exist in the target table.
   • The WHEN NOT MATCHED clause defines the insert logic for new rows.
3. Efficiency:
   • Reduces the need for multiple operations, improving performance and simplifying code.

Example: MERGE Statement in PL/SQL

This example demonstrates using the MERGE statement to synchronize the COPY_EMP table with data from the EMPLOYEES table.

Procedure: PROC_MERGE

PROCEDURE PROC_MERGE
AS
BEGIN
    DBMS_OUTPUT.PUT_LINE('PROC_MERGE');
    MERGE INTO COPY_EMP C
    USING EMPLOYEES E
    ON (C.EMPLOYEE_ID = E.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;
/

Explanation of the Code

1. MERGE INTO COPY_EMP:
   • Specifies that the COPY_EMP table is the target for the operation.
2. USING EMPLOYEES:
   • Specifies the EMPLOYEES table as the source of data.
3. ON (C.EMPLOYEE_ID = E.EMPLOYEE_ID):
   • Defines the condition for matching rows in the target and source tables.
4. WHEN MATCHED THEN UPDATE:
   • Updates rows in the target table that match the join condition with data from the source table.
5. WHEN NOT MATCHED THEN INSERT:
   • Inserts new rows into the target table for records in the source table that do not match the join condition.

Benefits of Using the MERGE Statement

1. Reduced Code Complexity:
   • Eliminates the need for separate UPDATE and INSERT statements.
2. Improved Performance:
   • Executes both operations in a single pass, reducing the number of reads and writes.
3. Data Synchronization:
   • Ensures that the target table is always up-to-date with the source data.

Output Example

When PROC_MERGE is executed:

• If an EMPLOYEE_ID exists in COPY_EMP, the record is updated with new data from EMPLOYEES.
• If an EMPLOYEE_ID does not exist in COPY_EMP, a new record is inserted.

Example Execution

BEGIN
    PROC_MERGE;
END;
/

Best Practices for Using MERGE

1. Index Key Columns:
   • Ensure the join columns (EMPLOYEE_ID in this example) are indexed for better performance.
2. Handle Conflicts:
   • Use ON conditions carefully to avoid unintended updates or inserts.
3. Monitor Performance:
   • For large datasets, test the query in a non-production environment to optimize execution time.

Conclusion

The PL/SQL MERGE statement is a powerful tool for synchronizing data efficiently. By combining update and insert operations in a single query, it simplifies code and improves performance. Try the example above to see how MERGE can enhance your database operations.

Would you like additional examples or guidance on PL/SQL optimization? 😊

Ahmad Gohar

With over 18 years of experience in software architecture, Java technologies, and leadership, I specialize in crafting scalable, future-proof solutions for global organizations. Whether it’s transforming legacy systems, building cutting-edge cloud-native applications, or mentoring teams to excel, I’m committed to delivering value-driven results.

See Full Bio