使用PL/SQL的UTL_FILE写xls和csv格式文件(转)

使用PL/SQL可以读写文件，我们可以将查询的数据以一定格式写入文件中，当我们注意分割字段的方式，就可以将它转换成我们需要的格式！

首先我了解到将字段存入txt文件中，如果使用制表符分隔字段，将文件名后缀改为xls，我们就可以以Excel打开这个文本文件，并且是按照表格形式展现的；同样道理，如果我们使用逗号进行分隔，然后将后缀改为cvs，也会以Excel的CVS格式打开这个文件!

下面写一个例子：

1 首先写一个用于写文件的公共方法

CREATE OR REPLACE PROCEDURE PROC_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 PROC_FILE_WRITE;
/

2 创建文件生成路径

CREATE OR REPLACE DIRECTORY  TEST AS 'C:TEST'

3 创建一个表并添加数据

CREATE TABLE TEST_FILE_T(
ID          NUMBER NOT NULL,
NAME   VARCHAR(32),
AGE      NUMBER,
SEX      VARCHAR(2)
);
--数据自行填写

4 写文件

CREATE OR REPLACE PROCEDURE PROC_OUT_FILE
AS
  v_fileMsg VARCHAR2(30000);
  CURSOR get_file_cur IS
    SELECT id, name,age,sex 
    FROM test_file_t;
  get_file_rec get_file_cur%ROWTYPE;
  vs_time     VARCHAR2(16);
BEGIN
  v_fileMsg := v_fileMsg || 'ID'|| chr(44) ||'NAME'||chr(44) ||'AGE'||chr(44) ||'SEX';
  OPEN get_file_cur;
  SELECT TO_CHAR(sysdate, 'yyyymmddhhmiss') INTO vs_time FROM DUAL;
  LOOP
    FETCH get_file_cur
      INTO get_file_rec;
    EXIT WHEN get_file_cur%NOTFOUND;
      --转换成cvs，需要用逗号（chr(44)）分隔字段，转换成xls，需要用制表符（chr(9)）分隔字段，换行（chr(10)）
      IF get_file_cur%ROWCOUNT=1 THEN
        v_fileMsg := v_fileMsg || 'ID'|| chr(44) ||'NAME'||chr(44) ||'AGE'||chr(44) ||'SEX';
      END IF;
      v_fileMsg := v_fileMsg || chr(10)||to_char( get_file_rec.id) || chr(44) ||
                    get_file_rec.name||chr(44) ||to_char(get_file_rec.age)||chr(44) ||get_file_rec.sex;
    PROC_FILE_WRITE('file'||vs_time,v_fileMsg,'csv');
  END LOOP;
  CLOSE get_file_cur;
EXCEPTION
  WHEN OTHERS THEN
    NULL;
END;
/

按照上面所述，就可以将数据转换成想要的格式，需要注意的是：

1 上面只有xls和cvs的，如果有其他格式的规则，也可以转换；

2 需要对特殊字符进行转换，如分隔cvs的逗号，需要过滤查询结果里的逗号；

3 当我以ID起头拼的串，会报一个小问题，有兴趣的可以试一下，无意中发现的，有明白的朋友请指点一二，谢谢！