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