oracle 存储过程读取文件 插入数据库

网上搜索到的文章

http://blog.csdn.net/jiangfeng08/article/details/5718456

 

http://www.cnblogs.com/OceanChen/archive/2009/02/11/1388185.html

 

 

http://cnhnslhs-163-com.iteye.com/blog/1343678

 

 

 

自己的需求 读txt 文件，改变表字段的值

update_customer_rank.txt的格式如下：

13123423126
13123423123
13123423125
13123423121
13123423122

 

 

 

代码如下：

--创建目录    
    create or replace directory MYFILEDIR as '/unicom-masc';
    
    GRANT READ, WRITE  ON DIRECTORY MYFILEDIR TO mascbj;
    

--从文件中读取手机号码更改ibs_customer的rank的值    
    CREATE OR REPLACE PROCEDURE UPDATE_CUSTOMER_RANK_BYFILE(V_FILEPATH VARCHAR2,--文件路径  
                                          V_FILENAME VARCHAR2,--文件名称  
                                          V_ROWS     OUT NUMBER)--返回处理记录数  
      
    AUTHID CURRENT_USER AS  
      V_FILE_HANDLE UTL_FILE.FILE_TYPE;  
      V_SQL         VARCHAR2(2000);  
      
      IS_EXISTS NUMBER(10);  
      MAX_ID    NUMBER(10);  
      COL1      VARCHAR2(1000);  
     type c_cursor       IS REF CURSOR;  
     c1 c_cursor;  
     --V_ROWS NUMBER(10);  
    BEGIN     
      --创建表  
      SELECT COUNT(*) INTO IS_EXISTS  FROM USER_OBJECTS  WHERE OBJECT_TYPE = 'TABLE'  AND OBJECT_NAME = 'IBS_CUS_RANK_TEMP';  
      IF IS_EXISTS = 0 THEN  
        V_SQL := 'CREATE TABLE IBS_CUS_RANK_TEMP  
                  (  
                    ID       INTEGER NOT NULL,  
                    Mobile      VARCHAR2(20)  
                  )';  
        EXECUTE IMMEDIATE V_SQL;  
      END IF;  
      
      --创建序列，序列最小值为IBS_CUS_RANK_TEMP表中是最大ID+1  
      open c1 for 'select nvl(max(id),0)+1 from IBS_CUS_RANK_TEMP';  
      fetch c1 into MAX_ID;  
      close c1;  
      V_SQL := 'DROP SEQUENCE SEQ_IBS_CUS_RANK_TEMP';   
      begin  
            EXECUTE IMMEDIATE V_SQL;  
      exception when others then  
                null;  
      end;  
      V_SQL := ' create  sequence SEQ_IBS_CUS_RANK_TEMP  
                    minvalue '||MAX_ID||'  
                    maxvalue 99999999  
                    start with '||MAX_ID||'  
                    increment by 1  
                    cache 20';   
      EXECUTE IMMEDIATE V_SQL;  
       
      
      V_FILE_HANDLE := UTL_FILE.FOPEN('MYFILEDIR', V_FILENAME, 'R');  
      V_ROWS        := 0; --处理记录数  
      LOOP  
        BEGIN  
          --将文件信息读取到COL1中,每次读取一行  
          UTL_FILE.GET_LINE(V_FILE_HANDLE, COL1);  
          
          --取序列值       
          open c1 for 'select SEQ_IBS_CUS_RANK_TEMP.nextval from dual';  
          fetch c1 into MAX_ID;  
          close c1;
            
          --插入数据   
          execute immediate 'INSERT INTO IBS_CUS_RANK_TEMP  
            (ID,Mobile) VALUES  (:1,:2)'  
          using MAX_ID,substr(COL1,0,LENGTH(COL1)-1);    
            
          
          execute immediate 'update ibs_customer set rank=4 where name=:1'
          using substr(COL1,0,LENGTH(COL1)-1);
          
          V_ROWS := V_ROWS + 1;
          
          --每5000条提交一次  
          IF MOD(V_ROWS, 5000) = 0 THEN  
            COMMIT;  
          END IF;  
          
        EXCEPTION  
          WHEN NO_DATA_FOUND THEN  
            EXIT;  
          WHEN OTHERS THEN  
            NULL;  
        END;  
      END LOOP;  
      COMMIT;  
      --关闭  
      UTL_FILE.FCLOSE(V_FILE_HANDLE);  
      --dbms_output.put_line(V_ROWS);   
       
      
    END UPDATE_CUSTOMER_RANK_BYFILE;
   
    
--调用    
    DECLARE  
    V_ROWS NUMBER(10);  
    BEGIN  
    UPDATE_CUSTOMER_RANK_BYFILE(v_filepath =>'/unicom-masc',  
               v_filename =>'update_customer_rank.txt',  
               v_rows =>v_rows);  
    END;