ORACLE学习之外部表

外部表只能在Oracle 9i之后来使用。外部表是指不在数据库内的表，如操作系统内有一个以逗号为分隔符的存储数据的文件，可以通过外部表将该文件的内容显示在数据库内，外部表的功能类似于视图，只能读，不能修改。

    创建外部表时首先要创建目录指定外部表的数据文件的位置，然后编辑外部表的创建语法：

    这里利用sqlldr生成一个外部表的标准，例如：

[oracle@orcl2 sqlldr2]$ pwd

/u01/myscript/sqlldr2

[oracle@orcl2 sqlldr2]$ ls

sqlldr2.ctl  sqlldr2.dat

[oracle@orcl2 sqlldr2]$ 

在/u01/myscript/sqlldr2目录下有sqlldr2.ctl和sqlldr2.dat两个文件，其中sqlldr2.ctl是sqlldr的控制文件，用来描述要导入数据的详细规则，sqlldr2.dat是数据文件，内部存有要加载的数据，并且以逗号为分隔符。具体内容如下，此处稍加解释，有关sqlldr的内容请看上篇详解。

[oracle@orcl2 sqlldr2]$ cat sqlldr2.ctl 

LOAD DATA //指定加载数据

INFILE sqlldr2.dat //指定加载的数据文件位置

APPEND INTO TABLE BONUS //指定要加载的表

FIELDS TERMINATED BY "," //指定数据文件以逗号为分隔符

(ENAME,JOB,SAL) //指定加载数据的列明

[oracle@orcl2 sqlldr2]$ cat sqlldr2.dat //此处为数据文件，以逗号为分隔符，不在赘述 

USER1,EMP,100

USER2,EMP,101

USER3,EMP,102

USER4,EMP,103

USER5,EMP,104

USER6,EMP,105

USER7,MGR,106

USER8,MGR,107

USER9,HR,108

先在数据内创建目录，并授权给scott（以scott用户为例）：

SQL> create directory sqlldr2 as '/u01/myscript/sqlldr2';

Directory created.

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

Grant succeeded.

利用sqlldr生成创建外部表的语句：

[oracle@orcl2 sqlldr2]$ sqlldr scott/oracle control=sqlldr2.ctl external_table=generate_only

SQL*Loader: Release 10.2.0.1.0 - Production on Wed Nov 20 10:21:27 2013

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

同目录下回生成一个.log结尾的日志文件，查看日志文件并获得sql语句

[oracle@orcl2 sqlldr2]$ cp sqlldr2.log sqlldr2.sql

[oracle@orcl2 sqlldr2]$ vi sqlldr2.sql 

删除无用的行，只保留一下语句即可：

CREATE TABLE "SYS_SQLLDR_X_EXT_BONUS"   //创建的外部表名，可自行修改

(

  "ENAME" VARCHAR2(20), //要创建外部表的列的属性

  "JOB" VARCHAR2(15),

  "SAL" NUMBER

)

ORGANIZATION external //指定该表为外部表，以下为外部表的属性

(

  TYPE oracle_loader    

/****加载数据的方式，ORACLE_LOADER是传统方式，还有皮ORACLE_DATAPUMP是数据泵的方式****/

  DEFAULT DIRECTORY SQLLDR2 //默认的加载路径，看，指定的是刚创建的目录

  ACCESS PARAMETERS

  (

    RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII  //记录结束标记，此处为换行结束

    BADFILE 'SQLLDR2':'sqlldr2.bad' //生成.bad文件，导入过程中记录坏的数据

    LOGFILE 'sqlldr2.log_xt' //生成日志文件

    READSIZE 1048576 //读取日志文件缓存区的大小，默认1M

    FIELDS TERMINATED BY "," LDRTRIM //指定分隔符

    REJECT ROWS WITH ALL NULL FIELDS //指定若为空值，则加载是为NUll(若整列均为空，则不予加载)

    (

      "ENAME" CHAR(255) //以下为列的属性

        TERMINATED BY ",",

      "JOB" CHAR(255)

        TERMINATED BY ",",

      "SAL" CHAR(255)

        TERMINATED BY ","

    )

  )

  location

  (

    'sqlldr2.dat' //要加载的数据文件名

  )

)REJECT LIMIT UNLIMITED //指定查询数据能结束的错误次数，此处不限制

好了，外部表语法解释完毕，熟悉的情况下可以自己手动创建，接下来去数据库执行创建该外部表：

SQL> get /u01/myscript/sqlldr2/sqlldr2.sql

  1  CREATE TABLE BONUS_TEST

  2  (

  3    "ENAME" VARCHAR2(20),

  4    "JOB" VARCHAR2(15),

  5    "SAL" NUMBER

  6  )

  7  ORGANIZATION external

  8  (

  9    TYPE oracle_loader

 10    DEFAULT DIRECTORY SQLLDR2

 11    ACCESS PARAMETERS

 12    (

 13      RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII

 14      BADFILE 'SQLLDR2':'sqlldr2.bad'

 15      LOGFILE 'sqlldr2.log_xt'

 16      READSIZE 1048576

 17      FIELDS TERMINATED BY "," LDRTRIM

 18      REJECT ROWS WITH ALL NULL FIELDS

 19      (

 20        "ENAME" CHAR(255)

 21          TERMINATED BY ",",

 22        "JOB" CHAR(255)

 23          TERMINATED BY ",",

 24        "SAL" CHAR(255)

 25          TERMINATED BY ","

 26      )

 27    )

 28    location

 29    (

 30      'sqlldr2.dat'

 31    )

 32* )REJECT LIMIT UNLIMITED

SQL> /

Table created.

SQL> select * from bonus_test;

ENAME                JOB                    SAL

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

USER1                EMP                    100

USER2                EMP                    101

USER3                EMP                    102

USER4                EMP                    103

USER5                EMP                    104

USER6                EMP                    105

USER7                MGR                    106

USER8                MGR                    107

USER9                HR                     108

9 rows selected.

数据文件中的9条数据完全加入到了数据库中

[oracle@orcl2 sqlldr2]$ ls

sqlldr2.ctl  sqlldr2.dat  sqlldr2.log  sqlldr2.log_xt  sqlldr2.sql

同目录下生成了一个.log_xt的日志文件

[oracle@orcl2 sqlldr2]$ cat sqlldr2.log_xt 

 LOG file opened at 11/20/13 10:36:42

Field Definitions for table BONUS_TEST

  Record format DELIMITED BY NEWLINE

  Data in file has same endianness as the platform

  Reject rows with all null fields

  Fields in Data Source: 

    ENAME                           CHAR (255)

      Terminated by ","

      Trim whitespace same as SQL Loader

    JOB                             CHAR (255)

      Terminated by ","

      Trim whitespace same as SQL Loader

    SAL                             CHAR (255)

      Terminated by ","

      Trim whitespace same as SQL Loader

该文件记录外部表的操作信息

若向sqlldr2.dat文件中加入一行数据USER10,HR,108，再次进入数据库查询该外部表会发现多出了一条，好神奇~

SQL> select * from bonus_test;

ENAME                JOB                    SAL

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

USER1                EMP                    100

USER2                EMP                    101

USER3                EMP                    102

USER4                EMP                    103

USER5                EMP                    104

USER6                EMP                    105

USER7                MGR                    106

USER8                MGR                    107

USER9                HR                     108

USER10               HR                     108

10 rows selected.

再次查看sqlldr2.log_xt日志会发现多出几条日志信息

未完待续...