【oracle】【demo】sqlldr批量导入数据

【oracle】【demo】sqlldr批量导入数据

【oracle】【demo】sqlldr批量导入数据

【oracle】【demo】sqlldr

--建表

create table t_student

(

id integer,

name varchar2(64),

age int

);

 

desc t_student;

 

--查看数据库服务端字符集

select * from v$nls_parameters  where parameter='NLS_CHARACTERSET;

 

--设置 环境变量 NLS_LANG

export NLS_LANG=.AL32UTF8

 

 

【demo01】sqlldr userid=user/password@ip:port/service_name control&#l data&#

 

文件

1001|Lucy|22

 

文件 l

LOAD DATA

append

INTO TABLE t_student

FIELDS TERMINATEDW BY '|'

(

id,

name,

age

)

 

文件 test.sh

typeset exec_path="/tmp/test/demo01/"

chmod -R 777 ${exec_path}

su - oracle -c "

cd ${exec_path};

sqlldr userid=user/password@ip:port/service_name

control=${exec_path}/l

data=${exec_path}/

log=${exec_path}/student.log

bad=${exec_path}/student.bad

direct=true"

 

root用户 执行 test.sh

cd /tmp/test/demo01

dos2unix *

sh -x test.sh

 

 

 

【demo02】sqlldr userid=user/password@ip:port/service_name control&#l data=filename.csv

文件 student.csv

id,name,age

1003,Amy,21

1004,Scott,20

 

文件 l

OPTIONS (skip=1,rows=2) --跳过第一行

LOAD DATA

truncate

INTO TABLE t_student

FIELDS TERMINATEDW BY ','

(

id "to_number(:id)",

name,

age "to_number(:age)"

)

 

文件 test.sh

typeset exec_path="/tmp/test/demo02/"

chmod -R 777 ${exec_path}

su - oracle -c "

cd ${exec_path};

sqlldr userid=user/password@ip:port/service_name

control=${exec_path}/l

data=${exec_path}/

log=${exec_path}/student.log

bad=${exec_path}/student.bad

skip=1 errors=2 rows=2"

 

root用户 执行 test.sh

cd /tmp/test/demo02

dos2unix *

sh -x test.sh

 

 

 

 

【demo03】sqlldr userid=user/password@ip:port/service_name control&#l

文件 l

OPTIONS (skip=1)

LOAD DATA

infile *  --数据在control文件里

append

INTO TABLE t_student

FIELDS TERMINATEDW BY ','

trailing nullcols

(

virtual_column filter,

id,

name,

age

)

BEGINDATA

 ,id,name,age

1,2001,zhangsan,25

2,2002,李四,20

3,2003,王二,

 

 

文件 test.sh

typeset exec_path="/tmp/test/demo03/"

chmod -R 777 ${exec_path}

su - oracle -c "

cd ${exec_path};

sqlldr userid=user/password@ip:port/service_name

control=${exec_path}/l

log=${exec_path}/student.log

bad=${exec_path}/student.bad"

 

root用户 执行 test.sh

cd /tmp/test/demo03

dos2unix *

sh -x test.sh

 

 

参考：

.html

 

posted on 2019-03-05 17:26 绿Z 阅读( ...) 评论( ...) 编辑 收藏

转载于:.html