表中现有列的无新行的命令

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

T1
A B C D
1

在第二个插入语句中,我想插入类似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语句被忽略,并且 缺少右括号。

评论
  • id_sed
    id_sed 回复

    这个版本有效。但是您应该注意命名标准。

    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 = x.a)
       when matched
       then
          update set
             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;
    
  • zipsum
    zipsum 回复

    您的过程中存在几个问题:

    • 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
    

    Not sure if MERGE can somehow be convinced to do what you want it to do, but you can use this procedure instead, making use of the sql%rowcount result counter:

    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;
    /
    

    您应该意识到,这种合并非常慢...