| 订阅 | 在线投稿
分享
 
 
当前位置: 王朝网络 >> oracle >> xiang xi jiang jie Oracle SQL*Loader de shi yong fang fa
 

xiang xi jiang jie Oracle SQL*Loader de shi yong fang fa

2008-08-19 06:50:54 编辑來源:互联网 评论
 
本文为【详细讲解Oracle SQL*Loader的使用方法】的拼音翻译版
  SQL*Loader shi Oracle shu ju ku dao ru wai bu shu ju de yi ge gong ju . ta he DB2 de Load gong ju xiang si , dan you geng duo de xuan ze , ta zhi chi bian hua de jia zai mo shi , ke xuan de jia zai ji duo biao jia zai .
  ru he shi yong SQL*Loader gong ju
  wo men ke yi yong Oracle de sqlldr gong ju lai dao ru shu ju 。 li ru :
  sqlldr scott/tiger control=loader.ctl
  kong zhi wen jian (loader.ctl) jiang jia zai yi ge wai bu shu ju wen jian ( han fen ge fu ). loader.ctl ru xia :
  load data
  infile 'c:\data\mydata.csv'
  into table emp
  fields terminated by "," optionally enclosed by '"'
  ( empno, empname, sal, deptno )
  mydata.csv ru xia :
  10001,"Scott Tiger", 1000, 40
  10002,"Frank Naude", 500, 20
  xia mian shi yi ge zhi ding ji lu chang du de shi li kong zhi wen jian 。"*" dai biao shu ju wen jian yu ci wen jian tong ming , ji zai hou mian shi yong BEGINDATA duan lai biao shi shu ju 。
  load data
  infile *
  replace
  into table departments
  ( dept position (02:05) char(4),
  deptname position (08:27) char(20)
  )
  begindata
  COSC COMPUTER SCIENCE
  ENGL ENGLISH LITERATURE
  MATH MATHEMATICS
  POLY POLITICAL SCIENCE
  Unloader zhe yang de gong ju
  Oracle mei you ti gong jiang shu ju dao chu dao yi ge wen jian de gong ju 。 dan shi , wo men ke yi yong SQL*Plus de select ji format shu ju lai shu chu dao yi ge wen jian :
  set echo off newpage 0 space 0 pagesize 0 feed off head off trimspool on
  spool oradata.txt
  select col1 || ',' || col2 || ',' || col3
  from tab1
  where col2 = 'XYZ';
  spool off
  ling wai , ye ke yi shi yong shi yong UTL_FILE PL/SQL bao chu li :
  rem Remember to update initSID.ora, utl_file_dir='c:\oradata' parameter
  declare
  fp utl_file.file_type;
  begin
  fp := utl_file.fopen('c:\oradata','tab1.txt','w');
  utl_file.putf(fp, '%s, %s\n', 'TextField', 55);
  utl_file.fclose(fp);
  end;
  /
  dang ran ni ye ke yi shi yong di san fang gong ju , ru SQLWays ,TOAD for Quest deng 。
  jia zai ke bian chang du huo zhi ding chang du de ji lu
  ru :
  LOAD DATA
  INFILE *
  INTO TABLE load_delimited_data
  FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
  TRAILING NULLCOLS
  ( data1,
  data2
  )
  BEGINDATA
  11111,AAAAAAAAAA
  22222,"A,B,C,D,"
  xia mian shi dao ru gu ding wei zhi ( gu ding chang du ) shu ju shi li :
  LOAD DATA
  INFILE *
  INTO TABLE load_positional_data
  ( data1 POSITION(1:5),
  data2 POSITION(6:15)
  )
  BEGINDATA
  11111AAAAAAAAAA
  22222BBBBBBBBBB
  tiao guo shu ju xing :
  ke yi yong "SKIP n" guan jian zi lai zhi ding dao ru shi ke yi tiao guo duo shao xing shu ju 。 ru :
  LOAD DATA
  INFILE *
  INTO TABLE load_positional_data
  SKIP 5
  ( data1 POSITION(1:5),
  data2 POSITION(6:15)
  )
  BEGINDATA
  11111AAAAAAAAAA
  22222BBBBBBBBBB
  dao ru shu ju shi xiu gai shu ju :
  zai dao ru shu ju dao shu ju ku shi , ke yi xiu gai shu ju 。 zhu yi , zhe jin shi he yu chang gui dao ru , bing bu shi he direct dao ru fang shi . ru :
  LOAD DATA
  INFILE *
  INTO TABLE modified_data
  ( rec_no "my_db_sequence.nextval",
  region CONSTANT '31',
  time_loaded "to_char(SYSDATE, 'HH24:MI')",
  data1 POSITION(1:5) ":data1/100",
  data2 POSITION(6:15) "upper(:data2)",
  data3 POSITION(16:22)"to_date(:data3, 'YYMMDD')"
  )
  BEGINDATA
  11111AAAAAAAAAA991201
  22222BBBBBBBBBB990112
  LOAD DATA
  INFILE 'mail_orders.txt'
  BADFILE 'bad_orders.txt'
  APPEND
  INTO TABLE mailing_list
  FIELDS TERMINATED BY ","
  ( addr,
  city,
  state,
  zipcode,
  mailing_addr "decode(:mailing_addr, null, :addr, :mailing_addr)",
  mailing_city "decode(:mailing_city, null, :city, :mailing_city)",
  mailing_state
  )
  jiang shu ju dao ru duo ge biao :
  ru :
  LOAD DATA
  INFILE *
  REPLACE
  INTO TABLE emp
  WHEN empno != ' '
  ( empno POSITION(1:4) INTEGER EXTERNAL,
  ename POSITION(6:15) CHAR,
  deptno POSITION(17:18) CHAR,
  mgr POSITION(20:23) INTEGER EXTERNAL
  )
  INTO TABLE proj
  WHEN projno != ' '
  ( projno POSITION(25:27) INTEGER EXTERNAL,
  empno POSITION(1:4) INTEGER EXTERNAL
  )
  dao ru xuan ding de ji lu :
  ru xia li : (01) dai biao di yi ge zi fu , (30:37) dai biao 30 dao 37 zhi jian de zi fu :
  LOAD DATA
  INFILE 'mydata.dat' BADFILE 'mydata.bad' DISCARDFILE 'mydata.dis'
  APPEND
  INTO TABLE my_selective_table
  WHEN (01) <> 'H' and (01) <> 'T' and (30:37) = '19991217'
  (
  region CONSTANT '31',
  service_key POSITION(01:11) INTEGER EXTERNAL,
  call_b_no POSITION(12:29) CHAR
  )
  dao ru shi tiao guo mou xie zi duan :
  ke yong POSTION(x:y) lai fen ge shu ju . zai Oracle8i zhong ke yi tong guo zhi ding FILLER zi duan shi xian 。FILLER zi duan yong lai tiao guo 、 hu lue dao ru shu ju wen jian zhong de zi duan . ru :
  LOAD DATA
  TRUNCATE INTO TABLE T1
  FIELDS TERMINATED BY ','
  ( field1,
  field2 FILLER,
  field3
  )
  dao ru duo xing ji lu :
  ke yi shi yong xia mian liang ge xuan xiang zhi yi lai shi xian jiang duo xing shu ju dao ru wei yi ge ji lu :
  CONCATENATE: - use when SQL*Loader should combine the same number of physical records together to form one logical record.
  CONTINUEIF - use if a condition indicates that multiple records should be treated as one. Eg. by having a '#' character in column 1.
  SQL*Loader shu ju de ti jiao :
  yi ban qing kuang xia shi zai dao ru shu ju wen jian shu ju hou ti jiao de 。
  ye ke yi tong guo zhi ding ROWS= shen shu lai zhi ding mei ci ti jiao ji lu shu 。
  ti gao SQL*Loader de xing neng :
  1) yi ge jian dan er rong yi hu lue de wen ti shi , mei you dui dao ru de biao shi yong ren he suo yin he / huo yue shu ( zhu jian )。 ru guo zhe yang zuo , shen zhi zai shi yong ROWS= shen shu shi , hui hen ming xian jiang di shu ju ku dao ru xing neng 。
  2) ke yi tian jia DIRECT=TRUE lai ti gao dao ru shu ju de xing neng 。 dang ran , zai hen duo qing kuang xia , bu neng shi yong ci shen shu 。
  3) tong guo zhi ding UNRECOVERABLE xuan xiang , ke yi guan bi shu ju ku de ri zhi 。 zhe ge xuan xiang zhi neng he direct yi qi shi yong 。
  4) ke yi tong shi yun xing duo ge dao ru ren wu .
  chang gui dao ru yu direct dao ru fang shi de qu bie :
  chang gui dao ru ke yi tong guo shi yong INSERT yu ju lai dao ru shu ju 。Direct dao ru ke yi tiao guo shu ju ku de xiang guan luo ji (DIRECT=TRUE), er zhi jie jiang shu ju dao ru dao shu ju wen jian zhong 。
  dao ru shu ju shi xiu gai shu ju :
  zai dao ru shu ju dao shu ju ku shi , ke yi xiu gai shu ju 。 zhu yi , zhe jin shi he yu chang gui dao ru , bing bu shi he direct dao ru fang shi . ru :
  LOAD DATA
  INFILE *
  INTO TABLE modified_data
  ( rec_no "my_db_sequence.nextval",
  region CONSTANT '31',
  time_loaded "to_char(SYSDATE, 'HH24:MI')",
  data1 POSITION(1:5) ":data1/100",
  data2 POSITION(6:15) "upper(:data2)",
  data3 POSITION(16:22)"to_date(:data3, 'YYMMDD')"
  )
  BEGINDATA
  11111AAAAAAAAAA991201
  22222BBBBBBBBBB990112
  LOAD DATA
  INFILE 'mail_orders.txt'
  BADFILE 'bad_orders.txt'
  APPEND
  INTO TABLE mailing_list
  FIELDS TERMINATED BY ","
  ( addr,
  city,
  state,
  zipcode,
  mailing_addr "decode(:mailing_addr, null, :addr, :mailing_addr)",
  mailing_city "decode(:mailing_city, null, :city, :mailing_city)",
  mailing_state
  )
  jiang shu ju dao ru duo ge biao :
  ru :
  LOAD DATA
  INFILE *
  REPLACE
  INTO TABLE emp
  WHEN empno != ' '
  ( empno POSITION(1:4) INTEGER EXTERNAL,
  ename POSITION(6:15) CHAR,
  deptno POSITION(17:18) CHAR,
  mgr POSITION(20:23) INTEGER EXTERNAL
  )
  INTO TABLE proj
  WHEN projno != ' '
  ( projno POSITION(25:27) INTEGER EXTERNAL,
  empno POSITION(1:4) INTEGER EXTERNAL
  )
  dao ru xuan ding de ji lu :
  ru xia li : (01) dai biao di yi ge zi fu , (30:37) dai biao 30 dao 37 zhi jian de zi fu :
  LOAD DATA
  INFILE 'mydata.dat' BADFILE 'mydata.bad' DISCARDFILE 'mydata.dis'
  APPEND
  INTO TABLE my_selective_table
  WHEN (01) <> 'H' and (01) <> 'T' and (30:37) = '19991217'
  (
  region CONSTANT '31',
  service_key POSITION(01:11) INTEGER EXTERNAL,
  call_b_no POSITION(12:29) CHAR
  )
  dao ru shi tiao guo mou xie zi duan :
  ke yong POSTION(x:y) lai fen ge shu ju . zai Oracle8i zhong ke yi tong guo zhi ding FILLER zi duan shi xian 。FILLER zi duan yong lai tiao guo 、 hu lue dao ru shu ju wen jian zhong de zi duan . ru :
  LOAD DATA
  TRUNCATE INTO TABLE T1
  FIELDS TERMINATED BY ','
  ( field1,
  field2 FILLER,
  field3
  )
  dao ru duo xing ji lu :
  ke yi shi yong xia mian liang ge xuan xiang zhi yi lai shi xian jiang duo xing shu ju dao ru wei yi ge ji lu :
  CONCATENATE: - use when SQL*Loader should combine the same number of physical records together to form one logical record.
  CONTINUEIF - use if a condition indicates that multiple records should be treated as one. Eg. by having a '#' character in column 1.
  SQL*Loader shu ju de ti jiao :
  yi ban qing kuang xia shi zai dao ru shu ju wen jian shu ju hou ti jiao de 。
  ye ke yi tong guo zhi ding ROWS= shen shu lai zhi ding mei ci ti jiao ji lu shu 。
  ti gao SQL*Loader de xing neng :
  1) yi ge jian dan er rong yi hu lue de wen ti shi , mei you dui dao ru de biao shi yong ren he suo yin he / huo yue shu ( zhu jian )。 ru guo zhe yang zuo , shen zhi zai shi yong ROWS= shen shu shi , hui hen ming xian jiang di shu ju ku dao ru xing neng 。
  2) ke yi tian jia DIRECT=TRUE lai ti gao dao ru shu ju de xing neng 。 dang ran , zai hen duo qing kuang xia , bu neng shi yong ci shen shu 。
  3) tong guo zhi ding UNRECOVERABLE xuan xiang , ke yi guan bi shu ju ku de ri zhi 。 zhe ge xuan xiang zhi neng he direct yi qi shi yong 。
  4) ke yi tong shi yun xing duo ge dao ru ren wu .
  chang gui dao ru yu direct dao ru fang shi de qu bie :
  chang gui dao ru ke yi tong guo shi yong INSERT yu ju lai dao ru shu ju 。Direct dao ru ke yi tiao guo shu ju ku de xiang guan luo ji (DIRECT=TRUE), er zhi jie jiang shu ju dao ru dao shu ju wen jian zhong 。
  sqlldr shi yong li zi shuo ming
  xian ba Excel ling cun wei .csv ge shi wen jian , ru test.csv, zai bian xie yi ge insert.ctl
  yong sqlldr jin xing dao ru !
  insert.ctl nei rong ru xia :
  load data --1、 kong zhi wen jian biao shi
  infile 'test.csv' --2、 yao shu ru de shu ju wen jian ming wei test.csv
  append into table table_name --3、 xiang biao table_name zhong zhui jia ji lu
  fields terminated by ',' --4、 zi duan zhong zhi yu ',', shi yi ge dou hao
  (field1,
  field2,
  field3,
  ...
  fieldn)----- ding yi lie dui ying shun xu
  zhu yi kuo hao zhong field pai lie shun xu yao yu csv wen jian zhong xiang dui ying
  ran hou jiu ke yi zhi xing ru xia ming ling :
  sqlldr user/password control=insert.ctl【原文】【汉音对照
 
 
 
 
 
 
 
 
