利用PL/SQL读写文件

恩，这是上星期五去面试的时候的面试问题，当时没回答上来，因为根本没用过，都是利用Java进行文件的读写的，我也不知道在PL/SQL中读写文件有什么好处，权当扩大一下知识面吧。

首先，我们需要用到Oracle的DIRECTORY，DIRECTORY简单的说就是一个目录，可以存放绝对路径，也可以存放相对路径，创建目录的语法如下：

Sql代码  

1. create directory exp_dir as 'E:/directory';  

create directory exp_dir as 'E:/directory';

当然，创建目录前我们需要把相应的权限授予用户，我们以scott用户为例，虽然scott doesnt know~~~~

Sql代码  

1. grant create any directory to scott;  

grant create any directory to scott;

然后我们再创建一个用作测试的存储过程：

Sql代码  

1. CREATE OR REPLACE PROCEDURE PROC_DIRECTORY_TEST AS   
2. BEGIN  
3.   declare  
4.       fhandle utl_file.file_type;  
5.     begin  
6.       fhandle := utl_file.fopen('EXP_DIR', 'example.txt', 'w');  
7.       utl_file.put_line(fhandle , 'eygle test write one');  
8.       utl_file.put_line(fhandle , 'eygle test write two');  
9.       utl_file.fclose(fhandle);  
10.     end;  
11.   
12. END PROC_DIRECTORY_TEST;  

CREATE OR REPLACE PROCEDURE PROC_DIRECTORY_TEST AS BEGIN  declare      fhandle utl_file.file_type;    begin      fhandle := utl_file.fopen('EXP_DIR', 'example.txt', 'w');      utl_file.put_line(fhandle , 'eygle test write one');      utl_file.put_line(fhandle , 'eygle test write two');      utl_file.fclose(fhandle);    end;END PROC_DIRECTORY_TEST;

运行这个存储过程，我们发现出现错误：
ORA-29283: 文件操作无效
ORA-06512: 在 "SYS.UTL_FILE", line 475
ORA-29283: 文件操作无效
ORA-06512: 在 "SCOTT.PROC_DIRECTORY_TEST", line 6
ORA-06512: 在 line 2

原因是我们需要手动创建目录'E:/directory'

创建后再执行就成功了,那读文件怎么办呢？很简单，我们一般读文件的需求就是将文件中的内容插入到表中，就不自己写了，直接转载：

文件zte.apsuic位于/home/zxin10/file下,其数据格式：
1|22|cheng
2|33|zhou
3|44|heng
4|55|yaya


表LOADDATA脚本：

Sql代码  

1. -- Create table  
2. create table LOADDATA  
3. (  
4.   ID   VARCHAR2(50),  
5.   AGE  VARCHAR2(50),  
6.   NAME VARCHAR2(50)  
7. )  

-- Create tablecreate table LOADDATA(  ID   VARCHAR2(50),  AGE  VARCHAR2(50),  NAME VARCHAR2(50))

   


程序如下：(读取文件时,指定文件名一定要预先存在,否则程序会失败)

Sql代码  

