oracle使用杂记2

10           *****   将数据导入多个表

    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_lob   dbms_lob 转载的数据要在服务器上 通过网络的不行

drop table demo;

create or replace directory dir1   as 'c:\temp\';

create or replace directory "dir2" as 'c:\temp\';

create table demo
( id        int primary key,
  theClob   clob
)
/

host echo 'Hello World\!' > c:\temp\test.txt

declare
    l_clob    clob;
    l_bfile   bfile;
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 DATA
INFILE demo21.dat "str X'7C0D0A'"
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(1000000)
)

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, 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
1,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 *
REPLACE
INTO TABLE LOB_DEMO
( owner      position(40:61),
  timestamp  position(1:18) "to_date(:timestamp||'m','mm/dd/yyyy  hh:miam')",
  filename   position(63:80),    -- 下面的LOB的filename是从这里来的
  text LOBFILE(filename) TERMINATED BY EOF
)
BEGINDATA
04/14/2001  12:36p               1,697 BUILTIN\Administrators demo10.log
        // 这是 windows 下面的情况 上面的数据是用 dir /q/n 看见的情况  *******

///// unix 下的情况
   用 ls -l 得到上面数据的情况
   控制文件就改下时间的格式

------------------------------
lob 到对象列

create table image_load( id number, name varchar2(255),
    image ordsys.ordimage )
/

desc ordsys.ordimage

desc ordsys.ordsource

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
1,icons,icons.gif

13           *****   转载varrays /嵌套表

create type myArrayType
as varray(10) of number(12,2)
/

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
1,2,3,4
2,10,1,2,3,4,5,6,7,8,9,10
3,5,5,4,3,2,1

------------------------------------

create or replace type myTableType
as table of number(12,2)
/

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 5)
  (
    y
  )
)

BEGINDATA
1,100,200,300,400,500
2,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 *                              

上一页  [1] [2] [3] [4] [5]  下一页

Copyright ＠ 2007-2012 敏韬网(敏而好学，文韬武略--MinTao.Net)（学习笔记） Inc All Rights Reserved.
闵涛 E_mail:admin@mintao.net(欢迎提供学习资源)

鄂公网安备 42011102001154号

站长：MinTao ICP备案号：鄂ICP备11006601号-18