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;