oracle 自定义类型

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;