java oracle导出csv_Oracle导出CSV文件

--建立存储过程

CREATE OR REPLACE PROCEDURESQL_TO_CSV

(

P_QUERYIN VARCHAR2, --PLSQL文

P_DIRIN VARCHAR2, --导出的文件放置目录

P_FILENAMEIN VARCHAR2 --CSV名

)ISL_OUTPUT UTL_FILE.FILE_TYPE;

L_THECURSORINTEGER DEFAULTDBMS_SQL.OPEN_CURSOR;

L_COLUMNVALUEVARCHAR2(4000);

L_STATUSINTEGER;

L_COLCNTNUMBER := 0;

L_SEPARATORVARCHAR2(1);

L_DESCTBL DBMS_SQL.DESC_TAB;

P_MAX_LINESIZENUMBER := 32000;BEGIN

--OPEN FILE

L_OUTPUT := UTL_FILE.FOPEN(P_DIR, P_FILENAME, ‘W‘, P_MAX_LINESIZE);--DEFINE DATE FORMAT

EXECUTE IMMEDIATE ‘ALTER SESSION SET NLS_DATE_FORMAT=‘‘YYYY-MM-DD HH24:MI:SS‘‘‘;--OPEN CURSOR

DBMS_SQL.PARSE( L_THECURSOR, P_QUERY, DBMS_SQL.NATIVE );

DBMS_SQL.DESCRIBE_COLUMNS( L_THECURSOR, L_COLCNT, L_DESCTBL );--DUMP TABLE COLUMN NAME

FOR I IN 1.. L_COLCNT LOOP

UTL_FILE.PUT( L_OUTPUT, L_SEPARATOR|| ‘”‘ || L_DESCTBL(I).COL_NAME || ‘”‘);

DBMS_SQL.DEFINE_COLUMN( L_THECURSOR, I, L_COLUMNVALUE,4000);

L_SEPARATOR := ‘,‘;ENDLOOP;

UTL_FILE.NEW_LINE( L_OUTPUT );--EXECUTE THE QUERY STATEMENT

L_STATUS := DBMS_SQL.EXECUTE(L_THECURSOR);--DUMP TABLE COLUMN VALUE

WHILE ( DBMS_SQL.FETCH_ROWS(L_THECURSOR) > 0) LOOP

L_SEPARATOR := ‘‘;FOR I IN 1.. L_COLCNT LOOP

DBMS_SQL.COLUMN_VALUE( L_THECURSOR, I, L_COLUMNVALUE );

UTL_FILE.PUT( L_OUTPUT, L_SEPARATOR|| ‘”‘ ||TRIM(BOTH‘ ‘ FROM REPLACE(L_COLUMNVALUE,‘”‘,‘””‘)) || ‘”‘);

L_SEPARATOR := ‘,‘;ENDLOOP;

UTL_FILE.NEW_LINE( L_OUTPUT );ENDLOOP;--CLOSE CURSOR

DBMS_SQL.CLOSE_CURSOR(L_THECURSOR);--CLOSE FILE

UTL_FILE.FCLOSE( L_OUTPUT );

EXCEPTIONWHEN OTHERS THENRAISE;END;/

--创建存放文件的目录

CREATE OR REPLACE DIRECTORY MYDIR AS ‘C:\‘;--执行块

beginsql_to_csv(‘select * from au_jxs‘,‘MYDIR‘,‘EXAMPLE.CSV‘);end;/