Oracle内建UTL_FILE使用

最新推荐文章于 2024-05-18 09:31:56 发布

pucheung

最新推荐文章于 2024-05-18 09:31:56 发布

阅读量578

点赞数

分类专栏： sql 文章标签： oracle UTL_FILE

sql 专栏收录该内容

13 篇文章 0 订阅

订阅专栏

包UTL_FILE提供了在操作系统层面上对文件系统中文件的读写功能。非超级用户在使用包UTL_FILE中任何函数或存储过程前必须由超级用户授予在这个包上的EXECUTE权限。例如：我们使用下列命令对用户mary进行授权：

GRANT EXECUTE ON PACKAGE SYS.UTL_FILE TOmary;

如果使用包UTL_FILE中的函数和存储过程访问文件，那么操作系统中的用户enterprisedb必须在要访问的目录和文件上有相应的读写权限。如果没有相应权限的话，在执行包UTL_FILE中函数或存储过程的时候，就会产生异常。

在引用文件的时候，要使用到一个文件句柄，来表示对文件的读或写。文件句柄是通过包UTL_FILE中名称为UTL_FILE.FILE_TYPE的公有变量来定义的。我们必须声明一个类型为FILE_TYPE的变量来接收通过函数FOPEN返回的文件句柄。这个文件句柄将用于随后在文件上的所有操作。

对于文件系统上目录的引用是通过使用目录名称，或者由CREATEDIRECTORY命令为目录分配的化名来实现的。

UTL_FILE常用方法：

FOPEN
IS_OPEN
GET_LINE
PUT
NEW_LINE
PUT_LINE
PUTF
FFLUSH
FCLOSE
FCLOSE_ALL

FRENAME

FREMOVE

FFLUSH

FCOPY

UTL_FILE.FOPEN 用法
FOPEN会打开指定文件并返回一个文件句柄用于操作文件。

适用范围：

所有PL/SQL版本，Oracle 8.0版及以上。

函数原型：

FUNCTION UTL_FILE.FOPEN(     FUNCTION UTL_FILE.FOPEN (
   location    INVARCHAR2,     location    IN VARCHAR2,
   filename    INVARCHAR2,     filename    IN VARCHAR2,
   open_mode    INVARCHAR2)     open_mode    INVARCHAR2,
RETURNfile_type;                max_linesize IN BINARY_INTEGER)
                               RETURN file_type;

参数
  location   文件地址其中的location并不能简单的指定为'D:/temp'等路径，要建立一个DIRECTORY变量并付给权限
  filename   文件名
  openmode   打开文件的模式（参见下面说明）

              3种文件打开模式：
              R 只读模式。一般配合UTL_FILE的GET_LINE来读文件。
              W 写（替换）模式。文件的所有行会被删除。

PUT,PUT_LINE, NEW_LINE, PUTF和FFLUSH都可使用
A 写（附加）模式。原文件的所有行会被保留。在最末尾行附加新行。

PUT, PUT_LINE, NEW_LINE, PUTF和FFLUSH都可使用
max_linesize 文件每行最大的字符数，包括换行符。最小为1，最大为32767。

打开文件时注意以下几点：
  *  文件路径和文件名合起来必须表示操作系统中一个合法的文件。
  *  文件路径必须存在并可访问；FOPEN并不会新建一个文件夹。
  *  如果你想打开文件进行读操作，文件必须存在；如果你想打开文件进行写操作，文件不存在时，会新建一个文件。
  *  如果你想打开文件进行附加操作，文件必须存在。A模式不同于W模式。文件不存在时，会抛出INVALID_OPERATION异常。

  *   FOPEN 会抛出以下异常
           UTL_FILE.INVALID_MODE
           UTL_FILE.INVALID_OPERATION
           UTL_FILE.INVALID_PATH
           UTL_FILE.INVALID_MAXLINESIZE

UTL_FILE.IS_OPEN用法
如果文件句柄指定的文件已打开，返回TRUE，否则FALSE

函数原型：

FUNCTIONUTL_FILE.IS_OPEN (file IN UTL_FILE.FILE_TYPE) RETURN BOOLEAN;

UTL_FILE.GET_LINE用法

UTL_FILE只提供一个方法去读取数据：GET_LINE 。读取指定文件的一行到提供的缓存。

函数原型：
PROCEDUREUTL_FILE.GET_LINE (file IN UTL_FILE.FILE_TYPE, buffer OUT VARCHAR2);

参数说明：

file 由FOPEN返回的文件句柄
buffer 读取的一行数据的存放缓存

