用PlSql导出数据到Excel文件

问题：写一个存储过程(OUT_FILE2XLS)动态的导出Oracle中指定表的数据

    Example：

        OUT_FILE2XLS('student');则导出student表到STUDENT.XLS文件

        OUT_FILE2XLS('course');则导出couse表到COURSE.XLS文件

关键词：动态Sql、数据字典、Plsql文件读写

平台：windows2000，oracle10g

ibeits：[e-mail]

解决方案：

写两个Procedure一个完成把传入的数据写入文件，另一个负责根据表名生成数据：

             0.在具体实现之前我們创建一个文件存放的目录：

                       CREATE OR REPLACE DIRECTORY TEST AS 'C:/TEST';

                      创建一个名为TEST的目录它的路径为'C:/TEST'，当然這是数据库所在的文件系统。

            1.FILE_WRITE：

                       功能：把传入的varchar2写入文件中

                       参数：i_title in varchar2文件名

                                 i_fileMsg in varchar2待写入文件中的内容

                                 i_fileType in varchar2文件类型既文件扩展名（无实际意义）

                       实现代码：

 

 CREATE OR REPLACE PROCEDURE FILE_WRITE(
                                  i_title            VARCHAR2,
                                 i_fileMsg      VARCHAR2,
                                 i_fileType     VARCHAR2
                               ) IS
                                 v_file             UTL_FILE.FILE_TYPE;      --文件引用
                                 PATH            VARCHAR2(1024);
                                 v_fileName VARCHAR2(2048);
                               BEGIN
                                 PATH:='TEST';                        --文件写入的目录名
                                 v_fileName:=i_title||'.'||i_fileType;
                                 IF UTL_FILE.IS_OPEN(v_file) THEN
                                      UTL_FILE.FCLOSE(v_file);
                                 END IF;
                                 v_file := UTL_FILE.FOPEN(PATH, v_fileName, 'w');
                                 UTL_FILE.PUT_LINE(v_file, i_fileMsg);
                                 UTL_FILE.FCLOSE(v_file);
                               EXCEPTION
                                 WHEN OTHERS THEN
                                   DBMS_OUTPUT.PUT_LINE(SQLERRM);   --写入数据
                                   IF UTL_FILE.IS_OPEN(v_file) THEN
                                      UTL_FILE.FCLOSE(v_file);
                                   END IF;
                               END FILE_WRITE;

在這个Procedure中调用UTL_FILE中的UTL_FILE.FOPEN创建文件，UTL_FILE.PUT_LINE写数据到文件。

          2.OUT_FILE2XLS

                       功能：根据的varchar2（表名）生成数据，并调用前面定义的FILE_WRITE把生成的数据写入文件。

                       参数：tName in varchar2表名

                       实现代码：

CREATE OR REPLACE PROCEDURE OUT_FILE2XLS(tName in varchar2)
                               AS
                                 v_fileMsg VARCHAR2(30000) :='';
                                 sqlstr         varchar2(1000);
                                 type        c_type is ref cursor;
                                 c1          c_type;
                                 r1          varchar2(30);
                                 c2          c_type;
                                 r2          varchar2(1000);
                               BEGIN
                                 open c1 for 'select COLUMN_NAME from all_tab_columns where TABLE_NAME=upper(:1)' using tName;                  --动态cursor
                                 sqlstr:='select ';
                                 fetch c1 into r1;
                                 sqlstr:=sqlstr||r1;
                                 loop
                                     fetch c1 into r1;
                                     exit when c1%notfound;
                                     sqlstr:=sqlstr||'||chr(9)||'||r1;     --excel数据文件的列之间用一个Tab(chr(9))分隔
                                 end loop;
                                 close c1;
                                 sqlstr:=sqlstr||' from '||tName;
                                 open c2 for sqlstr;
                                 fetch c2 into r2;
                                 if c2%found then 
                                      v_fileMsg := v_fileMsg||r2;
                                 end if;
                                 loop
                                      fetch c2 into r2;
                                      exit when c2%notfound;
                                      v_fileMsg := v_fileMsg||chr(10)||r2;   --excel的行之间用一个回车(chr(10))分隔
                                 end loop;
                                 FILE_WRITE(upper(tName),v_fileMsg,'XLS'); --调用FILE_WRITE写入文件
                                 CLOSE c2;
                               EXCEPTION
                                 WHEN OTHERS THEN
                                   NULL;
                               END;

 這里用到了动态cursor,oracle系统的数据字典all_tab_columns其中存储了表名和它的表结构信息，也就是通过這张表来完成动态查询的。

            OK 了，在sql plus在运行 exec OUT_FILE2XLS('tableName')文件就生了。('tableName'为你的表名)