将任意表数据导出为CSV文件的过程

 

  UTL_FILE包的简单例子 

开发人员要实现一个自动将表中数据导出到一个csv格式文件的功能。建议他们使用UTL_FILE包加DBMS_JOB实现。最后给他们写了一个简单的UTL_FILE包使用的例子。

以前需要使用UTL_FILE包，必须设置utl_file_dir初始化参数，必须重启数据库才能使这个初始化参数修改生效。这成为使用UTL_FILE包最不方便的地方。

在Oracle9i中，增强了UTL_FILE包的功能，使得UTL_FILE包可以使用DIRECTORY，这就解决了重启数据库的问题。

注意，由于对目录的读写具有比较大的安全隐患，因此CREATE DIRECTORY权限只授权给DBA用户，而且创建了DIRECTORY后，对DIRECTORY的读写操作的权限也要DBA来授权。

本例中，当前用户拥有DBA角色，因此省略了GRANT READ, WRITE ON DIRECTORY的步骤。

SQL> CREATE DIRECTORY D_OUTPUT AS 'E:';

目录已创建。

SQL> CREATE OR REPLACE PROCEDURE P_WRITE_EMP AS
2 V_FILE UTL_FILE.FILE_TYPE;
3 V_BUFFER VARCHAR2(32767);
4 BEGIN
5 V_FILE := UTL_FILE.FOPEN('D_OUTPUT', 'EMP' || TO_CHAR(SYSDATE, 'YYYY_MM_DD') || '.csv', 'w', 32767);
6 V_BUFFER := 'EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO';
7 UTL_FILE.PUT_LINE(V_FILE, V_BUFFER);
8 FOR I IN 
9 (
10 SELECT '"' || EMPNO || '","' || 
11 ENAME || '","' || 
12 JOB || '","' || 
13 MGR || '","' || 
14 HIREDATE || '","' || 
15 SAL || '","' || 
16 COMM || '","' || 
17 DEPTNO || '"' RESULT
18 FROM EMP
19 ) LOOP
20 UTL_FILE.PUT_LINE(V_FILE, I.RESULT);
21 END LOOP;
22 UTL_FILE.FCLOSE(V_FILE);
23 END;
24 /

过程已创建。

SQL> EXEC P_WRITE_EMP

PL/SQL 过程已成功完成。

例子很简单，这里就不多做解释了，最后给出文件EMP2006_09_06.csv的内容：

EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO
"7369","SMITH","CLERK","7902","1980-12-17 00:00:00","960","","20"
"7499","ALLEN","SALESMAN","7698","1981-02-20 00:00:00","1920","300","30"
"7521","WARD","SALESMAN","7698","1981-02-22 00:00:00","1500","500","30"
"7566","JONES","MANAGER","7839","1981-04-02 00:00:00","3570","","20"
"7654","MARTIN","SALESMAN","7698","1981-09-28 00:00:00","1500","1400","30"
"7698","BLAKE","MANAGER","7839","1981-05-01 00:00:00","3420","","30"
"7782","CLARK","MANAGER","7839","1981-06-09 00:00:00","2940","","10"
"7788","SCOTT","ANALYST","7566","1982-12-09 00:00:00","3600","","20"
"7839","KING","PRESIDENT","","1981-11-17 00:00:00","6000","","10"
"7844","TURNER","SALESMAN","7698","1981-09-08 00:00:00","1800","0","30"
"7876","ADAMS","CLERK","7788","1983-01-12 00:00:00","1320","","20"
"7900","JAMES","CLERK","7698","1981-12-03 00:00:00","1140","","30"
"7902","FORD","ANALYST","7566","1981-12-03 00:00:00","3600","","20"
"7934","MILLER","CLERK","7782","1982-01-23 00:00:00","1560","","10"

=======================================================================================================

将任意表数据导出为CSV文件的过程 

昨天写个一个将指定表的数据导出到CSV文件的过程。想了一下，觉得通用型不好，对于不同的表还需要重新改写过程。于是今天写了一个通用的过程，可以处理任意表的任意字段，而且可以添加查询条件。

上一个例子已经介绍过UTL_FILE包了，这里就不重复了，下面直接给出代码：

SQL> CREATE DIRECTORY D_OUTPUT AS 'E:';

目录已创建。