1. create or replace directory BBB as '/home/zxin10/file';  
2. /  
3. --作用法是将特定的文件路径信息想Oracle注册(注册信息存放在sys.dir$表中)  
4.   
5. CREATE OR REPLACE PROCEDURE V3_SUB_FETCH_TEST_3  
6. (  
7.    --文件中的信息导入表中  
8.      V_TEMP VARCHAR2,  
9.      v_retvalue   OUT NUMBER --1 成功 ，0失败  
10. AS   
11.   v_file_handle   utl_file.file_type;    
12.   v_sql varchar2(1000);  
13.   v_filepath Varchar2(500);  
14.   v_filename Varchar2(500);    
15.   --文件到表字段的映射  
16.   v_id varchar2(1000);  
17.   v_age varchar2(1000);  
18.   v_name varchar2(1000);  
19.   --缓冲区  
20.   v_str varchar2(1000);  
21.   --列指针  
22.   v_i number;  
23.   --字符串定位解析指针  
24.   v_sposition1 number;  
25.   v_sposition2 number;  
26.   begin  
27.       v_filepath := V_TEMP;  
28.       if v_filepath is null then  
29.          v_filepath := '/home/zxin10/file';  
30.       end if;  
31.       v_filename:='zte.apsuic';  
32.       --v_sql:= 'create or replace directory CCC as '''|| v_filepath || '''';  
33.       --execute immediate v_sql;   
34.         
35.       v_file_handle:=utl_file.fopen('CCC',v_filename,'r');         
36.       Loop  
37.              --将文件信息读取到缓冲区v_str中,每次读取一行  
38.              utl_file.get_line(v_file_handle,v_str);  
39.              --dbms_output.put_line(v_str);   
40.              --针对每行的列数  
41.              v_i := 1;  
42.              --针对字符串每次的移动指针  
43.              v_sposition1 := 1;  
44.              --文件中每行信息3列,循环3次  
45.              FOR I IN 1..3 loop                 
46.              --当instr(v_str, '|', 6)其中v_str为1|22|wuzhuocheng ，它返回0  
47.              v_sposition2 := instr(v_str, '|', v_sposition1);  
48.              --字符串解析正常情况  
49.              if v_sposition2 <> 0 then  
50.                if v_i=1     then  
51.                   v_id := substr(v_str, v_sposition1, v_sposition2 - v_sposition1);      --第一列                 
52.                elsif  v_i=2 then  
53.                     v_age := substr(v_str, v_sposition1, v_sposition2 - v_sposition1); --第二列  
54.                elsif v_i=3  then  
55.                   v_name := substr(v_str, v_sposition1, v_sposition2 - v_sposition1);    --第三列   
56.                else  
57.                   return;  
58.                end if;                              
59.              --字符串解析异常情况  
60.              else   
61.                if v_i=1    then  
62.                 v_id := substr(v_str, v_sposition1);      --第一列  
63.                elsif v_i=2 then  
64.                   v_age := substr(v_str, v_sposition1); --第二列  
65.                elsif v_i=3 then  
66.                 v_name := substr(v_str, v_sposition1);    --第三列   
67.                else  
68.                 return;  
69.                end if;    
70.              end if;     
71.              v_sposition1 := v_sposition2 + 1;  
72.              v_i := v_i+1;  
73.              end loop;   
74.              --每列循环完后将信息insert into表中  
75.              insert into zxdbm_ismp.loaddata values(v_id,v_age,v_name);                    
76.       End Loop;  
77.       --关闭句柄  
78.       utl_file.fClose(v_file_handle);  
79.       v_retvalue :=1;  
80.   exception when others then  
81.          v_retvalue :=0;   
82.   end V3_SUB_FETCH_TEST_3;  

create or replace directory BBB as '/home/zxin10/file';/--作用法是将特定的文件路径信息想Oracle注册(注册信息存放在sys.dir$表中)CREATE OR REPLACE PROCEDURE V3_SUB_FETCH_TEST_3(   --文件中的信息导入表中     V_TEMP VARCHAR2,     v_retvalue   OUT NUMBER --1 成功 ，0失败AS   v_file_handle   utl_file.file_type;    v_sql varchar2(1000);  v_filepath Varchar2(500);  v_filename Varchar2(500);    --文件到表字段的映射  v_id varchar2(1000);  v_age varchar2(1000);  v_name varchar2(1000);  --缓冲区  v_str varchar2(1000);  --列指针  v_i number;  --字符串定位解析指针  v_sposition1 number;  v_sposition2 number;  begin      v_filepath := V_TEMP;      if v_filepath is null then         v_filepath := '/home/zxin10/file';      end if;      v_filename:='zte.apsuic';      --v_sql:= 'create or replace directory CCC as '''|| v_filepath || '''';      --execute immediate v_sql;             v_file_handle:=utl_file.fopen('CCC',v_filename,'r');             Loop             --将文件信息读取到缓冲区v_str中,每次读取一行             utl_file.get_line(v_file_handle,v_str);             --dbms_output.put_line(v_str);              --针对每行的列数             v_i := 1;             --针对字符串每次的移动指针             v_sposition1 := 1;             --文件中每行信息3列,循环3次             FOR I IN 1..3 loop                            --当instr(v_str, '|', 6)其中v_str为1|22|wuzhuocheng ，它返回0             v_sposition2 := instr(v_str, '|', v_sposition1);             --字符串解析正常情况             if v_sposition2 <> 0 then               if v_i=1     then                  v_id := substr(v_str, v_sposition1, v_sposition2 - v_sposition1);      --第一列                              elsif  v_i=2 then                    v_age := substr(v_str, v_sposition1, v_sposition2 - v_sposition1); --第二列               elsif v_i=3  then                  v_name := substr(v_str, v_sposition1, v_sposition2 - v_sposition1);    --第三列                else                  return;               end if;                                         --字符串解析异常情况             else                if v_i=1    then                v_id := substr(v_str, v_sposition1);      --第一列               elsif v_i=2 then                  v_age := substr(v_str, v_sposition1); --第二列               elsif v_i=3 then                v_name := substr(v_str, v_sposition1);    --第三列                else                return;               end if;               end if;                v_sposition1 := v_sposition2 + 1;             v_i := v_i+1;             end loop;              --每列循环完后将信息insert into表中             insert into zxdbm_ismp.loaddata values(v_id,v_age,v_name);                        End Loop;      --关闭句柄      utl_file.fClose(v_file_handle);      v_retvalue :=1;  exception when others then         v_retvalue :=0;   end V3_SUB_FETCH_TEST_3;

分享到：