create or replace function gettypeidbyusername(username in varchar2) return varchar2 is
Result varchar2(4000);
type typeid_cur is ref cursor;
typeids typeid_cur;
begin
open typeids for select f.typeid
from funcinfo f,
functorole fr,
userinfo u,
usertorole ur
where f.id = fr.funcid
and fr.roleid = ur.roleid
and ur.userid = u.id
and u.loginname = username;
fetch typeids into Result;
close typeids;
return(Result);
end gettypeidbyusername;入参就是userinfo表中的username,根据这个条件找到userid。根据这个userid到usertorole表中找到roleid。然后根据这个roleid到functorole表中找到funcid。然后根据这个funcid到funcinfo表中找到typeid的这个列表。返回
Result varchar2(4000);
type typeid_cur is ref cursor;
typeids typeid_cur;
begin
open typeids for select f.typeid
from funcinfo f,
functorole fr,
userinfo u,
usertorole ur
where f.id = fr.funcid
and fr.roleid = ur.roleid
and ur.userid = u.id
and u.loginname = username;
fetch typeids into Result;
close typeids;
return(Result);
end gettypeidbyusername;入参就是userinfo表中的username,根据这个条件找到userid。根据这个userid到usertorole表中找到roleid。然后根据这个roleid到functorole表中找到funcid。然后根据这个funcid到funcinfo表中找到typeid的这个列表。返回
create or replace function gettypeidbyusername(username in varchar2) return varchar2 is
Result varchar2(4000);
begin
select wm_concat(f.typeid) into result
from funcinfo f,
functorole fr,
userinfo u,
usertorole ur
where f.id = fr.funcid
and fr.roleid = ur.roleid
and ur.userid = u.id
and u.loginname = username;
return(Result);
end gettypeidbyusername;