PL/SQL--)UTL_FILE包的使用介绍 - 数据库编程

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

1、UTL_FILE介绍

2、UTL_FILE包中的过程和函数

TYPE file_type IS RECORD (

b、UTL_FILE中相关过程函数的功能说明

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   : http://blog.csdn.net/leshami
  IS
     vsfile      UTL_FILE.file_type;
     vnewline    VARCHAR2 (200);
     v_utl_dir   VARCHAR2 (30);
  BEGIN
     v_utl_dir := UPPER (utl_dir_in);
     vsfile := UTL_FILE.fopen (v_utl_dir, file_name_in, 'r');   --->打开文件
  
     IF UTL_FILE.is_open (vsfile)
     THEN
        LOOP
           BEGIN
              UTL_FILE.get_line (vsfile, vnewline);   -->从文件读入行
  
              IF vnewline IS NULL
              THEN
                 EXIT;
              END IF;
  
              INSERT INTO tb_emp (val, file_name)     --->将读入的行插入到表
                   VALUES (vnewline, file_name_in);
           EXCEPTION
              WHEN NO_DATA_FOUND
              THEN
                 EXIT;
           END;
        END LOOP;
  
        COMMIT;
     END IF;
  
     UTL_FILE.fclose (vsfile);                       --->关闭打开的文件
     UTL_FILE.frename (v_utl_dir,                    --->此处进行了重命名
                       file_name_in,
                       v_utl_dir,
                       'x.txt',
                       TRUE);
  EXCEPTION                                          --->定义了相关的异常信息
     WHEN UTL_FILE.invalid_mode
     THEN
        raise_application_error (-20051, 'Invalid Mode Parameter');
     WHEN UTL_FIL

PL/SQL--)UTL_FILE包的使用介绍(一)