Oracle 9i新增的MERGE
語法搭配package function使用範例。
使用MERGE
的時機通常在新增或修改資料表時,也就是如果該筆資料存在則修改,若不存在就新增。
例如有一資料表EMPLOYEE
欄位如下:
- ID
- NAME
- 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;
沒有留言:
張貼留言