create or replace function get_shrdnmbyids
(
ids varchar2, --要分割的字符串
strsplit varchar2 --分隔符号
)
return varchar2
is
Result varchar2(4000);
tempstr varchar2(4000);
bmname varchar2(200);
userid varchar2(200);
begin
Result:='';
tempstr:=ltrim(rtrim(ids));
while instr(tempstr,strsplit)<>0 loop
userid:=substr(tempstr,0,instr(tempstr,strsplit)-1);
SELECT a.name||'('||b.name||')' into bmname FROM SYS_USER a left join sys_office b on a.company_id = b.id WHERE a.ID=userid;
tempstr:=substr(tempstr,instr(tempstr,strsplit)+length(strsplit),length(tempstr));
Result:=Result||','||bmname;
end loop;
if(instr(ltrim(rtrim(ids)),strsplit)<=0)
then
userid:=tempstr;
SELECT a.name||'('||b.name||')' into bmname FROM SYS_USER a left join sys_office b on a.company_id = b.id WHERE a.ID=userid;
Result:=bmname;
else
Result:=substr(Result,2,length(Result));
end if;
return Result;
end get_shrdnmbyids;
/