PL/SQL-->UTL_FILE包的使用介绍

PL/SQL-->UTL_FILE包的使用介绍

在PL/SQL中，UTL_FILE包提供了文本文件输入和输出互功能。也就是说我们可以通过该包实现从操作系统级别来实现文件读取输入或者是写入到操作系统文件。通过该包也可以将其他系统的数据加载到数据库中。如加载web服务器日志，用户登录数据库日志乃至Oracle日志文件等等。本文主要描述了UTL_FILE的功能以及通过实例演示并理解这个包下相关过程函数的用法。

1、UTL_FILE介绍
  a、实现基于操作系统级别的读取与写入功能
  b、该方式为基于服务器端的文本文件访问模式，不支持二进制文件
  c、可以通过设置参数utl_file_dir来设置pl/sql访问操作系统文件的多个路径
  d、所有用户可以读写utl_file_dir参数设定的目录，因此应考虑安全问题
  e、也可以将参数utl_file_dir置空，而通过创建directory以及授予对directory权限来进行访问os文件(推荐方式)

2、UTL_FILE包中的过程和函数
a、UTL_FILE中定义的file_type为记录类型，如下所示其成员是私有的，不能够被直接引用或改变这个记录的组件。

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

b、UTL_FILE中相关过程函数的功能说明
  FCLOSE Procedure            Closes a file
  FCLOSE_ALL Procedure        Closes all open file handles
  FCOPY Procedure            Copies a contiguous portion of a file to a newly created file
  FFLUSH Procedure            Physically writes all pending output to a file
  FGETATTR Procedure          Reads and returns the attributes of a disk file
  FGETPOS Function            Returns the current relative offset position within a file, in bytes
  FOPEN Function              Opens a file for input or output
  FOPEN_NCHAR Function        Opens a file in Unicode for input or output
  FREMOVE Procedure          Deletes a disk file, assuming that you have sufficient privileges
  FRENAME Procedure          Renames an existing file to a new name, similar to the UNIX mv function
  FSEEK Procedure            Adjusts the file pointer forward or backward within the file by the number of bytes specified
  GET_LINE Procedure          Reads text from an open file
  GET_LINE_NCHAR Procedure    Reads text in Unicode from an open file
  GET_RAW Procedure          Reads a RAW string value from a file and adjusts the file pointer ahead by the number of bytes read
  IS_OPEN Function            Determines if a file handle refers to an open file
  NEW_LINE Procedure          Writes one or more operating system-specific line terminators to a file
  PUT Procedure              Writes a string to a file
  PUT_LINE Procedure          Writes a line to a file, and so appends an operating system-specific line terminator
  PUT_LINE_NCHAR Procedure    Writes a Unicode line to a file
  PUT_NCHAR Procedure        Writes a Unicode string to a file
  PUTF Procedure              A PUT procedure with formatting
  PUTF_NCHAR Procedure        A PUT_NCHAR procedure with formatting, and writes a Unicode string to a file, with formatting
  PUT_RAW Procedure          Accepts as input a RAW data value and writes the value to the output buffer

3、演示ULT_FILE用法

a、使用UTL_FILE的主要步骤(使用directory方式)
  --先创建用于存放os文件的目录
  scott@USBO> ho mkdir -p /u03/database/usbo/db_utl_dir
 
  --在数据库层面添加directory
  scott@USBO> create directory db_utl_dir as '/u03/database/usbo/db_utl_dir';
 
  --权限授予
  scott@USBO> grant read,write on directory db_utl_dir to public;

b、从SQL查询写入到数据文件
  DECLARE
    vsfile  UTL_FILE.file_type;  --->定义用于接收文件句柄的类型
    v_cnt    PLS_INTEGER := 0;
  BEGIN
    vsfile :=                     
        UTL_FILE.fopen ('DB_UTL_DIR',  --->使用fopen打开文件，定义了文件路径，文件名，读写方式以及每一行字符的最大长度，缺省为1024
                        'emp.txt',
                        'W',
                        200);
 
    FOR i IN (SELECT t.ename || ',' || t.job AS msg    --->使用了一个for循环来读取scott.emp表
                FROM scott.emp t WHERE t.sal>2000)
    LOOP
        UTL_FILE.put_line (vsfile, i.msg);              --->将for循环查询的内容使用put_line写入到文件
        v_cnt := v_cnt + 1;                            --->计数器，用于统计写入的记录数
    END LOOP;
 
    UTL_FILE.fflush (vsfile);
    UTL_FILE.fclose (vsfile);
    DBMS_OUTPUT.put_line (v_cnt || ' rows unloaded');
  END;
  /
 
  6 rows unloaded
 
  PL/SQL procedure successfully completed.

  --查看产生的文件
  scott@USBO> ho more /u03/database/usbo/db_utl_dir/emp.txt
  JONES,MANAGER
  BLAKE,MANAGER
  CLARK,MANAGER
  SCOTT,ANALYST
  KING,PRESIDENT
  FORD,ANALYST

c、从数据文件读入并写入到表
  scott@USBO> create table tb_emp(val varchar2(30), file_name varchar2(10));
 
  scott@USBO> exec read_demo('emp.txt','db_utl_dir');  -->调用过程来实现，代码见文章尾部
 
  PL/SQL procedure successfully completed.
 
  scott@USBO> select * from tb_emp;
 
  VAL                          FILE_NAME
  ----------------------------- ---------------------
  JONES,MANAGER                emp.txt
  BLAKE,MANAGER                emp.txt
  CLARK,MANAGER                emp.txt
  SCOTT,ANALYST                emp.txt
  KING,PRESIDENT                emp.txt
  FORD,ANALYST                  emp.txt
 
  6 rows selected.

d、读写混合模式示例
  scott@USBO> set serveroutput on;
  scott@USBO> exec rw_demo;    -->调用过程来实现，代码见文章尾部
  14
  14
  28
  42
  56
  71
  84
 
  PL/SQL procedure successfully completed.
 
  scott@USBO> ho ls
  out.txt  x.txt
 
  scott@USBO> ho more out.txt
  JONES,MANAGER
  JONES,MANAGER
  BLAKE,MANAGER
  CLARK,MANAGER
  SCOTT,ANALYST
  KING,PRESIDENT
  FORD,ANALYST

e、演示中用到的过程
  --下面是读模式的过程代码
  CREATE OR REPLACE PROCEDURE read_demo (file_name_in VARCHAR2, utl_dir_in VARCHAR2)
  --两个传入参数，一个用于指定文件名，一个用于指定utl_file_dir目录
  --Author : Leshami
  --Blog  : l http://docs.oracle.com/cd/E11882_01/appdev.112/e40758/u_file.htm#BABGGEDF

Oracle 10g 安装后重启系统,用PLSQL连接报没有监听

ORA-03114 PLSQL过程编译断开连接错误

PLSQL 连接 Oracle简单配置

PLSQL批量Forall操作性能提升详解

使用Oracle SQLDeveloper连接数据库并创建用户

Oracle自带的PL/SQL Developer导入导出数据

在64位Win7系统下安装Oracle 11g和Oracle SQL Developer客户端