oralce存储过程中编写split函数

创建split的函数
create or replace package body test_common is
FUNCTION SPLIT(
in_str in varchar2,
in_fengefu in varchar2 DEFAULT(’,’)
)RETURN return_result
IS
j NUMBER := 0;
I NUMBER :=1;
len NUMBER :=0;
len NUMBER :=0;
str varchar2(1000);
split_result return_result := return_result();
BEGIN
len := LENGTH(s_str);
len1 := LENGTH(in_fengefu );
WHILE j < len LOOP
j := INSTR(in_str, in_fengefu ,i);
IF j = 0 THEN
j := len;
str := SUBSTR(in_str,i);
split_result.EXTEND;
split_result(split_result.COUNT) := str;
IF i >= len THEN
EXIT;
END IF;
ELSE
str := SUBSTR(in_str, i, j-i);
i := j + len1;
split_result.EXTEND;
split_result(split_result.COUNT) := str;
END IF;
END LOOP;
RETURN split_result;
END SPLIT; --split函数结束
–通过名称删除部门
PROCEDURE delete_dept_byname(
in_name in varchar2;
)
v_names return_result ;
DEGIN
if in_name is not null then
v_names := test_common.SPLIT(in_name, ‘,’); --把传入的name以逗号分割
FOR i IN 1 … v_names.count LOOP
delete from t_department where name = v_names(i);
END LOOP;
end if;
END delete_dept_byname;

end test_common;
注意事项：return_result为type类型，需要先创建，如下：
create or replace TYPE return_result IS TABLE OF VARCHAR2(3000);