buffer必须足够大。否则，会抛出VALUE_ERROR异常。行终止符不会被传进buffer。

异常：
    NO_DATA_FOUND
    VALUE_ERROR
    UTL_FILE.INVALID_FILEHANDLE
    UTL_FILE.INVALID_OPERATION
    UTL_FILE.READ_ERROR

UTL_FILE.PUT用法
在当前行输出数据

函数原型：
PROCEDUREUTL_FILE.PUT (file IN UTL_FILE.FILE_TYPE, buffer OUT VARCHAR2);

参数说明：
file 由FOPEN返回的文件句柄
buffer 包含要写入文件的数据缓存；Oracle8.0.3及以上最大允许32kB，早期版本只有1023B。

UTL_FILE.PUT输出数据时不会附加行终止符。

异常：
   UTL_FILE.INVALID_FILEHANDLE
   UTL_FILE.INVALID_OPERATION
   UTL_FILE.WRITE_ERROR

UTL_FILE.NEW_LINE
在当前位置输出新行或行终止符，必须使用NEW_LINE来结束当前行，或者使用PUT_LINE输出带有行终止符的完整行数据。

函数原型：

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

参数说明：
file 由FOPEN返回的文件句柄
lines 要插入的行数

注意：

   如果不指定lines参数，NEW_LINE会使用默认值1，在当前行尾换行。如果要插入一个空白行，可以使用以下语句：
     UTL_FILE.NEW_LINE (my_file, 2);
   如果lines参数为0或负数，什么都不会写入文件。

异常：

    VALUE_ERROR
   UTL_FILE.INVALID_FILEHANDLE
   UTL_FILE.INVALID_OPERATION
   UTL_FILE.WRITE_ERROR
例子：
如果要在UTL_FILE.PUT后立刻换行，可以如下例所示：
PROCEDURE add_line (file_in IN UTL_FILE.FILE_TYPE, line_in INVARCHAR2)
IS
BEGIN
   UTL_FILE.PUT (file_in,line_in);
   UTL_FILE.NEW_LINE(file_in);
END;

UTL_FILE.PUT_LINE
输出一个字符串以及一个与系统有关的行终止符

函数原型：
PROCEDURE UTL_FILE.PUT_LINE(file IN UTL_FILE.FILE_TYPE, buffer IN VARCHAR2);

参数说明：
file 由FOPEN返回的文件句柄
buffer 包含要写入文件的数据缓存；Oracle8.0.3及以上最大允许32kB，早期版本只有1023B
注意：

  在调用UTL_FILE.PUT_LINE前，必须先打开文件。
异常：
  UTL_FILE.INVALID_FILEHANDLE
  UTL_FILE.INVALID_OPERATION
   UTL_FILE.WRITE_ERROR

例子：
这里利用UTL_FILE.PUT_LINE从表emp读取数据到文件：
PROCEDURE emp2file
IS
   fileIDUTL_FILE.FILE_TYPE;
BEGIN
   fileID := UTL_FILE.FOPEN('/tmp', 'emp.dat', 'W');


   FOR emprec IN (SELECT * FROMemp)
   LOOP
     UTL_FILE.PUT_LINE
        (TO_CHAR (emprec.empno) || ',' ||
         emprec.ename || ',' ||
         ...
         TO_CHAR (emprec.deptno));
   END LOOP;

   UTL_FILE.FCLOSE(fileID);
END;
PUT_LINE相当于PUT后加上NEW_LINE；也相当于PUTF的格式串"%s\n"。

UTL_FILE.PUTF
以一个模版样式输出至多5个字符串，类似C中的printf

函数原型：

PROCEDURE UTL_FILE.PUTF
    (file INFILE_TYPE
    ,format INVARCHAR2
    ,arg1 INVARCHAR2 DEFAULT NULL
    ,arg2 INVARCHAR2 DEFAULT NULL
    ,arg3 INVARCHAR2 DEFAULT NULL
    ,arg4 INVARCHAR2 DEFAULT NULL
    ,arg5 INVARCHAR2 DEFAULT NULL);

参数说明：
   file   由FOPEN返回的文件句柄
   format   决定格式的格式串
   argN   可选的5个参数，最多5个

格式串可使用以下样式
%s 在格式串中可以使用最多5个%s，与后面的5个参数一一对应

%s会被后面的参数依次填充，如果没有足够的参数，%s会被忽视，不被写入文件
\n 换行符。在格式串中没有个数限制

异常：
   UTL_FILE.INVALID_FILEHANDLE
   UTL_FILE.INVALID_OPERATION
   UTL_FILE.WRITE_ERROR

