ITPub博客

首页 > Linux操作系统 > Linux操作系统 > oracle 写exel 文件 存储过程

oracle 写exel 文件 存储过程

原创 Linux操作系统 作者:wuft2003 时间:2011-08-12 16:39:45 0 删除 编辑

业务要求:

          1. 业务要求每天早晨08:00导出存款,贷款等数据信息

           2. 使用的数据需要上一日EOD 日终批处理的数据

            3. 数据以exel 文件呈现

  实现过程中的主要问题:

1. 写exel 文件

       写exel 的实现 使用oracle utl_file 实现, 写入数据的时候,exel 列与列要使用“,”号分隔

           UTL_FILE.PUT_LINE(v_Handle,dp_record.v_SETDT||',' dp_record.v_BRCD );

 这其中遇到的问题主要有2个:

        1. 数据字段中含有",",  这样写入的数据就会列发生错乱, 由于这边含有逗号的字段主要是名字,故我仅仅是用空格把逗号去掉,没有进一步的研究

        2. 中文乱码

          UTL_FILE.PUT_LINE(vHandle,convert(vOutput3, 'ZHS16GBK'));

         中文乱码可以使用上面的方法转换

  下面是存储过程脚本:

create or replace directory  UTL_DIR_TEST as 'C:\oracle\product\10.2.0\utl_dir';


grant read,write on directory UTL_DIR_TEST  to  scott;

drop procedure RT_daily_exel;

create or replace procedure RT_daily_exel( i_date char )
as
type deposit_record_type is record
(
  v_SETDT     rc1.TBRC_CASHFLOW.SETDT%type,
  v_BRCD      rc1.TBRC_CASHFLOW.BRCD%type,
  v_ENGBRNM   cs1.TBCS_BRCD.ENGBRNM%type,
  v_DECODE      RACE.T_CODE.DECODE%type,
  v_CUSTNUM    varchar2(11),
  v_NM       cm1.TBCM_GENERAL.nm%type,
  v_OVSAC    rc1.TBRC_CASHFLOW.OVSAC%type,
  v_ACCTNO    rc1.TBRC_CASHFLOW.ACCTNO%type,
  v_ACNMLF    gl1.TBGL_CACODB.ACNMLF%type,
   v_ACNMEF    gl1.TBGL_CACODB.ACNMEF%type,
   v_OPNDT    rc1.TBRC_CASHFLOW.OPNDT%type,
   v_MATDT     rc1.TBRC_CASHFLOW.MATDT%type,
   v_CCYCD    rc1.TBRC_CASHFLOW.CCYCD%type,
   v_BAL       rc1.TBRC_CASHFLOW.BAL%type,
   v_USDAMT    varchar2(22),
   v_CUSTDTLTPCD cm1.TBCM_GENERAL.CUSTDTLTPCD%type
);
 dp_record deposit_record_type;
 v_Handle UTL_FILE.FILE_TYPE;
 v_date char(8) := '99991231';
 v_Dirname varchar2(200);
 v_Filename_DP varchar2(200);
