Oracle（61）什么是外部表（External Table）？

外部表（External Table）是Oracle数据库中的一种特殊表类型，用于访问存储在外部文件系统中的数据，而不需要将数据实际加载到数据库内部。外部表的主要优势在于允许数据库用户在不移动或复制数据的情况下，直接查询和处理外部数据文件。

外部表的用途

• 数据加载：从外部文件加载数据到数据库。
• 数据卸载：将数据库数据导出到外部文件。
• 数据集成：将外部数据与数据库中的数据集成，进行联合查询或分析。

工作原理

外部表依赖于Oracle的外部表功能和Oracle Loader (SQL*Loader)技术。定义外部表时，需要指定文件的位置和格式信息。

示例代码

以下是如何创建和使用外部表的详细示例。

1. 创建目录对象

首先，需要创建一个目录对象来指向外部文件所在的目录。这需要数据库管理员权限。

CREATE DIRECTORY ext_tab_dir AS '/path/to/external/files';
GRANT READ, WRITE ON DIRECTORY ext_tab_dir TO your_user;

2. 准备外部文件

假设我们有一个名为employees.csv的外部文件，其内容如下：

1,Alice,HR,5000
2,Bob,IT,6000
3,Charlie,IT,7000
4,David,Finance,8000
5,Eva,HR,5500

3. 创建外部表

使用CREATE TABLE语句创建外部表，并指定外部文件的格式和位置。

CREATE TABLE external_employees (
    emp_id NUMBER,
    emp_name VARCHAR2(100),
    department VARCHAR2(50),
    salary NUMBER
)
ORGANIZATION EXTERNAL (
    TYPE ORACLE_LOADER
    DEFAULT DIRECTORY ext_tab_dir
    ACCESS PARAMETERS (
        RECORDS DELIMITED BY NEWLINE
        FIELDS TERMINATED BY ','
        MISSING FIELD VALUES ARE NULL
        (
            emp_id NUMBER,
            emp_name CHAR(100),
            department CHAR(50),
            salary NUMBER
        )
    )
    LOCATION ('employees.csv')
)
REJECT LIMIT UNLIMITED;

在这个示例中：

• ORGANIZATION EXTERNAL指定表是一个外部表。
• TYPE ORACLE_LOADER指定使用Oracle Loader加载数据。
• DEFAULT DIRECTORY指定外部文件所在的目录。
• ACCESS PARAMETERS提供了外部文件的格式信息。
• LOCATION指定外部文件的名称。

4. 查询外部表

创建外部表后，可以像查询普通表一样查询外部表。

SELECT * FROM external_employees;

输出示例：

EMP_ID | EMP_NAME | DEPARTMENT | SALARY
-------|----------|------------|-------
1      | Alice    | HR         | 5000
2      | Bob      | IT         | 6000
3      | Charlie  | IT         | 7000
4      | David    | Finance    | 8000
5      | Eva      | HR         | 5500

5. 将外部表数据加载到内部表

可以将外部表的数据加载到数据库内部表中，以便进一步处理。

CREATE TABLE employees AS SELECT * FROM external_employees;

总结

外部表（External Table）是Oracle数据库中用于访问存储在外部文件系统中的数据的一种特殊表类型。通过外部表，用户可以在不将数据实际加载到数据库内部的情况下，直接查询和处理外部数据文件。外部表使用Oracle Loader技术来定义外部文件的位置和格式，并允许用户像查询普通表一样查询外部数据。

示例代码总结

创建目录对象

CREATE DIRECTORY ext_tab_dir AS '/path/to/external/files';
GRANT READ, WRITE ON DIRECTORY ext_tab_dir TO your_user;

创建外部表

CREATE TABLE external_employees (
    emp_id NUMBER,
    emp_name VARCHAR2(100),
    department VARCHAR2(50),
    salary NUMBER
)
ORGANIZATION EXTERNAL (
    TYPE ORACLE_LOADER
    DEFAULT DIRECTORY ext_tab_dir
    ACCESS PARAMETERS (
        RECORDS DELIMITED BY NEWLINE
        FIELDS TERMINATED BY ','
        MISSING FIELD VALUES ARE NULL
        (
            emp_id NUMBER,
            emp_name CHAR(100),
            department CHAR(50),
            salary NUMBER
        )
    )
    LOCATION ('employees.csv')
)
REJECT LIMIT UNLIMITED;

查询外部表

SELECT * FROM external_employees;

将外部表数据加载到内部表

CREATE TABLE employees AS SELECT * FROM external_employees;