PLSQL存储过程内日期格式转换写入GPU_FILE_HIST表方案咨询

你当前提供的存储过程已经包含核心的日期格式转换逻辑，稍作调整和优化即可稳定运行，具体内容如下：

核心需求实现逻辑 #

你需要的BILL_DATE转6位YYYYMM、DUE_DATE转8位YYYYMMDD的需求，通过Oracle原生to_char()函数即可实现，你现有代码中的写法已经符合要求：

• to_char(rec.BILL_DATE,'YYYYMM')：将date类型的BILL_DATE转换为6位数字字符串
• to_char(rec.DUE_DATE,'YYYYMMDD')：将date类型的DUE_DATE转换为8位数字字符串

转换后的内容会拼接到行数据中，最终写入GPU_FILE_HIST的FILE_CONTENT字段，完全匹配你的需求。

代码优化建议 #

1. 空值兼容处理 #

如果GPU_INV表中BILL_DATE或DUE_DATE可能为NULL，建议搭配nvl()函数处理，避免拼接出来的行数据出现空段异常：

vs_rowdata := rec.BILL_NO||'|'||rec.INV_ID||'|'||nvl(to_char(rec.BILL_DATE,'YYYYMM'),'')||'|'||rec.BILL_ACCT_ID||'|'||rec.PARENT_BILL_ACCT_ID||'|'||nvl(to_char(rec.DUE_DATE,'YYYYMMDD'),'')||'|'||rec.CMPG_ID||'|'||rec.net_AMT||'|'||rec.DUE_AMT||'|'||rec.PROD_NUM||'|'||rec.DST_ID;

2. 临时CLOB资源释放 #

建议在存储过程执行完成后增加临时CLOB释放逻辑，避免占用会话级内存：

dbms_lob.freetemporary(vl_FILE_CLOB);

3. 异常捕获处理 #

增加异常块，避免存储过程执行出错时事务未回滚、资源泄漏：

EXCEPTION
  WHEN OTHERS THEN
    -- 释放CLOB资源
    IF dbms_lob.isopen(vl_FILE_CLOB) = 1 THEN
      dbms_lob.freetemporary(vl_FILE_CLOB);
    END IF;
    -- 回滚未提交事务
    ROLLBACK;
    -- 可根据业务需求选择抛出异常或写入错误日志
    RAISE;

完整优化后存储过程代码 #

CREATE OR REPLACE PROCEDURE GPU_FILE_EXTRACTOR is
  vs_FILE_NAME varchar2(50);
  vb_output_is_UNIX_FILE boolean := true;
  vs_header1 varchar2(20) := 'FCBS';
  vs_header2 varchar2(250) := 'BILLNO|INVOICEID|BILLDATE|BILLACCTID|PARENTBILLACCTID|DUEDATE|CMPGID|AMOUNT|GRANDAMOUNT|PRODNUM|DISTID';
  vs_footer varchar2(100);
  vs_rowdata varchar2(1000);
  vn_RECORD_COUNT number;
  vl_FILE_CLOB CLOB;
  vs_newline varchar2(2);
BEGIN
  if vb_output_is_UNIX_FILE then
    vs_newline := CHR(10);
  else
    vs_newline := CHR(13)||CHR(10);
  end if;

  SELECT 'FCBS_INVOICE_'||to_char(sysdate,'YYYYMMDD_HHMISS') ||'.txt' into vs_FILE_NAME FROM dual;

  vl_FILE_CLOB := empty_clob();
  dbms_lob.createtemporary(lob_loc => vl_FILE_CLOB, cache => true, dur => dbms_lob.session);


  dbms_lob.writeappend(vl_FILE_CLOB, length(vs_header1),vs_header1);
  dbms_lob.writeappend(vl_FILE_CLOB, length(vs_newline),vs_newline);

  dbms_lob.writeappend(vl_FILE_CLOB, length(vs_header2),vs_header2);
  dbms_lob.writeappend(vl_FILE_CLOB, length(vs_newline),vs_newline);

  select count(1) into vn_RECORD_COUNT from FCBSADM.GPU_INV;
  for rec in (select * from FCBSADM.GPU_INV)
  loop
    vs_rowdata := rec.BILL_NO||'|'||rec.INV_ID||'|'||nvl(to_char(rec.BILL_DATE,'YYYYMM'),'')||'|'||rec.BILL_ACCT_ID||'|'||rec.PARENT_BILL_ACCT_ID||'|'||nvl(to_char(rec.DUE_DATE,'YYYYMMDD'),'')||'|'||rec.CMPG_ID||'|'||rec.net_AMT||'|'||rec.DUE_AMT||'|'||rec.PROD_NUM||'|'||rec.DST_ID;
    dbms_lob.writeappend(vl_FILE_CLOB, length(vs_rowdata), vs_rowdata);
    dbms_lob.writeappend(vl_FILE_CLOB, length(vs_newline),vs_newline);
  end loop;
  vs_footer := vn_RECORD_COUNT||'|'||vs_FILE_NAME;

  dbms_lob.writeappend(vl_FILE_CLOB,  length(vs_footer),vs_footer );
  dbms_lob.writeappend(vl_FILE_CLOB, length(vs_newline),vs_newline);

  insert into FCBSADM.GPU_FILE_HIST(Cdate, File_Name, File_Content,CUSER) values (sysdate,vs_file_name, vl_FILE_CLOB, USER);
  commit;
  -- 释放临时CLOB资源
  dbms_lob.freetemporary(vl_FILE_CLOB);
EXCEPTION
  WHEN OTHERS THEN
    IF dbms_lob.isopen(vl_FILE_CLOB) = 1 THEN
      dbms_lob.freetemporary(vl_FILE_CLOB);
    END IF;
    ROLLBACK;
    RAISE;
END;
/

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