sql*loader的高级操作

由于oracle控制文件的强大功能，使得sql*loader可以如所愿的对数据文件进行处理，然后导入oracle数据库中。想想也是，数据库的存在无非是为了：1，更好的管理数据。2，能更轻松的和软件衔接，明确数据流的走向。这时，很多格式的数据文件如果要导入到oracle中就遇到一定的问题。

一，处理是以空格断值得数据文件

编辑如图所示的文件，保存在根文件夹下

将这样的数据文件导入到昨天创建的test表中，如何操作？查找oracle的控制文件语法，得知在引述列明时使用position 关键字。编写控制文件

这里我把infile 文件写法用绝对路径，执行结果

报出了异常，这里就是昨天我在博客中说的，一定要在执行的目录里添加数据文件。还是老老实修改数据文件，放在根目录下，在根目录下执行。

正确的控制文件的写法是：

SQL> select * from test;

        ID NAME
---------- ------------------------------
       100 y
       200
       300 n
         2 lily

SQL>
SQL>

通过思考，我总结出了规律，在写控制文件时一定要像写程序一样按着格式一步一步的写，排列成规矩的格式，这样才不会出错，出了错也好坚持。这真是一个IT者的好习惯。position(1:3)表示取第一个到第三个字符中间的值。这样的文件也成为定长字符串。第8个字符一定是y，n，所以后头的值没有取完。

举一反三，通过postion关键字可以实现将定长字符串的文件隔行插入。如下图文件展示的那样

很显然，数据库中没有性别这一行，我直接插入时一定会报错，并产生日志，错误文件。所以，我修改控制文件。

SQL>
SQL>
SQL> select * from test;

        ID NAME
---------- ------------------------------
       100 y
       200
       300 n
       100 y
       200
       300 n
      1000 王
      2000 莉
      3000 丽
      1000 王歌
      2000 莉莉
      3000 丽丽
         2 lily

13 rows selected

SQL>

因为，我插入了2次，所以append出了许多结果。因此可见控制文件的作用是非常强大的，处理的方法也是非常灵活的。

二：处理大批的数据，如多个数据文件同时导入到一个表中。解决方法：

还是修改控制文件，需要这样改变

load data

infile data1.dat /*第一个数据文件

infile data2.dat /*第二个数据文件

infile data3.dat

append into table tab1

fields terminated by ","

这样处理就行。方法灵活多变。同样如果不想插入数据文件中的第n到第n+3行。就需要关键字filler了。所以，方法多种多样。在用到时先试验，然后仔细使用就好。

很重要的一个问题，oracle中对空值的定义是null,当插入的值为空时，就会出现错误。我们实验一下。还以以前创的test表为例。定义值如图

写控制文件：

load data
infile dat_new.csv
append into table test
fields terminated by ","
(id,name)

sql*loader中执行，提示成功，但是出现bad文件。打开bad文件

4,
id ,name

这时由于空值引起的。注意.bad 文件和控制文件会在同一个目录里，bad文件会自动生成在控制文件的目录中。这样的文件在oracle中出现的可能性很大。因为oracle中的null值和excel中的空是不一样的存贮方式。为了执行的正确，需要修改控制文件，添加关键字TRAILING NULLCOLS。

LOAD DATA
INFILE dat_new.csv
TRUNCATE INTO TABLE TEST
FIELDS TERMINATED BY "," TRAILING NULLCOLS
(ID,NAME)

这次执行，没有问题了。查看一下

在实际工作中，TRAILING NULLCOLS 应用非常广泛。

工作了一天，好疲惫了。晚上头脑还在运转，更是对自己的调整。告诉自己，我能坚持，告诉自己，我一定行。

转载于:https://blog.51cto.com/bestlanzi/1104340

sql*loader的高级操作

相关推荐