合并通过使用nvl()将数据库中的现有值替换为null

Question

下面的存储过程运行良好，但Merging通过使用nvl((将数据库中的现有值替换为null

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;

Answer 1

如果它们是可选的，则将它们声明为

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

而在这个过程中，你必须决定在没有任何价值的情况下该怎么办。

您的代码可以从中简化

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,

至

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

---

此外，在Oracle中，null等于空字符串，您不必检查两次，例如

I_SAP_UUID is not null and I_SAP_UUID <> ''

这只是

I_SAP_UUID is not null

Answer 2

您可以使用DEFAULT指定默认值，如果没有提供参数，则会指定该值：

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
....

另一种方法是创建重载过程(具有相同名称和不同参数的过程(：

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;