ORACLE分割字符串

create or replace function DRAG_GET_VINDICATE_ID(V_VINDICATE_CODE in varchar2)
return varchar2 is
Result varchar2(200);
v_team_code varchar2(200);
v_temp_code varchar2(200);
v_department_id varchar2(200);
v_count number;
begin
Result := '';
v_count := 0;
v_team_code := V_VINDICATE_CODE;
select INSTR(v_team_code, ';', 1, 1) into v_count from dual;
--如果不包含分号
if v_count = 0 then
select p.department_id
into Result
from pub_department p
where p.code = v_team_code;
--如果包含分号
else
while v_team_code is not null loop
--判断是否有分号
select INSTR(v_team_code, ';', 1, 1) into v_count from dual;
if v_count = 0 then
--如果没有，赋值为空 下次循环跳出
v_temp_code := v_team_code;
v_team_code := '';
else
--如果有，截取第一个分号
select substr(v_team_code, 0, INSTR(v_team_code, ';', 1, 1) - 1)
into v_temp_code
from dual;
end if;
--根据CODE找ID
select p.department_id
into v_department_id
from pub_department p
where p.code = v_temp_code;
Result := Result || v_department_id || ';';
--截取分号后的字符串
select substr(v_team_code,
INSTR(v_team_code, ';', 1, 1) + 1,
length(v_team_code))
into v_team_code
from dual;
--去掉最后一个分号
if v_team_code is null then
Result := substr(Result, 0, length(Result) - 1);
end if;
end loop;
end if;
return(Result);
end DRAG_GET_VINDICATE_ID;

下面这个就很简洁

select pub_department.department_id
from pub_department
where instr(';' || 'CAS4592;CAS4601' || ';',
';' || pub_department.code || ';') > 0;