存储过程利用utl_file包以表格形式导出数据

转载--http://blog.sina.com.cn/s/blog_62defbef0101ol14.htmlutl_file包以表格形式导出数据

2013/11/12
一、概述
    工作中需要将许多表的数据导出到一份excel中，虽然PL/SQL自带有导出的功能，但是导出后需要进行手动的汇总到一张excel中，比较耗时。此时就可以利用oracle自带的utl_file包完成此工作。它可以将不同表的相关数据导出到一张excel里，省去了后续的工作。
------------------------------------------------------------------------------------
二、实验步骤
1.导入utl_file包。
  以sys用户在command窗口执行如下SQL脚本即可。
  utlfile.sql
2.建立文件导出目录并赋予读写权限
create or replace directory data_out as 'd:\';
grant read ,write on directory data_out to scott;
3.对scott用户赋予执行utl_file包的权限。
grant execute on utl_file to scott;
4.修改utl_file_dir参数，此参数为静态参数，然后重启数据库。
alter system set utl_file_dir = 'd:\' scope = spfile;
5.创建存储过程进行表数据导出
create or replace procedure export_2_excel(pfile_name  in varchar2, --输出文件名
                                           pexport_sql in varchar2) --执行的sql语句 ‘select empno||chr(9),ename||chr(9) from emp’
                                                                    --chr(9)是制表符。
 as
  --存储记录的对象类型
  type content_record_type is record(
    empno varchar2(25),
    ename varchar2(25),
    job   varchar2(25),
    sal   varchar2(25));
  content_re content_record_type;
 
  --建立游标
  type cur_content is ref cursor;
  c_content cur_content;

  l_file utl_file.file_type; --建立操作文件的句柄

