oracle 18C 之内联外部表

郑全

Oracle 数据库18c 允许您使用 SELECT 语句中定义的内联外部表访问平面文件中的数据。

　　什么是内联外部表？

　　内联外部表将外部表的定义直接放在SQL语句中，不需要额外在数据字典中创建外部表对象。当使用内联外部表的时候，与创建外部表(CREATE TABLE)相同的语法可以用在一个select语句上。可以在一个字句的 from 关键字后面指定内联外部表。含有内联外部表的查询也可以包含常规的表的关联(joins)，聚合(aggregation)等等。

    构造外部数据

创建目录对象
mkdir /home/oracle/scripts


[oracle@host01 scripts]$ cat sales_2016.txt
region,time_id,amount
EAST,20160101,6000
SOUTH,20160101,4000
WEST,20160101,2467
NORTH,20160101,2600

[oracle@host01 scripts]$ cat sales_2017.txt
region,time_id,amount
EAST,20170101,8000
SOUTH,20170101,2000
WEST,20170101,3400
NORTH,20170101,2000

[oracle@host01 scripts]$ cat sales_2018.txt
region,time_id,amount
EAST,20180101,2100
SOUTH,20180101,1300
WEST,20180101,3800
NORTH,20180101,2120

SYS@PROD4> create directory sales_dir as '/home/oracle/scripts';

Directory created.


WITH inline_ext_tab AS (
  SELECT *
  FROM   EXTERNAL (
           (
            region        varchar2(6)   NOT NULL,
            time_id       DATE          NOT NULL,
            amount        NUMBER(10,2)
           )
         TYPE ORACLE_LOADER
         DEFAULT DIRECTORY sales_dir
         ACCESS PARAMETERS
         (
                FIELDS TERMINATED BY ','
                MISSING FIELD VALUES ARE NULL
                (region,
                 time_id DATE 'yyyy-mm-dd',
                 amount
                 )
         )
         LOCATION ('sales_2016.txt','sales_2017.txt','sales_2018.txt')
         REJECT LIMIT UNLIMITED
      )
)
SELECT region,sum(amount)
FROM   inline_ext_tab
GROUP BY region
ORDER BY 1;

REGION SUM(AMOUNT)
------ -----------
EAST         16100
NORTH         6720
SOUTH         7300
WEST          9667