Oracle的SQLLDR用法簡介

目錄SQLLDR導入1.簡介2.語法和參數 3. 范例 4.Sqlldr 有兩種使用方式其他SQLLDR導入1.簡介

SQL*LOADER是ORACLE的數據加載工具，通常用來將操作系統文件（數據）遷移到ORACLE數據庫中。SQL*LOADER是大型數據倉庫選擇使用的加載方法，因為它提供了最快速的途徑（DIRECT，PARALLEL）。

2.語法和參數

語法： SQLLDR keyword=value [,keyword=value,...];

Sqlldr 參數一覽

Keyword默認值描述useridORACLE 用戶名/口令 control控制文件名log日志文件名bad錯誤文件名data數據文件名discard廢棄文件名discardmax全部允許廢棄的文件的數目skip0要跳過的邏輯記錄的數目load全部要加載的邏輯記錄的數目errors允許的錯誤的數目rows常規：64 默認路徑：全部常規路徑綁定數組中或直接路徑保存數據間的行數bindsize256000常規路徑綁定數組的大小silent運行過程中隱藏消息directFALSE使用直接路徑parfile參數文件: 包含參數說明的文件的名稱parallelFALSE執行并行加載file執行文件skip_unusable_indexesFALSE不允許/允許使用無用的索引或索引分區skip_index_maintenanceFALSE沒有維護索引, 將受到影響的索引標記為無用commit_discontinuedFALSE提交加載中斷時已加載的行readsize1048576讀取緩沖區的大小external_tableNOT_USED使用外部表進行加載; NOT_USED, GENERATE_ONLY, EXECUTEcolumnarrayrows5000直接路徑列數組的行數streamsize256000直接路徑流緩沖區的大小 (以字節計)multithreading在直接路徑中使用多線程resumableFALSE啟用或禁用當前的可恢復會話resumable_name有助于標識可恢復語句的文本字符串resumable_timeout7200RESUMABLE 的等待時間 (以秒計)date_cache1000日期轉換高速緩存的大小 (以條目計)3. 范例

利用PLSQL生成測試數據cux_sqlldr_test.txt

