UTL_FILE 学习

/* UTL_FILE 学习
UTL_FILE包 允许用户通过PL/SQL读写操作系统文件
*/

/*
1、创建目录：
*/
create or replace directory MY_DIR as 'E:\javaWP\Ttt';
create or replace directory TO_DIR as 'E:\javaWP\Ott';

查看目录

select * from dba_directories;

--给普通用户testdb 赋予相应权限
grant read,write on directory MY_DIR to testdb;
GRANT EXECUTE ON utl_file TO testdb;

/*
2、文件复制
FCOPY :Copies a contiguous portion of a file to a newly created file
use:
utl_file.fcopy (
location IN VARCHAR2, --需要文件目录
filename IN VARCHAR2, --需要复制的文件名
dest_dir IN VARCHAR2, --目的地目录
dest_file IN VARCHAR2, --复制后的新文件名
start_line IN PLS_INTEGER DEFAULT 1, --复制原文件内容的起始行
end_line IN PLS_INTEGER DEFAULT NULL); --复制原文件内容的末行
*/
--demo
BEGIN
utl_file.fcopy('MY_DIR', 'test1.txt', 'MY_DIR', 'cp_test1.txt',3,6);
END;

/*
3、关闭UTL_FILE 打开的所有文件
FCLOSE_ALL：Close All Files Opened By UTL_FILE
*/
--demo
set serveroutput on size(200);

DECLARE
vInHandle utl_file.file_type;
vOutHandle utl_file.file_type;
BEGIN
vInHandle := utl_file.fopen('MY_DIR', 'test1.txt', 'R');
vOutHandle := utl_file.fopen('MY_DIR', 'test2.txt', 'W');
IF utl_file.is_open(vInHandle) THEN
utl_file.fclose_all;
dbms_output.put_line('Closed All');
END IF;
END fopen;
/

/*
4、查看文件的属性
FGETATTR:Reads and returns the attributes of a disk file
use:
utl_file.fgetattr(
location IN VARCHAR2, --文件所在目录
filename IN VARCHAR2, --文件的名称
exists OUT BOOLEAN, --文件是否存在
file_length OUT NUMBER, --文件的长度(大小)
blocksize OUT NUMBER); --程序块尺寸
*/
--demo
set serveroutput on;

DECLARE
ex BOOLEAN;
flen NUMBER;
bsize NUMBER;
BEGIN
utl_file.fgetattr('MY_DIR', 'test1.txt', ex, flen, bsize);

IF ex THEN
dbms_output.put_line('File Exists');
ELSE
dbms_output.put_line('File Does Not Exist');
END IF;
dbms_output.put_line('File Length: ' || TO_CHAR(flen));
dbms_output.put_line('Block Size: ' || TO_CHAR(bsize));
END fgetattr;
/

--demo 2
DECLARE
ex BOOLEAN;
flen NUMBER;
bsize NUMBER;
BEGIN
utl_file.fgetattr('MY_DIR', 'test3.txt', ex, flen, bsize);

IF flen > 0 THEN
dbms_output.put_line('File Length is ' || flen ||' kb ');
ELSE
dbms_output.put_line('0 File Length is ' || flen ||' kb ');
END IF;
dbms_output.put_line('File Length: ' || TO_CHAR(flen));
dbms_output.put_line('Block Size: ' || TO_CHAR(bsize));
END fgetattr;
/

/*
5、打开文件
FOPEN：Open A File For Read Operations or Open A File For Write Operations
use ：
utl_file.fopen(
<file_location IN VARCHAR2>, --文件所在目录
<file_name IN VARCHAR2>, --文件名称
<open_mode IN VARCHAR2>, --文件打开方式
<max_linesize IN BINARY_INTEGER>) --maximum number of characters per line, including the newline character, for this file.
RETURN <file_type_package_data_type; --返回读取文件的内容
*/
--demo read
DECLARE
vInHandle utl_file.file_type;
vNewLine VARCHAR2(250);
BEGIN
vInHandle := utl_file.fopen('MY_DIR', 'test1.txt','R');
LOOP
BEGIN
utl_file.get_line(vInHandle, vNewLine);
dbms_output.put_line(vNewLine);
EXCEPTION
WHEN OTHERS THEN
EXIT;
END;
END LOOP;
utl_file.fclose(vInHandle);
END fopen;
/

--demo write
declare
vInHandle utl_file.file_type;
begin
vInHandle := utl_file.fopen('MY_DIR', 'test1.txt', 'w');
utl_file.put_line(vInHandle, 'hello lsxy');
utl_file.fclose(vInHandle);
end;
/

/*
6.文件删除
FREMOVE：Delete An Operating System File
use:
utl_file.fremove (location IN VARCHAR2, filename IN VARCHAR2);
*/
--demo
BEGIN
utl_file.fremove('MY_DIR', 'test3.txt');
END fremove;
/

/*
7.文件重命名 or 移动文件
FRENAME：Rename An Operating System File
use:
utl_file.frename (
location IN VARCHAR2,
filename IN VARCHAR2,
dest_dir IN VARCHAR2,
dest_file IN VARCHAR2,
overwrite IN BOOLEAN DEFAULT FALSE); --是否覆盖
*/
--demo 文件重命名
BEGIN
utl_file.frename('MY_DIR','test2.txt','MY_DIR','test2.txt',TRUE);
END frename;
/

--demo 移动文件
BEGIN
utl_file.frename('MY_DIR','test2.txt','TO_DIR','test2.txt',TRUE);
END frename;
/

D:\CRMDATA_bak

/*
8、获取文件内容
GET_LINE：Get (read) a line of text from the file
use: get_line(file IN file_type,
buffer OUT VARCHAR2,
len IN BINARY_INTEGER DEFAULT NULL);
*/
--demo
DECLARE
vInHandle utl_file.file_type;
vNewLine VARCHAR2(250);
BEGIN
vInHandle := utl_file.fopen('MY_DIR', 'test1.txt','R');
LOOP
BEGIN
utl_file.get_line(vInHandle, vNewLine);
dbms_output.put_line(vNewLine);
EXCEPTION
WHEN OTHERS THEN
EXIT;
END;
END LOOP;
utl_file.fclose(vInHandle);
END fopen;
/

/*
9、
向文件写入内容
PUT ： Put (write) text to file
use :
put(file IN file_type,
buffer IN VARCHAR2);
*/
--demo
declare
vInHandle utl_file.file_type;
begin
vInHandle := utl_file.fopen('MY_DIR', 'test2.txt', 'w');
utl_file.put(vInHandle, 'lsxy');
utl_file.fclose(vInHandle);
end;
/

/*
10、
NEW_LINE：Write line terminators to file
use:
new_line(file IN file_type,
lines IN NATURAL := 1);
*/
--demo
declare
vInHandle utl_file.file_type;
begin
vInHandle := utl_file.fopen('MY_DIR', 'test2.txt', 'w');
utl_file.new_line(vInHandle, 2);
utl_file.fclose(vInHandle);
end;
/

/*
11、
PUT_LINE ： Put (write) line to file
*/
--demo
declare
vInHandle utl_file.file_type;
begin
vInHandle := utl_file.fopen('MY_DIR', 'test1.txt', 'w');
utl_file.put_line(vInHandle, 'hello lsxy');
utl_file.fclose(vInHandle);
end;
/