/* v_Filename_LN varchar2(200);
 v_Filename_LNAPP varchar2(200);*/
 CURSOR cur_dp(v_date char default '99991231') is   
 SELECT A.SETDT
        ,A.BRCD
        ,B.ENGBRNM
        ,T1.DECODE
        ,A.BRCD||A.CUSTSEQ
        ,trim(replace(C.NM, ',' ,' ')) as NM
        ,A.OVSAC
        ,A.ACCTNO
        ,trim(D.ACNMLF) as ACNMLF
        ,D.ACNMEF
        ,A.OPNDT
        ,A.MATDT
        ,A.CCYCD
        ,A.BAL
        ,to_char(NVL(ROUND(CASE WHEN E.ROUNDFAC = 'R' THEN ROUND(TRUNC((A.BAL * E.BR_HQ_BSRT),8),E.SUBUNIT)
                        WHEN E.ROUNDFAC = 'D' THEN TRUNC(TRUNC((A.BAL * E.BR_HQ_BSRT),8),E.SUBUNIT)
                        WHEN E.ROUNDFAC = 'U' THEN CEIL (TRUNC((A.BAL * E.BR_HQ_BSRT),8) * POWER(10,E.SUBUNIT)) / POWER(10,E.SUBUNIT)
                        ELSE 0
                    END * (E.USDNVT / E.QUOTEUNIT),3),0)) AS USDAMT
        ,C.CUSTDTLTPCD
    FROM TBRC_CASHFLOW A
        ,TBCS_BRCD     B
        ,TBCM_GENERAL  C
        ,TBGL_CACODB   D
        ,TBRC_BREXRT   E
        ,(  SELECT *
              FROM T_CODE
             WHERE CAT_NAME LIKE '%CMCUSTTPCD%'
               AND COUNTRY = 'CN'
         ) T1
   WHERE A.CNTRY    = 'CN'
     AND A.BRCD     = B.BRCD
     AND A.BRCD     = C.BRCD
     AND A.BRCD     = D.BRCD
     AND A.SETDT    = v_date
     AND A.CUSTSEQ  = C.CUSTSEQ
     AND C.CUSTTPCD = T1.CODE
     AND A.ACCTCD   = D.ACCTCD
     AND A.BUSCD    = 'DP'
     AND E.BRCD     = A.BRCD
     AND E.NOTDT    = A.SETDT
     AND E.CCYCD    = A.CCYCD
     AND E.ONCCY    = 'CNY'
     AND E.CNTRY    = 'CN'
     AND E.EXCCY    = 'USD'
     AND A.OVSAC BETWEEN '31201000' AND '32000000'
     --     AND A.OVSAC <> '31701100'
     AND C.CUSTDTLTPCD NOT IN  ( '401','402','403','404','405'  );

BEGIN

IF i_date = '99991231' THEN
  select curbusday into v_date from cs1.tbcs_brcd where brcd = '5921';
ELSE
  v_date := i_date;
END IF;

   v_Dirname := ‘UTL_DIR_TEST’; --目录名字大写
   v_Filename_DP := v_date||'DEPOSIT.CSV';
 /*  v_Filename_LN := v_date||'LOAN.CSV';
   v_Filename_LNAPP := v_date||'LOAN_APPROVAL.CSV';*/

   v_Handle := UTL_FILE.FOPEN(v_Dirname,v_Filename_DP,'w');
  OPEN cur_dp(v_date => v_date);
  LOOP
    FETCH cur_dp INTO
           dp_record.v_SETDT
           ,dp_record.v_BRCD
           ,dp_record.v_ENGBRNM
           ,dp_record.v_DECODE
           ,dp_record.v_CUSTNUM
           ,dp_record.v_NM
           ,dp_record.v_OVSAC
           ,dp_record.v_ACCTNO
           ,dp_record.v_ACNMLF
           ,dp_record.v_ACNMEF
           ,dp_record.v_OPNDT
           ,dp_record.v_MATDT
           ,dp_record.v_CCYCD
           ,dp_record.v_BAL
           ,dp_record.v_USDAMT
           ,dp_record.v_CUSTDTLTPCD ;

          EXIT WHEN cur_dp%NOTFOUND;
          UTL_FILE.PUT_LINE(v_Handle,
              convert(dp_record.v_SETDT       ||','
                   ||dp_record.v_BRCD        ||','
                   ||dp_record.v_ENGBRNM     ||','
                   ||dp_record.v_DECODE      ||','
                   ||dp_record.v_CUSTNUM     ||','
                   ||dp_record.v_NM          ||','
                   ||dp_record.v_OVSAC       ||','
                   ||dp_record.v_ACCTNO      ||','
                   ||dp_record.v_ACNMLF      ||','
                   ||dp_record.v_ACNMEF      ||','
                   ||dp_record.v_OPNDT       ||','
                   ||dp_record.v_MATDT       ||','
                   ||dp_record.v_CCYCD       ||','
                   ||dp_record.v_BAL         ||','
                   ||dp_record.v_USDAMT      ||','
                   ||dp_record.v_CUSTDTLTPCD, 'ZHS16GBK') );          --- 转换字符格式
  END LOOP;
  CLOSE cur_dp;
  utl_file.fflush(v_Handle);
  UTL_FILE.FCLOSE(v_Handle);

EXCEPTION
      WHEN OTHERS THEN
         IF UTL_FILE.is_open(v_Handle) then
            utl_file.fclose(v_Handle);
         END IF;

END RT_daily_exel;

二:

添加执行计划,

三, 添加分发脚本

完整版存储过程:

