資源描述：
《oracle數(shù)據(jù)庫sqlload常用技巧總結(jié)》由會員上傳分享，免費(fèi)在線閱讀，更多相關(guān)內(nèi)容在應(yīng)用文檔-天天文庫。
1、1、控制文件中注釋用“--”。2、為防止導(dǎo)入出現(xiàn)中文亂碼，在控制文件中加入字符集控制1.LOAD?DATA?2.CHARACTERSET?ZHS16GBK??3、讓某一列成為行號，用RECNUM關(guān)鍵字1.load?data?????2.infile?*?????3.into?table?t?????4.replace?????5.(?seqno?RECNUM?//載入每行的行號?????6.text?Position(1:1024))?????7.BEGINDATA?????8.fsdfasj??4、過濾某一列，用FILLER關(guān)鍵字1.LOAD?DATA???2.TRUNCATE?INTO?
2、TABLE?T1???3.FIELDS?TERMINATED?BY?','???4.(?field1,???5.field2?FILLER,???6.field3???7.)???5、過濾行在INTOTABLEtable_name后加WHEN過濾條件，但功能有限，如果以豎線分隔符的文件，不能實(shí)現(xiàn)字段級的過濾，定長的還好。1.LOAD?DATA???2.INFILE?'mydata.dat'???3.BADFILE?'mydata.bad'???4.DISCARDFILE?'mydata.dis'???5.APPEND???6.INTO?TABLE?my_selective_table???7
3、.WHEN?(01)?<>?'H'?and?(01)?<>?'T'?and?(30:37)?=?'20031217'???8.(???9.region??????????????CONSTANT?'31',???10.service_key?????????POSITION(01:11)???INTEGER?EXTERNAL,???11.call_b_no???????????POSITION(12:29)???CHAR???12.)???6、過濾首行，用OPTIONS(SKIP1)選項(xiàng)，也可以寫在命令行中，如：sqlldr?sms/admin?control=test.ctl?skip=1
4、??7、TRAILINGNULLCOLS的使用，作用是表的字段沒有對應(yīng)的值時(shí)允許為空如：1.LOAD?DATA?????2.INFILE?*?????3.INTO?TABLE?DEPT?????4.REPLACE?????5.FIELDS?TERMINATED?BY?','?????6.TRAILING?NULLCOLS?//?其實(shí)下面的ENTIRE_LINE在BEGINDATA后面的數(shù)據(jù)中是沒有直接對應(yīng)的列的值的如果第一行改為?10,Sales,Virginia,1/5/2000,,?就不用TRAILING?NULLCOLS了?????7.(DEPTNO,?????8.DNAME?"upp
5、er(:dname)",?//?使用函數(shù)?????9.LOC?"upper(:loc)",?????10.LAST_UPDATED?date?'dd/mm/yyyy',?//?日期的一種表達(dá)方式?還有'dd-mon-yyyy'?等?????11.ENTIRE_LINE?":deptno
6、
7、:dname
8、
9、:loc
10、
11、:last_updated"?????12.)?????13.BEGINDATA?????14.10,Sales,Virginia,1/5/2000?????15.20,Accounting,Virginia,21/6/1999?????16.30,Consulting,Virg
12、inia,5/1/2000?????17.40,Finance,Virginia,15/3/2001??8、添加、修改數(shù)據(jù)（1）、??1.LOAD?DATA???2.INFILE?*???3.INTO?TABLE?tmp_test???4.(?rec_no??????????????????????"my_db_sequence.nextval",???5.region??????????????????????CONSTANT?'31',???6.time_loaded?????????????????"to_char(SYSDATE,?'HH24:MI')",???7.data1????
13、????POSITION(1:5)?":data1/100",???8.data2????????POSITION(6:15)?"upper(:data2)",???9.data3????????POSITION(16:22)"to_date(:data3,?'YYMMDD')"???10.)???11.BEGINDATA???12.11111AAAAAAAAAA991201???13.22222BBBBBB
