oracle 使用杂记2

下面的是关于sql*loader 的使用的一点总结 有些是来自itpub上的一些网友的总结大部分是Oracle专家高级编程上的实例 只是我实践以后写的结果 ;;;;sqlldr userid=lgone/tiger control=a.ctlLOAD DATA INFILE 't.dat';;;;// 要导入的文件 // INFILE 'tt.dat'// 导入多个文件 // INFILE *; // 要导入的内容就在control文件里 下面的BEGINDATA后面就是导入的内容 INTO TABLE table_name;// 指定装入的表 // into table t_name partition (p_1); 分区的载入 BADFILE 'c:bad.txt';;// 指定坏文件地址 *************以下是4种装入表的方式 APPEND;;;;// 原先的表有数据 就加在后面 // INSERT;// 装载空表 假如原先的表有数据 sqlloader会停止默认值 // REPLACE// 原先的表有数据 原先的数据会全部删除 // TRUNCATE; // 指定的内容和replace的相同 会用truncate语句删除现存数据 SKIP 5;可以用 'SKIP n' 要害字来指定导入时可以跳过多少行数据 *************; 指定的TERMINATED可以在表的开头 也可在表的内部字段部分 FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY ''' // 装载这种数据: 10,lg,'''lg''','lg,lg'; // 在表中结果: 10;lg;'lg'lg,lg // TERMINATED BY X '09';;;;// 以十六进制格式 '09' 表示的 // TERMINATED BY WRITESPACE// 装载这种数据: 10 lg lg; TRAILING NULLCOLS;*************表的字段没有对应的值时答应为空 *************; 下面是表的字段 ( col_1 , col_2 ,col_filler FILLER // FILLER 要害字 此列的数值不会被装载 // 如: lg,lg,not; 结果 lg; lg ) // 当没声明FIELDS TERMINATED BY ',' 时 // ( //col_1 [interger external] TERMINATED BY ',' , //col_2 [date 'dd-mon-yyy'] TERMINATED BY ',' , //col_3 [char];;;TERMINATED BY ',' OPTIONALLY ENCLOSED BY 'lg' // ) // 当没声明FIELDS TERMINATED BY ','用位置告诉字段装载数据 // ( //col_1 position(1:2), //col_2 position(3:10), //col_3 position(*:16),; // 这个字段的开始位置在前一字段的结束位置 //col_4 position(1:16), //col_5 position(3:10) char(8); // 指定字段的类型 // ) BEGINDATA;// 对应开始的 INFILE *; 要导入的内容就在control文件里 10,Sql,what 20,lg,show===================================================================================== ////////////; 注重begindata后的数值前面不能有空格1;;***** 普通装载 LOAD DATA; INFILE * INTO TABLE DEPT; REPLACE; FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY ''' (DEPTNO, DNAME, LOC ); BEGINDATA 10,Sales,'''USA''' 20,Accounting,'Virginia,USA' 30,Consulting,Virginia 40,Finance,Virginia 50,'Finance','',Virginia;// loc 列将为空 60,'Finance',,Virginia;;;// loc 列将为空2;;***** FIELDS TERMINATED BY WHITESPACE 和; FIELDS TERMINATED BY x'09' 的情况 LOAD DATA; INFILE * INTO TABLE DEPT; REPLACE; FIELDS TERMINATED BY WHITESPACE -- FIELDS TERMINATED BY x'09' (DEPTNO, DNAME, LOC ); BEGINDATA 10;;Sales;;Virginia3;***** 指定不装载那一列还可用 POSTION(x:y) 来分隔数据 LOAD DATA INFILE * INTO TABLE DEPT REPLACE FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY ''' ( DEPTNO, FILLER_1 FILLER,;// 下面的 'Something Not To Be Loaded' 将不会被装载 DNAME, LOC ) BEGINDATA 20,Something Not To Be Loaded,Accounting,'Virginia,USA'4;*****position的列子 LOAD DATA INFILE * INTO TABLE DEPT REPLACE ( DEPTNO;;;position(1:2), DNAME;;;;position(*:16),; // 这个字段的开始位置在前一字段的结束位置 LOC position(*:29),; ENTIRE_LINE position(1:29) ) BEGINDATA 10Accounting;Virginia,USA5;*****使用函数; 日期的一种表达; TRAILING NULLCOLS的使用 LOAD DATA INFILE * INTO TABLE DEPT REPLACE FIELDS TERMINATED BY ',' TRAILING NULLCOLS//; 其实下面的ENTIRE_LINE在BEGINDATA后面的数据中是没有直接对应//; 的列的值的 假如第一行改为 10,Sales,Virginia,1/5/2000,, 就不用TRAILING NULLCOLS了 (DEPTNO, DNAME;;;;;'upper(:dname)',;// 使用函数 LOC; 'upper(:loc)', LAST_UPDATED date 'dd/mm/yyyy',; // 日期的一种表达方式 还有'dd-mon-yyyy' 等 ENTIRE_LINE; ':deptno:dname:loc:last_updated' ) BEGINDATA 10,Sales,Virginia,1/5/2000 20,Accounting,Virginia,21/6/1999 30,Consulting,Virginia,5/1/2000 40,Finance,Virginia,15/3/20016;*****使用自定义的函数 // 解决的时间问题 使用函数这仅适合于常规导入，并不适合 direct导入方式 9i可能可以 create or replace function my_to_date( p_string in varchar2 ) return date as type fmtArray is table of varchar2(25); l_fmts; fmtArray := fmtArray( 'dd-mon-yyyy', 'dd-month-yyyy', 'dd/mm/yyyy', 'dd/mm/yyyy hh24:mi:ss' ); l_return date; begin for i in 1 .. l_fmts.count 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('01011970','ddmmyyyy') + 1/24/60/60 *; 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 10,Sales,Virginia,01-april-2001 20,Accounting,Virginia,13/04/2001 30,Consulting,Virginia,14/04/2001 12:02:02 40,Finance,Virginia,987268297 50,Finance,Virginia,02-apr-2001 60,Finance,Virginia,Not a date7;*****合并多行记录为一行记录 LOAD DATA INFILE * concatenate 3// 通过要害字concatenate 把几行的记录看成一行记录 INTO TABLE DEPT replace FIELDS TERMINATED BY ',' (DEPTNO, DNAME;;;;;'upper(:dname)', LOC; 'upper(:loc)', LAST_UPDATED date 'dd/mm/yyyy' ) BEGINDATA 10,Sales,;;;// 其实这3行看成一行; 10,Sales,Virginia,1/5/2000 Virginia, 1/5/2000// 这列子用 continueif list=',' 也可以 告诉sqlldr在每行的末尾找逗号 找到逗号就把下一行附加到上一行 LOAD DATA INFILE * continueif this(1:1) = '-'// 找每行的开始是否有连接字符 -; 有就把下一行连接为一行// 如-10,Sales,Virginia,//;;;1/5/2000;;就是一行;10,Sales,Virginia,1/5/2000// 其中1:1 表示从第一行开始 并在第一行结束;还有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;;;;;// 但是好象不能象右面的那样使用 -10,Sales,Virginia,;;;;;-10,Sales,Virginia, 1/5/20001/5/2000 -40,;;;;40,Finance,Virginia,13/04/2001 Finance,Virginia,13/04/2001 ================================ 用别的语言帮助解决的方法 txt文件中的每2行作为一个记录插入到数据库中的一条记录,文件是定长的 1; 2; 3; 4; 5 6; 7 插入数据记录是 1 2 3 4 5 6 7 ------------- 可以把换行符作为一个分隔符来处理 ------------- 1、到www.activeperl.com去下载一个activeperl5.6 MSI 2、安装 PERL 3、你的文本文件示例：test.old 1 2 3 4 5 6 7 a b c d e f g 4、我的PERL程序：test.pl $mycount=2; open(FILE_OLD','TEST.OLD'); open(FILE_NEW','>TEST.NEW'); while(<FILE_OLD>) { chomp; if ($mycount%2 == 0) {print FILE_NEW $_.' ';} else {print FILE_NEW $_.'n';} $mycount++; } 5、在命令窗口下执行 perl test.pl 6、得到一个新的文本文件：test.new,内容如下： 1 2 3 4 5 6 7 a b c d e f g --------------- load data infile 'test.txt' concatenate(2) into table aa fields terminated by whitespace (FIELD1,FIELD2,FIELD3,FIELD4,FIELD5,FIELD6,FIELD7) ==============================================================8*****载入每行的行号 load data infile * into table t replace ( seqno; RECNUM;;//载入每行的行号 text Position(1:1024)) BEGINDATA fsdfasj;;//自动分配一行号给载入 表t 的seqno字段; 此行为 1 fasdjfasdfl;;//; 此行为 2; ...9*****载入有换行符的数据 注重:unix 和 windows 不同; n; &/n 还可以用 dbms_lob; 和 bfile 看一个文件的回车 换行 等其他非凡字符 < 1 >使用一个非换行符的字符 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,'n',chr(10))'//; replace 的使用帮助转换换行符 ) BEGINDATA 10,Sales,Virginia,01-april-2001,This is the SalesnOffice in Virginia 20,Accounting,Virginia,13/04/2001,This is the AccountingnOffice in Virginia 30,Consulting,Virginia,14/04/2001 12:02:02,This is the ConsultingnOffice in Virginia 40,Finance,Virginia,987268297,This is the FinancenOffice in Virginia < 2 >使用fix属性 LOAD DATA INFILE demo17.dat 'fix 101' INTO TABLE DEPT REPLACE FIELDS TERMINATED BY ',' TRAILING NULLCOLS (DEPTNO, DNAME;;;;;'upper(:dname)', LOC; 'upper(:loc)', LAST_UPDATED 'my_to_date( :last_updated )', COMMENTS;; ) demo17.dat 10,Sales,Virginia,01-april-2001,This is the Sales Office in Virginia;;; 20,Accounting,Virginia,13/04/2001,This is the Accounting Office in Virginia;;;; 30,Consulting,Virginia,14/04/2001 12:02:02,This is the Consulting Office in Virginia;;; 40,Finance,Virginia,987268297,This is the Finance Office in Virginia; //; 这样装载会把换行符装入数据库;下面的方法就不会 但要求数据的格式不同 LOAD DATA INFILE demo18.dat 'fix 101' 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;; ) demo18.dat 10,Sales,Virginia,01-april-2001,'This is the Sales Office in Virginia'; 20,Accounting,Virginia,13/04/2001,'This is the Accounting Office in Virginia';; 30,Consulting,Virginia,14/04/2001 12:02:02,'This is the Consulting Office in Virginia'; 40,Finance,Virginia,987268297,'This is the Finance Office in Virginia'; < 3 >使用var属性 LOAD DATA INFILE demo19.dat 'var 3' // 3 告诉每个记录的前3个字节表示记录的长度 如第一个记录的 071 表示此记录有 71 个字节 INTO TABLE DEPT REPLACE FIELDS TERMINATED BY ',' TRAILING NULLCOLS (DEPTNO, DNAME;;;;;'upper(:dname)', LOC; 'upper(:loc)', LAST_UPDATED 'my_to_date( :last_updated )', COMMENTS;; ) demo19.dat 07110,Sales,Virginia,01-april-2001,This is the Sales Office in Virginia 07820,Accounting,Virginia,13/04/2001,This is the Accounting Office in Virginia 08730,Consulting,Virginia,14/04/2001 12:02:02,This is the Consulting Office in Virginia 07140,Finance,Virginia,987268297,This is the Finance Office in Virginia < 4 >使用str属性 // 最灵活的一中 可定义一个新的行结尾符; win 回车换行 : chr(13)chr(10) 此列中记录是以 arn 结束的 select utl_raw.cast_to_raw(''chr(13)chr(10)) from dual; 结果7C0D0A LOAD DATA INFILE demo20.dat 'str X'7C0D0A'' INTO TABLE DEPT REPLACE FIELDS TERMINATED BY ',' TRAILING NULLCOLS (DEPTNO, DNAME;;;;;'upper(:dname)', LOC; 'upper(:loc)', LAST_UPDATED 'my_to_date( :last_updated )', COMMENTS;; ) demo20.dat 10,Sales,Virginia,01-april-2001,This is the Sales Office in Virginia 20,Accounting,Virginia,13/04/2001,This is the Accounting Office in Virginia 30,Consulting,Virginia,14/04/2001 12:02:02,This is the Consulting Office in Virginia 40,Finance,Virginia,987268297,This is the Finance Office in Virginia10*****将数据导入多个表 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 ) 11*****转载 RAW 数据 或 转载长字段 options(bindsize=1075700,rows=1) load data infile my.data 'fix 53760'// 53760 * 20=1075700;1075700是小于64K 的最大因子 concatenate 20 preserve blanks into table foo append (id constant 1,bigdata raw(1075700))12*****转载 LOB 数据 用 dbms_lobdbms_lob 转载的数据要在服务器上 通过网络的不行drop table demo;create or replace Directory dir1as 'c:temp';create or replace directory 'dir2' as 'c:temp';create table demo( id;;;;;int primary key, theClobclob)/host echo 'Hello World!' > c:temptest.txtdeclare l_clob;clob; l_bfilebfile;begin insert into demo values ( 1, empty_clob() ) returning theclob into l_clob; l_bfile := bfilename( 'DIR1', 'test.txt' )-- DIR1 要大写 dbms_lob.fileopen( l_bfile ); dbms_lob.loadfromfile( l_clob, l_bfile, dbms_lob.getlength( l_bfile ) ); dbms_lob.fileclose( l_bfile );end;/select dbms_lob.getlength(theClob), theClob from demo/----------------------------------用 sqlldr; 在同一行的LOB; lob数据在同一个数据文件中LOAD DATAINFILE demo21.dat 'str X'7C0D0A''INTO TABLE DEPTREPLACEFIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '''TRAILING NULLCOLS(DEPTNO, DNAME;;;;;'upper(:dname)', LOC; 'upper(:loc)', LAST_UPDATED 'my_to_date( :last_updated )', COMMENTS;;char(1000000))10,Sales,Virginia,01-april-2001,This is the SalesOffice in Virginia20,Accounting,Virginia,13/04/2001,This is the AccountingOffice in Virginia30,Consulting,Virginia,14/04/2001 12:02:02,This is the ConsultingOffice in Virginia40,Finance,Virginia,987268297,'This is the FinanceOffice in Virginia, it has embedded commas and ismUCh longer then the other comments field.; If you feel the need to add double quoted text in here likethis: ''You will need to double up those quotes! '' to preserve them in the string.; This field keeps going for upto1,000,000 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;;varchar2(255), timestamp date, filename; varchar2(255), text;;;clob)/LOAD DATA///////////window 的INFILE *REPLACEINTO TABLE LOB_DEMO( owner;;;position(40:61), timestamp; position(1:18) 'to_date(:timestamp'm','mm/dd/yyyy; hh:miam')', filenameposition(63:80),;-- 下面的LOB的filename是从这里来的 text LOBFILE(filename) TERMINATED BY EOF)BEGINDATA04/14/2001; 12:36p;;;;1,697 BUILTINAdministrators demo10.log // 这是 windows 下面的情况 上面的数据是用 dir /q/n 看见的情况; *******///// unix 下的情况 用 ls -l 得到上面数据的情况 控制文件就改下时间的格式------------------------------lob 到对象列create table image_load( id number, name varchar2(255), image ordsys.ordimage )/desc ordsys.ordimagedesc ordsys.ordsourceLOAD DATAINFILE *INTO TABLE Treplacefields terminated by ','( id, name, fiel_name filler, image column object ( source column object ( localdatalobfile(file_name) terminated by bof nullif file_name='NONE' ) ))begindata1,icons,icons.gif13*****转载varrays /嵌套表create type myArrayTypeas varray(10) of number(12,2)/create table t( x int primary key, y myArrayType )/LOAD DATAINFILE *INTO TABLE Treplacefields terminated by ','( x, y_cnt;;;;FILLER, yvarray count (y_cnt) ( y ))BEGINDATA1,2,3,42,10,1,2,3,4,5,6,7,8,9,103,5,5,4,3,2,1------------------------------------create or replace type myTableTypeas table of number(12,2)/create table t( x int primary key, y myTableType )nested table y store as y_tab/LOAD DATAINFILE *INTO TABLE Treplacefields terminated by ','( x, ynested table count (CONSTANT 5) ( y ))BEGINDATA1,100,200,300,400,5002,123,243,542,123,432==============================================================================象这样的数据 用 nullif 子句; 10-jan-200002350Flipper seemed unusually hungry today. 10510-jan-200009945Spread over three meals. id position(1:3) nullif id=blanks; // 这里可以是blanks 或者别的表达式 //; 下面是另一个列子 第一行的 1 在数据库中将成为 null LOAD DATA; INFILE * INTO TABLE T; REPLACE; (n position(1:2) integer external nullif n='1', v position(3:8) );;;;; ;; BEGINDATA 1; 10 20lg------------------------------------------------------------假如是英文的日志 格式，可能需要修改环境变量 nls_lang or nls_date_format