ex1:
RIGHTOBJECT是UserID,RightTypeId,UserDutyId,RightId,RightFlag集合类型
CREATE OR REPLACE TYPE "RIGHTOBJECT" as object
(
UserID number,
RightTypeID number,
UserDutyID number,
RightID number,
RightFlag number
)
RIGHTOBJECTCOLLECTION 是RIGHTOBJECT的集合类型
CREATE OR REPLACE TYPE "RIGHTOBJECTCOLLECTION" as table of rightobject
用法:
function getuserrightlist(userid sys_userinfo.userid%type,
righttypeid rgt_righttype.righttypeid%type)
return rightobjectcollection as
result rightobjectcollection;
begin
result := rightobjectcollection();
for mycs in (
--个人自定义权限
select *
from rgt_userright
where userid = getuserrightlist.userid
and righttypeid = getuserrightlist.righttypeid) loop
result.extend;
result(result.count) := rightobject(0, 0, 0, 0, 0);
result(result.count).userid := mycs.userid;
result(result.count).righttypeid := mycs.righttypeid;
result(result.count).rightid := mycs.rightid;
result(result.count).userdutyid := mycs.userdutyid;
result(result.count).rightflag := mycs.rightflag;
end loop;
--插入个人用户组的权限
for mycs in (select rightid,
righttypeid,
max(rightflag) rightflag,
getuserrightlist.userid
from rgt_rightgroupcontent
where rightgroupid in
(select rightgroupid
from rgt_usergroupright
where usergroupid in
(select distinct usergroupid
from sys_groupuser
where userid = getuserrightlist.userid))
and righttypeid = getuserrightlist.righttypeid
and rightid not in (select rightid from table(result))
group by rightid, righttypeid /*,rightgroupid*/) loop
result.extend;
result(result.count) := rightobject(0, 0, 0, 0, 0);
result(result.count).userid := mycs.userid;
result(result.count).righttypeid := mycs.righttypeid;
result(result.count).rightid := mycs.rightid;
result(result.count).rightflag := mycs.rightflag;
end loop;
return result;
end;