首页 > 代码库 > 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 代码实现
声明:以上内容来自用户投稿及互联网公开渠道收集整理发布,本网站不拥有所有权,未作人工编辑处理,也不承担相关法律责任,若内容有误或涉及侵权可进行投诉: 投诉/举报 工作人员会在5个工作日内联系你,一经查实,本站将立刻删除涉嫌侵权内容。