使用utl_file将oracle数据库中数据写入excel文件

使用utl_file将oracle数据库中数据写入excel文件

主要实现思路：
    1、声明一个纪录，用来存储导出的数据；
    2、使用游标取数据到纪录中；
    3、使用utl_file将纪录中的数据写入excel文件；
    4、循环执行步骤2和3，完成数据的导出。
    做的过程中主要遇到的问题：
    1、excle文件中写数据如何写入下一列；
          使用TAB字符完成excel中横向跳格，excel中TAB字符表示单元格的结尾，其中使用了chr()函数，
应用举例如下：
         select U.USER_NAME||chr(9),U.ACCOUNT||chr(9) from USER U
                                            例句1
          例句1作为游标的主体，取出的数据每项都包含一个TAB字符，使用utl_file.put()往excel文件中
写数据时会自动跳格
    2、声明的纪录中各项的类型问题
           这个问题的产生主要是在类型的强转化时产生。如例句1种的U.ACCOUNT为number型时，
添加||chr(9)时会对进行强转化；当然这里的转化不会有问题（number转化为varchar2），
但是当这种强转换还是会出现问题的，如声明的纪录里某个属性声明为用户自己定义的一种类型，
无法进行转换，则会报错。解决的办法很简单，可以将纪录的属性都声明为varchar类型，但是要注意长度。
    3、导出文件存储路径问题
       utl_file在写文件时，文件的存储路径必须在初始化参数utl_file_dir中设置，
需重起服务才能生效。后来经过查资料发现可以先建立一个directory，在存储过程中使用它。
建立directory的语句：
create or replace directory FILEPATH as 'path' "；
例句2（注：path为存储文件的路径，如c:\Temp）

    以下是我简单做的处理hr.jobs表数据的存储过程：
      
CREATE OR REPLACE PROCEDURE SP_JOBS_DATA_OUT(
      p_file_name IN VARCHAR2           --***处理文件名称，需包含扩展名(xls用于写excel文件)***--
      ) as
     
   --***定义并声明存储交通资产信息的纪录***--
   --***record_define start***--
    TYPE job_record_type is RECORD(
    job_id hr.jobs.job_id%TYPE,
    job_title hr.jobs.job_title%TYPE,
    min_salary varchar2(30)
    );
    job_rec job_record_type;
   --***record_define end***-- 
  
通过学习这篇文章完成以下的procedure

CREATE OR REPLACE PROCEDURE SP_OUTPUT_COL_CUST_PAY AS
 RET_FLAG NUMBER := 1;
 RET_MSG  VARCHAR2(500);
 L_FLAG   NUMBER;
 CURSOR C_CURSOR IS
  SELECT  
                BSCS_CS_ID    ,    
                OHREFNUM      ,
                OHINVAMT_DOC  ,
                OHOPNAMT_DOC  ,
                OHDUEDATE     ,
                OHENTDATE                                                        
  FROM COL_CUST_PAY
  WHERE ROWNUM <= 5000000;
 RET_CURSOR C_CURSOR%ROWTYPE;
 FHANDLE  UTL_FILE.FILE_TYPE;
BEGIN           
 FHANDLE := UTL_FILE.FOPEN('d:\temp', 'COL_CUST_PAY.TXT', 'w');
 UTL_FILE.PUT_LINE(FHANDLE,'BSCS_CS_ID||OHREFNUM||OHINVAMT_DOC||OHOPNAMT_DOC||OHDUEDATE||OHENTDATE');
 OPEN C_CURSOR;
 LOOP        
  L_FLAG := L_FLAG - 1;
  FETCH C_CURSOR
   INTO RET_CURSOR; 
  EXIT WHEN C_CURSOR%NOTFOUND;
  UTL_FILE.PUT(FHANDLE, RET_CURSOR.BSCS_CS_ID    );        UTL_FILE.PUT(FHANDLE, '||');
  UTL_FILE.PUT(FHANDLE, RET_CURSOR.OHREFNUM      );        UTL_FILE.PUT(FHANDLE, '||');
  UTL_FILE.PUT(FHANDLE, RET_CURSOR.OHINVAMT_DOC  );        UTL_FILE.PUT(FHANDLE, '||');
  UTL_FILE.PUT(FHANDLE, RET_CURSOR.OHOPNAMT_DOC  );        UTL_FILE.PUT(FHANDLE, '||');
  UTL_FILE.PUT(FHANDLE, RET_CURSOR.OHDUEDATE     );        UTL_FILE.PUT(FHANDLE, '||');
  UTL_FILE.PUT(FHANDLE, RET_CURSOR.OHENTDATE     );
  UTL_FILE.PUT_LINE(FHANDLE,'');
 END LOOP;
 /*這是原來的一段例子,做測試
  for i in 1..100000 loop
  utl_file.putf(fhandle , 'test write two %s \n',i);
  --PUTF utl_file.PUT_LINE(fhandle , 'test write two %s \n',i);   --這里有點問題就是不可以直接PUT_LINE,^)^終于明白PUT多行時
  end loop;                             --是不可以PUT_LINE的...this is OK!!!
  */  
 UTL_FILE.FCLOSE(FHANDLE);
 CLOSE C_CURSOR; 
EXCEPTION
 WHEN OTHERS THEN
 DBMS_OUTPUT.PUT_LINE(SQLERRM );
  DBMS_OUTPUT.PUT_LINE(L_FLAG);
END;
/