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

https://blog.csdn.net/leshami/article/details/17348067

  在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用法

1.  
   a、使用UTL_FILE的主要步骤(使用directory方式)
2.  
   --先创建用于存放os文件的目录
3.  
   scott@USBO> ho mkdir -p /u03/database/usbo/db_utl_dir
4.  
    
5.  
   --在数据库层面添加directory
6.  
   scott@USBO> create directory db_utl_dir as '/u03/database/usbo/db_utl_dir';
7.  
    
8.  
   --权限授予
9.  
   scott@USBO> grant read,write on directory db_utl_dir to public;
10.  
     
11.  
    b、从SQL查询写入到数据文件
12.  
    DECLARE
13.  
    vsfile UTL_FILE.file_type; --->定义用于接收文件句柄的类型
14.  
    v_cnt PLS_INTEGER := 0;
15.  
    BEGIN
16.  
    vsfile :=
17.  
    UTL_FILE.fopen ('DB_UTL_DIR', --->使用fopen打开文件，定义了文件路径，文件名，读写方式以及每一行字符的最大长度，缺省为1024
18.  
    'emp.txt',
19.  
    'W',
20.  
    200);
21.  
     
22.  
    FOR i IN (SELECT t.ename || ',' || t.job AS msg --->使用了一个for循环来读取scott.emp表
23.  
    FROM scott.emp t WHERE t.sal>2000)
24.  
    LOOP
25.  
    UTL_FILE.put_line (vsfile, i.msg); --->将for循环查询的内容使用put_line写入到文件
26.  
    v_cnt := v_cnt + 1; --->计数器，用于统计写入的记录数
27.  
    END LOOP;
28.  
     
29.  
    UTL_FILE.fflush (vsfile);
30.  
    UTL_FILE.fclose (vsfile);
31.  
    DBMS_OUTPUT.put_line (v_cnt || ' rows unloaded');
32.  
    END;
33.  
    /
34.  
     
35.  
    6 rows unloaded
36.  
     
37.  
    PL/SQL procedure successfully completed.
38.  
     
39.  
    --查看产生的文件
40.  
    scott@USBO> ho more /u03/database/usbo/db_utl_dir/emp.txt
41.  
    JONES,MANAGER
42.  
    BLAKE,MANAGER
43.  
    CLARK,MANAGER
44.  
    SCOTT,ANALYST
45.  
    KING,PRESIDENT
46.  
    FORD,ANALYST
47.  
     
48.  
    c、从数据文件读入并写入到表
49.  
    scott@USBO> create table tb_emp(val varchar2(30), file_name varchar2(10));
50.  
     
51.  
    scott@USBO> exec read_demo('emp.txt','db_utl_dir'); -->调用过程来实现，代码见文章尾部
52.  
     
53.  
    PL/SQL procedure successfully completed.
54.  
     
55.  
    scott@USBO> select * from tb_emp;
56.  
     
57.  
    VAL FILE_NAME
58.  
    ----------------------------- ---------------------
59.  
    JONES,MANAGER emp.txt
60.  
    BLAKE,MANAGER emp.txt
61.  
    CLARK,MANAGER emp.txt
62.  
    SCOTT,ANALYST emp.txt
63.  
    KING,PRESIDENT emp.txt
64.  
    FORD,ANALYST emp.txt
65.  
     
66.  
    6 rows selected.
67.  
     
68.  
    d、读写混合模式示例
69.  
    scott@USBO> set serveroutput on;
70.  
    scott@USBO> exec rw_demo; -->调用过程来实现，代码见文章尾部
71.  
    14
72.  
    14
73.  
    28
74.  
    42
75.  
    56
76.  
    71
77.  
    84
78.  
     
79.  
    PL/SQL procedure successfully completed.
80.  
     
81.  
    scott@USBO> ho ls
82.  
    out.txt x.txt
83.  
     
84.  
    scott@USBO> ho more out.txt
85.  
    JONES,MANAGER
86.  
    JONES,MANAGER
87.  
    BLAKE,MANAGER
88.  
    CLARK,MANAGER
89.  
    SCOTT,ANALYST
90.  
    KING,PRESIDENT
91.  
    FORD,ANALYST
92.  
     
93.  
    e、演示中用到的过程
94.  
    --下面是读模式的过程代码
95.  
    CREATE OR REPLACE PROCEDURE read_demo (file_name_in VARCHAR2, utl_dir_in VARCHAR2)
96.  
    --两个传入参数，一个用于指定文件名，一个用于指定utl_file_dir目录
97.  
    --Author : Leshami
98.  
    --Blog : http://blog.csdn.net/leshami
99.  
    IS
100.  
     vsfile UTL_FILE.file_type;
101.  
     vnewline VARCHAR2 (200);
102.  
     v_utl_dir VARCHAR2 (30);
103.  
     BEGIN
104.  
     v_utl_dir := UPPER (utl_dir_in);
105.  
     vsfile := UTL_FILE.fopen (v_utl_dir, file_name_in, 'r'); --->打开文件
106.  
      
107.  
     IF UTL_FILE.is_open (vsfile)
108.  
     THEN
109.  
     LOOP
110.  
     BEGIN
111.  
     UTL_FILE.get_line (vsfile, vnewline); -->从文件读入行
112.  
      
113.  
     IF vnewline IS NULL
114.  
     THEN
115.  
     EXIT;
116.  
     END IF;
117.  
      
118.  
     INSERT INTO tb_emp (val, file_name) --->将读入的行插入到表
119.  
     VALUES (vnewline, file_name_in);
