2024年4月28日发(作者：)

ORACLE 借助utl_file使用存储过程将oracle表中的数据导出成文本文件

摘要：在使用数据库的过程中，我们经常会遇到一个问题：如何将数据按照

想要的格式导出到一个文本文件中。当然，我们可以通过编写一些应用程序或通

过第三方的软件工具来解决这个问题，但是其效率和灵活性并不一定是最优的解

决方案。本文就探讨使用数据库管理系统自身的功能来实现以上的需求。本文研

究的实验环境为Oracle 9i数据库系统，操作系统为Sun Solaris 10。

关键词：Oracle数据库；数据导出；数据文件；导出方法

1.引言

数据库已经应用比较普遍，目前

各类系统的开发几乎都离不开数据

库管理系统的支撑，当前占数据库管

理系统市场较大份额的有Oracle、

DB2、SQL Server等。在使用数据库

的过程中，难免会有将数据表中的数

据导出这样的需求，特别是一些企业

应用需要经常性的提取一些业务数

据作为分析决策的参考，而这些提取

数据的需求又具有格式经常变化等

特点，如果单纯依靠编写应用程序就

会显得很麻烦，成本也较大。第三方

工具因为需要另外安装等不方便的

原因，所以也很难满足要求。因此需

要利用数据库自身的工具或功能来

实现数据表数据的提取。

create table test

(

2.本文约定的实验环境

Oracle 9i是由甲骨文（Oracle）

公司出品的一款企业级数据库管理

系统，目前最新的版本是11g，但9i

版本应用比较普遍，主要应用于电

信、公安等行业的数据库管理。

Solaris 10 是由太阳（SUN）公司出

品的一种UNIX操作系统，因为其稳

定性及安全性较好，在一些大型的企

业级应用中也比较广泛。

文中约定实验环境中的

Oracle9i的服务名（SID）为example，

其管理员用户（sys）的口令为

passwd，普通用户的用户名为user，

其口令为userpwd，在user用户方案

下存在一个数据库表，其表结构如

下：

id number(1),

name varchar2(20),

address varchar2(20)

);

向表中插入一些初始化数据，内容如下：

insert into test values(1,'wanglp','sy');

insert into test values(2,'gaoliang','dl');

insert into test values(3,'fuyaxian','as');

insert into test values(4,'wangtong','bx');

insert into test values(5,'gaoqi','dd');

最终实验输出的文本文件的内容及格式如下：

1|wanglp|sy

2|gaoliang|dl

3|fuyaxian|as

4|wangtong|bx

5|gaoqi|dd

3.实验过程

接下来将采用两种方法来实现

数据输出到文本的功能。

方法一：使用Oracle SQL/Plus

的 Spool工具

set heading off

set echo off

set term off

set line 0

set pages 0

set feed off

spool /export/home2/

第一步：建立脚本文件，在磁盘

上建立一个以sql为扩展名的文本文

件（），这里约定存放在

“/export/home2/”路径下编辑内容

如下：

select id||'|'||name||'|'||address as newcloumn from test;

spool off

set heading on

set echo on

set term on

set feed on

第二步：执行脚本，得到输出数据文件。

登录Oracle 的SQL/PLUS工具，并执行脚本文件。

sqlplus user/userpwd@example

SQL>@/export/home2/script

运行成功后，即可在/export/home2/目录下找到文件。

图1 操作示意图

* 说明：方法一比较简单，只要安装了Oracle数据库的SQL/PLUS客户端工具，

就可以在本地客户端或者远程数据库服务器端生成数据文件。

方法二：使用Oracle内置的函数包UTL_FILE

第一步：在SQL/PLUS中使用sys用户登录到数据库

sqlplus user/userpwd@example

SQL>conn sys/passwd@example as sysdba

第二步：设置输出目录

SQL> create or replace directory TMP as '/export/home2/';

第三步：授权user用户对该目录的访问权限

SQL> grant read,write on directory TMP to user;

第四步：使用user用户登录到数据库

SQL> conn user/userpwd;

第五步：建立Oracle数据库程序包及程序包体（或存储过程），这里把程序

包及包体的内容存储到/export/home2/文件中。

--建立程序包

create or replace package wlptest

AS

procedure START_OUT;

end wlptest;

/

--建立程序包体

create or replace package body wlptest

AS

OutputFile UTL__TYPE; --输出文件对象

type struct_records is record(

id number(1),

name varchar2(20),

address varchar2(20)

);

logtemp struct_records;

CURSOR log_cursor IS

select id,name,address from test;

PROCEDURE START_OUT

AS

BEGIN

OutputFile := UTL_('TMP','','a');

DBMS__LINE('***BEGIN TO EXPORT DATA!***');

OPEN log_cursor;

loop

fetch log_cursor into logtemp;

exit when log_cursor%notfound;

UTL_(OutputFile,'%s|%s|%sn',,

,s);

END loop;

CLOSE log_cursor;

DBMS__LINE('***FINISHED EXPORT DATA!***');

UTL_(OutputFile);

UTL_(OutputFile);

END START_OUT;

END wlptest;

/

show errors;

执行创建及编译包及包体

SQL> @/export/home2/

第六步：执行程序包，生成数据文件。

SQL> set serverout on

SQL> exec _OUT;

4.结论

本文所提出的实验方法，使导出

数据表文件的工作变得非常简单，不

需要编写应用程序，也不需要使用第

三方的软件工具，保证了以最高的效

率得到想要的数据导出文件，具有较

强的实际意义。

文中提供的两种导出方法，各有

优点：方法一可以在数据库服务器端

或客户端生成结果文件，方法二虽然

只能在数据库服务器端生成结果文

件，但可以经过进一步的改造完成一

个数据导出小模块，供第三方进行很

方便的调用。我们需要根据具体的环

境和情况来选择合理使用哪种方法。

参考文献

[1]

Thomas Kyte .Expert Oracle Database

Architecture 9i and 10g Programming

Techniques and Solutions

,2006.