一、说明
UTL_FILE是oracle提供的一个标准的工具包,用来读写文件使用。
二、路径
包UTL_FILE用于读写操作系统的文件,前提是首先创建路径(Directory)并授权。ORACLE目录的作用就是让ORACLE数据库和操作系统之前进行文件的交互。
为了创建目录,必须具有DBA角色或者赋予了CREATE ANY DIRECTORY权限。如果普通用户被赋予了CREATE ANY DIRECTORY权限,那么用户就自动具备目录的READ和WRITE权限
创建:CREATE [OR REPLACE] DIRECTORY directory_name AS 'path_name' ;
create or replace directory DB_OUT as ‘/usr/local/xilen/DB_OUT’;
赋权:GRANT READ[,WRITE] ON DIRECTORY directory_namne TO user_name;
--路径授权,添加对路径读、写权限
grant read,write on directory DB_OUT to xilen;
--utl_file包授权,添加执行权限
GRANT EXECUTE ON utl_file TO xilen;
查看(具有READ和WRITE权限的目录):
-- 限制单列的显示长度
COL DIRECTORY_PATH format a60;
-- 查询路径
SELECT DIRECTORY_NAME, DIRECTORY_PATH, GRANTEE, PRIVILEGE FROM USER_TAB_PRIVS T, ALL_DIRECTORIES D WHERE T.TABLE_NAME(+)=D.DIRECTORY_NAME;
三、详解
1、FILE_TYPE
该类型是UTL_FILE包中定义的记录类型,其成员是私有的,不能够被直接引用。该类型的定义如下:
TYPE file_type IS RECORD(
id BINARY_INTEGER,
datatype BINARY_INTEGER
);
2、FOPEN
该函数用于打开文件。使用这个函数最多可以打开50个文件,语法如下:
UTL_FILE.FOPEN(
location IN VARCHAR2, -- 文件路径(必须要使用DIRECTORY对象,并且其名称必须为大写)
filename IN VARCHAR2, -- 文件名
open_mode IN VARCHAR2, -- 打开模式
max_linesize IN BINARY_INTEGER -- 指定文件每行存放的最大字符数
)
RETURN file_type; -- 预操作文件的指针(句柄)
注:文件的打开模式包含'r', 'w', 'a' 'rb', 'wb', 'ab'六种:
--'r':读文件(文本),一定要保证有该文件,不然会报UTL_FILE.INVALID_PATH异常
--'w':写文件(文本),没有该文件的话会自动添加;有的话会覆盖
--'a':追加文件(文本),如果文件不存在,则会以write模式创建此文件
--带有'b'后缀的为使用byte(字节)模式,BLOB与VARCHAR2不一样,BLOB打开时一定要用带有'b'后缀的模式
3、IS_OPEN
该函数用于确定文件是否已经被打开,语法如下:
UTL_FILE.IS_OPEN(
file IN FILE_TYPE -- 指定文件的句柄
)
RETURN BOOLEAN; -- 如果已经打开则返回TRUE,否则返回FALSE
该函数的示例如下:
DECLARE
HANDLE UTL_FILE.FILE_TYPE;
BEGIN
IF NOT UTL_FILE.is_open(HANDLE) THEN
HANDLE := UTL_FILE.fopen('CZW','DYWT.TXT','R',1000);
END IF;
DBMS_OUTPUT.PUT_LINE('D:\DYWT.TXT已经被打开');
END;
4、FCLOSE
该过程用于关闭已经打开的文件,语法如下:
UTL_FILE.FCLOSE(FILE IN OUT FILE_TYPE);
5、FCLOSE_ALL
该过程用于关闭当前打开的所有文件。语法如下:
UTL_FILE.FCLOSE_ALL;
6、GET_LINE
该过程用于从已经打开的文件中读取行内容,行内容会被读取到输出缓冲区。语法如下:
UTL_FILE.GET_LINE(
file IN FILE_TYPE, -- 文件句柄
buffer OUT VARCHAR2, -- 存储读取的Buff
linesize IN NUMBER, -- 读取的最大字节数
len IN PLS_INTEGER DEFAULT NULL -- 实际读取长度
);
使用该过程的示例如下:
DECLARE
HANDLE UTL_FILE.FILE_TYPE;
BUFFER VARCHAR2(100);
BEGIN
IF NOT UTL_FILE.is_open(HANDLE) THEN
HANDLE := UTL_FILE.fopen('CZW','DYWT.TXT','R',1000);
END IF;
UTL_FILE.GET_LINE(HANDLE,BUFFER,100);
DBMS_OUTPUT.PUT_LINE(BUFFER);
UTL_FILE.fclose(HANDLE);
END;
7、GET_RAW
该过程用于从文件中读取RAW字符串,并调节文件指针到读取位置。语法如下:
UTL_FILE.GET_RAW(
fid IN UTL_FILE.TYPE, -- 文件句柄
r OUT NOCOPY RAW, -- 读取的缓存Buff
len IN PLS_INTEGER DEFAULT NULL -- 读取长度
);
上面所示:fid用于指定文件的句柄,r用于取得读取信息,示例如下:
DECLARE
HANDLE UTL_FILE.FILE_TYPE;
BUFFER VARCHAR2(2000);
BEGIN
IF NOT UTL_FILE.is_open(HANDLE) THEN
HANDLE := UTL_FILE.fopen('CZW','DYWT.TXT','R',1000);
END IF;
UTL_FILE.GET_RAW(HANDLE,BUFFER,1000);
DBMS_OUTPUT.PUT_LINE(BUFFER);
UTL_FILE.fclose(HANDLE);
END;
8、PUT
该过程用于将缓冲区内容写入到文件中。当使用PUT过程的时候,文件必须以写方式打开,在写入缓冲区之后,如果要结束行,那么可以使用NEW_LINE过程。语法如下:
UTL_FILE.PUT(
file IN FILE_TYPE,
buffer IN VARCHAR2
);
使用该过程的示例如下:
DECLARE
HANDLE UTL_FILE.FILE_TYPE;
BUFFER VARCHAR2(2000);
BEGIN
IF NOT UTL_FILE.IS_OPEN(HANDLE) THEN
HANDLE := UTL_FILE.FOPEN('CZW','NEW.TXT','W',1000);
END IF;
BUFFER:='&CONTENT1';
UTL_FILE.PUT(HANDLE,BUFFER);
UTL_FILE.NEW_LINE(HANDLE);
BUFFER:='&CONTENT2';
UTL_FILE.PUT(HANDLE,BUFFER);
UTL_FILE.NEW_LINE(HANDLE);
UTL_FILE.FCLOSE(HANDLE);
END;
9、PUT_RAW
该过程用于将RAW缓冲区中的数据写入文件中。语法如下:
UTL_FILE.PUT_RAW(
fid IN UTL_FILE.FILE_TYPE,
R IN RAW,
autoflush IN BOOLEAN DEFAULT FALSE
);
上述,fid用于指定文件句柄,r用于指定存放RAW数据的缓冲区,autoflush用于指定是否自动刷新缓冲区数据.该过程的示例如下:
DECLARE
HANDLE UTL_FILE.FILE_TYPE;
BUFFER VARCHAR2(2000);
BEGIN
IF NOT UTL_FILE.IS_OPEN(HANDLE) THEN
HANDLE := UTL_FILE.FOPEN('CZW','NEW.TXT','W',1000);
END IF;
BUFFER:='&CONTENT1';
UTL_FILE.PUT_RAW(HANDLE,BUFFER);
UTL_FILE.NEW_LINE(HANDLE);
UTL_FILE.FCLOSE(HANDLE);
END;
10、NEW_LINE
该过程用于为文件增加行终止符,语法如下:
UTL_FILE.NEW_LINE(
file IN FILE_TYPE,
lines IN NATURAL :=1 -- 指定在文件中增加的行终止符的个数
);
11、PUT_LINE
该过程用于将文本缓冲区内容写入到文件中。当使用该过程为文件追加内容时,会自动在文件的尾部追加行终止符。
UTL_FILE.PUT_LINE (
file IN FILE_TYPE,
buffer IN VARCHAR2,
autoflush IN BOOLEAN DEFAULT FALSE
);
12、FFLUSH
该过程用于将数据强制性写入到文件中,正常情况下,当给文件写入数据的时候,数据会被暂时的放到缓存中。过程FFLUSH用于强制将数据写入到文件中。语法如下:
UTL_FILE.FFLUSH(file IN FILE_TYPE):
13、FREMOVE
该过程用于删除磁盘文件。语法如下:
UTL_FILE.FREMOVE(
location IN VARCHAR2, -- 指定DIRECTORY,注意,这里也必须要大写
filename IN VARCHAR2 -- 指定要删除的文件名
);
14、FCOPY
该过程用于将源文件的全部或者部分内容复制到目标文件中。当使用该过程的时候,如果不设起始行和结束行,则将复制文件所有的内容。语法如下:
UTL_FILE.FCOPY(
location IN VARCHAR2, -- 指定源DIRECTORY路径对象
filename IN VARCHAR2, -- 指定文件名
dest_dir IN VARCHAR2, -- 希望复制到的Directory路径对象
dest_file IN VARCHAR2, -- 复制后的文件名
start_line IN PLS_INTEGER DEFAULT 1, -- 指定起始行号
end_line IN PLS_INTEGER DEFALUT NULL -- 指定结束行号
);
15、FRENAME
该过程用于修改已经存在的文件名字,其作用于UNIX的MV命令完全相同,在修改文件名字的时候,通过指定overwrite参数可以覆盖已经存在的文件。语法如下:
UTL_FILE.FRENAME(
location IN VARCHAR2,
filename IN VARCHAR2,
dest_dir IN VARCHAR2,
dest_file IN VARCHAR2,
overwrite IN BOOLEAN DEFAULT FALSE -- 是否覆盖已经存在的文件
);
四、示例
导出表数据示例:
DECLARE
HANDLE UTL_FILE.FILE_TYPE;
BEGIN
HANDLE := UTL_FILE.FOPEN('CZW_DIR','DEPT.TXT','W',1000);
FOR I IN (SELECT T.DEPTNO||','||T.DNAME||','||T.LOC AS MSG FROM SCOTT.DEPT T) LOOP
UTL_FILE.PUT_LINE(HANDLE,I.MSG);
END LOOP;
UTL_FILE.FFLUSH(HANDLE);
UTL_FILE.FCLOSE(HANDLE);
END;
五、参考
http://blog.csdn.net/bisal/article/details/24667609
http://zhangzhongjie.iteye.com/blog/1903024
http://www.2cto.com/database/201301/181232.html
http://blog.csdn.net/john2522/article/details/8124087
————————————————
版权声明:本文为CSDN博主「袭冷」的原创文章,遵循CC 4.0 BY-SA版权协议,转载请附上原文出处链接及本声明。
原文链接:https://blog.csdn.net/guoxilen/article/details/70159895