Oracle split(分隔字符串函数)

vtlqqepzth

一 Oracle版本: 10g PLSQL: 8.0
二 具体代码如下:
1 类型

CREATE OR REPLACE TYPE ty_str_split IS TABLE OF VARCHAR2 (4000)

2 函数

CREATE OR REPLACE FUNCTION fn_split (p_str IN VARCHAR2, p_delimiter IN VARCHAR2)
RETURN ty_str_split
IS
j INT := 0;
i INT := 1;
len INT := 0;
len1 INT := 0;
str VARCHAR2 (4000);
str_split ty_str_split := ty_str_split ();
BEGIN
len := LENGTH (p_str);
len1 := LENGTH (p_delimiter);
WHILE j < len
LOOP
j := INSTR (p_str, p_delimiter, i);
IF j = 0
THEN
j := len;
str := SUBSTR (p_str, i);
str_split.EXTEND;
str_split (str_split.COUNT) := str;
IF i >= len
THEN
EXIT;
END IF;
ELSE
str := SUBSTR (p_str, i, j - i);
i := j + len1;
str_split.EXTEND;
str_split (str_split.COUNT) := str;
END IF;
END LOOP;
RETURN str_split;
END fn_split;

3 具体应用

CREATE OR REPLACE procedure testsplit(objids in varchar2,message out varchar2)
as
objid varchar2(32);
begin
for x in (select  column_value as objid from (SELECT * FROM TABLE (CAST (fn_split (objids, ',') AS ty_str_split)))t) loop
objid := x.objid;
--执行删除或者有关与ID相关的操作
--delete t wher id=objid;
--commit;
message := message || ' ID:' || objid;   
end loop;
EXCEPTION
WHEN OTHERS THEN
message :='测试split函数失败...';
end testsplit;

其它运用如下：

select t.column_value,rownum row_ from  
table(  
cast (  
fn_split ('1,2,3,4,5,,7,,', ',') as ty_str_split  
)  
) t