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

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

Sql代码   

1. update_customer_rank.txt的格式如下：  
2.   
3. 13123423126  
4. 13123423123  
5. 13123423125  
6. 13123423121  
7. 13123423122  

 

 

 

代码如下：

Sql代码   

1. --创建目录      
2.     create or replace directory MYFILEDIR as '/unicom-masc';  
3.       
4.     GRANT READ, WRITE  ON DIRECTORY MYFILEDIR TO mascbj;  
5.       
6.   
7. --从文件中读取手机号码更改ibs_customer的rank的值      
8.     CREATE OR REPLACE PROCEDURE UPDATE_CUSTOMER_RANK_BYFILE(V_FILEPATH VARCHAR2,--文件路径    
9.                                           V_FILENAME VARCHAR2,--文件名称    
10.                                           V_ROWS     OUT NUMBER)--返回处理记录数    
11.         
12.     AUTHID CURRENT_USER AS    
13.       V_FILE_HANDLE UTL_FILE.FILE_TYPE;    
14.       V_SQL         VARCHAR2(2000);    
15.         
16.       IS_EXISTS NUMBER(10);    
17.       MAX_ID    NUMBER(10);    
18.       COL1      VARCHAR2(1000);    
19.      type c_cursor       IS REF CURSOR;    
20.      c1 c_cursor;    
21.      --V_ROWS NUMBER(10);    
22.     BEGIN       
23.       --创建表    
24.       SELECT COUNT(*) INTO IS_EXISTS  FROM USER_OBJECTS  WHERE OBJECT_TYPE = 'TABLE'  AND OBJECT_NAME = 'IBS_CUS_RANK_TEMP';    
25.       IF IS_EXISTS = 0 THEN    
26.         V_SQL := 'CREATE TABLE IBS_CUS_RANK_TEMP    
27.                   (    
28.                     ID       INTEGER NOT NULL,    
29.                     Mobile      VARCHAR2(20)    
30.                   )';    
31.         EXECUTE IMMEDIATE V_SQL;    
32.       END IF;    
33.         
34.       --创建序列，序列最小值为IBS_CUS_RANK_TEMP表中是最大ID+1    
35.       open c1 for 'select nvl(max(id),0)+1 from IBS_CUS_RANK_TEMP';    
36.       fetch c1 into MAX_ID;    
37.       close c1;    
38.       V_SQL := 'DROP SEQUENCE SEQ_IBS_CUS_RANK_TEMP';     
39.       begin    
40.             EXECUTE IMMEDIATE V_SQL;    
41.       exception when others then    
42.                 null;    
43.       end;    
44.       V_SQL := ' create  sequence SEQ_IBS_CUS_RANK_TEMP    
45.                     minvalue '||MAX_ID||'    
46.                     maxvalue 99999999    
47.                     start with '||MAX_ID||'    
48.                     increment by 1    
49.                     cache 20';     
50.       EXECUTE IMMEDIATE V_SQL;    
51.          
52.         
53.       V_FILE_HANDLE := UTL_FILE.FOPEN('MYFILEDIR', V_FILENAME, 'R');    
54.       V_ROWS        := 0; --处理记录数    
55.       LOOP    
56.         BEGIN    
57.           --将文件信息读取到COL1中,每次读取一行    
58.           UTL_FILE.GET_LINE(V_FILE_HANDLE, COL1);    
59.             
60.           --取序列值         
61.           open c1 for 'select SEQ_IBS_CUS_RANK_TEMP.nextval from dual';    
62.           fetch c1 into MAX_ID;    
63.           close c1;  
64.               
65.           --插入数据     
66.           execute immediate 'INSERT INTO IBS_CUS_RANK_TEMP    
67.             (ID,Mobile) VALUES  (:1,:2)'    
68.           using MAX_ID,substr(COL1,0,LENGTH(COL1)-1);      
69.               
70.             
71.           execute immediate 'update ibs_customer set rank=4 where name=:1'  
72.           using substr(COL1,0,LENGTH(COL1)-1);  
73.             
74.           V_ROWS := V_ROWS + 1;  
75.             
76.           --每5000条提交一次    
77.           IF MOD(V_ROWS, 5000) = 0 THEN    
78.             COMMIT;    
79.           END IF;    
80.             
81.         EXCEPTION    
82.           WHEN NO_DATA_FOUND THEN    
83.             EXIT;    
84.           WHEN OTHERS THEN    
85.             NULL;    
86.         END;    
87.       END LOOP;    
88.       COMMIT;    
89.       --关闭    
90.       UTL_FILE.FCLOSE(V_FILE_HANDLE);    
91.       --dbms_output.put_line(V_ROWS);     
92.          
93.         
94.     END UPDATE_CUSTOMER_RANK_BYFILE;  
95.      
96.       
97. --调用      
98.     DECLARE    
99.     V_ROWS NUMBER(10);    
100.     BEGIN    
101.     UPDATE_CUSTOMER_RANK_BYFILE(v_filepath =>'/unicom-masc',    
102.                v_filename =>'update_customer_rank.txt',    
103.                v_rows =>v_rows);    
104.     END;  

存储过程使用游标进行遍历

为了熟悉oracle存储过程以及游标的使用，在这里简单叙述一下存储过程使用游标进行遍历！

   

 create or replace procedure Test
    --有参数就在（）内写，没有就不写（）
     (
      id in number;   --ID
      )
     is 
      cursor c_test is select id,name from t_user t where  t.id =id ;           --声明显式游标  
      c_t c_test%rowtype;                                                                    --定义游标变量，该变量的类型为基于游标c_test的记录  
    begin 
      --for 循环  
      for c_t in c_test loop  
        dbms_output.put_line(c_t.id|| '-1-' || c_t.name);  
      end loop; 
 
      --while 循环  
      open c_test;--必须要明确的打开游标  
        fetch c_test into c_t;  
        while c_test%found loop  
          dbms_output.put_line(c_t.id|| '-3-' || c_t.name);
          fetch c_test into c_t;  
        end loop;  
      close c_test;   --必须要明确的关闭游标


      --fetch 循环  
      open c_test;--必须要明确的打开游标  
      loop  
        fetch c_test
          into c_t;  
        exit when c_test%notfound;  
        dbms_output.put_line(c_t.id|| '-2-' || c_t.name); 
      end loop;  
      close c_test;  --必须要明确的关闭游标
     
    end Test;