以固定分隔符拆分字符串

  在做项目时，经常要读取txt记录文件数据，并要求按照固定格式进行拆分，所以编写了如下的存储过程：

点击(此处)折叠或打开

1. PROCEDURE Str_To_Arr(p_Source_Str IN VARCHAR2,
   
2.                        p_Split_Str IN VARCHAR2,
   
3.                        Errbuf OUT VARCHAR2,
   
4.                        Retcode OUT VARCHAR2,
   
5.                        p_Dens_Array OUT Type_Tab_Arr) IS
   
6. 
   
7.     v_Source_Str VARCHAR2(1000);
   
8.     v_Split_Str VARCHAR2(8);
   
9.     v_Count NUMBER; ----分割的个数
   
10.     v_Split_Local NUMBER; ----分割符出现的位置
    
11.     v_Pos_Local NUMBER; ----分割起始位置
    
12.     v_Tab_Arr Xxt_Gl_Load_Vat_Pkg.Type_Tab_Arr;
    
13. 
    
14.   BEGIN
    
15.     Retcode := 0;
    
16.     v_Pos_Local := 1;
    
17.     v_Count := 1;
    
18.     v_Source_Str := p_Source_Str;
    
19.     v_Split_Str := p_Split_Str;
    
20.     IF Length(v_Source_Str) = 0 THEN
    
21.       Log('判断1');
    
22.       Errbuf := '要分割的字符串为空，请输入要分割的字符串！';
    
23.       Retcode := '2';
    
24.       RETURN;
    
25.     END IF;
    
26.     IF Length(v_Split_Str) = 0 THEN
    
27.       Log('判断2');
    
28.       Errbuf := '请输入要分割的界定符！';
    
29.       Retcode := '2';
    
30.       RETURN;
    
31.     END IF;
    
32.     ---取出字符串的个数
    
33.     SELECT Length(v_Source_Str) -
    
34.            Length(REPLACE(Translate(v_Source_Str, v_Split_Str, ' '),
    
35.                           ' ',
    
36.                           '')) / Length(v_Split_Str)
    
37.       INTO v_Count
    
38.       FROM Dual;
    
39.     FOR i IN 1 .. v_Count LOOP
    
40. 
    
41.       SELECT Instr(v_Source_Str, v_Split_Str, v_Pos_Local + 1, 1)
    
42.         INTO v_Split_Local
    
43.         FROM Dual;
    
44. 
    
45.       IF v_Split_Local = 0 THEN
    
46. 
    
47.         v_Tab_Arr(i) := v_Source_Str;
    
48. 
    
49.         dbms_output.put_line(v_tab_arr(i));
    
50.         EXIT;
    
51.       ELSE
    
52.         IF i = 1 THEN
    
53.           v_Tab_Arr(i) := Substr(v_Source_Str,
    
54.                                  v_Pos_Local,
    
55.                                  v_Split_Local - v_Pos_Local);
    
56.           v_Pos_Local := v_Split_Local;
    
57.           Dbms_Output.Put_Line(v_Tab_Arr(i));
    
58. 
    
59.         ELSE
    
60.           v_Tab_Arr(i) := Substr(v_Source_Str,
    
61.                                  v_Pos_Local + Length(v_Split_Str),
    
62.                                  v_Split_Local -
    
63.                                  (v_Pos_Local + Length(v_Split_Str)));
    
64.           v_Pos_Local := v_Split_Local;
    
65. 
    
66.           --Dbms_Output.Put_Line(v_Tab_Arr(i));
    
67.         END IF;
    
68.       END IF;
    
69.     END LOOP;
    
70.     p_Dens_Array := v_Tab_Arr;
    
71.   END Str_To_Arr;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29507357/viewspace-2124552/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29507357/viewspace-2124552/