create or replace procedure RT_daily_exel( i_date char )
as
type deposit_record_type is record
(
  v_SETDT     rc1.TBRC_CASHFLOW.SETDT%type,
  v_BRCD      rc1.TBRC_CASHFLOW.BRCD%type,
  v_ENGBRNM   cs1.TBCS_BRCD.ENGBRNM%type,
  v_DECODE      RACE.T_CODE.DECODE%type,
  v_CUSTNUM    varchar2(11),
  v_NM       cm1.TBCM_GENERAL.nm%type,
  v_OVSAC    rc1.TBRC_CASHFLOW.OVSAC%type,
  v_ACCTNO    rc1.TBRC_CASHFLOW.ACCTNO%type,
  v_ACNMLF    gl1.TBGL_CACODB.ACNMLF%type,
   v_ACNMEF    gl1.TBGL_CACODB.ACNMEF%type,
   v_OPNDT    rc1.TBRC_CASHFLOW.OPNDT%type,
   v_MATDT     rc1.TBRC_CASHFLOW.MATDT%type,
   v_CCYCD    rc1.TBRC_CASHFLOW.CCYCD%type,
   v_BAL       rc1.TBRC_CASHFLOW.BAL%type,
   v_USDAMT    varchar2(22),
   v_CUSTDTLTPCD cm1.TBCM_GENERAL.CUSTDTLTPCD%type
);
type loan_record_type is record
(
  v_SETDT     rc1.TBRC_CASHFLOW.SETDT%type,
  v_BRCD      rc1.TBRC_CASHFLOW.BRCD%type,
  v_ENGBRNM   cs1.TBCS_BRCD.ENGBRNM%type,
  v_DECODE      RACE.T_CODE.DECODE%type,
  v_CUSTNUM    varchar2(11),
  v_NM       cm1.TBCM_GENERAL.nm%type,
  v_OVSAC    rc1.TBRC_CASHFLOW.OVSAC%type,
  v_ACCTNO    rc1.TBRC_CASHFLOW.ACCTNO%type,
  v_ACNMLF    gl1.TBGL_CACODB.ACNMLF%type,
   v_ACNMEF    gl1.TBGL_CACODB.ACNMEF%type,
   v_OPNDT    rc1.TBRC_CASHFLOW.OPNDT%type,
   v_MATDT     rc1.TBRC_CASHFLOW.MATDT%type,
   v_CCYCD    rc1.TBRC_CASHFLOW.CCYCD%type,
   v_BAL       rc1.TBRC_CASHFLOW.BAL%type,
   v_USDAMT    varchar2(22),
   v_CUSTDTLTPCD cm1.TBCM_GENERAL.CUSTDTLTPCD%type
);
 type loanapp_record_type is record
 (
 v_BRCD      LN1.TBLN_APPRH.BRCD%type,
 v_BRCDLN     LN1.TBLN_APPRH.BRCDLN%type,
 v_CUSTSEQLN     LN1.TBLN_APPRH.CUSTSEQLN%type,
 v_CUSTNM    CM1.TBCM_GENERAL.NMLOC%type,
 v_IDNONATL  CM1.TBCM_GENERAL.IDNONATL%type,
 v_APPRNO    varchar2(18),
 v_ACCTCODE  varchar2(10),
 v_ACCTNM    varchar2(255),
 v_CCYCD     LN1.TBLN_APPRH.CCYCD%type,
 v_APPRAMT   LN1.TBLN_APPRH.APPRAMT%type,
 v_DSBSBAL   LN1.TBLN_APPRH.DSBSBAL%type,
 v_APPRDT    LN1.TBLN_APPRH.APPRDT%type,
 v_MATDT     LN1.TBLN_APPRH.MATDT%type,
 v_APPRAMT_USDAMT varchar2(30),
 v_DSBSBAL_USDAMT varchar2(30)
 );
 dp_record deposit_record_type;
 loan_record loan_record_type;
 loanapp_record loanapp_record_type;
 
 v_Handle UTL_FILE.FILE_TYPE;
 v_ln_Handle UTL_FILE.FILE_TYPE;
 v_lnapp_Handle UTL_FILE.FILE_TYPE;
 v_date char(8) := '99991231';
 v_Dirname varchar2(200);
 v_Filename_DP varchar2(200);