日版宠物情人插曲《Winding Road》歌词

日版宠物情人2017的插曲,很带节奏感,日语的,女生唱的。 最后听见是在第8集的时候女主手割伤了,然后男主用嘴帮她吸了一下,插曲就出来了。 歌手:Def...

兄弟共妻,我成了他们夜里的美食

老钟家的两个儿子很特别,就是跟其他的人不太一样,魔一般的执着。兄弟俩都到了要结婚的年龄了,不管自家老爹怎么磨破嘴皮子,兄弟俩说不娶就不娶,老父母为兄弟两操碎了心...

如何磨出破洞牛仔裤?牛仔裤怎么剪破洞?

把牛仔裤磨出有线的破洞 1、具体工具就是磨脚石,下面垫一个硬物,然后用磨脚石一直磨一直磨,到把那块磨薄了,用手撕开就好了。出来的洞啊很自然的。需要猫须的话调几...

我就是扫描下图得到了敬业福和爱国福

先来看下敬业福和爱国福 今年春节,支付宝再次推出了“五福红包”活动,表示要“把欠大家的敬业福都还给大家”。 今天该活动正式启动,和去年一样,需要收集“五福”...

冰箱异味产生的原因和臭味去除的方法

有时候我们打开冰箱就会闻到一股异味,冰箱里的这种异味是因为一些物质发出的气味的混合体,闻起来让人恶心。 产生这些异味的主要原因有以下几点。 1、很多人有这种习...

 
 
