Oracle PL/SQL MERGE statement in package function

Oracle 9i新增的MERGE語法搭配package function使用範例。

使用MERGE的時機通常在新增或修改資料表時，也就是如果該筆資料存在則修改，若不存在就新增。

例如有一資料表EMPLOYEE欄位如下：

1. ID
2. NAME
3. AGE

則下面是對EMPLOYEE進行新增或修改的function。

PG_DMEO (spec)

CREATE OR REPLACE PG_DEMO AS

    FUNCTION FN_ADD_OR_UPDATE_EMPLOYEE (
        I_ID IN EMPLOYEE.ID%TYPE,
        I_NAME IN EMPLOYEE.NAME%TYPE,
        I_AGE IN EMPLOYEE.AGE%TYPE
    ) RETURN NUMBER;

END PG_DEMO;

PG_DMEO (body)

CREATE OR REPLACE PG_DEMO BODY AS

    FUNCTION FN_ADD_OR_UPDATE_EMPLOYEE (
        I_ID IN EMPLOYEE.ID%TYPE,
        I_NAME IN EMPLOYEE.NAME%TYPE,
        I_AGE IN EMPLOYEE.AGE%TYPE
    ) RETURN NUMBER
    AS
    BEGIN
    
        MERGE INTO EMPLOYEE e
            USING (
                SELECT 
                    I_ID AS ID,
                    I_NAME AS NAME,
                    I_AGE AS AGE
                FROM DUAL;
            ) i ON (i.ID = e.ID) -- 比對條件
            WHEN MATCHED THEN -- 條件符合則修改
                UPDATE SET
                    e.ID = i.ID,
                    e.NAME = i.NAME,
                    e.AGE = i.AGE
            WHEN NOT MATCHED THEN -- 條件不符則新增
                INSERT (
                    ID,
                    NAME,
                    AGE
                ) VALUES (
                    s.ID,
                    s.NAME,
                    s.AGE
                );

        RETURN SQL%ROWCOUNT; -- 回傳DML異動筆數
        
    END FN_ADD_OR_UPDATE_EMPLOYEE;

END PG_DEMO;

• Oracle MERGE
• MERGE Statement