当前位置: 代码迷 >> SQL >> PL/SQL->UTL_FILE包的使用介绍
  详细解决方案

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

热度:74   发布时间:2016-05-05 11:52:55.0
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文件的目录

[email protected]> ho mkdir -p /u03/database/usbo/db_utl_dir

  --在数据库层面添加directory

[email protected]> create directory db_utl_dir as '/u03/database/usbo/db_utl_dir';

  --权限授予

[email protected]> 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.

  --查看产生的文件

[email protected]> ho more /u03/database/usbo/db_utl_dir/emp.txt

  JONES,MANAGER

  BLAKE,MANAGER

  CLARK,MANAGER

  SCOTT,ANALYST

  KING,PRESIDENT

  FORD,ANALYST

  c、从数据文件读入并写入到表

[email protected]> create table tb_emp(val varchar2(30), file_name varchar2(10));

[email protected]> exec read_demo('emp.txt','db_utl_dir'); -->调用过程来实现,代码见文章尾部

  PL/SQL procedure successfully completed.

[email protected]> 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、读写混合模式示例

[email protected]> set serveroutput on;

[email protected]> exec rw_demo; -->调用过程来实现,代码见文章尾部

  14

  14

  28

  42

  56

  71

  84

  PL/SQL procedure successfully completed.

[email protected]> ho ls

  out.txt x.txt

[email protected]> 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://www.linuxidc.com

  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”错误