Oracle数据库基础学习13-外部表

外部表是指存储在外部文件中的数据，Oracle可以通过创建外部表以只读的方式来查询文件数据的内容，这对于文件数据的分析非常有用，而且还可以轻松的将外部表的内容插入到数据库中

注意：Oracle只能处理位于Oracle服务器上的外部文件，它依赖于Oracle的目录对象和 ORACLE_LOADER 来加载外部文件中的数据

实际上创建外部表只是在数据字典中添加了外部表的元数据信息，并没有在数据库中为外部文件创建数据表。Oracle通过访问驱动程序来读取外部表中的数据。Oracle提供了两种访问驱动，默认使用 ORACLE_LOADER 作为加载并访问外部文件的驱动。

下面是一张员工表的基本员工信息，保存为CSV文件，字段内容以逗号分隔

360,Json,CLERK,121,2008-09-12,3000,0,50,jjanus
361,Rose,SALES,145,2009-02-05,8000,0.1,80,mjasper
362,Marry,AD_REP,200,2010-10-12,5500,0,10,bstarr
363,Henny,ACCOUNT,145,2008-11-12,9000,0.15,90,aalda

接下来将使用如下几个步骤来创建一个使用 ORACLE_LOADER 作为访问驱动的外部表

1、要能访问服务器上的操作系统中的文件，首先必须在数据库中建立一个指向该数据文件所在位置的目录对象，通过目录对象访问相应的操作系统文件。假设员工表 empxt1.csv 文件位于 /home/oracle/flatfiles/data 文件夹中，可以使用如下的代码创建一个Oracle目录对象

create or replace directory admin_dat_dir as '/home/oracle/flatfiles/data';

由于外部表在加载过程中，还可以产生日志文件和未成功导入的错误文件，因此也可以分别为这几个文件将存放的位置分别创建服务器目录

create or replace directory admin_log_dir as '/home/oracle/flatfiles/log';

create or replace directory admin_bad_dir as '/home/oracle/flatfiles/bad';

2、创建目录对象之后，接下来开始创建一个外部表，外部表使用 ORGANIZATION EXTERNAL 子句指定其为外部表，外部表的创建分为两部分：一部分是描述列的数据类型；另一部分是描述操作系统文件数据于表列的对应关系

create table EXT_EMP
(
	EMPLOYEE_ID NUMBER(4),  --员工编号
	EMP_NAME VARCHAR2(20),  --员工英文名
	JOB_ID VARCHAR2(30),  --职位简称
	MANAGER_ID NUMBER(4),  --经理编号
	HIRE_DATE DATE,  --入职日期
	SALARY NUMBER(8,2),  --入职工资
	COMMISSION_PCT NUMBER(2,2),  --如果是销售人员的提成率
	DEPARTMENT_ID NUMBER(4),  --所在部门编号
	EMAIL VARCHAR2(25)  --邮件地址
)
ORGANIZATION EXTERNAL  --表示创建一个外部表
(
	TYPE ORACLE_LOADER  --使用 ORACLE_LOADER 驱动
	default directory admin_dat_dir  --数据所在的服务器目录
	ACCESS PARAMETERS  --访问参数
	(
		records delimited by newline  --每条记录通过换行符区分
		characterset UTF8  --读取的服务器端字符集
		bdafile admin_bad_dir:'empxt%a_%p.bad'  --错误文件的存放路径
		logfile admin_log_dir:'empxt%a_%p.log'  --日志文件的存放路径
		fields terminated by ','  --字段之间使用逗号分隔
		missing field values are null  --不存在的字段值用NULL
		(
			EMPLOYEE_ID,EMP_NAME,JOB_ID,MANAGER_ID,
			HIRE_DATE CHAR date_format date mask "yyyy-mm-dd",
			SALARY,COMMISSION_PCT,DEPARTMENT_ID,EMAIL
		)
	)
	LOCATION ('empxt1.csv', 'empxt2.csv')  --要加载的外部文件列表
)
REJECT LIMIT UNLIMITED;  --不限制任何数据错误的次数

下面来分析以下这个外部表创建代码的实现过程

1. 可以看到外部表的创建也是使用 CREATE TABLE 语句，后面跟要创建的表结构字段
2. 在定义了表结构之后，使用 ORGANIZATION EXTERNAL 开始外部表的配置过程，同时也表示该表是一个来自外部文件的表，该表本身不包含任何数据，它只是创建到外部文件引用的元数据
3. TYPE ORACLE_LOADER 用来指定将使用 ORACLE_LOADER 访问驱动来访问数据库，因此 ACCESS PARAMETERS 参数都会针对这个访问驱动进行配置
4. default directory 用来指定外部文件所在的缺省路径
5. 在 ACCESS PARAMETERS 内部的 records delimited by newline 指定外部文件的换行方式是换行符
6. characterset 指定字符集
7. bdafile 用来指定如果导入失败是=时，将要存储的错误文件的位置，一般以 .bad 作为扩展名
8. logfile 用来指定加载过程中的日志记录，通过日志可以了解到外部表的加载成功与否，并可以查看详细的失败信息
9. fields terminated by 用来指定字段的分隔符，这里使用逗号分隔，因此在单引号中直接输入了 ， 号。其他的特殊符号使用十六进制表示，比如 TAB键使用 0X'09' 表示。它们通过 0X + “十六进制” 的表示方法。
10. 接下来指定要加载的字段列表，在这里可以单独指定外部文件的字段类型和 TERMINATED 的分隔方式
11. missing field values are null 用来指定当某些字段值为空时，将其设为 NULL
12. LOCATION 用来指定外部文件在服务器目录中的位置，如果有多个文件，只要它们的基本结构相同，都可以进行导入，多个文件之间以逗号分隔
13. REJECT LIMIT UNLIMITED 在创建外部表时最后加入 LIMIT 子句，表示可以允许错误的发生个数，默认值为0.设定为 UNLIMITED 则错误不受限制

在成功的创建了外部表之后，就可以像查询普通表一样查询外部文件，还可以将外部表中的数据插入到一个新表，实现外部文件的导入工作。如果外部表数据加载失败，可以查询在 logfile 和 badfile 中指定日志和错误文件名来查看详细的错误信息。外部表中的数据是只读的，不能对外部表进行DML操作。