Oracle存儲過程讀寫文件

Oracle存儲過程讀寫文件是實現文件與數據庫表之間交互的重要手段，下面就為您詳細介紹Oracle存儲過程讀寫文件方面的知識，希望對您能有所幫助。

有時候我們需要在文件與數據庫表之間利用程序來實現兩者的交互，這裡可以利用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;