oracle external table excel,Oracle 外部表（External Table）的用法与SQLLDR

对于外部表(External Table)，数据库中只保存了表结构信息，而数据存放在文件系统上的外部文件中，所以外部表只能被只读访问。

下面通过一个实例来介绍一下外部表的用法。

要使用外部表首先要创建目录(在Oracle 9i之前目录需要通过utl_file_dir静态参数来设置)并授权，目录是数据文件的存放地点，数据文件是指外部表要读取的文件，通常是文本文件。

SQL> connect / as sysdba

Connected.

SQL> create or replace directory sqldr

2 as '/opt/oracle/sqldr';

Directory created.

SQL> grant read,write on directory sqldr to eygle;

Grant succeeded.

SQL> col DIRECTORY_PATH for a30

SQL> col DIRECTORY_NAME for a10

SQL> select * from dba_directories where DIRECTORY_NAME='SQLDR';

OWNER DIRECTORY_ DIRECTORY_PATH

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

SYS SQLDR /opt/oracle/sqldr

然后就可以创建外部表了，以下是一个外部表的创建范例，其关键字在于ORGANIZATION external。

CREATE TABLE "USERS"

(

USERNAME VARCHAR2(30),

USER_ID NUMBER,

PASSWORDVARCHAR2(30)

)

ORGANIZATION external

(

TYPE oracle_loader

DEFAULT DIRECTORY SQLDR

ACCESS PARAMETERS

(

RECORDS DELIMITED BY NEWLINE CHARACTERSET ZHS16GBK

BADFILE 'SQLDR':'users.bad'

DISCARDFILE 'SQLDR':'users.dis'

LOGFILE 'SQLDR':'users.log'

READSIZE 1048576

FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' LDRTRIM

MISSING FIELD VALUES ARE NULL

REJECT ROWS WITH ALL NULL FIELDS

(

USERNAME CHAR(30)

TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',

USER_ID CHAR(30)

TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',

PASSWORD CHAR(30)

TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'

)

)

location('data.txt')

)

REJECT LIMIT UNLIMITED

创建完成之后，就可以通过SQL语句访问这个外部表中的数据了：

SQL> select * from users;

USERNAME USER_ID PASSWORD

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

SYS 0 8A8F025737A9097A

SYSTEM 5 D4DF7931AB130E37

OUTLN 11 4A3BA55E08595C81

SCOTT 38 F894844C34402B67

ORACLE 45 EXTERNAL

PERFSTAT 47 AC98877DE1297365

EYGLE 41 B726E09FE21F8E83

DBSNMP 19 E066D214D5421CCC

WMSYS 21 7C9BA362F8314299

9 rows selected.

如果大家注意一下外部表的创建语句，你会发现这与我们熟悉的SQLLDR语法非常相似。

下面来简要介绍一下SQLLDR的用法，使用SQLLDR首先需要创建一个控制文件，通过控制文件可以将数据很容易地加载入数据库中：

LOAD

INFILE '/opt/oracle/sqldr/data.txt'

badfile '/opt/oracle/sqldr/users.bad'

discardfile '/opt/oracle/sqldr/users.dis'

APPEND

INTO TABLE users

fields terminated by ',' optionally enclosed by '"'

trailing nullcols

( username char(30),

user_id char(30),

password char(30)

)

加载过程很简单：

[oracle@jumper sqldr]$ sqlldr eygle/eygle control=user.ctl

SQL*Loader: Release 9.2.0.4.0 - Production on Mon Mar 19 16:51:35 2007

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Commit point reached - logical record count 9

现在这些数据就已经被加载到数据库中了。

在Oracle 9i中，SQLLDR增加了一个新的参数external_table。

[oracle@jumper sqldr]$ sqlldr |grep external

external_table -- use external table for load; NOT_USED, GENERATE_ONLY, EXECUTE (Default NOT_USED)

通过这个参数的GENERATE_ONLY选项，可以生成完整的外部表创建语句：

[oracle@jumper sqldr]$ sqlldr eygle/eygle control=user.ctl external_table=GENERATE_ONLY

SQL*Loader: Release 9.2.0.4.0 - Production on Mon Mar 19 16:48:22 2007

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

这个操作只是生成了外部表维护语句，并不会真正地加载数据。现在检查user.log文件，这个文件包括了非常详细的内容：

SQL*Loader: Release 9.2.0.4.0 - Production on Mon Mar 19 16:58:03 2007

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

首先是列举控制文件的相关信息：

Control File: user.ctl

Data File: /opt/oracle/sqldr/data.txt

Bad File: /opt/oracle/sqldr/users.bad

Discard File: /opt/oracle/sqldr/users.dis

(Allow all discards)

Number to load: ALL

Number to skip: 0

Errors allowed: 50

Continuation: none specified

Path used: External Table

Table USERS, loaded from every logical record.

Insert option in effect for this table: APPEND

TRAILING NULLCOLS option in effect

Column Name Position Len Term Encl Datatype

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

USERNAME FIRST 30 , O(") CHARACTER

USER_ID NEXT 30 , O(") CHARACTER

PASSWORD NEXT 30 , O(") CHARACTER

接下来是自动生成的创建目录的脚本，目录名称是按规则自动生成的，路径则指向当前数据文件的路径：

CREATE DIRECTORY statements needed for files

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

CREATE DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000 AS '/opt/oracle/sqldr/'

然后是创建外部表的完整语句：

CREATE TABLE statement for external table:

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

CREATE TABLE "SYS_SQLLDR_X_EXT_USERS"

(

USERNAME VARCHAR2(30),

USER_ID NUMBER,

PASSWORD VARCHAR2(30)

)

ORGANIZATION external

(

TYPE oracle_loader

DEFAULT DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000

ACCESS PARAMETERS

(

RECORDS DELIMITED BY NEWLINE CHARACTERSET ZHS16GBK

BADFILE 'SYS_SQLLDR_XT_TMPDIR_00000':'users.bad'

DISCARDFILE 'SYS_SQLLDR_XT_TMPDIR_00000':'users.dis'

LOGFILE 'user.log_xt'

READSIZE 1048576

FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' LDRTRIM

MISSING FIELD VALUES ARE NULL

REJECT ROWS WITH ALL NULL FIELDS

(

USERNAME CHAR(30)

TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',

USER_ID CHAR(30)

TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',

PASSWORD CHAR(30)

TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'

)

)

location

(

'data.txt'

)

)REJECT LIMIT UNLIMITED

下面是加载数据的INSERT语句，可以通过INSERT语句将数据转移到数据库的内部表中：

INSERT statements used to load internal tables:

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

INSERT /*+ append */ INTO USERS

(

USERNAME,

USER_ID,

PASSWORD

)

SELECT

USERNAME,

USER_ID,

PASSWORD

FROM "SYS_SQLLDR_X_EXT_USERS"

最后完成整个过程，外部表和目录可以被删除：

statements to cleanup objects created by previous statements:

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

DROP TABLE "SYS_SQLLDR_X_EXT_USERS"

DROP DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000

Run began on Mon Mar 19 16:58:03 2007

Run ended on Mon Mar 19 16:58:03 2007

Elapsed time was: 00:00:00.11

CPU time was: 00:00:00.05

通过这个例子可以看到，外部表实际上就是通过SQLLDR的接口驱动来完成外部数据访问的，Oracle的外部表实际上是对SQLLDR功能的进一步扩展和增强。