生成CVS文件的过程

生成CVS文件的过程[@more@]

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外部过程了。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/32202/viewspace-889182/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/32202/viewspace-889182/