oracle 使用雜記二

　　下面的是關於sql*loader 的使用的一點總結 有些是來自itpub上的一些網友的總結
　　
　　大部分是oracle專家高級編程上的實例 只是我實踐以後寫的結果
　　
　　sqlldr userid=lgone/tiger control=actl
　　
　　LOAD DATA
　　INFILE tdat　　　 // 要導入的文件
　　// INFILE ttdat　// 導入多個文件
　　// INFILE *　　　　　// 要導入的內容就在control文件裡 下面的BEGINDATA後面就是導入的內容
　　
　　INTO TABLE table_name　　// 指定裝入的表
　　// into table t_name partition (p_)　分區的載入
　　
　　BADFILE c:\badtxt　　 // 指定壞文件地址
　　
　　*************　 以下是種裝入表的方式
　　APPEND　　　　　　　 // 原先的表有數據 就加在後面
　　// INSERT　　　　　　// 裝載空表 如果原先的表有數據 sqlloader會停止　 默認值
　　// REPLACE　　　　　 // 原先的表有數據 原先的數據會全部刪除
　　// TRUNCATE　　　　　// 指定的內容和replace的相同 會用truncate語句刪除現存數據
　　
　　SKIP 　　可以用 SKIP n 關鍵字來指定導入時可以跳過多少行數據
　　
　　*************　指定的TERMINATED可以在表的開頭 也可在表的內部字段部分
　　FIELDS TERMINATED BY OPTIONALLY ENCLOSED BY
　　// 裝載這種數據: lglglglg
　　// 在表中結果: 　　lg　　lg　 lglg
　　// TERMINATED BY X 　　　 // 以十六進制格式 表示的
　　// TERMINATED BY WRITESPACE　 // 裝載這種數據: lg lg
　　
　　TRAILING NULLCOLS　　*************　 表的字段沒有對應的值時允許為空
　　
　　*************　下面是表的字段
　　(
　　col_ col_ col_filler FILLER // FILLER 關鍵字 此列的數值不會被裝載
　　// 如: lglgnot　結果 lg　lg
　　)
　　// 當沒聲明FIELDS TERMINATED BY 時
　　// (
　　//　 col_ [interger external] TERMINATED BY
　　//　 col_ [date ddmonyyy] TERMINATED BY
　　//　 col_ [char]　　　　　　　TERMINATED BY OPTIONALLY ENCLOSED BY lg
　　// )
　　// 當沒聲明FIELDS TERMINATED BY 用位置告訴字段裝載數據
　　// (
　　//　 col_ position(:)
　　//　 col_ position(:)
　　//　 col_ position(*:)　// 這個字段的開始位置在前一字段的結束位置
　　//　 col_ position(:)
　　//　 col_ position(:) char()　// 指定字段的類型
　　// )
　　
　　BEGINDATA　　// 對應開始的 INFILE *　要導入的內容就在control文件裡
　　Sqlwhat
　　lgshow
　　
　　=====================================================================================
　　////////////　注意begindata後的數值前面不能有空格
　　
　　　　　　　　 ***** 普通裝載
　　LOAD DATA
　　INFILE *
　　INTO TABLE DEPT
　　REPLACE
　　FIELDS TERMINATED BY OPTIONALLY ENCLOSED BY
　　(DEPTNO
　　DNAME
　　LOC
　　)
　　BEGINDATA
　　SalesUSA
　　AccountingVirginiaUSA
　　ConsultingVirginia
　　FinanceVirginia
　　FinanceVirginia　　// loc 列將為空
　　FinanceVirginia　　　// loc 列將為空
　　
　　　　　　　　 ***** FIELDS TERMINATED BY WHITESPACE 和　FIELDS TERMINATED BY x 的情況
　　LOAD DATA
　　INFILE *
　　INTO TABLE DEPT
　　REPLACE
　　FIELDS TERMINATED BY WHITESPACE
　　 FIELDS TERMINATED BY x
　　(DEPTNO
　　DNAME
　　LOC
　　)
　　BEGINDATA
　　　Sales　Virginia
　　
　　　　　　　　***** 指定不裝載那一列　 還可用 POSTION(x:y) 來分隔數據
　　LOAD DATA
　　INFILE *
　　INTO TABLE DEPT
　　REPLACE
　　FIELDS TERMINATED BY OPTIONALLY ENCLOSED BY
　　( DEPTNO
　　FILLER_ FILLER　　// 下面的 Something Not To Be Loaded 將不會被裝載
　　DNAME
　　LOC
　　)
　　BEGINDATA
　　Something Not To Be LoadedAccountingVirginiaUSA
　　
　　　　　　　　*****　 position的列子
　　LOAD DATA
　　INFILE *
　　INTO TABLE DEPT
　　REPLACE
　　( DEPTNO　　　position(:)
　　DNAME　　　 position(*:)　// 這個字段的開始位置在前一字段的結束位置
　　LOC　　　　 position(*:)
　　ENTIRE_LINE position(:)
　　)
　　BEGINDATA
　　Accounting　　VirginiaUSA
　　
　　　　　　　　*****　 使用函數　日期的一種表達　TRAILING NULLCOLS的使用
　　LOAD DATA
　　INFILE *
　　INTO TABLE DEPT
　　REPLACE
　　FIELDS TERMINATED BY
　　TRAILING NULLCOLS　 //　其實下面的ENTIRE_LINE在BEGINDATA後面的數據中是沒有直接對應
　　//　的列的值的 如果第一行改為 SalesVirginia// 就不用TRAILING NULLCOLS了
　　(DEPTNO
　　DNAME　　　　upper(:dname)　　// 使用函數
　　LOC　　　　　upper(:loc)
　　LAST_UPDATED date dd/mm/yyyy　// 日期的一種表達方式 還有ddmonyyyy 等
　　ENTIRE_LINE　:deptno||:dname||:loc||:last_updated
　　)
　　BEGINDATA
　　SalesVirginia//
　　AccountingVirginia//
　　ConsultingVirginia//
　　FinanceVirginia//
　　
　　　　　　　　*****　 使用自定義的函數　　　　 // 解決的時間問題
　　使用函數這僅適合於常規導入並不適合 direct導入方式 i可能可以
　　create or replace
　　function my_to_date( p_string in varchar ) return date
　　as
　　type fmtArray is table of varchar();
　　
　　l_fmts　fmtArray := fmtArray( ddmonyyyy ddmonthyyyy
　　dd/mm/yyyy
　　dd/mm/yyyy hh:mi:ss );
　　l_return date;
　　begin
　　for i in unt
　　loop
　　begin
　　l_return := to_date( p_string l_fmts(i) );
　　exception
　　when others then null;
　　end;
　　EXIT when l_return is not null;
　　end loop;
　　
　　if ( l_return is null )
　　then
　　l_return :=
　　new_time( to_date(ddmmyyyy) + /// *
　　p_string GMT EST );
　　end if;
　　
　　return l_return;
　　end;
　　/
　　
　　LOAD DATA
　　INFILE *
　　INTO TABLE DEPT
　　REPLACE
　　FIELDS TERMINATED BY
　　TRAILING NULLCOLS
　　(DEPTNO
　　DNAME　　　　upper(:dname)
　　LOC　　　　　upper(:loc)
　　LAST_UPDATED my_to_date( :last_updated )　　 // 使用自定義的函數
　　)
　　BEGINDATA
　　SalesVirginiaapril
　　AccountingVirginia//
　　ConsultingVirginia// ::
　　FinanceVirginia
　　FinanceVirginiaapr
　　FinanceVirginiaNot a date
　　
　　　　　　　　*****　 合並多行記錄為一行記錄
　　LOAD DATA
　　INFILE *
　　concatenate 　 // 通過關鍵字concatenate 把幾行的記錄看成一行記錄
　　INTO TABLE DEPT
　　replace
　　FIELDS TERMINATED BY
　　(DEPTNO
　　DNAME　　　　upper(:dname)
　　LOC　　　　　upper(:loc)
　　LAST_UPDATED date dd/mm/yyyy
　　)
　　BEGINDATA
　　Sales　　　// 其實這行看成一行　SalesVirginia//
　　Virginia
　　//
　　// 這列子用 continueif list= 也可以
　　告訴sqlldr在每行的末尾找逗號 找到逗號就把下一行附加到上一行
　　
　　LOAD DATA
　　INFILE *
　　continueif this(:) = 　// 找每行的開始是否有連接字符 　有就把下一行連接為一行
　　// 如　 SalesVirginia
　　//　　　//　　　　　　 就是一行　　SalesVirginia//
　　// 其中: 表示從第一行開始 並在第一行結束　　還有continueif next 但continueif list最理想
　　INTO TABLE DEPT
　　replace
　　FIELDS TERMINATED BY
　　(DEPTNO
　　DNAME　　　　upper(:dname)
　　LOC　　　　　upper(:loc)
　　LAST_UPDATED date dd/mm/yyyy
　　)
　　BEGINDATA　　　　　　　　　　　　// 但是好象不能象右面的那樣使用
　　SalesVirginia　　　　　　　　　　　　SalesVirginia
　　//　　　　　　　　　　　　　　　　　 //
　　　　　　　　　　　　　　　　　　　　　 FinanceVirginia//
　　FinanceVirginia//
　　
　　================================ 用別的語言幫助解決的方法
　　txt文件中的每行作為一個記錄插入到數據庫中的一條記錄文件是定長的
　　　　　　
　　　
　　插入數據記錄是
　　
　　
　　可以把換行符作為一個分隔符來處理
　　
　　
　　到去下載一個activeperl MSI
　　安裝 PERL
　　
　　你的文本文件示例testold
　　
　　
　　a b c d e
　　f g
　　
　　我的PERL程序testpl
　　$mycount=;
　　open(FILE_OLDTESTOLD);
　　open(FILE_NEW>TESTNEW);
　　while(<FILE_OLD>)
　　{
　　chomp;
　　if ($mycount% == )
　　{print FILE_NEW $_ ;}
　　else
　　{print FILE_NEW $_\n;}
　　$mycount++;
　　}
　　
　　在命令窗口下執行 perl testpl
　　得到一個新的文本文件testnew內容如下
　　
　　
　　a b c d e f g
　　
　　
　　load data
　　infile testtxt
　　concatenate()
　　into table aa
　　fields terminated by whitespace
　　(FIELDFIELDFIELDFIELDFIELDFIELDFIELD)
　　==============================================================
　　
　　　　　　　 *****　 載入每行的行號
　　
　　load data
　　infile *
　　into table t
　　replace
　　( seqno　RECNUM　　 //載入每行的行號
　　text Position(:))
　　BEGINDATA
　　fsdfasj　　　　　　 //自動分配一行號給載入 表t 的seqno字段　此行為
　　fasdjfasdfl　　　　　　　　　　　　　　　　　　　　　　 //　此行為 　
　　
　　　　　　　 *****　 載入有換行符的數據
　　注意:　 unix 和 windows 不同　\\n　&　/n
　　還可以用 dbms_lob　和 bfile 看一個文件的回車 換行 等其他特殊字符
　　
　　< >　 使用一個非換行符的字符
　　LOAD DATA
　　INFILE *
　　INTO TABLE DEPT
　　REPLACE
　　FIELDS TERMINATED BY
　　TRAILING NULLCOLS
　　(DEPTNO
　　DNAME　　　　upper(:dname)
　　LOC　　　　　upper(:loc)
　　LAST_UPDATED my_to_date( :last_updated )
　　COMMENTS　　 replace(:comments\nchr())　 //　replace 的使用幫助轉換換行符
　　)
　　BEGINDATA
　　SalesVirginiaaprilThis is the Sales\nOffice in Virginia
　　AccountingVirginia//This is the Accounting\nOffice in Virginia
　　ConsultingVirginia// ::This is the Consulting\nOffice in Virginia
　　FinanceVirginiaThis is the Finance\nOffice in Virginia
　　
　　< >　 使用fix屬性
　　LOAD DATA
　　INFILE demodat fix
　　INTO TABLE DEPT
　　REPLACE
　　FIELDS TERMINATED BY
　　TRAILING NULLCOLS
　　(DEPTNO
　　DNAME　　　　upper(:dname)
　　LOC　　　　　upper(:loc)
　　LAST_UPDATED my_to_date( :last_updated )
　　COMMENTS
　　)
　　demodat
　　SalesVirginiaaprilThis is the Sales
　　Office in Virginia
　　AccountingVirginia//This is the Accounting
　　Office in Virginia
　　ConsultingVirginia// ::This is the Consulting
　　Office in Virginia
　　FinanceVirginiaThis is the Finance
　　Office in Virginia
　　
　　//　這樣裝載會把換行符裝入數據庫　　下面的方法就不會 但要求數據的格式不同
　　
　　LOAD DATA
　　INFILE demodat fix
　　INTO TABLE DEPT
　　REPLACE
　　FIELDS TERMINATED BY OPTIONALLY ENCLOSED BY
　　TRAILING NULLCOLS
　　(DEPTNO
　　DNAME　　　　upper(:dname)
　　LOC　　　　　upper(:loc)
　　LAST_UPDATED my_to_date( :last_updated )
　　COMMENTS
　　)
　　demodat
　　SalesVirginiaaprilThis is the Sales
　　Office in Virginia
　　AccountingVirginia//This is the Accounting
　　Office in Virginia
　　ConsultingVirginia// ::This is the Consulting
　　Office in Virginia
　　FinanceVirginiaThis is the Finance
　　Office in Virginia
　　
　　< >　 使用var屬性
　　LOAD DATA
　　INFILE demodat var
　　// 告訴每個記錄的前個字節表示記錄的長度 如第一個記錄的 表示此記錄有 個字節
　　INTO TABLE DEPT
　　REPLACE
　　FIELDS TERMINATED BY
　　TRAILING NULLCOLS
　　(DEPTNO
　　DNAME　　　　upper(:dname)
　　LOC　　　　　upper(:loc)
　　LAST_UPDATED my_to_date( :last_updated )
　　COMMENTS
　　)
　　demodat
　　SalesVirginiaaprilThis is the Sales
　　Office in Virginia
　　AccountingVirginia//This is the Accounting
　　Office in Virginia
　　ConsultingVirginia// ::This is the Consulting
　　Office in Virginia
　　FinanceVirginiaThis is the Finance
　　Office in Virginia
　　
　　< >　 使用str屬性
　　// 最靈活的一中 可定義一個新的行結尾符　win 回車換行 : chr()||chr()
　　
　　此列中記錄是以 a|\r\n 結束的
　　select utl_rawcast_to_raw(|||chr()||chr()) from dual;
　　結果　 CDA
　　
　　LOAD DATA
　　INFILE demodat str XCDA
　　INTO TABLE DEPT
　　REPLACE
　　FIELDS TERMINATED BY
　　TRAILING NULLCOLS
　　(DEPTNO
　　DNAME　　　　upper(:dname)
　　LOC　　　　　upper(:loc)
　　LAST_UPDATED my_to_date( :last_updated )
　　COMMENTS
　　)
　　demodat
　　SalesVirginiaaprilThis is the Sales
　　Office in Virginia|
　　AccountingVirginia//This is the Accounting
　　Office in Virginia|
　　ConsultingVirginia// ::This is the Consulting
　　Office in Virginia|
　　FinanceVirginiaThis is the Finance
　　Office in Virginia|
　　
　　　　　　　 *****　 將數據導入多個表
　　
　　LOAD DATA
　　INFILE *
　　REPLACE
　　INTO TABLE emp WHEN empno !=
　　(
　　empno POSITION(:) INTEGER EXTERNAL
　　ename POSITION(:) CHAR
　　deptno POSITION(:) CHAR
　　mgr POSITION(:) INTEGER EXTERNAL
　　)
　　INTO TABLE proj　WHEN projno !=
　　(
　　projno POSITION(:) INTEGER EXTERNAL
　　empno POSITION(:) INTEGER EXTERNAL
　　)
　　
　　　　　　　 *****　 轉載 RAW 數據 或 轉載長字段
　　
　　options(bindsize=rows=)
　　load data
　　infile mydata fix 　 // * =　　是小於K 的最大因子
　　concatenate
　　preserve blanks
　　into table foo
　　append
　　(id constant bigdata raw())
　　
　　　　　　　 *****　 轉載 LOB 數據
　　
　　用 dbms_lob　 dbms的數據要在服務器上 通過網絡的不行
　　
　　drop table demo;
　　
　　create or replace directory dir　 as c:\temp\;
　　
　　create or replace directory dir as c:\temp\;
　　
　　create table demo
　　( id　　　　int primary key
　　theClob　 clob
　　)
　　/
　　
　　host echo Hello World\! > c:\temp\testtxt
　　
　　declare
　　l_clob　　clob;
　　l_bfile　 bfile;
　　begin
　　insert into demo values ( empty_clob() )
　　returning theclob into l_clob;
　　
　　l_bfile := bfilename( DIR testtxt );　 DIR 要大寫
　　dbms_lobfileopen( l_bfile );
　　
　　dbms_lobloadfromfile( l_clob l_bfile
　　dbms_lobgetlength( l_bfile ) );
　　
　　dbms_lobfileclose( l_bfile );
　　end;
　　/
　　
　　select dbms_lobgetlength(theClob) theClob from demo
　　/
　　
　　
　　用 sqlldr　在同一行的LOB　lob數據在同一個數據文件中
　　
　　LOAD DATA
　　INFILE demodat str XCDA
　　INTO TABLE DEPT
　　REPLACE
　　FIELDS TERMINATED BY OPTIONALLY ENCLOSED BY
　　TRAILING NULLCOLS
　　(DEPTNO
　　DNAME　　　　upper(:dname)
　　LOC　　　　　upper(:loc)
　　LAST_UPDATED my_to_date( :last_updated )
　　COMMENTS　　 char()
　　)
　　
　　SalesVirginiaaprilThis is the Sales
　　Office in Virginia|
　　AccountingVirginia//This is the Accounting
　　Office in Virginia|
　　ConsultingVirginia// ::This is the Consulting
　　Office in Virginia|
　　FinanceVirginiaThis is the Finance
　　Office in Virginia it has embedded commas and is
　　much longer then the other comments field　If you
　　feel the need to add double quoted text in here like
　　this: You will need to double up those quotes! to
　　preserve them in the string　This field keeps going for upto
　　 bytes or until we hit the magic end of record marker
　　the | followed by a end of line it is right here >|
　　
　　
　　用 sqlldr　不在同一行的LOB　就是lob數據在單獨的文件中
　　
　　create table lob_demo
　　( owner　　 varchar()
　　timestamp date
　　filename　varchar()
　　text　　　clob
　　)
　　/
　　
　　LOAD DATA　 ///////////　 window 的
　　INFILE *
　　REPLACE
　　INTO TABLE LOB_DEMO
　　( owner　　　position(:)
　　timestamp　position(:) to_date(:timestamp||mmm/dd/yyyy　hh:miam)
　　filename　 position(:)　　 下面的LOB的filename是從這裡來的
　　text LOBFILE(filename) TERMINATED BY EOF
　　)
　　BEGINDATA
　　//　:p　　　　　　　 BUILTIN\Administrators demolog
　　// 這是 windows 下面的情況 上面的數據是用 dir /q/n 看見的情況　*******
　　
　　///// unix 下的情況
　　用 ls l 得到上面數據的情況
　　控制文件就改下時間的格式
　　
　　
　　lob 到對象列
　　
　　create table image_load( id number name varchar()
　　image ordsysordimage )
　　/
　　
　　desc ordsysordimage
　　
　　desc ordsysordsource
　　
　　LOAD DATA
　　INFILE *
　　INTO TABLE T
　　replace
　　fields terminated by
　　(
　　id
　　name
　　fiel_name filler
　　image column object
　　(
　　source column object
　　(
　　localdatalobfile(file_name) terminated by bof
　　nullif file_name=NONE
　　)
　　)
　　)
　　begindata
　　iconsiconsgif
　　
　　　　　　　 *****　 轉載varrays /嵌套表
　　
　　create type myArrayType
　　as varray() of number()
　　/
　　
　　create table t
　　( x int primary key y myArrayType )
　　/
　　
　　LOAD DATA
　　INFILE *
　　INTO TABLE T
　　replace
　　fields terminated by
　　(
　　x
　　y_cnt　　　　　　　 FILLER
　　y　　　　　　　　　 varray count (y_cnt)
　　(
　　y
　　)
　　)
　　
　　BEGINDATA
　　
　　
　　
　　
　　
　　
　　create or replace type myTableType
　　as table of number()
　　/
　　
　　create table t
　　( x int primary key y myTableType )
　　nested table y store as y_tab
　　/
　　
　　LOAD DATA
　　INFILE *
　　INTO TABLE T
　　replace
　　fields terminated by
　　(
　　x
　　y　　　　　　　　　 nested table count (CONSTANT )
　　(
　　y
　　)
　　)
　　
　　BEGINDATA
　　
　　
　　
　　==============================================================================
　　象這樣的數據 用 nullif 子句
　　
　　janFlipper seemed unusually hungry today
　　janSpread over three meals
　　
　　id position(:) nullif id=blanks　// 這裡可以是blanks 或者別的表達式
　　//　下面是另一個列子 第一行的 在數據庫中將成為 null
　　LOAD DATA
　　INFILE *
　　INTO TABLE T
　　REPLACE
　　(n position(:) integer external nullif n=
　　v position(:)
　　)
　　BEGINDATA
　　　
　　lg
　　
　　
　　如果是英文的日志 格式可能需要修改環境變量 nls_lang or nls_date_format
From:http://tw.wingwit.com/Article/program/Oracle/201311/17716.html