PLSQL存储过程billdate参数为空时自动取上月最后一天的实现方法

核心需求实现 #

你当前的参数默认值写法仅能覆盖「调用时不传参数」的场景，若调用方显式传入NULL，默认值不会触发，pid_billdate会保持为NULL，导致ab.bill_date = pid_billdate条件查询不到任何结果。需要在过程开头补充NULL值判断，实现全场景兼容：

create or replace procedure GPU_DATA_EXTRACTOR_TEST(pid_billdate DATE DEFAULT LAST_DAY(ADD_MONTHS(TRUNC(SYSDATE), -1))) is
c_limit   CONSTANT PLS_INTEGER DEFAULT 10000;

CURSOR c1 IS
SELECT DISTINCT intl_prod_id
      FROM apld_bill_rt abr,
           acct_bill ab
      WHERE abr.CHRG_TP = 'INSTALLMENT'
          AND abr.TAX_CATG_ID = 'NOTAX'
          AND abr.acct_bill_id = ab.acct_bill_id
          AND ab.bill_date = pid_billdate;

TYPE prod_ids_t IS TABLE OF apld_bill_rt.intl_prod_id%TYPE INDEX BY PLS_INTEGER;
l_prod_ids   prod_ids_t;
begin
  -- 新增：处理显式传入NULL的场景
  IF pid_billdate IS NULL THEN
    pid_billdate := LAST_DAY(ADD_MONTHS(TRUNC(SYSDATE), -1));
  END IF;

  execute immediate 'truncate table GPU_INV_TEST';

  -- 以下原有逻辑保持不变
  OPEN c1;
  LOOP
     FETCH c1 BULK COLLECT INTO l_prod_ids LIMIT c_limit;
     
     EXIT WHEN l_prod_ids.COUNT = 0;

    FORALL indx IN 1 .. l_prod_ids.COUNT
    
      INSERT INTO GPU_INV_TEST
         SELECT AB.ACCT_BILL_ID,
                AB.BILL_NO,
                AB.INV_ID,
                AB.BILL_DATE,
                ba2.bill_acct_id,
                ba1.bill_acct_id parent_bill_acct_id,
                AB.DUE_DATE,
                PG.CMPG_ID,
                ABR.NET_AMT,
                AB.DUE_AMT,
                P.PROD_NUM,
                pds.DST_ID,
                ABR.DESCR,
                p.intl_prod_id
           FROM apld_bill_rt abr,
                acct_bill ab,
                prod p,
                FCBSADM.PROD_DST pds,
                bill_acct_prod bap,
                bill_acct ba1,
                bill_acct ba2,
                prod_cmpg pg
          WHERE ab.intl_bill_acct_id = ba1.intl_bill_acct_id
                AND AB.ACCT_BILL_ID = ABR.ACCT_BILL_ID
                AND ba1.intl_bill_acct_id = ba2.parent_bill_acct_id
                AND ba2.intl_bill_acct_id = bap.intl_bill_acct_id
                AND bap.intl_prod_id = abr.intl_prod_id
                AND ABR.CHRG_TP = 'INSTALLMENT'
                AND bap.intl_prod_id = pds.intl_prod_id
                AND bap.intl_prod_id = p.intl_prod_id
                AND p.intl_prod_id = pg.intl_prod_id(+)
                AND ABR.intl_prod_id = l_prod_ids(indx)
UNION
    SELECT AB.ACCT_BILL_ID,
                AB.BILL_NO,
                AB.INV_ID,
                AB.BILL_DATE,
                ba1.bill_acct_id,
                ba1.bill_acct_id parent_bill_acct_id,
                AB.DUE_DATE,
                PG.CMPG_ID,
                ABR.NET_AMT,
                AB.DUE_AMT,
                P.PROD_NUM,
                pds.DST_ID,
                ABR.DESCR,
                p.intl_prod_id
           FROM apld_bill_rt abr,
                acct_bill ab,
                prod p,
                FCBSADM.PROD_DST pds,
                bill_acct_prod bap,
                bill_acct ba1,
                prod_cmpg pg
          WHERE ab.intl_bill_acct_id = ba1.intl_bill_acct_id
                AND AB.ACCT_BILL_ID = ABR.ACCT_BILL_ID
                AND ba1.intl_bill_acct_id = bap.intl_bill_acct_id
                AND bap.intl_prod_id = abr.intl_prod_id
                AND ABR.CHRG_TP = 'INSTALLMENT'
                AND bap.intl_prod_id = pds.intl_prod_id
                AND bap.intl_prod_id = p.intl_prod_id
                AND p.intl_prod_id = pg.intl_prod_id(+)
                AND ABR.intl_prod_id = l_prod_ids(indx);

    COMMIT;  
    
    END LOOP;
   CLOSE c1;
EXCEPTION
  -- 新增异常处理，方便排查问题
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('执行失败，错误信息：'||SQLERRM||'，错误码：'||SQLCODE);
    RAISE; -- 抛出异常，避免静默失败
end;

代码优化建议 #

• 数据一致性优化：TRUNCATE是DDL语句，执行后会自动提交事务，无法回滚，若后续插入逻辑执行失败，目标表会被清空且无法恢复。如果对数据一致性要求高，建议改用DELETE FROM GPU_INV_TEST，出错时可以整体回滚，仅在数据量过大、DELETE性能不足时再选择TRUNCATE。
• 可读性优化：当前使用的Oracle老式逗号连接语法可读性较低，建议改用ANSI标准JOIN语法，关联逻辑更清晰，避免漏写关联条件产生笛卡尔积。比如左连接不用写(+)，直接用LEFT JOIN即可。
• 可维护性优化：两段UNION的查询逻辑高度重合，仅差异在是否关联ba2表，建议抽离公共逻辑或者用CASE判断合并，后续修改规则时不用同步改两段代码，降低维护成本。
• 性能优化：当前用BULK COLLECT + FORALL的批量处理写法性能已经较好，如果后续数据量持续增长，可以在INSERT语句前加/*+ APPEND */提示走直接路径加载，大幅提升插入速度。

内容的提问来源于stack exchange，提问作者vitaminJava