针对现有列的表中没有新行的命令

Question

假设我有一个表，其中有4个列标题a、b、c、d。在插入的第一句话中，我插入a=1。所以我的T1表包含

T1
A B C D
1

在第二个insert语句中，我想插入a=1，b=2，c=3，d=4这样的值。所以我想要的输出

T1
A B C D
1 2 3 4

而不是

A B C D
1
1 2 3 4.

需要注意的是：该列可能不是一直都是A。它可以是A、B、C、D中的任何一个，即；每次我都要检查所有4列的对应值是否存在。

我尝试了以下代码

PROCEDURE INSERT_FOM_SPM_B2B (
A IN VARCHAR,
B IN VARCHAR,
C IN VARCHAR,
D IN VARCHAR
)
IS 
BEGIN
MERGE INTO MISIMD_FOM_SPM_B2B USING (select 1 from dual) m ON (A=A or B=B or C=C or D=D)
WHEN MATCHED THEN UPDATE SET 
A = nv12(A,A,A),
B = nv12(B,B,B),
C = nv12(C,C,C),
D = nv12(D,D,D)
WHEN NOT MATCHED THEN INSERT (
A,B,C,D
) VALUES (
A,B,C,D
);
EXCEPTION
WHEN OTHERS THEN
NULL;
END INSERT_FOM_SPM_B2B;

它给了我错误PL/SQL语句被忽略，并且缺少右括号。

Answer 1

您的过程中存在以下几个问题：

SET子句中缺少

• 逗号
• 列名和参数名的混合

修复这些问题，至少使其编译，结果是：

create or replace PROCEDURE INSERT_FOM_SPM_B2B (
pA IN VARCHAR,
pB IN VARCHAR,
pC IN VARCHAR,
pD IN VARCHAR
) IS 
BEGIN
MERGE INTO MISIMD_FOM_SPM_B2B USING (select 1 from dual) m ON (A=pA or B=pB or C=pC or D=pD)
WHEN MATCHED THEN UPDATE SET 
A = nvl(A,pA),
B = nvl(B,pB),
C = nvl(C,pC),
D = nvl(D,pD)
WHEN NOT MATCHED THEN INSERT (
A,B,C,D
) VALUES (
pA,pB,pC,pD
);
END INSERT_FOM_SPM_B2B;
/

但这仍然不起作用，因为它在执行过程中抛出了一个异常：

exec INSERT_FOM_SPM_B2B(1,null,null,null)
ORA-38104: Columns referenced in the ON Clause cannot be updated: "A"
ORA-06512: at "MYSCHEMA.INSERT_FOM_SPM_B2B", line 10
ORA-06512: at line 1
38104. 00000 -  "Columns referenced in the ON Clause cannot be updated: %s"
*Cause:    LHS of UPDATE SET contains the columns referenced in the ON Clause

不确定是否可以以某种方式说服MERGE做你想让它做的事情，但你可以使用这个过程，利用sql%rowcount结果计数器：

create or replace PROCEDURE INSERT_FOM_SPM_B2B (
pA IN VARCHAR,
pB IN VARCHAR,
pC IN VARCHAR,
pD IN VARCHAR
) IS 
BEGIN
update MISIMD_FOM_SPM_B2B
set A = nvl(A,pA),
B = nvl(B,pB),
C = nvl(C,pC),
D = nvl(D,pD)
where A = pA
or B = pB
or C = pC
or D = pD;
if sql%rowcount=0 then
insert into MISIMD_FOM_SPM_B2B (A, B, C, D)
values ( pA, pB, pC, pD );
end if;
END INSERT_FOM_SPM_B2B;
/

你应该意识到这种合并非常缓慢。。。

BTW，这个过程调用序列的期望结果是什么：

INSERT_FOM_SPM_B2B(1,null,null,null);
INSERT_FOM_SPM_B2B(null,2,null,null);
INSERT_FOM_SPM_B2B(null,null,3,null);
INSERT_FOM_SPM_B2B(null,null,null,4);
INSERT_FOM_SPM_B2B(1,2,3,4);

或者这个：

INSERT_FOM_SPM_B2B(1,null,null,1);
INSERT_FOM_SPM_B2B(null,2,null,2);
INSERT_FOM_SPM_B2B(1,2,null,null);

Answer 2

此版本有效。但您应该注意命名标准。

create or replace procedure insert_fom_spm_b2b (param_a   in varchar,
param_b   in varchar,
param_c   in varchar,
param_d   in varchar)
is
begin
merge into misimd_fom_spm_b2b x
using (select param_a param_a,
param_b param_b,
param_c param_c,
param_d param_d
from DUAL) m
on (   (m.param_a, 'dummy') = ( (x.a, 'dummy'))
or (m.param_b, 'dummy') = ( (x.b, 'dummy'))
or (m.param_c, 'dummy') = ( (x.c, 'dummy'))
or (m.param_d, 'dummy') = ( (x.d, 'dummy')))
when matched
then
update set
a= NVL (param_a, a),  b = NVL (param_b, b), c = NVL (param_c, c), d = NVL (param_d, d)
when not matched
then
insert     (a,
b,
c,
d)
values (param_a,
param_b,
param_c,
param_d);
exception
when others
then
DBMS_OUTPUT.put_line (SQLERRM);
end insert_fom_spm_b2b;