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 | 获得临时目录路径。 |
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、创建并切换至兼容模式为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