Oracle 存储过程读文件

create or replace
PROCEDURE PR_FINANCE_PRODUCT_PARSE
(
v_filename  IN  VARCHAR2,   --解析文件名称
v_retvalue  OUT NUMBER      --1为成功，0为失败      
)
AS
    --文件句柄
    filehandle utl_file.file_type;
    --缓冲区
    filebuffer varchar2(255); 
    --文件名
    lv_filename varchar2(100);   
    --文件到表字段的映射
    lv_product_number  varchar2(255);
    lv_product_name    varchar2(255);
    lv_min_capital     varchar2(255);
    lv_max_capital     varchar2(255);
    lv_interest_rate   varchar2(255);
    lv_begin_time      varchar2(255);
    lv_end_time        varchar2(255);
    lv_time_limit      varchar2(255);
    lv_product_status  varchar2(255); 
    --列指针   
    lv_i number;   
    --字符串定位解析指针   
    lv_sposition1 number;
    lv_sposition2 number;
    lv_sposition3 number;
    lv_sposition4 number;
    lv_sposition5 number;
    lv_sposition6 number;
    lv_sposition7 number;
    lv_sposition8 number;
   
     --声明异常
    invalid_data_format  exception;
   
BEGIN
   
    if v_filename is null then
      lv_filename:='finance_product_'|| to_char(sysdate,'YYYYMMDD') ||'.txt' ;
    else
      lv_filename := v_filename;
    end if;       
   
    --打开一个文件句柄 ,同时fopen的第一个参数必须是大写
    filehandle := utl_file.fopen('FINANCE_PRODUCT_DIR',lv_filename,'r');

    --if UTL_FILE.IS_OPEN(filehandle) then   
    --   EXECUTE IMMEDIATE 'truncate table T_FINANCING_PRODUCT';
    --end if;

   Loop   
      begin
          --将文件信息读取到缓冲区filebuffer中,每次读取一行              
          utl_file.get_line(filehandle,filebuffer);            
         dbms_output.put_line(filebuffer); 
         filebuffer:=CONVERT(filebuffer,'UTF8','ZHS16GBK'); --转化汉字，处理乱码 
                     
          --数据文件一行字符串中数据分割符'|'的位置                                                   
          lv_sposition1 := instr(filebuffer, '|', 1,1);  
          lv_sposition2 := instr(filebuffer, '|', 1,2);
          lv_sposition3 := instr(filebuffer, '|', 1,3);
          lv_sposition4 := instr(filebuffer, '|', 1,4);
          lv_sposition5 := instr(filebuffer, '|', 1,5);
          lv_sposition6 := instr(filebuffer, '|', 1,6);
          lv_sposition7 := instr(filebuffer, '|', 1,7);
          lv_sposition8 := instr(filebuffer, '|', 1,8);
         
          --字符串解析正常情况 
          if lv_sposition1 <>0 then                                    
             lv_product_number := substr(filebuffer, 0, lv_sposition1-1);                               --第一列
          else
             raise invalid_data_format;
          end if;
          if lv_sposition2 <> 0 then                                    
             lv_product_name := substr(filebuffer, lv_sposition1+1, lv_sposition2-lv_sposition1-1);
--CONVERT(substr(filebuffer, lv_sposition1+1, lv_sposition2-lv_sposition1-1),'AL32UTF8','ZHS16GBK'); --第二列
          else
             raise invalid_data_format;
          end if;
          if lv_sposition3 <> 0 then                                    
            lv_min_capital := substr(filebuffer, lv_sposition2+1, lv_sposition3-lv_sposition2-1);         --第三列
          else
             raise invalid_data_format;
          end if;
          if lv_sposition4 <> 0 then                                    
             lv_max_capital  := substr(filebuffer, lv_sposition3+1, lv_sposition4-lv_sposition3-1);        --第四列
          else
             raise invalid_data_format;
          end if;  
          if lv_sposition5 <> 0 then                                    
             lv_interest_rate  := substr(filebuffer, lv_sposition4+1, lv_sposition5-lv_sposition4-1);      --第五列
          else
             raise invalid_data_format;
          end if;
           if lv_sposition6 <> 0 then                                    
             lv_begin_time  := substr(filebuffer, lv_sposition5+1, lv_sposition6-lv_sposition5-1);          --第六列 
          else
             raise invalid_data_format;
          end if;
           if lv_sposition7 <> 0 then                                    
              lv_end_time := substr(filebuffer, lv_sposition6+1, lv_sposition7-lv_sposition6-1);             --第七列
          else
             raise invalid_data_format;
          end if;
          if lv_sposition8 <> 0 then                                    
             lv_time_limit  := substr(filebuffer, lv_sposition7+1, lv_sposition8-lv_sposition7-1);            --第八列
             lv_product_status := substr(filebuffer, lv_sposition8+1);                                        --第九列
          else
             raise invalid_data_format;
          end if;
         
          --每列循环完后将信息保存到表中
          dbms_output.put_line('insert data into table');
          insert into T_FINANCING_PRODUCT
          (
          PRODUCT_NUMBER,
          PRODUCT_NAME,
          MIN_CAPITAL,
          MAX_CAPITAL,
          INTEREST_RATE,
          BEGIN_TIME,
          END_TIME,
          TIME_LIMIT,
          PRODUCT_STATUS
          )
          values
          (
          lv_product_number,
          lv_product_name,
          lv_min_capital,
          lv_max_capital,
          lv_interest_rate,
          lv_begin_time,
          lv_end_time,
          lv_time_limit,
          lv_product_status
          );
      exception
             WHEN no_data_found THEN
             exit ;
      end; 
    End Loop;
       delete from T_FINANCING_PRODUCT where to_char(OPERATION_DATE,'YYYYMMDD')=to_char(sysdate-1,'YYYYMMDD');
       commit;
      --关闭句柄       
      utl_file.fclose(filehandle);   
      v_retvalue :=1;   
EXCEPTION
    WHEN invalid_data_format THEN
        v_retvalue :=0;
        rollback;
        dbms_output.put_line('invalid_data_format exception');
    when others then         
       v_retvalue :=0;
       rollback;
       dbms_output.put_line('Othre exception,error code='||sqlcode||',error msg='||sqlerrm); 
END PR_FINANCE_PRODUCT_PARSE;