UTL_FILE.FFLUSH
确保所有数据写入文件。

函数原型：
PROCEDUREUTL_FILE.FFLUSH (file IN UTL_FILE.FILE_TYPE);
参数说明：

file 由FOPEN返回的文件句柄

注意：

   操作系统可能会缓存数据来提高性能。因此可能调用put后，打开文件却看不到写入的数据。在关闭文件前要读取数据的话可以使用UTL_FILE.FFLUSH。
   典型的使用方法包括分析执行进度和调试纪录。
异常：
   UTL_FILE.INVALID_FILEHANDLE
   UTL_FILE.INVALID_OPERATION
   UTL_FILE.WRITE_ERROR

UTL_FILE.FCLOSE
关闭文件

函数原型：
PROCEDUREUTL_FILE.FCLOSE (file IN OUT FILE_TYPE);

参数说明：
file 由FOPEN返回的文件句柄

注意：

file是一个INOUT参数，因为在关闭文件后会设置为NULL。当试图关闭文件时有缓存数据未写入文件，会抛出WRITE_ERROR异常

异常：
UTL_FILE.INVALID_FILEHANDLE
UTL_FILE.WRITE_ERROR

UTL_FILE.FCLOSE_ALL
关闭所有已打开的文件

原型：
PROCEDUREUTL_FILE.FCLOSE_ALL;

在结束程序时要确保所有打开的文件已关闭，可使用FCLOSE_ALL
也可以在EXCEPTION使用，当异常退出时，文件也会被关闭。
EXCEPTION
   WHEN OTHERS

THEN
     UTL_FILE.FCLOSE_ALL;
     ... other clean up activities ...
END;

注意：

当使用FCLOSE_ALL关闭所有文件时，文件句柄并不会标记为NULL，使用IS_OPEN会返回TRUE。但是，那些关闭的文件不能执行读写操作（除非你再次打开文件）。
异常：
UTL_FILE.WRITE_ERROR

UTL_FILE.FCOPY

存储过程FCOPY把一个文件中文本拷贝到另外一个文件中。

函数原型：

FCOPY(location VARCHAR2, filename VARCHAR2,dest_dir VARCHAR2, dest_file VARCHAR2[, start_line PLS_INTEGER [, end_line PLS_INTEGER ] ])

参数

location

表示目录名称，存放在pg_catalog.edb_dir.dirname中，这个目录包含要拷贝的文件。

filename

要拷贝文件的名称。

dest_dir

表示目录名称，存放在pg_catalog.edb_dir.dirname中，是源文件要拷贝到目的目录。

dest_file

目标文件的名称。

start_line

源文件中文本行号，用于指定开始拷贝的位置。缺省值是1。

end_line

源文件中最后一行要拷贝文本的行号。如果省略这个参数或者这个参数为空，那么就一直拷贝到文件中最后一行。

示例：

下面的示例中产生文件c:\temp\empdir\empfile.csv的拷贝。这个文件中包含一个逗号分隔的列表，内容是表emp中的雇员信息。然后列出了empcopy.csv的内容。

CREATE DIRECTORY empdir AS 'C:/TEMP/EMPDIR';
DECLARE
    v_empfile       UTL_FILE.FILE_TYPE;
    v_src_dir       VARCHAR2(50) := 'empdir';
    v_src_file      VARCHAR2(20) := 'empfile.csv';
    v_dest_dir      VARCHAR2(50) := 'empdir';
    v_dest_file     VARCHAR2(20) := 'empcopy.csv';
    v_emprec        VARCHAR2(120);
    v_count         INTEGER := 0;