begin
  l_file := utl_file.fopen('d:\', pfile_name || '.xls', 'w'); --打开文件'
  utl_file.put_line(l_file,
                    'empno' || chr(9) || 'ename' || chr(9) || 'job' ||
                    chr(9) || 'sal'); --输出标头
  open c_content for pexport_sql; --打开游标
  loop
    fetch c_content
      into content_re.empno,
           content_re.ename,
           content_re.job,
           content_re.sal;
    exit when c_content%notfound;
 
    utl_file.put(l_file, content_re.empno);
    utl_file.put(l_file, content_re.ename);
    utl_file.put(l_file, content_re.job);
    utl_file.put_line(l_file, content_re.sal);
  end loop;
  close c_content;
  UTL_FILE.FFLUSH (l_file);--确保缓存已经写入文件。
  utl_file.fclose(l_file); --关闭句柄，一定不能忘！！！
end;
----------------------------------------------------------
附：utl_file包的介绍
    utl_file包的应用是非常广泛的，而且也很实用，应为Oracle虽然有SQL*Loader可以将文本的内容读到数据库里， 但是不能将数据库内容输出到文本。所以基本上是要用到utl_file包来操作。先看了一下Oracle官方文档中的介绍， utl_file推荐直接使用自己创建的DIRECTORY来操作文件，而不要继续使用UTL_FILE_DIR包来指定。utl_file包的 工作机制是这样的：首先要使用FOPEN函数，将文件的路径、文件名、以及打开模式的参数传入，然后Oracle会到 ALL_DIRECTORIES视图中查看路径是否已经创建。如果路径和文件名均合法，则该文件被打开到一个file_type中， 然后可以进行各种操作，最后使用FCLOSE函数将其关闭。具体了解包可以查看utlfile.sql里面的内容，这里仅列举 一下常用过程和函数。
    FOPEN ，    IS_OPEN   ，  GET_LINE   ，  PUT   ，  NEW_LINE    ， PUT_LINE  ，    PUTF    ，      FFLUSH ，    FCLOSE   ，  FCLOSE_ALL 。
---------------------------------------------------------------
1.UTL_FILE.FOPEN
--FOPEN会打开指定文件并返回一个文件句柄用于操作文件。
 FUNCTION UTL_FILE.FOPEN (    
    location     IN VARCHAR2,   --输出文件地址   
    filename     IN VARCHAR2,   --输出文件名 
    open_mode    IN VARCHAR2,   --打开文件的模式      
 max_linesize IN BINARY_INTEGER DEFAULT NULL --文件每行最大的字符数，包括换行符。最小为1，最大为32767
 )   
RETURN file_type;                
3种文件打开模式：
R 只读模式。一般配合UTL_FILE的GET_LINE来读文件。
W 写（替换）模式。文件的所有行会被删除。PUT, PUT_LINE, NEW_LINE, PUTF和FFLUSH都可使用。
A 写（附加）模式。原文件的所有行会被保留。在最末尾行附加新行。PUT, PUT_LINE, NEW_LINE, PUTF和FFLUSH都可使用。

2.UTL_FILE.IS_OPEN
--如果文件句柄指定的文件已打开，返回TRUE，否则FALSE
FUNCTION UTL_FILE.IS_OPEN (file IN UTL_FILE.FILE_TYPE--文件句柄
                                                       )
RETURN BOOLEAN;

3.UTL_FILE.GET_LINE
--UTL_FILE只提供一个方法去读取数据：GET_LINE
读取指定文件的一行到提供的缓存。
PROCEDURE UTL_FILE.GET_LINE(file   IN  UTL_FILE.FILE_TYPE, --文件句柄
                            buffer OUT VARCHAR2,--存放缓存，行终止符不会被传进buffer
       len    IN BINARY_INTEGER DEFAULT NULL);

4.UTL_FILE.PUT
--在当前行输出数据
--UTL_FILE.PUT输出数据时不会附加行终止符。
PROCEDURE UTL_FILE.PUT(file   IN UTL_FILE.FILE_TYPE, --文件句柄
                       buffer OUT VARCHAR2);--要写入的数据缓存

5.UTL_FILE.NEW_LINE
--在当前位置输出新行或行终止符，使用UTL_FILE.PUT必须使用NEW_LINE来结束当前行。
--但可以使用PUT_LINE输出带有行终止符的完整行数据。
PROCEDURE UTL_FILE.NEW_LINE(file IN UTL_FILE.FILE_TYPE, --文件句柄
                           lines IN NATURAL := 1); --要插入的行数，默认为1行
        
6.UTL_FILE.PUT_LINE
--输出一个字符串以及一个与系统有关的行终止符。
--PUT_LINE相当于PUT后加上NEW_LINE；也相当于PUTF的格式串"%s/n"。
PROCEDURE UTL_FILE.PUT_LINE (file   IN UTL_FILE.FILE_TYPE, --文件句柄
                             buffer IN VARCHAR2);--写入文件的数据缓存

7.UTL_FILE.PUTF
以一个模版样式输出至多5个字符串，类似C中的printf
PROCEDURE UTL_FILE.PUTF(file   IN FILE_TYPE,--文件句柄
                        format IN VARCHAR2,--格式串
                        arg1   IN VARCHAR2 DEFAULT NULL,--可选参数，一共5个
                        arg2   IN VARCHAR2 DEFAULT NULL,
                        arg3   IN VARCHAR2 DEFAULT NULL,
                        arg4   IN VARCHAR2 DEFAULT NULL,
                        arg5   IN VARCHAR2 DEFAULT NULL);

格式串可使用以下样式
%s
在格式串中可以使用最多5个%s，与后面的5个参数一一对应
/n
换行符。在格式串中没有个数限制
%s会被后面的参数依次填充，如果没有足够的参数，%s会被忽视，不被写入文件

8.UTL_FILE.FFLUSH
--确保所有数据写入文件。
--操作系统可能会缓存数据来提高性能。因此可能调用put后，打开文件却看不到写入的数据。
--在关闭文件前要读取数据的话可以使用UTL_FILE.FFLUSH。
PROCEDURE UTL_FILE.FFLUSH (file IN UTL_FILE.FILE_TYPE);

9.UTL_FILE.FCLOSE
--关闭文件
PROCEDURE UTL_FILE.FCLOSE (file IN OUT FILE_TYPE);

10.UTL_FILE.FCLOSE_ALL
--关闭所有已打开的文件
PROCEDURE UTL_FILE.FCLOSE_ALL;
在结束程序时要确保所有打开的文件已关闭，可使用FCLOSE_ALL。