Merging replaces existing values in database with null though using nvl()

user_513057 0
Viewed 57

Below stored procedure works fine but Merging replaces existing values in database with null though using nvl()

  create or replace PROCEDURE P_UPSERT_CONNECTDISCONNECT
(
  I_SAP_UUID IN VARCHAR2 DEFAULT NULL
, I_METER_ID IN VARCHAR2 
, I_STATUS IN VARCHAR2 
, I_CUR_SYSTEM IN VARCHAR2 
, I_REQ_CREATION_TIME IN DATE DEFAULT NULL
, I_CAT_CODE IN VARCHAR2 DEFAULT NULL
, I_PLANNED_TIME IN DATE DEFAULT NULL
, I_ASB_REC_TIME IN DATE DEFAULT NULL
, I_MDM_REC_TIME IN DATE DEFAULT NULL
, I_LOAD_ACT_CODE IN VARCHAR2 DEFAULT NULL
, I_NRT_REC_TIME IN DATE DEFAULT NULL
, I_NRT_VER_REC_TIME IN DATE DEFAULT NULL
, I_CONF_UUID IN VARCHAR2 DEFAULT NULL
, I_ERROR_DETAIL IN VARCHAR2 DEFAULT NULL
) AS 
BEGIN
  MERGE INTO CONNECTDISCONNECT e
     using (select I_SAP_UUID SAP_UUID, I_METER_ID METER_ID,I_STATUS STATUS, I_CUR_SYSTEM CUR_SYSTEM, I_REQ_CREATION_TIME REQ_CREATION_TIME, I_CAT_CODE CAT_CODE, I_PLANNED_TIME PLANNED_TIME,I_ASB_REC_TIME ASB_REC_TIME,I_MDM_REC_TIME MDM_REC_TIME,I_LOAD_ACT_CODE LOAD_ACT_CODE,I_NRT_REC_TIME NRT_REC_TIME,I_NRT_VER_REC_TIME NRT_VER_REC_TIME,I_CONF_UUID CONF_UUID,I_ERROR_DETAIL ERROR_DETAIL   from dual) s
  on (e.METER_ID = s.METER_ID) 
    WHEN MATCHED THEN
            UPDATE SET SAP_UUID      = nvl(I_SAP_UUID, s.SAP_UUID),
                   STATUS            = nvl(I_STATUS, s.STATUS),
                   CUR_SYSTEM        = nvl(I_CUR_SYSTEM, s.CUR_SYSTEM),
                   REQ_CREATION_TIME = nvl(I_REQ_CREATION_TIME, s.REQ_CREATION_TIME),
                   CAT_CODE          = nvl(I_CAT_CODE, s.CAT_CODE),
                   PLANNED_TIME      = nvl(I_PLANNED_TIME, s.PLANNED_TIME),
                   ASB_REC_TIME      = nvl(I_ASB_REC_TIME, s.ASB_REC_TIME),
                   MDM_REC_TIME      = nvl(I_MDM_REC_TIME, s.MDM_REC_TIME),
                   LOAD_ACT_CODE     = nvl(I_LOAD_ACT_CODE, s.LOAD_ACT_CODE),
                   NRT_REC_TIME      = nvl(I_NRT_REC_TIME, s.NRT_REC_TIME),
                   NRT_VER_REC_TIME  = nvl(I_NRT_VER_REC_TIME, s.NRT_VER_REC_TIME),
                   CONF_UUID         = nvl(I_CONF_UUID, s.CONF_UUID),
                   ERROR_DETAIL      = nvl(I_ERROR_DETAIL, s.ERROR_DETAIL)
    WHEN NOT MATCHED THEN
      INSERT (e.SAP_UUID, e.METER_ID, e.STATUS, e.CUR_SYSTEM,e.REQ_CREATION_TIME,e.CAT_CODE,e.PLANNED_TIME,e.ASB_REC_TIME,e.MDM_REC_TIME,e.LOAD_ACT_CODE,e.NRT_REC_TIME,e.NRT_VER_REC_TIME,e.CONF_UUID,e.ERROR_DETAIL)
      VALUES (s.SAP_UUID, s.METER_ID, s.STATUS, s.CUR_SYSTEM,s.REQ_CREATION_TIME,s.CAT_CODE,s.PLANNED_TIME,s.ASB_REC_TIME,s.MDM_REC_TIME,s.LOAD_ACT_CODE,s.NRT_REC_TIME,s.NRT_VER_REC_TIME,s.CONF_UUID,s.ERROR_DETAIL);
END;
oracle11g plsql null oracle sql
2 Answers

You can use DEFAULT to specify default value which will be assigned if parameter is not provided:

create or replace PROCEDURE P_UPSERT_CONNECTDISCONNECT
(
  I_SAP_UUID IN VARCHAR2 DEFAULT NULL
, I_METER_ID IN VARCHAR2 DEFAULT NULL
, I_STATUS IN VARCHAR2   DEFAULT NULL
, I_CUR_SYSTEM IN VARCHAR2 DEFAULT NULL
....

Another way is to create overloaded procedures (procedures with the same name and different parameters):

CREATE PROCEDURE P_UPSERT_CONNECTDISCONNECT(I_SAP_UUID IN VARCHAR2, I_METER_ID IN VARCHAR2) AS
BEGIN
  -- 2 arguments 
END P_UPSERT_CONNECTDISCONNECT;

CREATE PROCEDURE P_UPSERT_CONNECTDISCONNECT(I_SAP_UUID IN VARCHAR2, I_METER_ID IN VARCHAR2, I_STATUS IN VARCHAR2) AS
BEGIN
  -- 3 arguments 
END P_UPSERT_CONNECTDISCONNECT;
user_27003440

If they are optional, then declare them as

create or replace PROCEDURE P_UPSERT_CONNECTDISCONNECT
(
  I_SAP_UUID IN VARCHAR2 DEFAULT NULL
, I_METER_ID IN VARCHAR2 DEFAULT NULL

while - within the procedure - you have to decide what to do in cases when they don't have any value.

Your code can be simplified from

UPDATE SET 
SAP_UUID = Case When (I_SAP_UUID is not null and I_SAP_UUID <> '') Then I_SAP_UUID Else e.SAP_UUID End,

to

update set
s.sap_uuid = nvl(i_sap_uuid, e.sap_uuid),

Also, as in Oracle null is equal to an empty string, you don't have to check that twice, e.g.

I_SAP_UUID is not null and I_SAP_UUID <> ''

That's just

I_SAP_UUID is not null
user_90979060
Related