SQL> CREATE OR REPLACE PROCEDURE P_WRITE_FILE
2 (
3 P_TABLE_NAME IN VARCHAR2, 
4 P_COLUMN_LIST IN VARCHAR2 DEFAULT NULL, 
5 P_WHERE_STR IN VARCHAR2 DEFAULT NULL) AS
6 V_FILE UTL_FILE.FILE_TYPE;
7 V_BUFFER VARCHAR2(32767);
8 V_RESULT VARCHAR2(32767);
9 C_RESULT SYS_REFCURSOR;
10 BEGIN
11 V_FILE := UTL_FILE.FOPEN('D_OUTPUT', P_TABLE_NAME || TO_CHAR(SYSDATE, 'YYYY_MM_DD') || '.csv',
'w', 32767);
12 IF P_COLUMN_LIST IS NULL THEN
13 FOR C_COLUMN IN (SELECT COLUMN_NAME FROM USER_TAB_COLUMNS WHERE TABLE_NAME = P_TABLE_NAME) LOOP
14 V_BUFFER := V_BUFFER || C_COLUMN.COLUMN_NAME || ',';
15 END LOOP;
16 V_BUFFER := RTRIM(V_BUFFER, ',');
17 ELSE
18 V_BUFFER := P_COLUMN_LIST;
19 END IF;
20 UTL_FILE.PUT_LINE(V_FILE, V_BUFFER);
21 V_BUFFER := REPLACE(V_BUFFER, ',', '|| ''","'' ||');
22 V_BUFFER := 'SELECT ''"'' ||' || V_BUFFER || '||''"'' RESULT FROM ' || P_TABLE_NAME;
23 IF P_WHERE_STR IS NOT NULL THEN
24 IF SUBSTR(LTRIM(P_WHERE_STR), 1, 5) != 'WHERE' THEN
25 V_BUFFER := V_BUFFER || ' WHERE';
26 END IF;
27 V_BUFFER := V_BUFFER || ' ' || P_WHERE_STR;
28 END IF;
29 OPEN C_RESULT FOR V_BUFFER;
30 LOOP
31 FETCH C_RESULT INTO V_RESULT;
32 EXIT WHEN C_RESULT%NOTFOUND;
33 UTL_FILE.PUT_LINE(V_FILE, V_RESULT);
34 END LOOP;
35 UTL_FILE.FCLOSE(V_FILE); 
36 END;
37 /

过程已创建。

SQL> EXEC P_WRITE_FILE('EMP')

PL/SQL 过程已成功完成。

得到的文件输出文件：

EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO
"7369","SMITH","CLERK","7902","17-12月-80","960","","20"
"7499","ALLEN","SALESMAN","7698","20-2月 -81","1920","300","30"
"7521","WARD","SALESMAN","7698","22-2月 -81","1500","500","30"
"7566","JONES","MANAGER","7839","02-4月 -81","3570","","20"
"7654","MARTIN","SALESMAN","7698","28-9月 -81","1500","1400","30"
"7698","BLAKE","MANAGER","7839","01-5月 -81","3420","","30"
"7782","CLARK","MANAGER","7839","09-6月 -81","2940","","10"
"7788","SCOTT","ANALYST","7566","09-12月-82","3600","","20"
"7839","KING","PRESIDENT","","17-11月-81","6000","","10"
"7844","TURNER","SALESMAN","7698","08-9月 -81","1800","0","30"
"7876","ADAMS","CLERK","7788","12-1月 -83","1320","","20"
"7900","JAMES","CLERK","7698","03-12月-81","1140","","30"
"7902","FORD","ANALYST","7566","03-12月-81","3600","","20"
"7934","MILLER","CLERK","7782","23-1月 -82","1560","","10"

日期现在是默认的格式，可以通过ALTER SESSION SET NLS_DATE_FORMAT的方式来改变日期的格式。

SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';

会话已更改。

SQL> EXEC P_WRITE_FILE('EMP', 'EMPNO,ENAME,DEPTNO,HIREDATE')

PL/SQL 过程已成功完成。

输出文件：

EMPNO,ENAME,DEPTNO,HIREDATE
"7369","SMITH","20","1980-12-17 00:00:00"
"7499","ALLEN","30","1981-02-20 00:00:00"
"7521","WARD","30","1981-02-22 00:00:00"
"7566","JONES","20","1981-04-02 00:00:00"
"7654","MARTIN","30","1981-09-28 00:00:00"
"7698","BLAKE","30","1981-05-01 00:00:00"
"7782","CLARK","10","1981-06-09 00:00:00"
"7788","SCOTT","20","1982-12-09 00:00:00"
"7839","KING","10","1981-11-17 00:00:00"
"7844","TURNER","30","1981-09-08 00:00:00"
"7876","ADAMS","20","1983-01-12 00:00:00"
"7900","JAMES","30","1981-12-03 00:00:00"
"7902","FORD","20","1981-12-03 00:00:00"
"7934","MILLER","10","1982-01-23 00:00:00"

SQL> EXEC P_WRITE_FILE('EMP', 'EMPNO,ENAME,DEPTNO', 'WHERE DEPTNO = 30')

PL/SQL 过程已成功完成。

输出文件：

EMPNO,ENAME,DEPTNO
"7499","ALLEN","30"
"7521","WARD","30"
"7654","MARTIN","30"
"7698","BLAKE","30"
"7844","TURNER","30"
"7900","JAMES","30"

当然，过程还是有限制的，当取出的列的长度总和超过了32767，则UTL_FILE包就没有办法将内容写入文件了。这也是PL/SQL中VARCHAR2类型最大容量。

不过对于大多数的情况，32767应该足够了，如果超过这个数值，恐怕就只能使用C或JAVA外部过程了。