oracle 导出查询结果至CSV

来源：互联网发布：淘宝网禁止发布编辑：程序博客网时间：2024/04/27 16:09

使用oracle存储过程实现查询结果导出至csv文件中。调用过程时，需要在oracle服务器上定义目录（已经存在的目录）。

create or replace PROCEDURE PX_SYS_IMP_CSV(V_SQL IN VARCHAR2,V_DIR IN VARCHAR2,V_FILENAME IN VARCHAR2)

IS
V_FILE UTL_FILE.FILE_TYPE;
V_CURSOR INTEGER DEFAULT DBMS_SQL.OPEN_CURSOR;
V_COL_VALUE VARCHAR2(4000);

V_COL_CNT NUMBER := 0;
V_SEPARATOR CHAR(1);
V_TAB_DESC DBMS_SQL.DESC_TAB;

V_STATUS INTEGER;
V_MAX_LINESIZE NUMBER := 32000;
BEGIN
--OPEN FILE
V_FILE := UTL_FILE.FOPEN(V_DIR, V_FILENAME, 'W', V_MAX_LINESIZE);
EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_DATE_FORMAT=''YYYY-MM-DDHH24:MI:SS''';

--OPEN CURSOR
DBMS_SQL.PARSE( V_CURSOR, V_SQL, DBMS_SQL.NATIVE );
DBMS_SQL.DESCRIBE_COLUMNS( V_CURSOR, V_COL_CNT, V_TAB_DESC );

--DUMP TABLE COLUMN NAME
FOR I IN 1 .. V_COL_CNT LOOP
    UTL_FILE.PUT( V_FILE, V_SEPARATOR || '"' || V_TAB_DESC(I).COL_NAME ||'"' );
    DBMS_SQL.DEFINE_COLUMN( V_CURSOR, I, V_COL_VALUE, 4000 );
    V_SEPARATOR := ',';
END LOOP;
UTL_FILE.NEW_LINE( V_FILE );

--EXECUTE THE QUERY STATEMENT
V_STATUS := DBMS_SQL.EXECUTE(V_CURSOR);

--DUMP TABLE COLUMN VALUE
WHILE ( DBMS_SQL.FETCH_ROWS(V_CURSOR) > 0 ) LOOP
    V_SEPARATOR := '';
    FOR I IN 1 .. V_COL_CNT LOOP
      DBMS_SQL.COLUMN_VALUE( V_CURSOR, I, V_COL_VALUE );
      UTL_FILE.PUT( V_FILE, V_SEPARATOR || '"' ||
      TRIM(BOTH ' ' FROM REPLACE(V_COL_VALUE,'"','""')) ||'"');
      V_SEPARATOR := ',';
    END LOOP;
    UTL_FILE.NEW_LINE( V_FILE );
END LOOP;

--CLOSE CURSOR
DBMS_SQL.CLOSE_CURSOR(V_CURSOR);

--CLOSE FILE
UTL_FILE.FCLOSE( V_FILE );

END PX_SYS_IMP_CSV ;

1 0