• 博客访问: 288234
  • 博文数量: 167
  • 用 户 组: 普通用户
  • 注册时间: 2009-06-26 13:51
个人简介

暂无介绍

文章分类

全部博文(167)

文章存档

2011年(9)

2010年(12)

2009年(146)

我的朋友

分类: Linux操作系统

2009-07-08 16:33:09

2.1 基本知识

    Oracle 的  SQL* LOADER  可以将外部格式化的文本数据加载到数据库表中。通常 与 SPOOL导出文本数据方法配合使用。

    1.命令格式

    SQLLDR keyword=value [,keyword=value,……]

    例:$ sqlldr user/pwd control=emp.ctl data=emp.dat bad=emp.bad log=emp.log

    2.控制文件

    SQL*LOADER  根据控制文件可以找到需要加载的数据。并且分析和解释这些数据。

    控制文件由三个部分组成,具体参数参考帮助文档:1.  全局选件,行,跳过的记录数等;2. INFILE 子句指定的输入数据;3.  数据特性说明。

    comment: ——注释

    例:

    load data infile *

    append    ——除了 append外,还有 insert、replace、truncate等方式

    into table emp fields terminated b y ‘|’

    (

    no             float external, name char(20),

    age           integer external,

    duty         char(1),salary      float external,

    upd_ts     date(14) ‘YYYYMMDDHH24MISS’)

    begindata

    100000000003|Mulder|000020|1|000000005000|20020101000000

    100000000004|Scully|000025|2|000000008000|20020101235959

    控制文件中infile选项跟sqlldr 命令行中data 选项含义相同,如使用infile *则表明数据在本控制文件以 begin data 开头的区域内。 一些选项:FIELDS TERMINATED BY WHITESPACE FIELDS TERMINATED BY x'09' FILLER_1 FILLER, //  指定某一列将不会被装载

    DEPTNO position(1:2), DNAME position(*:16), //  指定列的位置SEQNO RECNUM //载入每行的行号

    SKIP n          //  指定导入时可以跳过多少行数据

    3.数据文件

    按控制文件数据格式定义的数据行集,

    例:

    100000000001|Tom|000020|1|000000005000|20020101000000

    100000000002|Jerry|000025|2|000000008000|20020101235959

    固定格式、可变格式、流记录格式:

    固定格式:

    当数据固定的格式(长度一样)时且是在文件中得到时,要用 INFILE "fix n"

    load data

    infile 'example.dat' "fix 11"

    into table example

    fields terminated b y ',' optionally enclosed by '"'

    (col1 char(5), col2 char(7)) example.dat:

    001, cd, 0002,fghi,

    00003,lmn,

    1, "pqrs",

    0005,uvwx,

    可变格式:

    当数据是可变格式(长度不一样)时且是在文件中得到时,要用 INFILE "var n".如:

    load data

    infile 'example.dat' "var 3"

    into table example

    fields terminated b y ',' optionally enclosed by '"'

    (col1 char(5), col2 char(7)) example.dat:

    009hello,cd,010world,im,

    012my,name is,

    流记录格式: // Stream-recored format:load data infile 'xx.dat' "str '|\n'"

    into table xx field terminated b y ',' optionally enclosed by '"'

    (col1 char(5), col2 char(7))

    example.dat:

    hello, ccd,|

    world, bb,|

    4.  坏文件

    bad=emp.bad坏文件包含那些被 SQL*Loader拒绝的记录。被拒绝的记录可能是不符合要求的记录。

    5.  日志文件及日志信息

    log=emp.log当 SQL*Loader  开始执行后,它就自动建立  日志文件。日志文件包含有加载的总 结,加载中的错误信息等。

