- create or replace package YLFileop_pack is
- -- Author : YANLEI
- -- Created : 2008-12-24 16:08:02
- -- Purpose : 文件操作包
- YL_DIR CONSTANT varchar2(32) := 'YL_DIR'; --财务表收费FormID
- --从后面查找
- function lastindex(str in varchar2, findstr in varchar2) return integer;
- --获得一个文件的路径
- function getpath(filename in varchar2) return varchar2;
- --获得一个文件的文件
- function getfilename(filename in varchar2) return varchar2;
- procedure writefile(filename in varchar2, str in long);
- function readfile return varchar2;
- end YLFileop_pack;
- /
- create or replace package body YLFileop_pack is
- isto_file utl_file.file_type; --如上
- procedure setoraclepath(path in varchar2) is
- sqlstr varchar2(1024);
- begin
- sqlstr := 'create or replace directory ' || YL_DIR || ' as ''' || path || '''';
- execute immediate sqlstr;
- end;
- --oracle字符串从后面查找 by yanleigis Email:landgis@126.com
- function lastindex(str in varchar2, findstr in varchar2) return integer is
- i integer;
- num integer;
- subln integer;
- begin
- num := length(str);
- subln := length(findstr);
- if subln > num then
- return - 1;
- end if;
- i := num - subln + 1;
- while i > 0 loop
- if substr(str, i, subln) = findstr then
- return i;
- end if;
- i := I - 1;
- end loop;
- return - 1;
- end;
- --获得一个文件的路径
- function getpath(filename in varchar2) return varchar2 is
- idx integer;
- begin
- idx := lastindex(filename, '/');
- if (idx > 0) then
- return substr(filename, 1, idx);
- end if;
- return '';
- end;
- --获得一个文件的文件
- function getfilename(filename in varchar2) return varchar2 is
- idx integer;
- begin
- idx := lastindex(filename, '/');
- if (idx > 0) then
- return substr(filename, idx + 1);
- end if;
- return '';
- end;
- function openfile(filename in varchar2, state in varchar2) return integer is
- path varchar2(1024);
- begin
- path := getpath(filename);
- setoraclepath(path);
- isto_file := utl_file.fopen(YL_DIR, getfilename(filename), state);
- return 1;
- exception
- when others then
- dbms_output.put_line(sqlcode || ':' || sqlerrm);
- return 0;
- end;
- function openfileread(filename in varchar2) return integer is
- begin
- return openfile(filename, 'R');
- end;
- function openfilewrite(filename in varchar2) return integer is
- begin
- return openfile(filename, 'W');
- end;
- procedure writeline(str in varchar2) is
- begin
- utl_file.put_line(isto_file, str); --写入字符串
- end;
- procedure writelinelong(str in long) is
- begin
- utl_file.put_line(isto_file, str); --写入字符串
- end;
- procedure closefile is
- sqlstr varchar2(1024);
- begin
- --utl_file.fflush(isto_file); --刷缓冲
- utl_file.fclose(isto_file); --关闭文件指针
- sqlstr := 'drop directory ' || YL_DIR;
- execute immediate sqlstr;
- end;
- procedure writefile(filename in varchar2, str in long) is
- begin
- if openfilewrite(filename) > 0 then
- writelinelong(str);
- closefile();
- end if;
- end;
- function readline return varchar2 is
- --读取一行放到 fp_buffer 变量里面
- fp_buffer varchar2(20000); --
- str varchar2(4000);
- begin
- loop
- begin
- utl_file.get_line(isto_file, str); --读取一行放到 fp_buffer 变量里面
- fp_buffer := fp_buffer || str;
- fp_buffer := fp_buffer || chr(13); --chr(10)
- exception
- when no_data_found then
- exit;
- end;
- end loop;
- return fp_buffer;
- end;
- function readfile return varchar2 is
- str varchar2(20000);
- begin
- if openfileread('c:/yl.w') > 0 then
- str := readline;
- closefile();
- return str;
- end if;
- return '读文件失败';
- end;
- end YLFileop_pack;
- /
oracle 文件操作封装
最新推荐文章于 2023-08-30 11:44:02 发布