UTL_FILE

功能描述

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

注意事项

该功能仅在数据库兼容模式为Oracle时能够使用（即创建DB时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	打开一个文件用于输入或输出。
IS_OPEN	检查一个文件句柄是否指向一个打开的文件。
GET_LINE	从一个打开的文件中读取指定的一行文本。
GET_NEXTLINE	获得下一行。
PUT	将一个字符串写入到文件。
PUT_LINE	在文件中写入一行，在行的末尾写入一个行终结符。
NEW_LINE	在文件末尾写入一个行终结符。
PUTF	格式化输出过程。
FFLUSH	将文件缓存刷到物理写入。
FCLOSE	关闭一个文件。
FCLOSE_ALL	关闭所有打开的文件。
FREMOVE	删除磁盘上的文件。
FRENAME	重命名一个已经存在的文件。
FCOPY	将一个文件中的内容拷贝到另一个文件中。
FGETATTR	获取文件属性。
TMPDIR	获得临时目录路径。
PUT_RAW	接受作为输入的RAW类型数值，并将该值写入输出缓冲区。

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();

PUT_RAW

语法格式

UTL_FILE.PUT_RAW (
   file          IN    UTL_FILE.FILE_TYPE,
   buffer        IN    RAW, 
   autoflush     IN    BOOLEAN DEFAULT FALSE);

参数说明

file

UTL_FILE.FILE_TYPE，打开文件的句柄。
buffer

写入缓冲区的RAW数据。
autoflush

如果为TRUE，会在将值写入输出缓冲区后刷新缓存；默认为FALSE。

示例

示例1： 调用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、创建并切换至兼容模式为Oracle的数据库db_oracle下。

CREATE DATABASE db_oracle dbcompatibility='A';    
\c db_oracle

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

insert into utl_file_dir values ('/tmp');

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

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

6、退出vsql程序。

\q

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

cat /tmp/test_cp.txt

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

333ccc
444ddd
555eee
666fff

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

vsql -d db_oracle -p 5432 -r

9、使用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

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

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

11、退出vsql程序。

\q

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

cat /tmp/test_cp.txt

返回结果如下：

hello lsxy

示例2： 调用UTL_FILE.PUT_RAW函数，并将一个文件写入另一个文件。

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

vsql -d vastbase -p 5432 -r

2、注册访问目录：获得临时目录路径 /tmp 并插入到utl_file_dir表中。（后续调用utl_file的接口时会判断该表中是否有对应路径的记录。）

INSERT INTO utl_file_dir(dir) VALUES(utl_file.tmpdir());

3、创建写入文件。

echo "111aaa
4月19日，第二十九届中国广播电视网络展览会（CCBN 2023）
333ccc
444ddd
666fff
777ggg
888hhh" > /tmp/f1_1143067.txt

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

vsql -d vastbase -p 5432 -r

5、创建并切换至兼容模式为Oracle的数据库db_oracle下。

CREATE DATABASE db_oracle dbcompatibility='A';    
\c db_oracle

6、调用UTL_FILE.PUT_RAW接口，将步骤3创建的文件f1_1143067.txt写入f2_1143067.txt中。

DECLARE
file1 UTL_FILE.FILE_TYPE;
file2 UTL_FILE.FILE_TYPE;
buffer RAW(32767);
info varchar;
name varchar:='lio';
BEGIN
file1:= UTL_FILE.FOPEN(utl_file.tmpdir(), 'f1_1143067.txt', 'R');
file2:= UTL_FILE.FOPEN(utl_file.tmpdir(), 'f2_1143067.txt', 'w', 32767);
--get_nextline获取信息
select * from utl_file.get_nextline(file1) into info;
--UTL_FILE.PUT_RAW写入文件
buffer:=UTL_RAW.CAST_TO_RAW(info);
UTL_FILE.PUT_RAW(file2, buffer);
--重复获取
info:=utl_file.get_nextline(file1);
buffer:=UTL_RAW.CAST_TO_RAW(info);
UTL_FILE.PUT_RAW(file2, buffer);
info:=utl_file.get_nextline(file1);
buffer:=UTL_RAW.CAST_TO_RAW(info);
UTL_FILE.PUT_RAW(file2, buffer);
info:=utl_file.get_nextline(file1);
buffer:=UTL_RAW.CAST_TO_RAW(info);
UTL_FILE.PUT_RAW(file2, buffer);
--PUTF
perform utl_file.PUTF(file2, '\nHello, world! My name is %s.\n',name);
perform utl_file.PUT_line(file2, 'Hello, world!');
--关闭文件句柄
UTL_FILE.FCLOSE(file1);
UTL_FILE.FCLOSE(file2);
END;
/

7、退出vsql程序。

\q

8、查看f2_1143067.txt文件。

cat /tmp/f2_1143067.txt;

查看结果如下：

111aaa4月19日，第二十九届中国广播电视网络展览会（CCBN 2023）333ccc444ddd
Hello, world! My name is lio.
Hello, world!

VastbaseG100

UTL_FILE

功能描述

注意事项

数据类型

子程序

FOPEN

IS_OPEN

GET_LINE

GET_NEXTLINE

PUT

PUT_LINE

NEW_LINE

PUTF

FFLUSH

FCLOSE

FCLOSE_ALL

FREMOVE

FRENAME

FCOPY

FGETATTR

TMPDIR

PUT_RAW

示例

弹框头部