ORACLE学习之外部表

################    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日志会发现多出几条日志信息


未完待续...

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29320885/viewspace-777062/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29320885/viewspace-777062/