oracle 自定义类型

最新推荐文章于 2021-04-08 07:58:25 发布

转载最新推荐文章于 2021-04-08 07:58:25 发布 · 72 阅读

0 ·

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/kelly/archive/2008/08/30/1280189.html

本文介绍了一个用于生成用户权限列表的PL/SQL过程。该过程通过创建两种类型RIGHTOBJECT和RIGHTOBJECTCOLLECTION来组织权限数据，并定义了getuserrightlist函数来收集用户的个人权限和用户组权限。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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;

转载于:https://www.cnblogs.com/kelly/archive/2008/08/30/1280189.html