關于SPOOL(SPOOL是SQLPLUS的命令，不是SQL語法里面的東西。)

對于SPOOL數據的SQL，最好要自己定義格式，以方便程序直接導入,SQL語句如：

select empno||','||ename||','||sal from emp;

spool常用的設置

set colsep' ';　　　 //域輸出分隔符

set echo off;　　　　//顯示start啟動的腳本中的每個sql命令，缺省為on

set feedback off;　　//回顯本次sql命令處理的記錄條數，缺省為on

set heading off;　　 //輸出域標題，缺省為on

set pagesize 0;　　　//輸出每頁行數，缺省為24,為了避免分頁，可設定為0。

set termout off;　　 //顯示腳本中的命令的執行結果，缺省為on

set trimout on;　　　//去除標準輸出每行的拖尾空格，缺省為off

set trimspool on;　　//去除重定向(spool)輸出每行的拖尾空格，缺省為off

導出文本數據的建議格式：SQL*PLUS環境設置

SET NEWPAGE NONE

SET HEADING OFF

SET SPACE 0

SET PAGESIZE 0

SET TRIMOUT ON

SET TRIMSPOOL ON

SET LINESIZE 2500

注：LINESIZE要稍微設置大些，免得數據被截斷，它應和相應的TRIMSPOOL結合使用防止導出的文本有太多的尾部空格。但是如果LINESIZE設置太大，會大大降低導出的速度，另外在WINDOWS下導出最好不要用PLSQL導出，速度比較慢，直接用COMMEND下的SQLPLUS命令最小化窗口執行。

對于字段內包含很多回車換行符的應該給與過濾，形成比較規矩的文本文件。通常情況下，我們使用SPOOL方法，將數據庫中的表導出為文本文件的時候會采用兩種方法，如下述：

方法一：(設置分隔符導出)

set colsep ','??? --設置,為分隔符,符合CSV文件格式

set trimspool on --設置導出樣式

set linesize 220

set pagesize 2000

set newpage NONE

set heading off

set term off

set num 18

set feedback off

spool D:/A.CSV??? --你要生成的文件路徑

select * from EMP;? --你要獲取數據的表的SQL語句

spool off? --導出

方法二：(拼接SQL導出)

set trimspool on

set linesize 220

set pagesize 2000

set newpage none

set heading off

set term off

spool D:/A.CSV

select empno||','||ename||','||sal? from EMP;

spool off

比較以上方法，即方法一采用設定分隔符然后由sqlplus自己使用設定的分隔符對字段進行分割，方法二將分隔符拼接在SELECT語句中，即手工控制輸出格式。

在實踐中，發現通過方法一導出來的數據具有很大的不確定性，這種方法導出來的數據再由sqlldr導入的時候出錯的可能性在95%以上，尤其對大批量的數據表，如100萬條記錄的表更是如此，而且導出的數據文件大。

而方法二導出的數據文件格式很規整，數據文件的大小可能是方法一的1/4左右。經這種方法導出來的數據文件再由sqlldr導入時，出錯的可能性很小，基本都可以導入成功。

因此，實踐中我建議大家使用方法二手工去控制spool文件的格式，這樣可以減小出錯的可能性，避免走很多彎路(為什么會這樣呢,沒有說明..)

后面還有LINUX的shell腳本寫的導出,(LINUX戰時沒有深入,待學習..)

結合之前的HTML導出的方式,看下面一段代碼導出比較公整的EXCEL文件:

conn SCOTT/TIGER@FZLGFM

set heading off

Set pagesize 0

Set term off

Set feedback off

set linesize 99999

set trimspool off

set term off verify off feedback off pagesize 99999

spool D:/b.xls

set markup html on? entmap ON spool on preformat off

select '工號','姓名','薪水','部門號' from dual

union all

select to_char(sal), ename, to_char(sal), to_char(sal) from emp;

spool off

工號

姓名

薪水

部門號

7369

SMITH

800

20

7499

ALLEN

1600

30

7521

WARD

1250

30

7566

JONES

2975

20

7654

MARTIN

1250

30

7698

BLAKE

2850

30

7782

CLARK

2450

10

7788

SCOTT

4000

20

PS:注意,UNION ALL字段類型要匹配

select '工號','姓名','薪水','部門號' from dual

union all

select to_char(sal), ename, to_char(sal), to_char(sal) from emp;

以上是通過客戶端實現的導出,再來看看服務器的導出的存儲過程:

第一步:創建目錄和賦權限

--*********************************************************************

--***

--***首先以dba或者是其他具有create directory的用戶創建directory，然后授權

--***

--*********************************************************************

CREATE OR REPLACE DIRECTORY dir_export AS 'd:/oracle';

GRANT read,write ON directory dir_export TO scott;

第二步:建立導出存儲過程:

--*********************************************************************

--***

--***執行導出文本文件的存儲過程：p_exp

--***p_tname:要導出的表明；p_query：where條件，根據這個條件導出，如果為null,則全表導出

--***

--***

--*********************************************************************

create or replace procedure p_exp(p_tname varchar2)

as

v_line? varchar2(2000);????????????????????? --記錄的數據

v_sql??? varchar2(2000);????????????????????? --動態sql變量

v_fname? varchar2(40);??????????????????????? --導出數據的文件名字

cursor cursor_column????????????????????????? --游標：通過數據字典查找出表有哪些字段

is select column_name,data_type

from user_tab_columns where table_name=upper(p_tname);?? --獲取表字段

type type_cursor is ref cursor ;

mycursor type_cursor;

outf utl_file.file_type;???? --導出文件類型

begin

v_fname:=p_tname||'.csv';??? --導出的文件名和類型

for col in cursor_column loop

--判斷字段類型,生成對應的數據格式

if col.data_type='DATE' then

v_sql:=v_sql||'to_char('||col.column_name||',''yyyy-mm-dd-hh24:mi:ss'')'||''',''';

else

v_sql:=v_sql||col.column_name||'||'',''||';

end if;

end loop;

v_sql:=rtrim(v_sql,'||'',''||'); --把拼接字段最后的拼接字符去除

v_sql:='select '||v_sql||' from '||p_tname;

dbms_output.put_line(v_sql);

outf:=utl_file.fopen('DIR_EXPORT',v_fname,'w'); --打開文件夾,創建文件

open mycursor for v_sql;

loop

fetch mycursor into v_line;

exit when mycursor%notfound;

--dbms_output.put_line(v_line); --控制臺打印每條記錄

utl_file.put_line(outf,v_line);? --將每條記錄寫入文件

end loop;

utl_file.fclose(outf);?? --關閉文件讀寫流

dbms_output.put_line('導出成功');

end;

第三布:執行該存儲過程得到 EMP.TXT里的如下數據

7369,?SMITH?,CLERK?,7902?,1980-12-17-00:00:00,?800,?200?,20

7499?,ALLEN?,SALESMAN,?7698?,1981-02-20-00:00:00?,1600?,300?,30

..........

呵呵,導出CSV成功,,,如果有其它的需求,繼續深入,我的需求這些方法都無法滿足,期待下一篇.....

總結

以上是生活随笔為你收集整理的oracle set markup,oracle sql*plus set spool介绍(二)的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。