Oracle导出文本文件的三种方法(spool,UTL_FILE,sqluldr2)

一、spool

1. spool的基本语法是

spool file_name
sql_command;
spool off

2.其中file_name指需要导出的文件名，可以是全路径也可以是部分路径，sql_command为需要执行的sql语句。

1. 运行示例如下：

spool D:\test.txt                      /* 指定文件名 */
SELECT empno,ename,job,sal             /* 执行查询 */
FROM emp
WHERE deptno = 20;
spool off

二、UTL_FILE

1. UTL_FILE是一种文件操作工具，可以用来读写文本文件，需要在数据库中创建一个directory来指定文本文件的路径。

2. 创建directory的语法为：

CREATE DIRECTORY dir_name AS 'path';

其中dir_name为自定义的directory名，path为文件路径。

1. 需要注意的是，directory创建完成后需要给其授权。

授权的语法为：

GRANT READ, WRITE ON DIRECTORY dir_name TO user_name;

其中user_name为被授权的用户。

1. UTL_FILE的基本操作流程为：

1.在PL/SQL块中使用DIRHANDLE类型的变量打开文件；
2.使用PUT_LINE函数写入内容；
3.使用FCLOSE函数关闭文件。

1. 运行示例如下：

CREATE DIRECTORY my_dir AS 'D:\';     /* 创建directory */
GRANT READ, WRITE ON DIRECTORY my_dir TO scott;  /* 授权 */

DECLARE
   file_handle UTL_FILE.FILE_TYPE;
BEGIN
   file_handle := UTL_FILE.FOPEN('MY_DIR', 'test', 'w');
   UTL_FILE.PUT_LINE(file_handle, 'This is a test.');
   UTL_FILE.FCLOSE(file_handle);
END;

三、sqluldr2

1. sqluldr2是Oracle提供的一个导出工具，可以将数据导出为文本文件或二进制文件。

2. 使用sqluldr2需要先安装，安装方式如下：

SQL> CONNECT / AS SYSDBA
SQL> @?/rdbms/admin/utluldr.sql
SQL> @?/rdbms/admin/utluldr.sql {parameter}
SQL> QUIT

1. sqluldr2的基本命令如下：

sqluldr2 [userid=<username>/<password>] control=<filename>.ctl log=<filename>.log

其中username和password为数据库用户名和密码，control为控制文件名，log为日志文件名。

1. 控制文件的格式为：

00010 LOAD DATA
00020 INFILE 'data.txt'
00030 BADFILE 'data.bad'
00040 DISCARDFILE 'data.dis'
00050 APPEND
00060 INTO TABLE emp
00070 ( empno INTEGER EXTERNAL(4),
00080 ename CHAR(10),
00090 job CHAR(9),
00100 mgr INTEGER EXTERNAL(4),
00110 hiredate DATE 'YYYY-MM-DD',
00120 sal DECIMAL(7,2),
00130 comm DECIMAL(7,2),
00140 deptno INTEGER EXTERNAL(2)
00150 )

其中INFILE指定需要导出的文件名，APPEND为数据导入方式，INTO TABLE指定目标表名和列信息。

1. 运行示例如下：

sqluldr2 userid=scott/tiger control=my_control.ctl log=my_log.log

其中my_control.ctl指示控制文件的路径，my_log.log指示日志文件的路径。