oracle 文件操作封装

1. createorreplacepackageYLFileop_packis
3. --Author:YANLEI
4. --Created:2008-12-2416:08:02
5. --Purpose:文件操作包
6. YL_DIRCONSTANTvarchar2(32):='YL_DIR';--财务表收费FormID
7. --从后面查找
8. functionlastindex(strinvarchar2,findstrinvarchar2)returninteger;
9. --获得一个文件的路径
10. functiongetpath(filenameinvarchar2)returnvarchar2;
11. --获得一个文件的文件
12. functiongetfilename(filenameinvarchar2)returnvarchar2;
13. procedurewritefile(filenameinvarchar2,strinlong);
14. functionreadfilereturnvarchar2;
16. endYLFileop_pack;
17. /
18. createorreplacepackagebodyYLFileop_packis
19. isto_fileutl_file.file_type;--如上
20. proceduresetoraclepath(pathinvarchar2)is
21. sqlstrvarchar2(1024);
22. begin
23. sqlstr:='createorreplacedirectory'||YL_DIR||'as'''||path||'''';
24. executeimmediatesqlstr;
25. end;
26. --oracle字符串从后面查找byyanleigisEmail:[e-mail]
27. functionlastindex(strinvarchar2,findstrinvarchar2)returnintegeris
28. iinteger;
29. numinteger;
30. sublninteger;
31. begin
32. num:=length(str);
33. subln:=length(findstr);
34. ifsubln>numthen
35. return-1;
36. endif;
37. i:=num-subln+1;
38. whilei>0loop
39. ifsubstr(str,i,subln)=findstrthen
40. returni;
41. endif;
42. i:=I-1;
43. endloop;
44. return-1;
45. end;
46. --获得一个文件的路径
47. functiongetpath(filenameinvarchar2)returnvarchar2is
48. idxinteger;
49. begin
50. idx:=lastindex(filename,'/');
51. if(idx>0)then
52. returnsubstr(filename,1,idx);
53. endif;
54. return'';
55. end;
56. --获得一个文件的文件
57. functiongetfilename(filenameinvarchar2)returnvarchar2is
58. idxinteger;
59. begin
60. idx:=lastindex(filename,'/');
61. if(idx>0)then
62. returnsubstr(filename,idx+1);
63. endif;
64. return'';
65. end;
66. functionopenfile(filenameinvarchar2,stateinvarchar2)returnintegeris
67. pathvarchar2(1024);
68. begin
69. path:=getpath(filename);
70. setoraclepath(path);
71. isto_file:=utl_file.fopen(YL_DIR,getfilename(filename),state);
72. return1;
73. exception
74. whenothersthen
75. dbms_output.put_line(sqlcode||':'||sqlerrm);
76. return0;
77. end;
78. functionopenfileread(filenameinvarchar2)returnintegeris
80. begin
81. returnopenfile(filename,'R');
82. end;
84. functionopenfilewrite(filenameinvarchar2)returnintegeris
85. begin
86. returnopenfile(filename,'W');
87. end;
88. procedurewriteline(strinvarchar2)is
89. begin
90. utl_file.put_line(isto_file,str);--写入字符串
91. end;
92. procedurewritelinelong(strinlong)is
93. begin
94. utl_file.put_line(isto_file,str);--写入字符串
95. end;
96. procedureclosefileis
97. sqlstrvarchar2(1024);
98. begin
99. --utl_file.fflush(isto_file);--刷缓冲
100. utl_file.fclose(isto_file);--关闭文件指针
101. sqlstr:='dropdirectory'||YL_DIR;
102. executeimmediatesqlstr;
104. end;
106. procedurewritefile(filenameinvarchar2,strinlong)is
107. begin
108. ifopenfilewrite(filename)>0then
109. writelinelong(str);
111. closefile();
113. endif;
114. end;
115. functionreadlinereturnvarchar2is
116. --读取一行放到fp_buffer变量里面
117. fp_buffervarchar2(20000);--
118. strvarchar2(4000);
119. begin
120. loop
121. begin
122. utl_file.get_line(isto_file,str);--读取一行放到fp_buffer变量里面
123. fp_buffer:=fp_buffer||str;
124. fp_buffer:=fp_buffer||chr(13);--chr(10)
126. exception
127. whenno_data_foundthen
128. exit;
129. end;
130. endloop;
132. returnfp_buffer;
133. end;
134. functionreadfilereturnvarchar2is
135. strvarchar2(20000);
136. begin
137. ifopenfileread('c:/yl.w')>0then
138. str:=readline;
139. closefile();
140. returnstr;
142. endif;
143. return'读文件失败';
144. end;
146. endYLFileop_pack;
147. /