v_Filename_LN varchar2(200);
 v_Filename_LNAPP varchar2(200);
 CURSOR cur_dp(v_date char default '99991231') is   
 SELECT A.SETDT
        ,A.BRCD
        ,B.ENGBRNM
        ,T1.DECODE
        ,A.BRCD||A.CUSTSEQ
        ,trim(replace(C.NM, ',' ,' ')) as NM
        ,A.OVSAC
        ,A.ACCTNO
        ,trim(D.ACNMLF) as ACNMLF
        ,D.ACNMEF
        ,A.OPNDT
        ,A.MATDT
        ,A.CCYCD
        ,A.BAL
        ,to_char(NVL(ROUND(CASE WHEN E.ROUNDFAC = 'R' THEN ROUND(TRUNC((A.BAL * E.BR_HQ_BSRT),8),E.SUBUNIT)
                        WHEN E.ROUNDFAC = 'D' THEN TRUNC(TRUNC((A.BAL * E.BR_HQ_BSRT),8),E.SUBUNIT)
                        WHEN E.ROUNDFAC = 'U' THEN CEIL (TRUNC((A.BAL * E.BR_HQ_BSRT),8) * POWER(10,E.SUBUNIT)) / POWER(10,E.SUBUNIT)
                        ELSE 0
                    END * (E.USDNVT / E.QUOTEUNIT),3),0)) AS USDAMT
        ,C.CUSTDTLTPCD
    FROM TBRC_CASHFLOW A
        ,TBCS_BRCD     B
        ,TBCM_GENERAL  C
        ,TBGL_CACODB   D
        ,TBRC_BREXRT   E
        ,(  SELECT *
              FROM T_CODE
             WHERE CAT_NAME LIKE '%CMCUSTTPCD%'
               AND COUNTRY = 'CN'
         ) T1
   WHERE A.CNTRY    = 'CN'
     AND A.BRCD     = B.BRCD
     AND A.BRCD     = C.BRCD
     AND A.BRCD     = D.BRCD
     AND A.SETDT    = v_date
     AND A.CUSTSEQ  = C.CUSTSEQ
     AND C.CUSTTPCD = T1.CODE
     AND A.ACCTCD   = D.ACCTCD
     AND A.BUSCD    = 'DP'
     AND E.BRCD     = A.BRCD
     AND E.NOTDT    = A.SETDT
     AND E.CCYCD    = A.CCYCD
     AND E.ONCCY    = 'CNY'
     AND E.CNTRY    = 'CN'
     AND E.EXCCY    = 'USD'
     AND A.OVSAC BETWEEN '31201000' AND '32000000'
     --     AND A.OVSAC <> '31701100'
     AND C.CUSTDTLTPCD NOT IN  ( '401','402','403','404','405'  );
 CURSOR cur_loan(v_date char default '99991231') is
  SELECT A.SETDT
        ,A.BRCD
        ,B.ENGBRNM
        ,T1.DECODE
        ,A.BRCD||A.CUSTSEQ
        ,trim(replace(C.NM, ',' ,' ')) as NM
        ,A.OVSAC
        ,A.ACCTNO
        ,trim(D.ACNMLF) as ACNMLF
        ,D.ACNMEF
        ,A.OPNDT
        ,A.MATDT
        ,A.CCYCD
        ,A.BAL
        ,NVL(ROUND(CASE WHEN E.ROUNDFAC = 'R' THEN ROUND(TRUNC((A.BAL * E.BR_HQ_BSRT),8),E.SUBUNIT)
                        WHEN E.ROUNDFAC = 'D' THEN TRUNC(TRUNC((A.BAL * E.BR_HQ_BSRT),8),E.SUBUNIT)
                        WHEN E.ROUNDFAC = 'U' THEN CEIL (TRUNC((A.BAL * E.BR_HQ_BSRT),8) * POWER(10,E.SUBUNIT)) / POWER(10,E.SUBUNIT)
                        ELSE 0
                    END * (E.USDNVT / E.QUOTEUNIT), 3
                  ), 0) AS USDAMT
        ,C.CUSTDTLTPCD
    FROM TBRC_CASHFLOW A
        ,TBCS_BRCD     B
        ,TBCM_GENERAL  C
        ,TBGL_CACODB   D
        ,TBRC_BREXRT   E
        ,(  SELECT *
              FROM T_CODE
             WHERE CAT_NAME LIKE '%CMCUSTTPCD%'
               AND COUNTRY = 'CN'
         ) T1
   WHERE A.CNTRY    = 'CN'
     AND A.BRCD     = B.BRCD
     AND A.BRCD     = C.BRCD
     AND A.BRCD     = D.BRCD
     AND A.SETDT    = v_date
     AND A.CUSTSEQ  = C.CUSTSEQ
     AND C.CUSTTPCD = T1.CODE
     AND A.ACCTCD   = D.ACCTCD
     AND E.BRCD     = A.BRCD
     AND E.NOTDT    = A.SETDT
     AND E.CCYCD    = A.CCYCD
     AND E.ONCCY    = 'CNY'
     AND E.CNTRY    = 'CN'
     AND E.EXCCY    = 'USD'
     AND A.OVSAC BETWEEN '24100001' AND '25506000'
     AND A.OVSAC NOT IN  ( '24851000'  );
  
