Oracle 的SQL*LOADER可以將外部資料載入到資料庫表中。下面是SQL*LOADER的基本特點：
1）能裝入不同資料型別檔案及多個資料檔案的資料
2）可裝入固定格式，自由定界以及可度長格式的資料
3）可以裝入二進位制，壓縮十進位制資料
4）一次可對多個表裝入資料
5）連線多個物理記錄裝到一個記錄中
6）對一單記錄分解再裝入到表中
7）可以用數對制定列生成唯一的KEY
8）可對磁碟或磁帶資料檔案裝入製表中
9）提供裝入錯誤報告
10）可以將檔案中的整型字串，自動轉成壓縮十進位制並裝入列表中。
[oracle@app1 ~]$ sqlldr

SQL*Loader: Release 11.2.0.3.0 - Production on Thu Nov 12 10:30:13 2015

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

Usage: SQLLDR keyword=value [,keyword=value,...]

Valid Keywords:

    userid -- ORACLE username/password
   control -- control file name
       log -- log file name
       bad -- bad file name
      data -- data file name
   discard -- discard file name
discardmax -- number of discards to allow          (Default all)
      skip -- number of logical records to skip    (Default 0)
      load -- number of logical records to load    (Default all)
    errors -- number of errors to allow            (Default 50)
      rows -- number of rows in conventional path bind array or between direct path data saves
               (Default: Conventional path 64, Direct path all)
bindsize -- size of conventional path bind array in bytes (Default 256000)
    silent -- suppress messages during run (header,feedback,errors,discards,partitions)
    direct -- use direct path                      (Default FALSE)
   parfile -- parameter file: name of file that contains parameter specifications
parallel -- do parallel load                     (Default FALSE)
      file -- file to allocate extents from
skip_unusable_indexes -- disallow/allow unusable indexes or index partitions (Default FALSE)
skip_index_maintenance -- do not maintain indexes, mark affected indexes as unusable (Default FALSE)
commit_discontinued -- commit loaded rows when load is discontinued (Default FALSE)
readsize -- size of read buffer                  (Default 1048576)
external_table -- use external table for load; NOT_USED, GENERATE_ONLY, EXECUTE (Default NOT_USED)
columnarrayrows -- number of rows for direct path column array (Default 5000)
streamsize -- size of direct path stream buffer in bytes (Default 256000)
multithreading -- use multithreading in direct path
resumable -- enable or disable resumable for current session (Default FALSE)
resumable_name -- text string to help identify resumable statement
resumable_timeout -- wait time (in seconds) for RESUMABLE (Default 7200)
date_cache -- size (in entries) of date conversion cache (Default 1000)
no_index_errors -- abort load on any index errors (Default FALSE)

PLEASE NOTE: Command-line parameters may be specified either by
position or by keywords. An example of the former case is 'sqlldr
scott/tiger foo'; an example of the latter is 'sqlldr control=foo
userid=scott/tiger'. One may specify parameters by position before
but not after parameters specified by keywords. For example,
'sqlldr scott/tiger control=foo logfile=log' is allowed, but
'sqlldr scott/tiger control=foo log' is not, even though the
position of the parameter 'log' is correct.

1) insert     --為預設方式，在資料裝載開始時要求表為空
2) append --在表中追加新記錄
3) replace --刪除舊記錄(用 delete from table 語句)，替換成新裝載的記錄
4) truncate --刪除舊記錄(用 truncate table 語句)，替換成新裝載的記錄
5)trailing nullcols --如要匯入原始檔此列內容為空，在匯入到資料庫表中，此列內容就是null
6)terminated   by   whitespace　--空白分隔
7)fields terminated by ',' optionally enclose by '"' --有帶字元""插入
8）WHEN --增加條件

