赞
踩
经常有需求,需要导出数据到文本文件,使用spool要刷屏,比较慢,那么使用utl_file是个不错的选择,可以通过如下脚本:
调用的时候直接输入参数:文件名和相应的sql查询:
CREATE OR REPLACE PROCEDURE expdata(filename varchar2,lsql varchar2) AS vFileName VARCHAR2(256); vFileHandle UTL_FILE.FILE_TYPE; vNum NUMBER(20); BeginTime DATE; EndTime DATE; CostTime VARCHAR2(14); DbResult VARCHAR2(4000); vsql VARCHAR2(4000); type rc is ref cursor; SELECTCURSOR rc; BEGIN vsql :=lsql; vFileName := filename ; vFileHandle := UTL_FILE.fopen('DATA_PUMP_DIR', vFileName, 'w',32767); SELECT SYSDATE INTO BeginTime FROM DUAL ; DBMS_OUTPUT.PUT_LINE('Begin Time:'||TO_CHAR(BeginTime,'YYYY-MM-DD HH24:MI:SS')); vNum:=0; OPEN SELECTCURSOR for vsql; LOOP FETCH SELECTCURSOR INTO DbResult; EXIT WHEN SELECTCURSOR%NOTFOUND; vNum := vNum + 1; dbms_output.put_line(DbResult); UTL_FILE.put_line(vFileHandle, DbResult); END LOOP; CLOSE SELECTCURSOR; IF vNum =0 THEN UTL_FILE.fclose(vFileHandle); UTL_FILE.fremove('DATA_PUMP_DIR', vFileName); DBMS_OUTPUT.PUT_LINE('No Data Found'); ROLLBACK; RETURN; END IF; UTL_FILE.fflush(vFileHandle); UTL_FILE.fclose(vFileHandle); SELECT SYSDATE INTO EndTime FROM DUAL ; DBMS_OUTPUT.PUT_LINE('End Time:'||TO_CHAR(EndTime,'YYYY-MM-DD HH24:MI:SS')); DBMS_OUTPUT.PUT_LINE('Elapes:'||trunc(EndTime-BeginTime)*24*60||'Min'); DBMS_OUTPUT.PUT_LINE('Summary Rows='||vNum||'rows'); COMMIT; EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('Not Find Data'); ROLLBACK; -- WHEN OTHERS THEN -- ROLLBACK; END; /
Copyright © 2003-2013 www.wpsshop.cn 版权所有,并保留所有权利。