CURSOR cur_loanapp(v_date char default '99991231') is  
select K.BRCD , K.BRCDLN ,K.CUSTSEQLN,  K.CUSTNM  , K.IDNONATL , K.APPRNO , K.ACCTCODE , K.ACCTNM , K.CCYCD , K.APPRAMT , K.DSBSBAL , K.APPRDT , K.MATDT ,K.APPRAMT_USDAMT, K.DSBSBAL_USDAMT
from
(SELECT A.BRCD,
        A.BRCDLN,
    A.CUSTSEQLN,
    trim(replace(B.NMLOC, ',' ,' ')) as CUSTNM ,
        B.IDNONATL ,
        A.BRCD||'-'||A.APPRID||'-'||A.APPRSEQ APPRNO,
        DECODE(A.LNACCTTPCD, '02', A.FACLCD, DECODE(NVL(D.DRAC1, '999999'), '999999', A.LNTPCD  , D.DRAC1 )) ACCTCODE
       ,DECODE(A.LNACCTTPCD, '02', C.DECODE, DECODE(NVL(D.DRAC1, '999999'), '999999', E.LNSBTPNM, F.ACNMES)) ACCTNM
       ,A.CCYCD
       ,A.APPRAMT
       ,NVL(A.DSBSBAL, '-1') DSBSBAL
       ,A.APPRDT
       ,A.MATDT
       ,to_char(NVL(ROUND(CASE WHEN G.ROUNDFAC = 'R' THEN ROUND(TRUNC((A.APPRAMT * G.BR_HQ_BSRT), 8), G.SUBUNIT)
                       WHEN G.ROUNDFAC = 'D' THEN TRUNC(TRUNC((A.APPRAMT * G.BR_HQ_BSRT), 8), G.SUBUNIT)
                       WHEN G.ROUNDFAC = 'U' THEN CEIL (TRUNC((A.APPRAMT * G.BR_HQ_BSRT), 8) * POWER(10, G.SUBUNIT)) / POWER(10, G.SUBUNIT) ELSE 0 END * (G.USDNVT / G.QUOTEUNIT), 3), 0)) AS APPRAMT_USDAMT
        ,to_char(NVL(ROUND(CASE WHEN G.ROUNDFAC = 'R' THEN ROUND(TRUNC((A.DSBSBAL * G.BR_HQ_BSRT), 8), G.SUBUNIT)
                       WHEN G.ROUNDFAC = 'D' THEN TRUNC(TRUNC((A.DSBSBAL * G.BR_HQ_BSRT), 8), G.SUBUNIT) WHEN G.ROUNDFAC = 'U' THEN CEIL (TRUNC((A.DSBSBAL * G.BR_HQ_BSRT), 8) * POWER(10, G.SUBUNIT)) / POWER(10, G.SUBUNIT) ELSE 0
                  END * (G.USDNVT / G.QUOTEUNIT), 3), 0)) AS DSBSBAL_USDAMT
       FROM TBLN_APPRH   A,TBCM_GENERAL B ,T_CODE       C ,TBGL_ACKEYDD D,TBLN_LNACCT  E,TBGL_CACODB  F,TBRC_BREXRT  G
        WHERE A.BTDT  = v_date
          AND A.STSCD = '01'
          AND A.BRCDLN = B.BRCD (+)
          AND A.CUSTSEQLN = B.CUSTSEQ(+)
          AND A.FACLCD      = C.CODE(+)
          AND C.CAT_NAME(+) = 'LNFACLCD'
          AND C.COUNTRY (+) = 'CC'
          AND A.BRCD = D.BRCD (+) 
          AND A.LNTPCD   = D.TRREF(+)
          AND D.SUBTP  (+) = '01'
          AND D.EVENTTP(+) = 'DS' 
          AND A.BRCD  = E.BRCD (+)
          AND A.LNTPCD = E.LNTPCD(+)
          AND E.LNSBTPCD(+) = '00'
          AND D.BRCD  = F.BRCD  (+)
          AND D.DRAC1 = F.ACCTCD(+)
          AND G.BRCD (+) = A.BRCD
          AND G.NOTDT(+) = A.BTDT
          AND G.CCYCD(+) = A.CCYCD
          AND G.ONCCY(+) = 'CNY'
          AND G.CNTRY(+) = 'KR'
          AND G.EXCCY(+) = 'USD' ) K
