菜单 角色 权限 用户

创建菜单表

-- Create table
create table B_PERMISSION
(
  PERMISSIONID    NUMBER not null,
  PERMISSIONNAME  VARCHAR2(500),
  PERMISSIONURL   VARCHAR2(500),
  PERMISSIONSORT  VARCHAR2(50),
  PERMISSIONLEFT  NUMBER,
  PERMISSIONRIGHT NUMBER,
  REMARK          CLOB
)
tablespace YH
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64
    next 1
    minextents 1
    maxextents unlimited
  );
-- Add comments to the columns 
comment on column B_PERMISSION.PERMISSIONNAME
  is '名称';
comment on column B_PERMISSION.PERMISSIONURL
  is 'url地址';
comment on column B_PERMISSION.PERMISSIONSORT
  is '类别';
comment on column B_PERMISSION.PERMISSIONLEFT
  is 'left';
comment on column B_PERMISSION.PERMISSIONRIGHT
  is 'right';
comment on column B_PERMISSION.REMARK
  is '备注';

创建角色表

-- Create table
create table B_ROLE
(
  ROLEID          NUMBER not null,
  ROLENAME        VARCHAR2(50),
  ROLEDESCRIPTION CLOB
)
tablespace YH
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64
    next 1
    minextents 1
    maxextents unlimited
  );
-- Add comments to the columns 
comment on column B_ROLE.ROLEID
  is '角色编号';
comment on column B_ROLE.ROLENAME
  is '角色名称';
comment on column B_ROLE.ROLEDESCRIPTION
  is '角色备注';

角色权限表

create table B_ROLEPERMISSION
(
  ROLEID       NUMBER not null,
  PERMISSIONID NUMBER,
  REMARK       CLOB
)
tablespace YH
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64
    next 1
    minextents 1
    maxextents unlimited
  );
-- Add comments to the columns 
comment on column B_ROLEPERMISSION.ROLEID
  is '角色编号';
comment on column B_ROLEPERMISSION.PERMISSIONID
  is '权限编号';
comment on column B_ROLEPERMISSION.REMARK
  is '备注';

用户角色表

-- Create table
create table B_USERROLE
(
  ACCOUNTID NUMBER not null,
  ROLEID    NUMBER
)
tablespace YH
  pctfree 10
  initrans 1
  maxtrans 255;
-- Add comments to the columns 
comment on column B_USERROLE.ACCOUNTID
  is '用户编号';
comment on column B_USERROLE.ROLEID
  is '角色编号';

 存储过程

1、删除菜单

CREATE OR REPLACE PROCEDURE "P_DELETENODES"
(
    pid in number
) AS
pleft number;pright number;pcount number;
BEGIN
    select count(*) into pcount from B_Permission where Permissionid=pid;
    if pcount > 0 then
      delete from B_RolePermission where Permissionid=pid;
      select PermissionLeft,PermissionRight INTO pleft,pright from B_Permission where Permissionid=pid;
      delete from B_Permission where PermissionLeft>=pleft and PermissionRight<=pright;
      update B_Permission set PermissionLeft=PermissionLeft-((pright+1)-pleft) where PermissionLeft>pleft;
      update B_Permission set PermissionRight=PermissionRight-((pright+1)-pleft) where PermissionRight>pright;
    end if;
    commit;
      exception  when others then
        rollback;
END;

2、添加子菜单

CREATE OR REPLACE PROCEDURE "P_INSERTLASTNODE"
(
    pid in number,
    pname in varchar2,
    purl in varchar2,
    psort in number,
    premark in varchar2
) AS
pright number;pcount number;
BEGIN
    select count(*) into pcount from B_Permission where Permissionid=pid;
    if pcount > 0 then
      select PermissionRight into pright from B_Permission where Permissionid=pid;
      update B_Permission set PermissionLeft=PermissionLeft+2 where PermissionLeft>pright;
      update B_Permission set PermissionRight=PermissionRight+2 where PermissionRight>=pright;
      select PermissionRight into pright from B_Permission where Permissionid=pid;
      insert into B_Permission(PermissionId,PermissionName,PermissionUrl,PermissionSort,PermissionLeft,PermissionRight,Remark)
      values(S_B_Permission.NEXTVAL,pname,purl,psort,pright-2,pright-1,premark);
    end if;
END;

3、添加兄弟菜单

CREATE OR REPLACE PROCEDURE "P_INSERTRIGHTNODE"
(
    pid in number,
    pname in varchar2,
    purl in varchar2,
    psort in number,
    premark in varchar2
) AS
pright number;pcount number;
BEGIN
    select count(*) into pcount from B_Permission where Permissionid=pid and PermissionLeft!=1;
    if pcount > 0 then
      select PermissionRight into pright from B_Permission where Permissionid=pid;
      update B_Permission set PermissionLeft=PermissionLeft+2 where PermissionLeft>pright;
      update B_Permission set PermissionRight=PermissionRight+2 where PermissionRight>pright;
      insert into B_Permission(PermissionId,PermissionName,PermissionUrl,PermissionSort,PermissionLeft,PermissionRight,Remark)
      values(S_B_Permission.NEXTVAL,pname,purl,psort,pright+1,pright+2,premark);
    end if;
END;

 视图

create or replace view v_tool_biddirectory as
select
PermissionId,
PermissionName,
PermissionUrl,
PermissionSort,
PermissionLeft,
PermissionRight,
treelayer(PermissionId) as PermissionLayer,
parentnode(PermissionId) as PermissionParent
from B_Permission
with read only

函数

1、获取节点层次

CREATE OR REPLACE FUNCTION "TREELAYER"
(
   fid number
)
return number
as
rid number;fleft number;fright number;fcount number;
begin
   rid:=0;
   select count(*) into fcount from B_Permission where PermissionId=fid;
   if fcount > 0 then
      select PermissionLeft,PermissionRight into fleft,fright from B_Permission where PermissionId=fid;
      select count(*) into rid from B_Permission where PermissionLeft<=fleft and PermissionRight>=fright;
   end if;
return rid;
end;

2、获取父节点

CREATE OR REPLACE FUNCTION "PARENTNODE"
(
    fid number
)
return number
as
rid number;fleft number;fright number;fcount number;
begin
 rid:=-1;
  select count(*) into fcount from B_Permission where PermissionId=fid;
   if fcount > 0 then
     select PermissionLeft,PermissionRight into fleft,fright from B_Permission where PermissionId=fid;
     if fleft = 1 then
        rid:= 0;
     else
        select PermissionId into rid from(select tb.*,rownum from B_Permission tb where PermissionLeft<fleft and PermissionRight>fright order by PermissionLeft desc) where rownum=1;
     end if;
   end if;
   return rid;
end;