【收藏】Oracle存储过程读写文件

最近有朋友问我用Oricle存储过程读写文件，我说应该没有问题，然后本人用Google搜索一篇好文章，故收藏之！

测试环境：Oracle 9i +Linux for SUSE

有时候我们需要在文件与数据库表之间利用程序来实现两者的交互，这里可以利用UTL_FILE包实现对文件的I/O操作．下面就分别介绍文件写表以及表数据写文件．

[1]表信息导出到文件

在SUSE上建议一个文件夹/home/zxin10/file，然后对其chmod g+w file进行授权(否则无法导出到文件)，再对您指定的路径(/home/zxin10/file)向Oracle的系统表sys.dir$进行注册(否则也是无法成功将信息导出到文件),操作完后可以查询sys.dir$可以看到表中的OS_PATH中有您指定的路径位置．

注册方式：执行SQL语句create or replace directory BBB as '/home/zxin10/file';　即可

存储过程如下：( 写文件时,文件名可以不用先创建,程序中会自动创建指定文件)

CREATE   OR   REPLACE   PROCEDURE  V3_SUB_FETCH_TEST_2
(
   V_TEMP  VARCHAR2 ,
    -- 1为成功，0为失败
   v_retvalue   OUT  NUMBER  
 )
AS
   -- 游标定义
  type ref_cursor_type  is  REF  CURSOR ;
  cursor_select   ref_cursor_type;
  select_cname     varchar2 ( 1000 );
  
  v_file_handle   utl_file.file_type;
  
  v_sql  varchar2 ( 1000 );
  v_filepath  Varchar2 ( 500 );
  v_filename  Varchar2 ( 500 );
   -- 缓冲区
  v_results  Varchar2 ( 500 );
  
  v_pid  varchar2 ( 1000 );
  v_cpcnshortname  Varchar2 ( 500 );
 
   begin
      v_filepath : =  V_TEMP;
       if  v_filepath  is   null   then
         v_filepath : =   ' /home/zxin10/file3 ' ;
       end   if ;
      v_filename: = ' free_ ' ||  substr(to_char(sysdate, ' YYYYMMDDHH24MI ' ), 1 , 10 )  || ' .all '  ;
       -- 游标开始
      select_cname: = ' select cpid,cpcnshortname from zxdbm_ismp.scp_basic ' ; 
       -- 打开一个文件句柄 ,同时fopen的第一个参数必须是大写   
      v_file_handle: = utl_file.fopen( ' BBB ' ,v_filename, ' A ' );
       Open  cursor_select  For  select_cname;   
       Fetch   cursor_select  into  v_pid,v_cpcnshortname;
       While   cursor_select % Found   
      Loop
      v_results : =  v_pid || ' | ' || v_cpcnshortname;
       -- 将v_results写入文件
      utl_file.put_line(v_file_handle,v_results);   
       Fetch   cursor_select  into  v_pid,v_cpcnshortname;      
       End  Loop;
      
       Close  cursor_select; -- 关闭游标
      utl_file.fClose(v_file_handle); -- 关闭句柄
      v_retvalue : = 1 ;
  exception  when  others  then
         v_retvalue : = 0 ; 
   end  V3_SUB_FETCH_TEST_2;

[2]将文件信息导入到表中

和上面一样，先对指定文件路径进行chmod,然后想Oracle的sys.dir$进行路径注册．

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


表LOADDATA脚本：

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

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

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;

文章出处：http://www.blogjava.net/cheneyfree/archive/2007/12/04/165275.html