WHERE K.ACCTCODE <> '751'
 and K.ACCTCODE <> '753'
 and K.ACCTCODE <> '710'
 and K.ACCTCODE <> '466'
 ORDER BY K.BRCDLN ,K.CUSTSEQLN ,K.BRCD ,K.APPRNO;
 
BEGIN

IF i_date = '99991231' THEN
  select curbusday into v_date from cs1.tbcs_brcd where brcd = '5921';
ELSE
  v_date := i_date;
END IF;

   v_Dirname := 'UTL_DIR_RT'; --目录名字大写
   v_Filename_DP := v_date||'_DEPOSIT.csv';
   v_Filename_LN := v_date||'_LOAN.csv';
   v_Filename_LNAPP := v_date||'_LOAN_APPROVAL.csv';

   v_Handle := UTL_FILE.FOPEN(v_Dirname,v_Filename_DP,'w');
  v_ln_Handle := UTL_FILE.FOPEN(v_Dirname,v_Filename_LN,'w');
  v_lnapp_Handle := UTL_FILE.FOPEN(v_Dirname,v_Filename_LNAPP,'w');
  OPEN cur_dp(v_date => v_date);
  LOOP
    FETCH cur_dp INTO
           dp_record.v_SETDT
           ,dp_record.v_BRCD
           ,dp_record.v_ENGBRNM
           ,dp_record.v_DECODE
           ,dp_record.v_CUSTNUM
           ,dp_record.v_NM
           ,dp_record.v_OVSAC
           ,dp_record.v_ACCTNO
           ,dp_record.v_ACNMLF
           ,dp_record.v_ACNMEF
           ,dp_record.v_OPNDT
           ,dp_record.v_MATDT
           ,dp_record.v_CCYCD
           ,dp_record.v_BAL
           ,dp_record.v_USDAMT
           ,dp_record.v_CUSTDTLTPCD ;

          EXIT WHEN cur_dp%NOTFOUND;
          UTL_FILE.PUT_LINE(v_Handle,
              convert(dp_record.v_SETDT       ||','
                   ||dp_record.v_BRCD        ||','
                   ||dp_record.v_ENGBRNM     ||','
                   ||dp_record.v_DECODE      ||','
                   ||dp_record.v_CUSTNUM     ||','
                   ||dp_record.v_NM          ||','
                   ||dp_record.v_OVSAC       ||','
                   ||dp_record.v_ACCTNO      ||','
                   ||dp_record.v_ACNMLF      ||','
                   ||dp_record.v_ACNMEF      ||','
                   ||dp_record.v_OPNDT       ||','
                   ||dp_record.v_MATDT       ||','
                   ||dp_record.v_CCYCD       ||','
                   ||dp_record.v_BAL         ||','
                   ||dp_record.v_USDAMT      ||','
                   ||dp_record.v_CUSTDTLTPCD, 'ZHS16GBK') );
  END LOOP;
  CLOSE cur_dp;
 OPEN cur_loan(v_date => v_date);
  LOOP
    FETCH cur_loan INTO
           loan_record.v_SETDT
           ,loan_record.v_BRCD
           ,loan_record.v_ENGBRNM
           ,loan_record.v_DECODE
           ,loan_record.v_CUSTNUM
           ,loan_record.v_NM
           ,loan_record.v_OVSAC
           ,loan_record.v_ACCTNO
           ,loan_record.v_ACNMLF
           ,loan_record.v_ACNMEF
           ,loan_record.v_OPNDT
           ,loan_record.v_MATDT
           ,loan_record.v_CCYCD
           ,loan_record.v_BAL
           ,loan_record.v_USDAMT
           ,loan_record.v_CUSTDTLTPCD ;

          EXIT WHEN cur_loan%NOTFOUND;
          UTL_FILE.PUT_LINE(v_ln_Handle,
              convert(loan_record.v_SETDT       ||','
                   ||loan_record.v_BRCD        ||','
                   ||loan_record.v_ENGBRNM     ||','
                   ||loan_record.v_DECODE      ||','
                   ||loan_record.v_CUSTNUM     ||','
                   ||loan_record.v_NM          ||','
                   ||loan_record.v_OVSAC       ||','
                   ||loan_record.v_ACCTNO      ||','
                   ||loan_record.v_ACNMLF      ||','
                   ||loan_record.v_ACNMEF      ||','
                   ||loan_record.v_OPNDT       ||','
                   ||loan_record.v_MATDT       ||','
                   ||loan_record.v_CCYCD       ||','
                   ||loan_record.v_BAL         ||','
                   ||loan_record.v_USDAMT      ||','
                   ||loan_record.v_CUSTDTLTPCD, 'ZHS16GBK') );
  END LOOP;
  CLOSE cur_loan;
 OPEN cur_loanapp(v_date => v_date);
  LOOP
    FETCH cur_loanapp INTO
           loanapp_record.v_BRCD
           ,loanapp_record.v_BRCDLN
      ,loanapp_record.v_CUSTSEQLN
           ,loanapp_record.v_CUSTNM
           ,loanapp_record.v_IDNONATL
           ,loanapp_record.v_APPRNO
           ,loanapp_record.v_ACCTCODE
           ,loanapp_record.v_ACCTNM
           ,loanapp_record.v_CCYCD
           ,loanapp_record.v_APPRAMT
           ,loanapp_record.v_DSBSBAL
           ,loanapp_record.v_APPRDT
           ,loanapp_record.v_MATDT
           ,loanapp_record.v_APPRAMT_USDAMT
           ,loanapp_record.v_DSBSBAL_USDAMT ;

          EXIT WHEN cur_loanapp%NOTFOUND;
          UTL_FILE.PUT_LINE(v_lnapp_Handle,
              convert(loanapp_record.v_BRCD       ||','
                  ||loanapp_record.v_BRCDLN||'-'||loanapp_record.v_CUSTSEQLN        ||','
                    ||loanapp_record.v_CUSTNM     ||','
                   ||loanapp_record.v_IDNONATL      ||','
                   ||loanapp_record.v_APPRNO    ||','
                   ||loanapp_record.v_ACCTCODE          ||','
                   ||loanapp_record.v_ACCTNM       ||','
                   ||loanapp_record.v_CCYCD      ||','
                   ||loanapp_record.v_APPRAMT      ||','
                   ||loanapp_record.v_DSBSBAL      ||','
                   ||loanapp_record.v_APPRDT       ||','
                   ||loanapp_record.v_MATDT       ||','
                   ||loanapp_record.v_APPRAMT_USDAMT       ||','
                   ||loanapp_record.v_DSBSBAL_USDAMT , 'ZHS16GBK') );
  END LOOP;
  CLOSE cur_loanapp;
  utl_file.fflush(v_Handle);
  UTL_FILE.FCLOSE(v_Handle);
  utl_file.fflush(v_ln_Handle);
  UTL_FILE.FCLOSE(v_ln_Handle);
 utl_file.fflush(v_lnapp_Handle);
  UTL_FILE.FCLOSE(v_lnapp_Handle);
EXCEPTION
      WHEN OTHERS THEN
    dbms_output.put_line(' EXCEPTION Happen ');
         IF UTL_FILE.is_open(v_Handle) then
            utl_file.fclose(v_Handle);
         END IF;
         IF UTL_FILE.is_open(v_ln_Handle) then
            utl_file.fclose(v_ln_Handle);
         END IF;
     IF UTL_FILE.is_open(v_lnapp_Handle) then
            utl_file.fclose(v_lnapp_Handle);
         END IF;
END RT_daily_exel;

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/21634752/viewspace-704758/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2009-05-12

  • 博文量
    295
  • 访问量
    324150