UTL_FILE

功能描述

UTL_FILE内置包提供了在存储过程或函数中对系统文件进行读写的功能。

注意事项

该功能仅在数据库兼容模式为Oracle时支持（即数据库实例初始化时指定DBCOMPATIBILITY='A'）。

数据类型

UTL_FILE包声明了一种RECORD类型：FILE_TYPE，是UTL_FILE中子程序所使用的文件句柄类型。

在引用文件的时候，要使用一个文件句柄来表示对文件的读或写，通过声明一个类型为FILE_TYPE的变量来接收通过函数FOPEN返回的文件句柄。这个文件句柄将用于随后在文件上的所有操作。

TYPE file_type IS (
   id          BINARY_INTEGER, 
   datatype    BINARY_INTEGER,
   byte_mode   BOOLEAN);

字段描述

• id

  需要处理的文件句柄号的数值。

• datatype

  指示文件是CHAR文件、NCHAR文件还是其他（二进制）文件。

• byte_mode

  指示文件打开后的类型是二进制模式还是文本模式。

子程序

表1 UTL_FILE包支持的子程序

FOPEN

语法格式

UTL_FILE.FOPEN (
   location     IN VARCHAR2,
   filename     IN VARCHAR2,
   open_mode    IN VARCHAR2,
   max_linesize IN BINARY_INTEGER DEFAULT 1024) 
  RETURN FILE_TYPE;

参数说明

• location

  文件的目录位置。必须在该目录对象上授予读权限才能运行FOPEN。

• filename

  文件名，包括扩展名（文件类型），不包括目录路径。如果目录路径作为文件名的一部分给出，FOPEN将忽略它。

• open_mode

  指定打开文件的方式。模式包括:

  • r：读文件（文本）。

  • w：写文件（文本）。

  • a：追加文件（文本），如果文件不存在将会以写（w）模式创建此文件。

  • rb：使用byte（字节）模式读文件。

  • wb：使用byte（字节）模式写文件。

  • ab：使用byte（字节）模式追加文件，如果文件不存在将会以写（wb）模式创建此文件。

• max_linesize

  该文件每行（包括换行字符）的最大字节数，最小值1，最大值32767。如果未指定，默认值为1024。

IS_OPEN

语法格式

UTL_FILE.IS_OPEN (
   file  IN FILE_TYPE)
  RETURN BOOLEAN;

参数说明

file：由FOPEN调用返回的活跃（active）文件句柄。

GET_LINE

语法格式

UTL_FILE.GET_LINE (
   file        IN  FILE_TYPE,
   buffer      OUT VARCHAR2,
   len         IN  PLS_INTEGER DEFAULT NULL);

参数说明

• file

  由FOPEN调用返回的活动文件句柄。

• buffer

  数据缓冲区，用于接收从文件中读取的行。

• len

  从文件中读取的字节数。默认为NULL。如果为NULL，使用max_linesize的值。

GET_NEXTLINE

语法格式

GET_NEXTLINE(
    file utl_file.FILE_TYPE,
    buffer OUT VARCHAR2);

参数说明

• file

  由FOPEN调用返回的活动文件句柄。

• buffer

  数据缓冲区，用于接收从文件中读取的行。

PUT

语法格式

UTL_FILE.PUT (
   file      IN FILE_TYPE,
   buffer    IN VARCHAR2);

参数说明

• file

  FOPEN调用返回的活跃文件句柄。文件必须打开才能写入。

• buffer

  包含要写入文件的文本的缓冲区。

PUT_LINE

语法格式

UTL_FILE.PUT_LINE (
   file      IN FILE_TYPE,
   buffer    IN VARCHAR2,
   autoflush IN BOOLEAN DEFAULT FALSE);

参数说明

• file

  由FOPEN调用返回的活动文件句柄。

• buffer

  包含要写入文件的行的文本缓冲区。

• autoflush

  写（WRITE）操作后将缓冲区刷新到磁盘。

NEW_LINE

语法格式

UTL_FILE.NEW_LINE (
   file     IN FILE_TYPE,
   lines    IN BINARY_INTEGER := 1);

参数说明

• file

  由FOPEN调用返回的活跃文件句柄。

• lines

  写入文件的行终止符的数目，默认为1。

PUTF

语法格式

UTL_FILE.PUTF (
   file    IN FILE_TYPE,
   format  IN VARCHAR2,
   [arg1 VARCHAR2 DEFAULT NULL,
   arg2 VARCHAR2 DEFAULT NULL,
   arg3 VARCHAR2 DEFAULT NULL,
   arg4 VARCHAR2 DEFAULT NULL,
   arg5 VARCHAR2 DEFAULT NULL]);

参数说明

• file

  由FOPEN调用返回的活跃文件句柄。

• format

  格式字符串，可以包含文本以及格式化字符\n和%s

• arg1…arg5

  操作参数字符串。

FFLUSH

语法格式

UTL_FILE.FFLUSH (
   file  IN FILE_TYPE);

参数说明

file：由FOPEN调用返回的活跃文件句柄。

FCLOSE

语法格式

UTL_FILE.FCLOSE (
   file IN OUT FILE_TYPE);

参数说明

file：由FOPEN调用返回的活跃文件句柄。

FCLOSE_ALL

语法格式

UTL_FILE.FCLOSE_ALL;

FREMOVE

语法格式

