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;
/