BEGIN? FOR iIN1..100??? LOOP????? IFMOD(i,2)=1THEN??????? dbms_output.put_line('''||i||'','column1_'||i||'','||''column2_'||i||'','||''column3_'||i||'','||''show_column_'||i||'','||''hide_column_'||i||'','2017-01-01'');?????? ELSE??????? dbms_output.put_line('''||i||'','column1_'||i||'', ,'||''column3_'||i||'','||''show_column_'||i||'','||''hide_column_'||i||''');?????????????? ENDIF;??? ENDLOOP;END;

建表

CREATETABLE cux.cux_sqlldr_test(line_num?NUMBER,?seq_num NUMBER,?column1 VARCHAR2(30),?column2 VARCHAR2(30)NOTNULL,?column3 VARCHAR2(30)DEFAULT'column2',?show_column VARCHAR2(30),?hide_column VARCHAR2(30),?creation_date DATE);CREATEORREPLACE?SYNONYM apps.cux_sqlldr_test?FOR cux.cux_sqlldr_test;CREATESEQUENCE cux.cux_sqlldr_test_sSTARTWITH10001;CREATEORREPLACESYNONYM apps.cux_sqlldr_test_s FOR cux.cux_sqlldr_test_s;4.Sqlldr 有兩種使用方式

（1）在控制文件中包涵數據.

創建一個文件命名為cux_sqlldr_test.ctl，在服務器下創建目錄Sqlldr,在sqlldr下創建log和bad文件夾，

上傳cux_sqlldr_test.ctl至服務器 ，如下圖所示：

cux_sqlldr_test.ctl內容如下:

OPTIONS (skip=3,rows=128)load data?????CHARACTERSET ZHS16GBK??infile? *??????badfile? '/u02/ebsuat/app/fs2/EBSapps/appl/cux/12.0.0/sqlldr/bad/cux_sqlldr_test.bad'discardfile? '/u02/ebsuat/app/fs2/EBSapps/appl/cux/12.0.0/sqlldr/bad/cux_sqlldr_test.disc'?TRUNCATE into table cux_sqlldr_test?WHEN column1 != 'column1_1'Fields terminated by ','Optionally enclosed by '''TRAILING NULLCOLS?(line_num? RECNUM ,seq_num? 'cux_sqlldr_test_s.nextval' ,column1 ,column2 ,column3 NULLIF (column3='column3_4'),show_column 'UPPER(:show_column)' ,hide_column? FILLER ,?creation_date? DATE? 'YYYY-MM-DD' 'CASE WHEN :creation_date is null THEN TO_CHAR(sysdate,'YYYY-MM-DD')? ELSE :creation_date END')BEGINDATA'1','column1_1','column2_1','column3_1','show_column_1','hide_column_1','2017-01-01''2','column1_2', ,'column3_2','show_column_2','hide_column_2''3','column1_3','column2_3','column3_3','show_column_3','hide_column_3','2017-01-01''4','column1_4', ,'column3_4','show_column_4','hide_column_4''5','column1_5','column2_5','column3_5','show_column_5','hide_column_5','2017-01-01''6','column1_6', ,'column3_6','show_column_6','hide_column_6''7','column1_7','column2_7','column3_7','show_column_7','hide_column_7','2017-01-01''8','column1_8', ,'column3_8','show_column_8','hide_column_8''9','column1_9','column2_9','column3_9','show_column_9','hide_column_9','2017-01-01''10','column1_10', ,'column3_10','show_column_10','hide_column_10'

運行命令

sqlldr userid=apps/appsjiahuicontrol=/u02/ebsuat/app/fs2/EBSapps/appl/cux/12.0.0/sqlldr/file/cux_sqlldr_test.ctl log=$CUX_TOP/sqlldr/file/cux_sqlldr_test.log

查看結果

查看表

由上圖可以看出，運行命令后，在file文件夾下生成了cux_sqlldr_test.log，在bad下生成了cux_sqlldr_test.bad，在表中生成了3條記錄，下面分析一下cux_sqlldr_test.ctl的內容和結果

代碼說明OPTIONS (skip=3,rows=128)sqlldr 的內容可以寫在cotrol文件load_data的前面，此處跳過前3行,每次提交128行load data 加載數據CHARACTERSET ZHS16GBK 字符集編碼（如果出現亂碼要考慮一下）infile * 加載的文件，* 表示本文件badfile 錯誤的數據所放的文件（校驗錯誤）discardfile丟棄的數據放的路徑（記錄的格式錯誤或過濾行）TRUNCATE into table cux_sqlldr_test 先TRUNCATE  cux_sqlldr_test再將記錄插入表WHEN column1 != "column1_1"過濾行，對于值為column1_1的行過濾Fields terminated by ","多個字段間用“,”隔開Optionally enclosed by '"'單個字段用“"”，“"”開始結束TRAILING NULLCOLS 對于值為空的字段允許為空(line_num RECNUM序號，自動生成，并不取自數據seq_num "cux_sqlldr_test_s.nextval"取每條記錄的第一個字段，此處應為1..10，但是這里賦值序列。

表2

代碼說明column1column1column2column2，表定義為非空字段，雖然上面允許為空，但是如果該值為空，不能插入表種column3 NULLIF (column3="column3_4")column3="column3_4"時候默認為空show_column "UPPER(:show_column)" 大寫列（調用UPPER大寫函數）hide_column FILLERFILLER 隱藏列creation_date DATE 'YYYY-MM-DD'"CASE WHEN :creation_date is null THENTO_CHAR(sysdate,'YYYY-MM-DD') ELSE :creation_date END"日期類型，格式為YYYY-MM-DD，為空的時候取系統日期BEGINDATA數據開始*******數據內容，默認每行一條記錄

從日志可以看出7條數據中，4條記錄無法沒導入的原因。

查看cux_sqlldr_test.bad，其中記錄4條錯誤的數據。

（2）在控制文件中不包涵數據.

上傳cux_sqlldr_test.txt,cux_sqlldr_test.ctl至服務器，cux_sqlldr_test.txt由上面PLSQL腳本生成，cux_sqlldr_test.ctl如下

OPTIONS (skip=3,rows=128)load data???? CHARACTERSET ZHS16GBK??infile? '/u02/ebsuat/app/fs2/EBSapps/appl/cux/12.0.0/sqlldr/file/cux_sqlldr_test.txt'?????badfile? '/u02/ebsuat/app/fs2/EBSapps/appl/cux/12.0.0/sqlldr/bad/cux_sqlldr_test.bad'discardfile '/u02/ebsuat/app/fs2/EBSapps/appl/cux/12.0.0/sqlldr/bad/cux_sqlldr_test.disc'?TRUNCATE into table cux_sqlldr_test?WHEN column1 != 'column1_4'Fields terminated by ','Optionally enclosed by '''TRAILING NULLCOLS?(line_num? RECNUM ,seq_num? 'cux_sqlldr_test_s.nextval' ,column1 ,column2 'nvl(:column2,'***')',column3 NULLIF (column3='column3_4'),show_column 'UPPER(:show_column)' ,hide_column? FILLER ,?creation_date? DATE? 'YYYY-MM-DD' 'CASE WHEN :creation_date is null THEN TO_CHAR(sysdate,'YYYY-MM-DD')? ELSE :creation_date END')

運行命令

sqlldr userid=apps/appsjiahuicontrol=/u02/ebsuat/app/fs2/EBSapps/appl/cux/12.0.0/sqlldr/file/cux_sqlldr_test.ctl log=$CUX_TOP/sqlldr/file/cux_sqlldr_test.log

100條數據由于skip = 3 從第4條開始處理變成97條，第四條數據由于WHEN column1 != "column1_4"

被丟棄在bad的cux_sqlldr_test.disc路徑下，沒有錯誤數據。查看表共96條數據，如下圖所示：

column2 "nvl(:column2,'***')", 對于 column2默認為 “***” .

其他

此外，sqlload可以實現同時加載多個文件，同時把數據加載到多個表。

到此這篇關于Oracle的SQLLDR用法簡介的文章就介紹到這了,更多相關Oracle SQLLDR內容請搜索好吧啦網以前的文章或繼續瀏覽下面的相關文章希望大家以后多多支持好吧啦網！