2.2  高级选项

    1. Conventional Path Load与Direct Path Load

    Conventional-path Load:通过常规通道方式上载。

    特点:commit, always  gen redo logs,  enforce all constraints, fire insert triggers, can load into cluster, other user can make change

    rows:每次提交的记录数

    bindsize:每次提交记录的缓冲区

    readsize:与 bindsize 成对使用,其中较小者会自动调整到较大者

    sqlldr 先计算单条记录长度,乘以 rows,如小于 bindsize,不会试图扩张,rows以填充 bindsize;如超出,则以 bindsize 为准。 命令为:

    $ sqlldr dbuser/oracle control=emp.ctl log=emp.log rows=10000 bindsize=8192000

    Direct-Path Load:

    通过直通方式上载,可以跳过数据库的相关逻辑,不进行  SQL解析,而直接将数 据导入到数据文件中。

    特点:save, conditionly gen redo logs, enforce PK UK NN, not fire triggers, can not load into cluster, other user can not make change命令为:

    $ sqlldr dbuser/oracle control=emp.ctl log=emp.log direct=true

    2. SPOOL导出文本数据方法

    导入的数据文件可以用 SPOOL导出文本数据方法生成。

    SQL*PLUS环境设置

    SET NEWPAGE NONE HEADING OFF SPACE 0

    PAGESIZE 0 SET TRIMOUT ON TRIMSPOOL ON LINESIZE 2500

    注:LINESIZE 要稍微设置大些,免得数据被截断,它应和相应的 TRIMSPOOL结合使用防止导出的文本有太多的尾部空格。

    但是如果 LINESIZE 设置太大,会大大降低导出的速度,另外在 WINDOWS下导 出最好不要用 PLSQL导出,速度比较慢,直接用  COMMEND 下的 SQLPLUS命令最小化窗口执行。对于字段内包含很多回车换行符的应该给与过滤,形成比较规矩的文本 文件。

    通常情况下,我们使用 SPOOL方法,将数据库中的表导出为文本文件,如下述:

    set trimspool on

    set linesize 120 pagesize 2000 newpage 1 heading off    term off spool  路径+文件名

    select col1||','||col2||','||col3||','||col4||'……' from tablename;

    spool off

  2.3 脚本

    1.  将表中数据记录导出为字段值用分隔符'|'分开的。dat文件

    #!/bin/ksh

    ##################################################################

    ##    名称: unloadtable

    ##    功能:  本 shell 用于将表中数据记录导出

    ##                 导出为字段值用分隔符'|'分开的。dat文件

    ##    编者:

    ##    日期: 2006.03.18

    ##################################################################

    if [ $# -ne 3 ]

    then echo "usage:unloadtable tablename username password."

    exit 0

    fi

    ##准备工作

    echo "set heading off     " >/tmp/$1.col

    echo "set pagesize 0" >>/tmp/$1.col

    echo "set linesize 800    " >>/tmp/$1.col

    echo "set feedback off    " >>/tmp/$1.col

    echo "set tab off              " >>/tmp/$1.col

    echo  "select  column_name||','  from  user_tab_columns  where  lower(table_name)='$1'  order  by

    column_id; " >> /tmp/$1.col

    ##产生 select 语句

    echo "set heading off     " >/tmp/$1.sel

    echo "set pagesize 0" >>/tmp/$1.sel

    echo "set linesize 800    " >>/tmp/$1.sel

    echo "set feedback off    " >>/tmp/$1.sel

    echo "set tab off              " >>/tmp/$1.sel

    echo "select " >>/tmp/$1.sel

    echo  `sqlplus  -s  $2/$3  <  /tmp/$1.col`  |sed  "s/,/||'|'||/g"  |sed  "s/||$//g"|sed  "s/date/\"date\"/g"

>>/tmp/$1.sel

    ##生成 dat文件

    #echo "from $1;\n/" >>/tmp/$1.sel    由于  /  导致多执行一次 select

    echo "from $1;\n" >>/tmp/$1.sel

    sqlplus -s $2/$3 < /tmp/$1.sel >$1_tmp.dat

    #awk '{if(FNR!=1) print $0}' $1_tmp.dat >$1.dat       FNR 选项使得第一条记录选不出

    awk '{print $0}' $1_tmp.dat >$1.dat

    rm -f $1_tmp.dat

    2.  将数据导入到相应表中

    #!/bin/ksh

    ##################################################################

    ##    名称:loadtable

    ##    功能:本 shell 用于将已经准备好的。dat数据文件导入相应的表中

    ##               .dat 文件各个字段值用分隔符'|'分开。

    ##    编者:

    ##    日期: 2006.03.18

    ##################################################################

    if [ $# -ne 3 ]

    then

    echo "usage:loadtable tablename username    password." exit 0 fi

    ##准备工作

    echo "set heading off     " >/tmp/$1.colsql

    echo "set pagesize 0" >>/tmp/$1.colsql

    echo "set linesize 800    " >>/tmp/$1.colsql

    echo "set feedback off    " >>/tmp/$1.colsql

    echo "set tab off              " >>/tmp/$1.colsql

    echo  "select  column_name||','  from  user_tab_columns  where  lower(table_name)='$1'  order  by

    column_id; " >> /tmp/$1.colsql

    ##产生 ctl文件

    echo "load data" >/tmp/$1.ctl

    echo "infile *" >>/tmp/$1.ctl

    echo "into table $1" >>/tmp/$1.ctl

    echo "fields terminated by '|'" >>/tmp/$1.ctl

    echo `sqlplus -s $2/$3 < /tmp/$1.colsql` |sed "s/,$/)/g" |sed "s/^/(/g" >>/tmp/$1.ctl

    ##开始导入数据

    echo "truncate table $1;" >/tmp/$1.sql

    sqlplus $2/$3 < /tmp/$1.sql

    sqlldr $2/$3 data=$1.dat control=/tmp/$1.ctl log=/tmp/$1.log

简述数据库导入Oracle SQL*Loader指南:

SQL*Loader是Oracle数据库导入外部数据的一个工具。它和DB2的Load工具相似,但有更多的选择,它支持变化的加载模式,可选的加载及多表加载。

如何使用 SQL*Loader 工具

我们可以用Oracle的sqlldr工具来导入数据。例如:

sqlldr scott/tiger control=loader.ctl

控制文件(loader.ctl) 将加载一个外部数据文件(含分隔符)loader.ctl如下:



load data 
infile 'c:\data\mydata.csv'
into table emp
fields terminated by "," optionally enclosed by '"'
( empno, empname, sal, deptno )

mydata.csv 如下:

10001,"Scott Tiger", 1000, 40 
10002,"Frank Naude", 500, 20

下面是一个指定记录长度的示例控制文件。"*" 代表数据文件与此文件同名,即在后面使用BEGINDATA段来标识数据。

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这样的工具

Oracle 没有提供将数据导出到一个文件的工具。但是,我们可以用SQL*Plus的select 及 format 数据来输出到一个文件:

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

另外,也可以使用使用 UTL_FILE PL/SQL 包处理:

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;
/

当然你也可以使用第三方工具,如SQLWays ,TOAD for Quest等。

加载可变长度或指定长度的记录:


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,"
下面是导入固定位置(固定长度)数据示例:
LOAD DATA
INFILE *
INTO TABLE load_positional_data
( data1 POSITION(1:5),
data2 POSITION(6:15)
)
BEGINDATA
11111AAAAAAAAAA
22222BBBBBBBBBB

跳过数据行,可以用 “SKIP n”关键字来指定导入时可以跳过多少行数据。如:

LOAD DATA 
INFILE *
INTO TABLE load_positional_data
SKIP 5
( data1 POSITION(1:5),
data2 POSITION(6:15)
)
BEGINDATA
11111AAAAAAAAAA
22222BBBBBBBBBB

导入数据时修改数据:

在导入数据到数据库时,可以修改数据。注意,这仅适合于常规导入,并不适合 direct导入方式.如:

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
)

 

将数据导入多个表:

如:



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
)

