sql loader的简单用法

最新推荐文章于 2021-05-16 17:19:34 发布

Stainky

最新推荐文章于 2021-05-16 17:19:34 发布

阅读量648

点赞数

分类专栏： Oracle 文章标签： Oracle dba 数据库数据 append

本文链接：https://blog.csdn.net/lanyu_lee/article/details/39053551

版权

Oracle 专栏收录该内容

98 篇文章 0 订阅

订阅专栏

参考文档：http://wenku.baidu.com/link?url=U4SFri69OvvUk1qNmyBDh19VYg9OOuNHaQPcxAvH-vuPQ2K2Jsew5zzzv7bXust8FYrE3UOdWcguGhFLUFSoIDOpobDPFETUX-gBRVGlYKW

1.建表

CREATE TABLE animal_feeding (
animal_id NUMBER,
feeding_date DATE,
pounds_eaten NUMBER (5,2),
note VARCHAR2(80)
);

2.准备csv文件(位置：c:\test\animal_feeding.csv)

100,1-1月-2000,23.5,"Flipper seemed unusually hungry today."
105,1-1月-2000,99.45,"Spread over three meals."
112,1-1月-2000,10,"No comment."
151,1-1月-2000,55
166,1-1月-2000,17.5,"Shorty ate Squacky."
145,1-1月-2000,0,"Squacky is no more."
175,1-1月-2000,35.5,"Paintuin skipped his first meal."
199,1-1月-2000,0.5,"Nosey wasn’t very hungry today."
202,1-1月-2000,22.0
240,1-1月-2000,28,"Snoops was lethargic and feverish."

3.准备ctl文件(位置：c:\test\animal_feeding.ctl)

LOAD DATA
INFILE 'c:\test\animal_feeding.csv'
BADFILE 'c:\test\animal_feeding.bad'
APPEND
INTO TABLE animal_feeding
TRAILING NULLCOLS
(
animal_id INTEGER EXTERNAL TERMINATED BY ",",
feeding_date DATE "dd-mon-yyyy" TERMINATED BY ",",
pounds_eaten DECIMAL EXTERNAL TERMINATED BY ",",
note CHAR TERMINATED BY ","
OPTIONALLY ENCLOSED BY '"'
)

(注意APEND，作用在于附加到目标数据库数据后面，如果用INSERT，则目标数据库必须为空。)

4.执行

sqlldr animal/animal control=C:\test\animal_feeding.ctl log='c:\test\animal_feeding.log'

(log用于指定日志位置，这样可以方便查找执行过程中出现的问题)

5.查看数据

SQL> select * from animal_feeding;
 
 ANIMAL_ID FEEDING_DATE POUNDS_EATEN NOTE
---------- ------------ ------------ --------------------------------------------------------------------------------
       100 01-一月-00          23.50 Flipper seemed unusually hungry today.
       105 01-一月-00          99.45 Spread over three meals.
       112 01-一月-00          10.00 No comment.
       151 01-一月-00          55.00 
       166 01-一月-00          17.50 Shorty ate Squacky.
       145 01-一月-00           0.00 Squacky is no more.
       175 01-一月-00          35.50 Paintuin skipped his first meal.
       199 01-一月-00           0.50 Nosey wasn’t very hungry today.
       202 01-一月-00          22.00 
       240 01-一月-00          28.00 Snoops was lethargic and feverish.
 
10 rows selected