SQL*Loader shi Oracle shu ju ku dao ru wai bu shu ju de yi ge gong ju . ta he DB2 de Load gong ju xiang si , dan you geng duo de xuan ze , ta zhi chi bian hua de jia zai mo shi , ke xuan de jia zai ji duo biao jia zai . ru he shi yong SQL*Loader gong ju wo men ke yi yong Oracle de sqlldr gong ju lai dao ru shu ju 。 li ru : sqlldr scott/tiger control=loader.ctl kong zhi wen jian (loader.ctl) jiang jia zai yi ge wai bu shu ju wen jian ( han fen ge fu ). loader.ctl ru xia : load data infile 'c:\data\mydata.csv' into table emp fields terminated by "," optionally enclosed by '"' ( empno, empname, sal, deptno ) mydata.csv ru xia : 10001,"Scott Tiger", 1000, 40 10002,"Frank Naude", 500, 20 xia mian shi yi ge zhi ding ji lu chang du de shi li kong zhi wen jian 。"*" dai biao shu ju wen jian yu ci wen jian tong ming , ji zai hou mian shi yong BEGINDATA duan lai biao shi shu ju 。 load data infile * replace into table departments ( dept position (02:05) char(4), deptname position (08:27) char(20) ) begindata COSC COMPUTER SCIENCE ENGL ENGLISH LITERATURE MATH MATHEMATICS POLY POLITICAL SCIENCE Unloader zhe yang de gong ju Oracle mei you ti gong jiang shu ju dao chu dao yi ge wen jian de gong ju 。 dan shi , wo men ke yi yong SQL*Plus de select ji format shu ju lai shu chu dao yi ge wen jian : set echo off newpage 0 space 0 pagesize 0 feed off head off trimspool on spool oradata.txt select col1 || ',' || col2 || ',' || col3 from tab1 where col2 = 'XYZ'; spool off ling wai , ye ke yi shi yong shi yong UTL_FILE PL/SQL bao chu li : rem Remember to update initSID.ora, utl_file_dir='c:\oradata' parameter declare fp utl_file.file_type; begin fp := utl_file.fopen('c:\oradata','tab1.txt','w'); utl_file.putf(fp, '%s, %s\n', 'TextField', 55); utl_file.fclose(fp); end; / dang ran ni ye ke yi shi yong di san fang gong ju , ru SQLWays ,TOAD for Quest deng 。 jia zai ke bian chang du huo zhi ding chang du de ji lu ru : LOAD DATA INFILE * INTO TABLE load_delimited_data FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' TRAILING NULLCOLS ( data1, data2 ) BEGINDATA 11111,AAAAAAAAAA 22222,"A,B,C,D," xia mian shi dao ru gu ding wei zhi ( gu ding chang du ) shu ju shi li : LOAD DATA INFILE * INTO TABLE load_positional_data ( data1 POSITION(1:5), data2 POSITION(6:15) ) BEGINDATA 11111AAAAAAAAAA 22222BBBBBBBBBB tiao guo shu ju xing : ke yi yong "SKIP n" guan jian zi lai zhi ding dao ru shi ke yi tiao guo duo shao xing shu ju 。 ru : LOAD DATA INFILE * INTO TABLE load_positional_data SKIP 5 ( data1 POSITION(1:5), data2 POSITION(6:15) ) BEGINDATA 11111AAAAAAAAAA 22222BBBBBBBBBB dao ru shu ju shi xiu gai shu ju : zai dao ru shu ju dao shu ju ku shi , ke yi xiu gai shu ju 。 zhu yi , zhe jin shi he yu chang gui dao ru , bing bu shi he direct dao ru fang shi . ru : LOAD DATA INFILE * INTO TABLE modified_data ( rec_no "my_db_sequence.nextval", region CONSTANT '31', time_loaded "to_char(SYSDATE, 'HH24:MI')", data1 POSITION(1:5) ":data1/100", data2 POSITION(6:15) "upper(:data2)", data3 POSITION(16:22)"to_date(:data3, 'YYMMDD')" ) BEGINDATA 11111AAAAAAAAAA991201 22222BBBBBBBBBB990112 LOAD DATA INFILE 'mail_orders.txt' BADFILE 'bad_orders.txt' APPEND INTO TABLE mailing_list FIELDS TERMINATED BY "," ( addr, city, state, zipcode, mailing_addr "decode(:mailing_addr, null, :addr, :mailing_addr)", mailing_city "decode(:mailing_city, null, :city, :mailing_city)", mailing_state ) jiang shu ju dao ru duo ge biao : ru : LOAD DATA INFILE * REPLACE INTO TABLE emp WHEN empno != ' ' ( empno POSITION(1:4) INTEGER EXTERNAL, ename POSITION(6:15) CHAR, deptno POSITION(17:18) CHAR, mgr POSITION(20:23) INTEGER EXTERNAL ) INTO TABLE proj WHEN projno != ' ' ( projno POSITION(25:27) INTEGER EXTERNAL, empno POSITION(1:4) INTEGER EXTERNAL ) dao ru xuan ding de ji lu : ru xia li : (01) dai biao di yi ge zi fu , (30:37) dai biao 30 dao 37 zhi jian de zi fu : LOAD DATA INFILE 'mydata.dat' BADFILE 'mydata.bad' DISCARDFILE 'mydata.dis' APPEND INTO TABLE my_selective_table WHEN (01) <> 'H' and (01) <> 'T' and (30:37) = '19991217' ( region CONSTANT '31', service_key POSITION(01:11) INTEGER EXTERNAL, call_b_no POSITION(12:29) CHAR ) dao ru shi tiao guo mou xie zi duan : ke yong POSTION(x:y) lai fen ge shu ju . zai Oracle8i zhong ke yi tong guo zhi ding FILLER zi duan shi xian 。FILLER zi duan yong lai tiao guo 、 hu lue dao ru shu ju wen jian zhong de zi duan . ru : LOAD DATA TRUNCATE INTO TABLE T1 FIELDS TERMINATED BY ',' ( field1, field2 FILLER, field3 ) dao ru duo xing ji lu : ke yi shi yong xia mian liang ge xuan xiang zhi yi lai shi xian jiang duo xing shu ju dao ru wei yi ge ji lu : CONCATENATE: - use when SQL*Loader should combine the same number of physical records together to form one logical record. CONTINUEIF - use if a condition indicates that multiple records should be treated as one. Eg. by having a '#' character in column 1. SQL*Loader shu ju de ti jiao : yi ban qing kuang xia shi zai dao ru shu ju wen jian shu ju hou ti jiao de 。 ye ke yi tong guo zhi ding ROWS= shen shu lai zhi ding mei ci ti jiao ji lu shu 。 ti gao SQL*Loader de xing neng : 1) yi ge jian dan er rong yi hu lue de wen ti shi , mei you dui dao ru de biao shi yong ren he suo yin he / huo yue shu ( zhu jian )。 ru guo zhe yang zuo , shen zhi zai shi yong ROWS= shen shu shi , hui hen ming xian jiang di shu ju ku dao ru xing neng 。 2) ke yi tian jia DIRECT=TRUE lai ti gao dao ru shu ju de xing neng 。 dang ran , zai hen duo qing kuang xia , bu neng shi yong ci shen shu 。 3) tong guo zhi ding UNRECOVERABLE xuan xiang , ke yi guan bi shu ju ku de ri zhi 。 zhe ge xuan xiang zhi neng he direct yi qi shi yong 。 4) ke yi tong shi yun xing duo ge dao ru ren wu . chang gui dao ru yu direct dao ru fang shi de qu bie : chang gui dao ru ke yi tong guo shi yong INSERT yu ju lai dao ru shu ju 。Direct dao ru ke yi tiao guo shu ju ku de xiang guan luo ji (DIRECT=TRUE), er zhi jie jiang shu ju dao ru dao shu ju wen jian zhong 。 dao ru shu ju shi xiu gai shu ju : zai dao ru shu ju dao shu ju ku shi , ke yi xiu gai shu ju 。 zhu yi , zhe jin shi he yu chang gui dao ru , bing bu shi he direct dao ru fang shi . ru : LOAD DATA INFILE * INTO TABLE modified_data ( rec_no "my_db_sequence.nextval", region CONSTANT '31', time_loaded "to_char(SYSDATE, 'HH24:MI')", data1 POSITION(1:5) ":data1/100", data2 POSITION(6:15) "upper(:data2)", data3 POSITION(16:22)"to_date(:data3, 'YYMMDD')" ) BEGINDATA 11111AAAAAAAAAA991201 22222BBBBBBBBBB990112 LOAD DATA INFILE 'mail_orders.txt' BADFILE 'bad_orders.txt' APPEND INTO TABLE mailing_list FIELDS TERMINATED BY "," ( addr, city, state, zipcode, mailing_addr "decode(:mailing_addr, null, :addr, :mailing_addr)", mailing_city "decode(:mailing_city, null, :city, :mailing_city)", mailing_state ) jiang shu ju dao ru duo ge biao : ru : LOAD DATA INFILE * REPLACE INTO TABLE emp WHEN empno != ' ' ( empno POSITION(1:4) INTEGER EXTERNAL, ename POSITION(6:15) CHAR, deptno POSITION(17:18) CHAR, mgr POSITION(20:23) INTEGER EXTERNAL ) INTO TABLE proj WHEN projno != ' ' ( projno POSITION(25:27) INTEGER EXTERNAL, empno POSITION(1:4) INTEGER EXTERNAL ) dao ru xuan ding de ji lu : ru xia li : (01) dai biao di yi ge zi fu , (30:37) dai biao 30 dao 37 zhi jian de zi fu : LOAD DATA INFILE 'mydata.dat' BADFILE 'mydata.bad' DISCARDFILE 'mydata.dis' APPEND INTO TABLE my_selective_table WHEN (01) <> 'H' and (01) <> 'T' and (30:37) = '19991217' ( region CONSTANT '31', service_key POSITION(01:11) INTEGER EXTERNAL, call_b_no POSITION(12:29) CHAR ) dao ru shi tiao guo mou xie zi duan : ke yong POSTION(x:y) lai fen ge shu ju . zai Oracle8i zhong ke yi tong guo zhi ding FILLER  zi duan shi xian 。FILLER zi duan yong lai tiao guo 、 hu lue dao ru shu ju wen jian zhong de zi duan . ru : LOAD DATA TRUNCATE INTO TABLE T1 FIELDS TERMINATED BY ',' ( field1, field2 FILLER, field3 ) dao ru duo xing ji lu : ke yi shi yong xia mian liang ge xuan xiang zhi yi lai shi xian jiang duo xing shu ju dao ru wei yi ge ji lu : CONCATENATE: - use when SQL*Loader should combine the same number of physical records together to form one logical record. CONTINUEIF - use if a condition indicates that multiple records should be treated as one. Eg. by having a '#' character in column 1. SQL*Loader shu ju de ti jiao : yi ban qing kuang xia shi zai dao ru shu ju wen jian shu ju hou ti jiao de 。 ye ke yi tong guo zhi ding ROWS= shen shu lai zhi ding mei ci ti jiao ji lu shu 。 ti gao SQL*Loader  de xing neng : 1) yi ge jian dan er rong yi hu lue de wen ti shi , mei you dui dao ru de biao shi yong ren he suo yin he / huo yue shu ( zhu jian )。 ru guo zhe yang zuo , shen zhi zai shi yong ROWS= shen shu shi , hui hen ming xian jiang di shu ju ku dao ru xing neng 。 2) ke yi tian jia DIRECT=TRUE lai ti gao dao ru shu ju de xing neng 。 dang ran , zai hen duo qing kuang xia , bu neng shi yong ci shen shu 。 3) tong guo zhi ding  UNRECOVERABLE xuan xiang , ke yi guan bi shu ju ku de ri zhi 。 zhe ge xuan xiang zhi neng he direct yi qi shi yong 。 4) ke yi tong shi yun xing duo ge dao ru ren wu . chang gui dao ru yu direct dao ru fang shi de qu bie : chang gui dao ru ke yi tong guo shi yong INSERT yu ju lai dao ru shu ju 。Direct dao ru ke yi tiao guo shu ju ku de xiang guan luo ji (DIRECT=TRUE), er zhi jie jiang shu ju dao ru dao shu ju wen jian zhong 。 sqlldr shi yong li zi shuo ming xian ba Excel ling cun wei .csv ge shi wen jian , ru test.csv, zai bian xie yi ge insert.ctl yong sqlldr jin xing dao ru ! insert.ctl nei rong ru xia : load data           --1、 kong zhi wen jian biao shi infile 'test.csv'       --2、 yao shu ru de shu ju wen jian ming wei test.csv append into table table_name     --3、 xiang biao table_name zhong zhui jia ji lu fields terminated by ','   --4、 zi duan zhong zhi yu ',', shi yi ge dou hao (field1, field2, field3, ... fieldn)----- ding yi lie dui ying shun xu zhu yi kuo hao zhong field pai lie shun xu yao yu csv wen jian zhong xiang dui ying ran hou jiu ke yi zhi xing ru xia ming ling : sqlldr user/password control=insert.ctl
󰈣󰈤
  免责声明:本文仅代表作者个人观点,与王朝网络无关。王朝网络登载此文出于传递更多信息之目的,并不意味着赞同其观点或证实其描述,其原创性以及文中陈述文字和内容未经本站证实,对本文以及其中全部或者部分内容、文字的真实性、完整性、及时性本站不作任何保证或承诺,请读者仅作参考,并请自行核实相关内容。
 
 
温柔美女韩雪写真
杨幂全新写真
网易红人PLMM草梅队长
河智苑Compagna09春装
看 这就是我的学校
惠山古迹区
雪后看花仙
元宵烟火
 
>>返回首页<<
 为你推荐
 
 
 转载本文
 UBB代码 HTML代码
复制到剪贴板...
 
 热帖排行
 
 
 
 
©2005- 王朝网络 版权所有