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;