oracle 如何把表导出csv

发布时间：2020-12-12 15:43:19 所属栏目：百科来源：网络整理

导读：CREATE OR REPLACE PROCEDURE SQL_TO_CSV ( P_QUERY IN VARCHAR2, --PLSQL文 P_DIR IN VARCHAR2, --导出的文件放置目录 P_FILENAME IN VARCHAR2 --CSV名 ) IS L_OUTPUTUTL_FILE.FILE_TYPE; L_THECURSOR INTEGER DEFAULT DBMS_SQL.OPEN_CURSOR; L_COLUMNVALUE

CREATE OR REPLACE PROCEDURE SQL_TO_CSV ( P_QUERY IN VARCHAR2, --PLSQL文 P_DIR IN VARCHAR2, --导出的文件放置目录 P_FILENAME IN VARCHAR2 --CSV名 ) IS L_OUTPUTUTL_FILE.FILE_TYPE; L_THECURSOR INTEGER DEFAULT DBMS_SQL.OPEN_CURSOR; L_COLUMNVALUEVARCHAR2(4000); L_STATUS INTEGER ; L_COLCNTNUMBER:=0; L_SEPARATORVARCHAR2(1); L_DESCTBLDBMS_SQL.DESC_TAB; P_MAX_LINESIZENUMBER:=32000; BEGIN --OPENFILE L_OUTPUT:=UTL_FILE.FOPEN(P_DIR,P_FILENAME,‘W’,P_MAX_LINESIZE); --DEFINEDATEFORMAT EXECUTE IMMEDIATE‘ ALTER SESSION SET NLS_DATE_FORMAT=’’YYYY-MM-DDHH24:MI:SS’’’; --OPENCURSOR DBMS_SQL.PARSE(L_THECURSOR,P_QUERY,DBMS_SQL.NATIVE); DBMS_SQL.DESCRIBE_COLUMNS(L_THECURSOR,L_COLCNT,L_DESCTBL); --DUMPTABLECOLUMNNAME FOR I IN 1..L_COLCNTLOOP UTL_FILE.PUT(L_OUTPUT,L_SEPARATOR||‘”’||L_DESCTBL(I).COL_NAME||‘”’); DBMS_SQL.DEFINE_COLUMN(L_THECURSOR,I,L_COLUMNVALUE,4000); L_SEPARATOR:=‘,’; END LOOP; UTL_FILE.NEW_LINE(L_OUTPUT); --EXECUTETHEQUERYSTATEMENT L_STATUS:=DBMS_SQL. EXECUTE (L_THECURSOR); --DUMPTABLECOLUMNVALUE WHILE(DBMS_SQL.FETCH_ROWS(L_THECURSOR)>0)LOOP L_SEPARATOR:=‘’; FOR I IN 1..L_COLCNTLOOP DBMS_SQL.COLUMN_VALUE(L_THECURSOR,L_COLUMNVALUE); UTL_FILE.PUT(L_OUTPUT,L_SEPARATOR||‘”’|| TRIM(BOTH‘‘ FROM REPLACE (L_COLUMNVALUE,’”’,’””’))||‘”’); L_SEPARATOR:=‘,’; END LOOP; UTL_FILE.NEW_LINE(L_OUTPUT); END LOOP; --CLOSECURSOR DBMS_SQL.CLOSE_CURSOR(L_THECURSOR); --CLOSEFILE UTL_FILE.FCLOSE(L_OUTPUT); EXCEPTION WHEN OTHERS THEN RAISE; END ;

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!