VastbaseG100

基于openGauss内核开发的企业级关系型数据库。

Menu

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