首页 > 代码库 > Oracle 存储过程 split 代码实现

Oracle 存储过程 split 代码实现

实现 字符串分割, 算法 如下:

算法 1:

DECLARE  remove_column   myvarray_list;  x    varchar(1000);  sub  varchar(1000);  i    NUMBER;  j    NUMBER;  c    NUMBER;  rcount NUMBER;BEGIN    remove_column := myvarray_list();   --init array.        sub:=ORA-26786: 键为 ("AAC001") = (11370911196606055225) 的行存在, 但具有冲突列 "AAC003", "AAE011", "AAE036", "AAE476" (在表 BI3.AC01 中)ORA-01403: 未找到任何数据;    --sub:=‘ORA-26786: A row with key ("C1", "C2") = (TEST1, TEST1) exists but has conflicting column(s) "C4" in table SK.T1 ORA-01403: no data found‘;    i:= INSTR(sub,=,1, 1);    sub := SUBSTR(sub, i);    i:= INSTR(sub, ",1, 1);    sub := SUBSTR(sub, i);    i := INSTR(sub,",-1, 1);    sub := SUBSTR(sub, 1,i);     sub:=REPLACE(sub,",‘‘);    sub:=REPLACE(sub, ,‘‘);    --DBMS_OUTPUT.PUT_LINE(sub);   ------------------------------------------------------------    j:=1;    rcount :=1;    WHILE  INSTR(sub,,,1, j) !=0 LOOP         i := INSTR(sub,,,1, j);       IF j = 1 THEN          c :=1;        ELSE           c :=1+ INSTR(sub,,,1, j-1);       END IF;       x := SUBSTR(sub,c,i-c);        j:=j+1;       --DBMS_OUTPUT.PUT_LINE(x);       remove_column.extend;        remove_column(rcount) := x;       rcount :=rcount+1;           END LOOP;    IF j > 1 THEN       i := INSTR(sub,,,1, j-1);       x := SUBSTR(sub,i+1);       --DBMS_OUTPUT.PUT_LINE(x);    ELSE       x := sub;       --DBMS_OUTPUT.PUT_LINE(x);    END IF;    remove_column.extend;     remove_column(rcount) := x;        FOR x in 1..remove_column.count loop         dbms_output.put_line(remove_column(x));    END loop;END;

算法 2:

DECLARE  str_split   myvarray_list;  p_str  varchar(1000);  j INT := 0;  i INT := 1;  lens INT := 0;  lenp INT := 0;  p_delimiter VARCHAR(1) := ,;  str VARCHAR2 (1000);BEGIN    str_split := myvarray_list();   --init array.        p_str:=ORA-26786: 键为 ("AAC001") = (11370911196606055225) 的行存在, 但具有冲突列 "AAC003", "AAE011", "AAE036", "AAE476" (在表 BI3.AC01 中)ORA-01403: 未找到任何数据;    --p_str:=‘ORA-26786: A row with key ("C1", "C2") = (TEST1, TEST1) exists but has conflicting column(s) "C4", "C3" in table SK.T1 ORA-01403: no data found‘;    i:= INSTR(p_str,=,1, 1);    p_str := SUBSTR(p_str, i);    i:= INSTR(p_str, ",1, 1);    p_str := SUBSTR(p_str, i);    i := INSTR(p_str,",-1, 1);    p_str := SUBSTR(p_str, 1,i);     p_str:=REPLACE(p_str,",‘‘);    p_str:=REPLACE(p_str, ,‘‘);   ------------------------------------------------------------    lens := LENGTH (p_str);    lenp := LENGTH (p_delimiter);    dbms_output.put_line(lens);    i := 1;    j :=0;    WHILE j < lens LOOP        j := INSTR (p_str, p_delimiter, i);        IF j = 0 THEN            j := lens;            str := SUBSTR (p_str, i);                    str_split.EXTEND;            str_split (str_split.COUNT) := str;            IF i >= lens            THEN                EXIT;            END IF;        ELSE            str := SUBSTR (p_str, i, j - i);            i := j + lenp;            str_split.EXTEND;            str_split (str_split.COUNT) := str;        END IF;    END LOOP;    FOR x in 1..str_split.count loop         dbms_output.put_line(str_split(x));    END loop;END;

 

Oracle 存储过程 split 代码实现