sql - 空输出被下一个输出覆盖

Question

代码的输出不应覆盖空值。我需要附加格式的输出（第一个输出），它以不同的格式显示（第二个输出）。请建议我如何实现这一目标。

Customer_ID         Event_ID   Supp_CD
(null)              2534660914  (null)
(null)              2534660913  (null)
4000000000033919590 2534660916  (null)
7000000000491566892 2534660918  (null)
7000000000888215846 2534660917  (null)
7000000000945520325 2534660912  (null)
7000000000862996964 2534660919  (null)
7000000000554412111 2534660915  (null)

Customer_ID         Event_ID   Supp_CD
2534660914  
2534660913  
4000000000033919590 2534660916  
7000000000491566892 2534660918  
7000000000888215846 2534660917  
7000000000945520325 2534660912  
7000000000862996964 2534660919  
7000000000554412111 2534660915  

我已经编写了 SQL 脚本，但是 DBMS_OUTPUT 的格式化会覆盖空输出。最后一个字段 SUPP_CD 是文本，前两列由数字组成。

SET SERVEROUTPUT ON FORMAT WRAPPED
SET VERIFY OFF
SET FEEDBACK OFF
SET TERMOUT ON

SPOOL /u01/apps/Miscellaneous/bin/spool.txt

DECLARE
  CSTID CDB_ADMIN.CPM_PREF_EVENT_STG.cdb_customer_id%TYPE;
  EVNTID CDB_ADMIN.CPM_PREF_EVENT_STG.cdb_pref_event_id%TYPE;
  SUPCD CDB_ADMIN.CPM_PREF_EVENT_STG.supp_cd%TYPE;
  COUNTS INTEGER:=0;

  cursor c1 is
    <select * from A.B>;

BEGIN
  DBMS_OUTPUT.PUT_LINE(LPAD('Customer_ID',25)||LPAD('Event_ID',25)||RPAD('Supp_ID',25));

  OPEN C1;
  LOOP
    FETCH C1 INTO CSTID,EVNTID,SUPCD;
    EXIT WHEN C1%NOTFOUND;
    DBMS_OUTPUT.put_line(LPAD(CSTID,25)||LPAD(EVNTID,25)||RPAD(SUPCD,25));
    COUNTS := COUNTS+1;
  END LOOP;
  CLOSE C1;

  DBMS_OUTPUT.PUT_LINE('');
  DBMS_OUTPUT.PUT_LINE('NUMBER OF ROWS: ' || COUNTS);
END;
/

SPOOL OFF;

我尝试通过用 IF 语句替换这一行来编写下面的代码

DBMS_OUTPUT.put_line(LPAD(CSTID,25)||LPAD(EVNTID,25)||RPAD(SUPCD,25));

IF CSTID is null then
dbms_output.put_line(LPAD(coalesce(null,' '),25)||LPAD(EVNTID,25)||'  '||RPAD(SUPCD,40));
end if;
IF EVNTID is null then
dbms_output.put_line(LPAD(CSTID,25)||LPAD(coalesce(null,' '),25)||'  '||RPAD(SUPCD,40));
end if;
IF SUPCD is null then
dbms_output.put_line(LPAD(CSTID,25)||LPAD(EVNTID,25)||'  '||RPAD(coalesce(null,' '),40));
end if;

输出是

              Customer_ID                 Event_ID  Supp_ID

                                        2534660914
               2534660914
                                        2534660913
               2534660913
      4000000000033919590               2534660916

      7000000000491566892               2534660918

      7000000000888215846               2534660917

      7000000000945520325               2534660912

      7000000000862996964               2534660919

      7000000000554412111               2534660915

还在想办法。请让我知道这是否可以以更好的方式完成。

标签： sqloracleplsql

Answer 1

您需要替换 line: DBMS_OUTPUT.put_line(LPAD(CSTID,25)||LPAD(EVNTID,25)||RPAD(SUPCD,25));如下，否则其他代码将正常工作

DBMS_OUTPUT.PUT_LINE(
    CASE
        WHEN CSTID IS NULL AND EVNTID IS NULL THEN LPAD(SUPCD, 25)
        WHEN CSTID IS NULL THEN
            CASE
                WHEN SUPCD IS NULL THEN LPAD(EVNTID, 25)
                ELSE LPAD(EVNTID, 25)
                     || RPAD(SUPCD, 25)
            END
        WHEN EVNTID IS NULL THEN
            CASE
                WHEN SUPCD IS NULL THEN LPAD(CSTID, 25)
                ELSE LPAD(CSTID, 25)
                     || RPAD(SUPCD, 25)
            END
        ELSE
            CASE
                WHEN SUPCD IS NULL THEN LPAD(CSTID, 25)
                                        || LPAD(EVNTID, 25)
                ELSE LPAD(CSTID, 25)
                     || LPAD(EVNTID, 25)
                     || RPAD(SUPCD, 25)
            END
    END
);

希望，这会有所帮助。

DBMS_OUTPUT.PUT_LINE(RPAD('Customer_ID',20)||RPAD('Event_ID',20)||RPAD('Supp_ID',20));
DBMS_OUTPUT.PUT_LINE(
    CASE
        WHEN CSTID IS NULL AND EVNTID IS NULL THEN RPAD(SUPCD, 20)
        WHEN CSTID IS NULL THEN
            CASE
                WHEN SUPCD IS NULL THEN RPAD(EVNTID, 20)
                ELSE RPAD(EVNTID, 20)
                     || RPAD(SUPCD, 20)
            END
        WHEN EVNTID IS NULL THEN
            CASE
                WHEN SUPCD IS NULL THEN RPAD(CSTID, 20)
                ELSE RPAD(CSTID, 20)
                     || RPAD(SUPCD, 20)
            END
        ELSE
            CASE
                WHEN SUPCD IS NULL THEN RPAD(CSTID, 20)
                                        || RPAD(EVNTID, 20)
                ELSE RPAD(CSTID, 20)
                     || RPAD(EVNTID, 20)
                     || RPAD(SUPCD, 20)
            END
    END
);
END;
/

DB Fiddle有几个例子

干杯！！

---------- 更新 ----------

您可以使用以下内容DBMS_OUPUT：

DBMS_OUTPUT.PUT_LINE(
    CASE 
    WHEN CSTID IS NOT NULL THEN RPAD(CSTID, 20) 
    ELSE RPAD(' ', 20) 
    END
    ||
    CASE 
    WHEN EVNTID IS NOT NULL THEN RPAD(EVNTID, 20) 
    ELSE RPAD(' ', 20) 
    END
    ||
    CASE 
    WHEN SUPCD IS NOT NULL THEN RPAD(SUPCD, 20) 
    ELSE RPAD(' ', 20) 
    END
);

DB<>小提琴演示

干杯！！