1． FIELDS給出記錄中欄位的分隔符，FIELDS格式為：
FIELDS [TERMIALED [BY] {WHITESPACE | [X] 'charcter'} ]
[ [ OPTIONALLY] ENCLOSE [BY] [X]'charcter' ]
TERMINATED 讀完前一個欄位即開始讀下一個欄位直到介紹。
WHITESPACE 是指結束符是空格的意思。包括空格、Tab、換行符、換頁符及回車符。如果是要判斷但字元，可以用單引號括起，如X'1B'等。
OPTIONALLY ENCLOSED 表示資料應由特殊字元括起來。也可以括在TERMINATED字元內。使用OPTIONALLY要同時用TERMINLATED。
ENCLOSED 指兩個分界符內的資料。如果同時用 ENCLOSED和TERMINAED ，則它們的順序決定計算的順序。
7）定義列：
column 是表列名。列的取值可以是：
BECHUM 表示邏輯記錄數。第一個記錄為1,第2個記錄為2。
CONSTANT 表示賦予常數。
SEQUENCE 表示序列可以從任意序號開始，格式為：
SEQUENCE （ { integer | MAX |COUNT} [,increment]
POSITION 給出列在邏輯記錄中的位置。可以是絕對的，或相對前一列的值。格式為：
POSITION ( {start[end] | * [+integer] } )
Start 開始位置
* 表示前欄位之後立刻開始。
+ 從前列開始向後條的位置數。
8）定義資料型別：
可以定義14種資料型別：
CHAR
DATE
DECIMAL EXTERNAL
DECIMAL
DOUBLE
FLOAT
FLOAT EXTERNAL
GRAPHIC EXTERNAL
INTEGER
INTEGER EXTERNAL
SMALLINT
VARCHAR
VARGRAPHIC
1．字元型別資料
CHAR[ (length)] [delimiter]
length預設為 1.
2.日期型別資料
DATE [ ( length)]['date_format' [delimiter]
使用to_date函式來限制。
3．字元格式中的十進位制
DECIMAL EXTERNAL [(length)] [delimiter]
用於常規格式的十進位制數（不是二進位制=> 一個位等於一個bit）。
4．壓縮十進位制格式資料
DECIMAL （digtial [,divcision]）
5．雙精度符點二進位制
DOUBLE
6．普通符點二進位制
FLOAT
7．字元格式符點數
FLOAT EXTERNAL [ (length) ] [delimiter]
8．雙位元組字串資料
GRAPHIC [ (legth)]
9．雙位元組字串資料
GRAPHIC EXTERNAL[ (legth)]
10．常規全字二進位制整數
INTEGER
11．字元格式整數
INTEGER EXTERNAL
12．常規全字二進位制資料
SMALLINT
13．可變長度字串
VARCHAR
14．可變雙位元組字串資料
VARGRAPHIC

2.2寫控制檔案CTL
1．各資料檔案的檔名;
2．各資料檔案格式;
3．各資料檔案裡各資料記錄欄位的屬性；
4．接受資料的ORACLE表列的屬性；
5．資料定義；
6．其它
資料檔案的要求：
資料型別的指定
CHAR 字元型
INTEGER EXTERNAL 整型
DECIMAL EXTERNAL 浮點型
3.1資料檔案的內容
可以在OS下的一個檔案；或跟在控制檔案下的具體資料。資料檔案可以是：
1、二進位制與字元格式：LOADER可以把二進位制檔案讀（當成字元讀）列表中
2、固定格式：記錄中的資料、資料型別、資料長度固定。
3、可變格式：每個記錄至少有一個可變長資料欄位，一個記錄可以是一個連續的字串。
資料段的分界（如姓名、年齡）如用“，”作欄位的分；用，"’作資料
括號等
4、 LOADER可以使用多個連續欄位的物理記錄組成一個邏輯記錄，記錄檔案執行情況檔案：包括以下內容：
1、執行日期：軟體版本號
2、全部輸入，輸出檔名；對命令列的展示資訊，補充資訊，
3、對每個裝入資訊報告：如表名，裝入情況；對初始裝入，加截入或更新裝
入的選擇情況，欄資訊
4、資料錯誤報告：錯誤碼；放棄記錄報告
5、每個裝X報告：裝入行；裝入行數，可能跳過行數；可能拒絕行數；可能放
棄行數等
6、統計概要：使用空間（包大小，長度）；讀入記錄數，裝入記錄數，跳過記
錄數；拒絕記錄數，放棄記錄數；執行時間等。

例子
load data infile 'F:\14\90.txt'
APPEND into table test_90
fields terminated by ''
trailing nullcols
(number_dest)

sqlldr userid='smsdb/zcl941217' control='F:\14\load data190.ctl' log='F:\14\test.log'

create or replace directory MY_DIR as '/opt/oracle';
grant create any directory to smsdb;

txt_handle := UTL_FILE.FOPEN('MY_DIR','total.txt','w');

方法一
DECLARE
row_result varchar2(1024);
selectsql varchar2(1024);
qrycursor SYS_REFCURSOR;
txt_handle UTL_FILE.file_type;
BEGIN
selectsql := 'select distinct number_dest from test_190';
txt_handle := UTL_FILE.FOPEN('MY_DIR','total.txt','w');
open qrycursor for selectsql;
loop
fetch qrycursor into row_result;
exit when qrycursor%notfound;
UTL_FILE.PUT_LINE(txt_handle,row_result);
end loop;
--關閉遊標
close qrycursor;
UTL_FILE.FCLOSE(txt_handle);
end;

方法二
DECLARE

mu_txt UTL_FILE.file_type;

BEGIN

mu_txt := UTL_FILE.FOPEN('P_DIR','DEPT.xls','W',1000);
UTL_FILE.PUT_LINE(mu_txt,'號碼'||chr(9)||'目標號碼'||chr(9)||'條數'||chr(9)||'內容'); --列名
FOR I IN (select c.src,c.dest,c.num,c.content,decode(c.status, 0,'等待傳送',1,'已提交到下行佇列',2,'傳送成功',3,'接收成功',-1,'提交失敗',-2,'接收失敗') 狀態 from t_smsgateway_mt_his c where c.cust_id=490 and
c.insert_time between to_date('2016-01-01','yyyy-mm-dd') and to_date('2016-01-31','yyyy-mm-dd')) LOOP

UTL_FILE.PUT_LINE(mu_txt,I.src_addr||chr(9)||I.dest_addr||chr(9)||I.charge_num||chr(9)||I.msg_content);

END LOOP;
UTL_FILE.FFLUSH(mu_txt);

UTL_FILE.FCLOSE(mu_txt);

END;

ASCII碼對應表
chr(9) tab空格 chr(10) 換行 chr(13) 回車 Chr(13)&chr(10) 回車換行 chr(32) 空格符 chr(34) 雙引號 chr(39) 單引號

chr(33) ! chr(34) " chr(35) # chr(36) $ chr(37) % chr(38) & chr(39) ‘ chr(40) ( chr(41) ) chr(42) *
chr(43) + chr(44) , chr(45) - chr(46) . chr(47) /

Chr(48) 0 Chr(49) 1 Chr(50) 2 Chr(51) 3 Chr(52) 4 Chr(53) 5 Chr(54) 6 Chr(55) 7 Chr(56) 8 Chr(57) 9

chr(58) chr(59) ; chr(60) < chr(61) = chr(62) > chr(63) ? chr(64) @

chr(65) A chr(66) B chr(67) C chr(68) D chr(69) E chr(70) F chr(71) G chr(72) H chr(73) I chr(74) J
chr(75) K chr(76) L chr(77) M chr(78) N chr(79) O chr(80) P chr(81) Q chr(82) R chr(83) S chr(84) T
chr(85) U chr(86) V chr(87) W chr(88) X chr(89) Y chr(90) Z

chr(91) [ chr(92) \ chr(93) ] chr(94) ^ chr(95) _ chr(96) `

chr(97) a chr(98) b chr(99) c chr(100) d chr(101) e chr(102) f chr(103) g chr(104) h chr(105) i
chr(106) j chr(107) k chr(108) l chr(109) m chr(110) n chr(111) o chr(112) p chr(113) q chr(114) r
chr(115) s chr(116) t chr(117) u chr(118) v chr(119) w chr(120) x chr(121) y chr(122) z

chr(123) { chr(124) | chr(125) } chr(126) ~ chr(127) chr(128) chr(153)? chr(169) © chr(174) ?

ORACLE百萬資料匯入匯出解決方法（LOADER、UTL_FILE）

相關文章