120.  
     EXCEPTION
121.  
     WHEN NO_DATA_FOUND
122.  
     THEN
123.  
     EXIT;
124.  
     END;
125.  
     END LOOP;
126.  
      
127.  
     COMMIT;
128.  
     END IF;
129.  
      
130.  
     UTL_FILE.fclose (vsfile); --->关闭打开的文件
131.  
     UTL_FILE.frename (v_utl_dir, --->此处进行了重命名
132.  
     file_name_in,
133.  
     v_utl_dir,
134.  
     'x.txt',
135.  
     TRUE);
136.  
     EXCEPTION --->定义了相关的异常信息
137.  
     WHEN UTL_FILE.invalid_mode
138.  
     THEN
139.  
     raise_application_error (-20051, 'Invalid Mode Parameter');
140.  
     WHEN UTL_FILE.invalid_path
141.  
     THEN
142.  
     raise_application_error (-20052, 'Invalid File Location');
143.  
     WHEN UTL_FILE.invalid_filehandle
144.  
     THEN
145.  
     raise_application_error (-20053, 'Invalid Filehandle');
146.  
     WHEN UTL_FILE.invalid_operation
147.  
     THEN
148.  
     raise_application_error (-20054, 'Invalid Operation');
149.  
     WHEN UTL_FILE.read_error
150.  
     THEN
151.  
     raise_application_error (-20055, 'Read Error');
152.  
     WHEN UTL_FILE.internal_error
153.  
     THEN
154.  
     raise_application_error (-20057, 'Internal Error');
155.  
     WHEN UTL_FILE.charsetmismatch
156.  
     THEN
157.  
     raise_application_error (-20058, 'Opened With FOPEN_NCHAR
158.  
     But Later I/O Inconsistent');
159.  
     WHEN UTL_FILE.file_open
160.  
     THEN
161.  
     raise_application_error (-20059, 'File Already Opened');
162.  
     WHEN UTL_FILE.invalid_maxlinesize
163.  
     THEN
164.  
     raise_application_error (-20060, 'Line Size Exceeds 32K');
165.  
     WHEN UTL_FILE.invalid_filename
166.  
     THEN
167.  
     raise_application_error (-20061, 'Invalid File Name');
168.  
     WHEN UTL_FILE.access_denied
169.  
     THEN
170.  
     raise_application_error (-20062, 'File Access Denied By');
171.  
     WHEN UTL_FILE.invalid_offset
172.  
     THEN
173.  
     raise_application_error (-20063, 'FSEEK Param Less Than 0');
174.  
     WHEN OTHERS
175.  
     THEN
176.  
     raise_application_error (-20099, 'Unknown UTL_FILE Error');
177.  
     END read_demo;
178.  
     /
179.  
      
180.  
     --下面是读写模式过程的代码，这个过程实现了从一个数据文件读出并写入到另外一个数据文件
181.  
     CREATE OR REPLACE PROCEDURE rw_demo
182.  
     IS
183.  
     infile UTL_FILE.file_type;
184.  
     outfile UTL_FILE.file_type;
185.  
     vnewline VARCHAR2 (4000);
186.  
     i PLS_INTEGER;
187.  
     j PLS_INTEGER := 0;
188.  
     seekflag BOOLEAN := TRUE;
189.  
     BEGIN
190.  
     -- open a file to read
191.  
     infile := UTL_FILE.fopen ('DB_UTL_DIR', 'x.txt', 'r'); -->打开源文件用于读取数据
192.  
     -- open a file to write
193.  
     outfile := UTL_FILE.fopen ('DB_UTL_DIR', 'out.txt', 'w'); -->创建目标文件用于存放数据
194.  
      
195.  
     -- if the file to read was successfully opened
196.  
     IF UTL_FILE.is_open (infile)
197.  
     THEN
198.  
     -- loop through each line in the file
199.  
     LOOP
200.  
     BEGIN
201.  
     UTL_FILE.get_line (infile, vnewline); -->从源文件读取行
202.  
      
203.  
     i := UTL_FILE.fgetpos (infile); -->将行的位置赋值并输出
204.  
     DBMS_OUTPUT.put_line (TO_CHAR (i));
205.  
      
206.  
     UTL_FILE.put_line (outfile, vnewline, FALSE); -->将得到的数据行写出到文件句柄缓冲
207.  
     UTL_FILE.fflush (outfile); -->将数据行从缓冲区写入到文件
208.  
      
209.  
     IF seekflag = TRUE
210.  
     THEN
211.  
     UTL_FILE.fseek (infile, NULL, -30); -->用于调整文件指针，即偏移量
212.  
     seekflag := FALSE;
213.  
     END IF;
214.  
     EXCEPTION
215.  
     WHEN NO_DATA_FOUND
216.  
     THEN
217.  
     EXIT;
218.  
     END;
219.  
     END LOOP;
220.  
      
221.  
     COMMIT;
222.  
     END IF;
223.  
      
224.  
     UTL_FILE.fclose (infile); -->关闭源文件
225.  
     UTL_FILE.fclose (outfile); -->关闭目标文件
226.  
     EXCEPTION
227.  
     WHEN OTHERS
228.  
     THEN
229.  
     raise_application_error (-20099, 'Unknown UTL_FILE Error');
230.  
     END rw_demo;
231.  
     /
232.  
      
233.  
     注意在使用UTL_FILE包用到DIRECTORY数据库对象时，名字一定要大写，否则会遭遇“ORA-29280: invalid directory path”错误
234.  
      
235.