UTL_FILE.FREMOVE (
   location IN VARCHAR2,
   filename IN VARCHAR2);

参数说明

• location

  文件的目录位置。

• filename

  待删除的文件名。

FRENAME

语法格式

UTL_FILE.FRENAME (
   src_location     IN   VARCHAR2,
   src_filename     IN   VARCHAR2, 
   dest_location    IN   VARCHAR2,
   dest_filename    IN   VARCHAR2,
   overwrite        IN   BOOLEAN DEFAULT FALSE);

参数说明

• src_location

  源文件的目录位置。

• src_filename

  要重命名的源文件。

• dest_location

  目标文件的目标目录。

• dest_filename

  文件的新名字。

• overwrite

  默认为FALSE，不覆盖。如果目标目录中存在文件，可以使用overwrite参数指定是否覆盖该文件。

FCOPY

语法格式

UTL_FILE.FCOPY (
   src_location    IN VARCHAR2,
   src_filename    IN VARCHAR2,
   dest_location   IN VARCHAR2,
   dest_filename   IN VARCHAR2,
   start_line      IN BINARY_INTEGER DEFAULT 1,
   end_line        IN BINARY_INTEGER DEFAULT NULL);

参数说明

• src_location

  源文件的目录位置。

• src_filename

  要复制的源文件。

• dest_location

  创建目标文件的目标目录。

• dest_filename

  从源文件创建的目标文件。

• start_line

  开始复制的行号。第一行的默认值是1。

• end_line

  停止复制的行号。默认值为NULL，表示文件结束。

FGETATTR

语法格式

UTL_FILE.FGETATTR(
   location     IN VARCHAR2, 
   filename     IN VARCHAR2, 
   fexists      OUT BOOLEAN, 
   file_length  OUT NUMBER, 
   block_size   OUT BINARY_INTEGER);

参数说明

• location

  源文件的目录位置。

• filename

  要检查的文件名。

• fexists

  一个布尔值，表示文件是否存在。

• file_length

  以字节为单位的文件长度。如果文件不存在，则为NULL。

• block_size

  以字节为单位的文件系统块大小。如果文件不存在，则为NULL。

TMPDIR

语法格式

FUNCTION TMPDIR() RETURN VARCHAR2 IS
     BEGIN
     RETURN pg_catalog.tmpdir();

示例

调用UTL_FILE包中的函数功能对系统文件进行读写。

1、创建测试文件，在操作系统中执行如下命令。

echo "111aaa
222bbb
333ccc
444ddd
555eee
666fff
777ggg
888hhh
999iii
101010jjj
111111kkk"  > /tmp/test.txt

2、使用vsql工具连接至客户端。

vsql -d vastbase -p 5432 -r

3、执行如下SQL语句，用来指定目录位置。

insert into utl_file_dir values ('/tmp');

4、在数据库下执行文件复制。（将一个文件中的内容拷贝到另一个文件中。）

select utl_file.fcopy('/tmp', 'test.txt', '/tmp', 'test_cp.txt',3,6);

5、退出vsql程序。

\q

6、在shell下执行如下语句，查看复制的文件,。

cat /tmp/test_cp.txt

返回结果如下，则表示文件复制成功：

333ccc
444ddd
555eee
666fff

7、使用vsql工具连接至db_oracle数据库

vsql -d db_oracle -p 5432 -r

8、使用FGETATTR函数查看文件属性。

do language plpgsql $$
declare
ex boolean;
flen int;
bsize int;
begin
-- utl_file.fgetattr('/tmp'::text, 'test.txt'::text, ex, flen, bsize);
select * from utl_file.fgetattr('/tmp'::text, 'test.txt'::text) into ex,flen,bsize;
IF ex THEN
raise notice 'File Exists';
ELSE
raise notice 'File Does Not Exist' ;
END IF;
raise notice 'FileLength: %' , flen;
raise notice 'BlockSize: %',bsize;
end;
$$;

返回结果为：

NOTICE:  File Exists
NOTICE:  FileLength: 83
NOTICE:  BlockSize: 65536
ANONYMOUS BLOCK EXECUTE

9、调用存储过程对文件进行读写。

do language plpgsql $$
declare
vInHandle utl_file.file_type;
vOutHandle utl_file.file_type;
vNewLine VARCHAR2(250);
begin
vInHandle := utl_file.fopen('/tmp', 'test.txt','R');
LOOP
BEGIN
select * from utl_file.get_line(vInHandle)into vNewLine ;
raise notice '%', vNewLine;
EXCEPTION
WHEN OTHERS THEN
EXIT;
END;
END LOOP;
vOutHandle := utl_file.fopen('/tmp', 'test_cp.txt', 'w');
perform utl_file.put_line(vOutHandle, 'hello lsxy');
utl_file.fflush(vOutHandle);
utl_file.fclose_all();
end;
$$;

返回结果为：

NOTICE:  111aaa
NOTICE:  222bbb
NOTICE:  333ccc
NOTICE:  444ddd
NOTICE:  555eee
NOTICE:  666fff
NOTICE:  777ggg
NOTICE:  888hhh
NOTICE:  999iii
NOTICE:  101010jjj
NOTICE:  111111kkk
ANONYMOUS BLOCK EXECUTE

10、退出vsql程序。

\q

11、在shell中执行如下命令，查看文件内容。

cat /tmp/test_cp.txt

返回结果如下：

hello lsxy