一 Oracle版本: 10g PLSQL: 8.0 (http://liuzidong.iteye.com/blog/953559)
二 具体代码如下:
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