oracle使用sql文件创建表,使用SQL*Loader创建外部表之一

使用SQL*Loader创建外部表：

---创建目录对象并授权给Scott用户：

SQL> create or replace directory loader_home as '/home/oracle/dirhome';

Directory created.

--授权：

SQL> grant read,write on directory loader_home to scott;

Grant succeeded.

---在scott用户下创建外部表：

SQL> conn scott/tiger

Connected.

SQL> show user

USER is "SCOTT"

--创建外部表：

SQL> create table myloader(

2  id number(3),

3  dname varchar2(12),

4  lname varchar2(10));

Table created.

--查看表结构：

SQL> desc myloader

Name                                      Null?    Type

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

ID                                                 NUMBER(3)

DNAME                                              VARCHAR2(12)

LNAME                                              VARCHAR2(10)

---在操作系统层创建两个存放外部数据的文件：

[oracle@enmo ~]$ cd dirhome/

[oracle@enmo dirhome]$ pwd

/home/oracle/dirhome

[oracle@enmo dirhome]$

--创建外部文件1：

[oracle@enmo dirhome]$ vi dt1.csv

10,ACCOUNTING,NEW YORK

20,RESEARCH,DALLAS

30,SALES,CHICAGO

40,OPERATIONS,BOSTON

~

--创建外部文件2：

[oracle@enmo dirhome]$ vi dt2.csv

40,OPERATIONS1,BOSTON0

50,OPERATIONS2,BOSTON9

60,OPERATIONS3,BOSTON8

70,OPERATIONS4444,BOSTON7

~

--查看创建的两个外部文件：

[oracle@enmo dirhome]$ ls

dt1.csv  dt2.csv

[oracle@enmo dirhome]$ ll

total 8

-rw-r--r-- 1 oracle oinstall 80 Nov  1 22:38 dt1.csv

-rw-r--r-- 1 oracle oinstall 95 Nov  1 22:39 dt2.csv

[oracle@enmo dirhome]$

--创建外部表控制文件：

[oracle@enmo dirhome]$

[oracle@enmo dirhome]$ vi load.ctl

load data

infile 'dt1.csv'

infile 'dt2.csv'

badfile 'load.bad'     #指明坏文件

into table myloader

truncate

fields terminated by ','

(id,dname,lname)

~

--查看控制文件：

[oracle@enmo dirhome]$ ls

dt1.csv  dt2.csv  load.ctl

---导入外部文件到Scott用户下的myloader表：

[oracle@enmo dirhome]$ sqlldr scott/tiger control=load.ctl

SQL*Loader: Release 11.2.0.4.0 - Production on Tue Nov 1 22:54:19 2016

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Commit point reached - logical record count 4

Commit point reached - logical record count 8

[oracle@enmo dirhome]$

--查看本目录下的文件：

[oracle@enmo dirhome]$

[oracle@enmo dirhome]$ ls

dt1.csv  dt2.csv  load.bad  load.ctl  load.log

#多生成了一个load.bad的坏文件，是存放因为不符合导入条件的数据。

--到Scott用户查看表myloader的数据：

SQL> select * from myloader;

ID DNAME        LNAME

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

10 ACCOUNTING   NEW YORK

20 RESEARCH     DALLAS

30 SALES        CHICAGO

40 OPERATIONS   BOSTON

40 OPERATIONS1  BOSTON0

50 OPERATIONS2  BOSTON9

60 OPERATIONS3  BOSTON8

7 rows selected.

#通过Scott用户下的SQL查询，发现 外部文件dt2.csv中的ID号为70的数据没有被导入表myloader中。

--尝试读取load.bad文件的额数据：

[oracle@enmo dirhome]$

[oracle@enmo dirhome]$ cat load.bad

70,OPERATIONS4444,BOSTON7

#看到因为第二个字段的长度超出了设置的长度，所以没有被导入到表中。

---空值null的处理：

--在Scott用户另外创建一个表hisloader：

SQL> create table hisloader(

2  id number(3),

3  dname varchar2(12),

4  lname varchar2(10));

Table created.

--编辑外部文件dt3.csv以及控制文件：

[oracle@enmo dirhome]$ vi dt3.csv

101,OPERATIONS5,BOSTON1

102,OPERATIONS6,BOSTON2

103,OPERATIONS7

~

#共3条数.

--创建控制文件：

[oracle@enmo dirhome]$ vi load1.ctl

load data

infile 'dt3.csv'

badfile 'load.bad'

into table hisloader

truncate

fields terminated by ','

trailing nullcols

(id,dname,lname)

~

--利用sqlldr将外部数据导入表hisloader中：

[oracle@enmo dirhome]$ sqlldr scott/tiger control=load1.ctl

SQL*Loader: Release 11.2.0.4.0 - Production on Tue Nov 1 23:22:58 2016

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Commit point reached - logical record count 3

[oracle@enmo dirhome]$

#可到有3条数据导入表中。

--到Scott用户查看hisloader表的数据：

SQL> select * from hisloader;

ID DNAME        LNAME

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

101 OPERATIONS5  BOSTON1

102 OPERATIONS6  BOSTON2

103 OPERATIONS7

#可以看到含有空值的数据也可以被导入到表hisloader中。

--查看目录下的外部文件：

[oracle@enmo dirhome]$ ll

total 32

-rw-r--r-- 1 oracle oinstall   80 Nov  1 22:38 dt1.csv

-rw-r--r-- 1 oracle oinstall   95 Nov  1 22:39 dt2.csv

-rw-r--r-- 1 oracle oinstall   64 Nov  1 23:18 dt3.csv

-rw-r--r-- 1 oracle oinstall  136 Nov  1 23:21 load1.ctl

-rw-r--r-- 1 oracle oinstall 1588 Nov  1 23:22 load1.log

-rw-r--r-- 1 oracle oinstall   26 Nov  1 22:54 load.bad

-rw-r--r-- 1 oracle oinstall  134 Nov  1 22:50 load.ctl

-rw-r--r-- 1 oracle oinstall 1852 Nov  1 22:54 load.log

[oracle@enmo dirhome]$

对于含有控制的数据，就需要在控制文件中添加语句trailing nullcols。