导入选定的记录:

如下例: (01) 代表第一个字符, (30:37) 代表30到37之间的字符:

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
)

导入时跳过某些字段:

可用 POSTION(x:y) 来分隔数据在Oracle8i中可以通过指定FILLER字段实现。FILLER 字段用来跳过、忽略导入数据文件中的字段。如:

LOAD DATA 
TRUNCATE INTO TABLE T1
FIELDS TERMINATED BY ','
( field1,
field2 FILLER,
field3
)

导入多行记录:

可以使用下面两个选项之一来实现将多行数据导入为一个记录:

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
oneEgby having a '#' character in column 1

SQL*Loader 数据的提交

一般情况下是在导入数据文件数据后提交的。也可以通过指定 ROWS= 参数来指定每次提交记录数。

提高 SQL*Loader 的性能:

(1) 一个简单而容易忽略的问题是,没有对导入的表使用任何索引和/或约束(主键)。如果这样做,甚至在使用ROWS=参数时,会很明显降低数据库导入性能。

(2) 可以添加 DIRECT=TRUE来提高导入数据的性能。当然,在很多情况下,不能使用此参数。

(3) 通过指定 UNRECOVERABLE选项,可以关闭数据库的日志。这个选项只能和 direct 一起使用。

(4) 可以同时运行多个导入任务。

常规导入与direct导入方式的区别:

常规导入可以通过使用 INSERT语句来导入数据。Direct导入可以跳过数据库的相关逻辑(DIRECT=TRUE),而直接将数据导入到数据文件中。

 原文地址 http://oracle.**.com/backup/760990.html
阅读(1076) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~
评论热议
请登录后评论。

登录 注册