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

浏览： ℃

字体：大中小

发布时间：2013-12-17 09:37:07

来源：

在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,ANALYSTc、从数据文件读入并写入到表  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,ANALYSTe、演示中用到的过程  --下面是读模式的过程代码  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_FILE.invalid_path     THEN        raise_application_error (-20052, 'Invalid File Location');     WHEN UTL_FILE.invalid_filehandle     THEN        raise_application_error (-20053, 'Invalid Filehandle');     WHEN UTL_FILE.invalid_operation     THEN        raise_application_error (-20054, 'Invalid Operation');     WHEN UTL_FILE.read_error     THEN        raise_application_error (-20055, 'Read Error');     WHEN UTL_FILE.internal_error     THEN        raise_application_error (-20057, 'Internal Error');     WHEN UTL_FILE.charsetmismatch     THEN        raise_application_error (-20058, 'Opened With FOPEN_NCHAR      But Later I/O Inconsistent');     WHEN UTL_FILE.file_open     THEN        raise_application_error (-20059, 'File Already Opened');     WHEN UTL_FILE.invalid_maxlinesize     THEN        raise_application_error (-20060, 'Line Size Exceeds 32K');     WHEN UTL_FILE.invalid_filename     THEN        raise_application_error (-20061, 'Invalid File Name');     WHEN UTL_FILE.access_denied     THEN        raise_application_error (-20062, 'File Access Denied By');     WHEN UTL_FILE.invalid_offset     THEN        raise_application_error (-20063, 'FSEEK Param Less Than 0');     WHEN OTHERS     THEN        raise_application_error (-20099, 'Unknown UTL_FILE Error');  END read_demo;  /    --下面是读写模式过程的代码，这个过程实现了从一个数据文件读出并写入到另外一个数据文件  CREATE OR REPLACE PROCEDURE rw_demo  IS     infile     UTL_FILE.file_type;     outfile    UTL_FILE.file_type;     vnewline   VARCHAR2 (4000);     i          PLS_INTEGER;     j          PLS_INTEGER := 0;     seekflag   BOOLEAN := TRUE;  BEGIN     -- open a file to read     infile := UTL_FILE.fopen ('DB_UTL_DIR', 'x.txt', 'r');       -->打开源文件用于读取数据     -- open a file to write     outfile := UTL_FILE.fopen ('DB_UTL_DIR', 'out.txt', 'w');   -->创建目标文件用于存放数据       -- if the file to read was successfully opened     IF UTL_FILE.is_open (infile)     THEN        -- loop through each line in the file        LOOP           BEGIN              UTL_FILE.get_line (infile, vnewline);                   -->从源文件读取行                i := UTL_FILE.fgetpos (infile);                      -->将行的位置赋值并输出              DBMS_OUTPUT.put_line (TO_CHAR (i));                UTL_FILE.put_line (outfile, vnewline, FALSE);  -->将得到的数据行写出到文件句柄缓冲              UTL_FILE.fflush (outfile);                        -->将数据行从缓冲区写入到文件                IF seekflag = TRUE              THEN                 UTL_FILE.fseek (infile, NULL, -30);            -->用于调整文件指针，即偏移量                 seekflag := FALSE;              END IF;           EXCEPTION              WHEN NO_DATA_FOUND              THEN                 EXIT;           END;        END LOOP;          COMMIT;     END IF;       UTL_FILE.fclose (infile);                                          -->关闭源文件     UTL_FILE.fclose (outfile);                                        -->关闭目标文件  EXCEPTION     WHEN OTHERS     THEN        raise_application_error (-20099, 'Unknown UTL_FILE Error');  END rw_demo;  /  注意在使用UTL_FILE包用到DIRECTORY数据库对象时，名字一定要大写，否则会遭遇“ORA-29280: invalid directory path”错误主要参考: http://psoug.org/reference/utl_file.html http://docs.oracle.com/cd/E11882_01/appdev.112/e40758/u_file.htm#BABGGEDF

更多参考

使用 DBMS_PROFILER 定位 PL/SQL 瓶颈代码

使用PL/SQL Developer剖析PL/SQL代码

对比 PL/SQL profiler 剖析结果

PL/SQL Profiler 剖析报告生成html

DML Error Logging 特性

PL/SQL --> 游标

PL/SQL --> 隐式游标(SQL%FOUND)

批量SQL之 FORALL 语句

批量SQL之 BULK COLLECT 子句

PL/SQL 集合的初始化与赋值

PL/SQL 联合数组与嵌套表