用Oracle SQL实现文件访问

Oracle9i有一个最引人注目的优点，这就是通过Oracle SQL访问非Oracle文件的能力。这一新功能被称做外部表（external tables），它对某些系统具有非常重要的作用，可以令非数据库应用程序利用外部文件，而且让这些文件被Oracle视作普通的数据表。

利用外部表即可定义服务器上几乎任何普通文件的结构，同时使得Oracle把这些文件看作真正的数据表，如图A所示。

图A

正如你所看到的那样，Oracle让数据库程序采用utl_file实用工具写普通文件。结合外部表的读取能力，这种新型拓扑不再需要所有的Oracle数据驻留在Oracle表内，也不需要为Oracle打开新的应用程序。下面我们就深入了解这种新特性的工作原理。

定义外部表

假设你要求Oracle引用以下逗号分隔的普通文本文件。
 
7369,SMITH,CLERK,7902,17-DEC-80,800,20
7499,ALLEN,SALESMAN,7698,20-FEB-81,1600,300,30
7521,WARD,SALESMAN,7698,22-FEB-81,1250,500,30
7566,JONES,MANAGER,7839,02-APR-81,2975,,20
7654,MARTIN,SALESMAN,7698,28-SEP-81,1250,1400,30
7698,BLAKE,MANAGER,7839,01-MAY-81,2850,,30
7782,CLARK,MANAGER,7839,09-JUN-81,2450,,10
7788,SCOTT,ANALYST,7566,19-APR-87,3000,,20
7839,KING,PRESIDENT,,17-NOV-81,5000,,10
7844,TURNER,SALESMAN,7698,08-SEP-81,1500,0,30
7876,ADAMS,CLERK,7788,23-MAY-87,1100,,20

以上文件包含一些职员信息：

• 职员工号
• 姓名
• 职务说明
• 经理的工号
• 参加工作日期
• 薪水
• 任命
• 部门

那么该如何给Oracle定义这一文件呢？首先，我们必须在数据字典中创建一个Oracle目录入口，而数据字典则指向以上文件所驻留的Windows目录。在这个例子中我们把目录命名为testdir并指向c:docspubsdbqueries目录：
 
SQL> create directory testdir as ‘c:docspubsdbqueries’;
 
Directory Created.

现在目录创建完毕，我们可以给Oracle定义外部表的结构了。代码如程序清单A所示。

从语法上看，我们对外部表列的定义方式同内部Oracle表的定义方式是一样的。外部定义采用有住址的外部子句，如表A所示。

表A

逗号分隔文件的外部定义

外部表定义好后就可以用SQL对外部表运行报告了，其工作方式就好象驻留在数据库内的普通数据表。查询示例如程序清单B所示，注意高级ROLLUP参数的复杂用法总计了部门和职位的薪水。结果如程序清单C所示。

外部表的局限性

由于外部表是一种崭新的特性，Oracle还不能完美地充分利用这一功能。在Oracle9i中这一特性还具有若干局限性，主要包括：

• 不支持DML。外部表都是只读的，但是原始数据可以用任何文本编辑器进行编辑。
• 高强度查询的响应较为迟钝。外部表具有一定的处理负载，不太适合大型表的应用。

小结

通过Oracle访问普通文件具有很大用途。接下来的文章里我们将教你为Oracle定义电子数据表（spreadsheet）。这一技术对那些用户可以控制桌面电子表内系统范围参数的商用系统非常有用。