Example Merge Procedure
Von Tobias Arnhold →
8.18.2013
I often have used UPDATE and INSERT statements during my development. In some complex updates especially when I had to select from other tables I sometimes got strange problems.
I even was able to update the wrong data because my statement was not correctly designed. After this experience I decided to switch to MERGE-statements. Merge Statements are easy to read especially when you use difficult select statements inside.
Here is an example package with an MERGE statement inside. It should show you how such an statement could look like and for me it is a good reminder how to design the code.
I even was able to update the wrong data because my statement was not correctly designed. After this experience I decided to switch to MERGE-statements. Merge Statements are easy to read especially when you use difficult select statements inside.
Here is an example package with an MERGE statement inside. It should show you how such an statement could look like and for me it is a good reminder how to design the code.
create or replace
PACKAGE BODY PKG_MERGE_EXAMPLE AS
/* Package Variables */
gv_proc_name VARCHAR2(100);
gv_action VARCHAR2(4000);
gv_ora_error VARCHAR2(4000);
gv_custom_error VARCHAR2(4000);
gv_parameter VARCHAR2(4000);
gv_user VARCHAR2(20) := UPPER(NVL(v('APP_USER'),USER));
/* Save errors */
/*
--------------------------------------------------------
-- DDL for Table ZTA_ERR_LOG
--------------------------------------------------------
CREATE TABLE "ERR_LOG"
( "PROC_NAME" VARCHAR2(200),
"ACTION" VARCHAR2(4000),
"APP_ID" NUMBER,
"APP_PAGE_ID" NUMBER,
"APP_USER" VARCHAR2(20),
"ORA_ERROR" VARCHAR2(4000),
"CUSTOM_ERROR" VARCHAR2(4000),
"PARAMETER" VARCHAR2(4000),
"TIME_STAMP" DATE
) ;
/
*/
PROCEDURE ADD_ERR IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT
INTO ERR_LOG
( PROC_NAME,ACTION,APP_ID,APP_PAGE_ID,APP_USER,ORA_ERROR,CUSTOM_ERROR,PARAMETER,TIME_STAMP )
VALUES
( gv_proc_name,gv_action,nvl(v('APP_ID'),0),nvl(v('APP_PAGE_ID'),0),nvl(nvl(v('APP_USER'),USER),'Unknown'),
gv_ora_error,gv_custom_error,gv_parameter,sysdate );
COMMIT;
END;
/* ************************************************************************************************************************************** */
/* Merge Example */
/* ************************************************************************************************************************************** */
PROCEDURE prc_merge_example
IS
BEGIN
gv_proc_name := 'pkg_merge_example.prc_merge_example';
gv_parameter := '';
gv_action := 'Merge Data instead of update and insert';
MERGE INTO TBL_MERGE_FACILITY t1
USING (
SELECT t2.id,
t2.facility_name,
t3.address
gv_user as current_user,
sysdate as current_timestamp
FROM TBL_FACILITY t2, TBL_ADDRESS t3
WHERE t2.address_id = t3.id
AND t2.activ = 1
) t4
ON (t1.facility_id = t4.id)
WHEN MATCHED THEN
UPDATE SET
t1.facility_name = t4.facility_name,
t1.facility_address = t4.address,
t1.updated_by = t4.current_user,
t1.updated_on = t4.current_timestamp
WHEN NOT MATCHED THEN
INSERT
(
facility_id,
facility_name,
facility_address,
created_by,
created_on
)
VALUES
(
t4.id,
t4.facility_name,
t4.address,
t4.current_user,
t4.current_timestamp
);
gv_action := 'Updated rows: ' sql%rowcount;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
gv_ora_error := SQLERRM;
gv_custom_error := 'Internal Error. Action canceled.';
ROLLBACK;
ADD_ERR; raise_application_error(-20001, gv_custom_error);
END;
END PKG_MERGE_EXAMPLE;