BEGIN
    UTL_FILE.FCOPY(v_src_dir,v_src_file,v_dest_dir,v_dest_file);
    v_empfile := UTL_FILE.FOPEN(v_dest_dir,v_dest_file,'r');
    DBMS_OUTPUT.PUT_LINE('The following is the destination file, ''' ||
        v_dest_file || '''');
    LOOP
        UTL_FILE.GET_LINE(v_empfile,v_emprec);
        DBMS_OUTPUT.PUT_LINE(v_emprec);
        v_count := v_count + 1;
    END LOOP;
    EXCEPTION
        WHEN NO_DATA_FOUND THEN
            UTL_FILE.FCLOSE(v_empfile);
            DBMS_OUTPUT.PUT_LINE(v_count || ' records retrieved');
        WHEN OTHERS THEN
            DBMS_OUTPUT.PUT_LINE('SQLERRM: ' || SQLERRM);
            DBMS_OUTPUT.PUT_LINE('SQLCODE: ' || SQLCODE);
END;

The following is the destination file, 'empcopy.csv'
7369,SMITH,CLERK,7902,17-DEC-80,800,,20
7499,ALLEN,SALESMAN,7698,20-FEB-81,1600,300,30
7521,WARD,SALESMAN,7698,22-FEB-81,1250,500,30
7566,JONES,MANAGER,7839,02-APR-81,2975,,20
7654,MARTIN,SALESMAN,7698,28-SEP-81,1250,1400,30
7698,BLAKE,MANAGER,7839,01-MAY-81,2850,,30
7782,CLARK,MANAGER,7839,09-JUN-81,2450,,10
7788,SCOTT,ANALYST,7566,19-APR-87,3000,,20
7839,KING,PRESIDENT,,17-NOV-81,5000,,10
7844,TURNER,SALESMAN,7698,08-SEP-81,1500,0,30
7876,ADAMS,CLERK,7788,23-MAY-87,1100,,20
7900,JAMES,CLERK,7698,03-DEC-81,950,,30
7902,FORD,ANALYST,7566,03-DEC-81,3000,,20
7934,MILLER,CLERK,7782,23-JAN-82,1300,,10
14 records retrieved

UTL_FILE.FFLUSH

  存储过程FFLUSH强制缓冲区中未写入磁盘的内容写到磁盘文件中，并将缓冲区的内容清空。

函数原型：FFLUSH(file FILE_TYPE)

参数：

file 包含一个文件句柄的变量，类型为FILE_TYPE。

示例：

调用存储过程NEW_LINE后，将缓冲区中的每一行记录强制写到磁盘中。

DECLARE
    v_empfile       UTL_FILE.FILE_TYPE;
    v_directory     VARCHAR2(50) := 'empdir';
    v_filename      VARCHAR2(20) := 'empfile.csv';
    CURSOR emp_cur IS SELECT * FROM emp ORDER BY empno;
BEGIN
    v_empfile := UTL_FILE.FOPEN(v_directory,v_filename,'w');
    FOR i IN emp_cur LOOP
        UTL_FILE.PUT(v_empfile,i.empno);
        UTL_FILE.PUT(v_empfile,',');
        UTL_FILE.PUT(v_empfile,i.ename);
        UTL_FILE.PUT(v_empfile,',');
        UTL_FILE.PUT(v_empfile,i.job);
        UTL_FILE.PUT(v_empfile,',');
        UTL_FILE.PUT(v_empfile,i.mgr);
        UTL_FILE.PUT(v_empfile,',');
        UTL_FILE.PUT(v_empfile,i.hiredate);
        UTL_FILE.PUT(v_empfile,',');
        UTL_FILE.PUT(v_empfile,i.sal);
        UTL_FILE.PUT(v_empfile,',');
        UTL_FILE.PUT(v_empfile,i.comm);
        UTL_FILE.PUT(v_empfile,',');
        UTL_FILE.PUT(v_empfile,i.deptno);
        UTL_FILE.NEW_LINE(v_empfile);
        UTL_FILE.FFLUSH(v_empfile);
    END LOOP;
    DBMS_OUTPUT.PUT_LINE('Created file: ' || v_filename);
    UTL_FILE.FCLOSE(v_empfile);
END;

UTL_FILE.FREMOVE

    存储过程FREMOVE用于从系统中删除一个文件。

函数原型：

    FREMOVE(location VARCHAR2, filename VARCHAR2)

如果要删除的文件不存在，那么会产生一个异常。

参数：

location

    目录名称，存放在pg_catalog.edb_dir.dirname中，这个目录包含要删除的文件。

filename

    要删除文件的名称。

示例：

下面的示例删除了文件 empfile.csv：

DECLARE
    v_directory     VARCHAR2(50) := 'empdir';
    v_filename      VARCHAR2(20) := 'empfile.csv';
BEGIN
    UTL_FILE.FREMOVE(v_directory,v_filename);
    DBMS_OUTPUT.PUT_LINE('Removed file: ' || v_filename);
    EXCEPTION
        WHEN OTHERS THEN
            DBMS_OUTPUT.PUT_LINE('SQLERRM: ' || SQLERRM);
            DBMS_OUTPUT.PUT_LINE('SQLCODE: ' || SQLCODE);
END;

Removed file: empfile.csv

UTL_FILE.FRENAME

存储过程FRENAME修改一个文件的名称，这样我们可以把一个文件从一个位置移动到另外一个位置。

函数原型：

FRENAME(location VARCHAR2, filename VARCHAR2,
  dest_dir VARCHAR2, dest_file VARCHAR2, [ overwrite BOOLEAN ])

参数：

location

    目录名称，存放在pg_catalog.edb_dir.dirname中，这个目录包含要改名的文件。

filename

    要改名的源文件名称。

dest_dir

    目录名称，存放在pg_catalog.edb_dir.dirname中，这个目录是被改名文件所在的目录。

dest_file

    原始文件的新名称。

overwrite

    如果设置为”true”,在dest_dir目录中覆盖任何名为dest_file的文件。若设置为”false”，就会产生异常。这是缺省情况。

示例：

下面我们将文件C:\TEMP\EMPDIR\empfile.csv重新命名，这个文件包含一个逗号分隔的列表，内容是表emp中雇员的信息。然后列出重新命名后的文件 C:\TEMP\NEWDIR\newemp.csv的内容。

CREATE DIRECTORY "newdir" AS 'C:/TEMP/NEWDIR';

DECLARE
    v_empfile       UTL_FILE.FILE_TYPE;
    v_src_dir       VARCHAR2(50) := 'empdir';
    v_src_file      VARCHAR2(20) := 'empfile.csv';
    v_dest_dir      VARCHAR2(50) := 'newdir';
    v_dest_file     VARCHAR2(50) := 'newemp.csv';
    v_replace       BOOLEAN := FALSE;
    v_emprec        VARCHAR2(120);
    v_count         INTEGER := 0;
BEGIN
    UTL_FILE.FRENAME(v_src_dir,v_src_file,v_dest_dir,
        v_dest_file,v_replace);
    v_empfile := UTL_FILE.FOPEN(v_dest_dir,v_dest_file,'r');
    DBMS_OUTPUT.PUT_LINE('The following is the renamed file, ''' ||
        v_dest_file || '''');
    LOOP
        UTL_FILE.GET_LINE(v_empfile,v_emprec);
        DBMS_OUTPUT.PUT_LINE(v_emprec);
        v_count := v_count + 1;
    END LOOP;
    EXCEPTION
        WHEN NO_DATA_FOUND THEN
            UTL_FILE.FCLOSE(v_empfile);
            DBMS_OUTPUT.PUT_LINE(v_count || ' records retrieved');
        WHEN OTHERS THEN
            DBMS_OUTPUT.PUT_LINE('SQLERRM: ' || SQLERRM);
            DBMS_OUTPUT.PUT_LINE('SQLCODE: ' || SQLCODE);
END;

The following is the renamed file, 'newemp.csv'
7369,SMITH,CLERK,7902,17-DEC-80 00:00:00,800.00,,20
7499,ALLEN,SALESMAN,7698,20-FEB-81 00:00:00,1600.00,300.00,30
7521,WARD,SALESMAN,7698,22-FEB-81 00:00:00,1250.00,500.00,30
7566,JONES,MANAGER,7839,02-APR-81 00:00:00,2975.00,,20
7654,MARTIN,SALESMAN,7698,28-SEP-81 00:00:00,1250.00,1400.00,30
7698,BLAKE,MANAGER,7839,01-MAY-81 00:00:00,2850.00,,30
7782,CLARK,MANAGER,7839,09-JUN-81 00:00:00,2450.00,,10
7788,SCOTT,ANALYST,7566,19-APR-87 00:00:00,3000.00,,20
7839,KING,PRESIDENT,,17-NOV-81 00:00:00,5000.00,,10
7844,TURNER,SALESMAN,7698,08-SEP-81 00:00:00,1500.00,0.00,30
7876,ADAMS,CLERK,7788,23-MAY-87 00:00:00,1100.00,,20
7900,JAMES,CLERK,7698,03-DEC-81 00:00:00,950.00,,30
7902,FORD,ANALYST,7566,03-DEC-81 00:00:00,3000.00,,20
7934,MILLER,CLERK,7782,23-JAN-82 00:00:00,1300.00,,10
14 records retrieved

pucheung

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
Oracle内建UTL_FILE使用

包UTL_FILE提供了在操作系统层面上对文件系统中文件的读写功能。非超级用户在使用包UTL_FILE中任何函数或存储过程前必须由超级用户授予在这个包上的EXECUTE权限。例如：我们使用下列命令对用户mary进行授权： GRANT EXECUTE ON PACKAGE SYS.UTL_FILE TOmary; 如果使用包UTL_FILE中的函数和存储过程访问文件，那么操作系统中
复制链接

扫一扫