oracle角色关联查询

今天看到一篇帖子问题是关于角色查询的,此角色表设计得不是很理想,但是还是能满足角色设计的基本要求的,就是查询起来可能会比较麻烦,

大致信息如下:

表i_project_permit
----------------------------------------------------------------
| User | ProjectID | GroupCode | RoleCode | PermitCode |
----------------------------------------------------------------
| U001 | P00000001 | G01;G02;G03; | R01;R02;R03; | P01;P02;P03;|
----------------------------------------------------------------
| U001 | P00000002 | G02;G04; | R02;R04; | P02;P04; |
----------------------------------------------------------------
| U001 | P00000003 | G03;G04; | R03;R04; | P03;P04 |
----------------------------------------------------------------

表i_role_permit
-----------------------------------------
| RoleCode | GroupCode | PermitCode |
-----------------------------------------
| R01 | G01;G04; | P02;P05; |
-----------------------------------------
| R02 | G02;G04; | P02;P06; |
-----------------------------------------
| R03 | G03;G05; | P02;P07; |
-----------------------------------------
| R04 | G04;G05; | P02;P08; |
-----------------------------------------

表i_permit_group
-----------------------------------------
| GroupCode | PermitCode |
-----------------------------------------
| G01 | P01;P02 |
-----------------------------------------
| G02 | P03;P04 |
-----------------------------------------
| G03 | P05;P06 |
-----------------------------------------
| G04 | P07;P08 |
-----------------------------------------
| G05 | P09;P10 |
-----------------------------------------

根据UserId 查询出它的权限

例如：
UserId U001
PermitCode = i_project_permit（PermitCode） + i_role_permit（PermitCode和） + i_permit_group（PermitCode和）

 

首先我按照推测大致还原表几个和数据如下:

create table i_project_permit(Users varchar2(20), ProjectID varchar2(20),
GroupCode varchar2(20), RoleCode varchar2(20), PermitCode varchar2(20));

insert into i_project_permit values( 'U001' , 'P00000001' , 'G01;G02;G03;' , 'R01;R02;R03;','P01;P02;P03;');
insert into i_project_permit values( 'U001' , 'P00000002' , 'G02;G04;' , 'R02;R04;','P02;P04;');

select *  from  i_project_permit;

 

create table i_role_permit(  RoleCode varchar2(20), GroupCode varchar2(20), PermitCode varchar2(20));

insert into i_role_permit values ('R01' , 'G01;G04;' ,'P02;P05;');
insert into i_role_permit values ('R02' , 'G02;G04;' ,'P02;P06;');
insert into i_role_permit values ('R03' , 'G03;G05;' ,'P02;P07;');
insert into i_role_permit values ('R04' , 'G04;G05;' ,'P02;P08;');

select *  from  i_role_permit;

create table i_permit_group(GroupCode varchar2(20), PermitCode varchar2(20));

insert into  i_permit_group values('G01', 'P01;P02');
insert into  i_permit_group values('G02' , 'P03;P04');
insert into  i_permit_group values('G03' , 'P05;P06');
insert into  i_permit_group values('G04' , 'P07;P08');
insert into  i_permit_group values('G05', 'P09;P10');

 

以上是基础表和数据准备.看看表的结构,发现里面组和角色都是字符串组合的,肯定要进行分割,

建立一个函数进行分割,返回数组类型,代码如下:

 

create or replace function split_str(var_str   in varchar2,var_split in varchar2)
/****************************************************
  注意 先执行下面语句 创建类型
  create or replace type t_ret_table is table of varchar2(100)

  ** 函数名称：split_str

  ** 参    数：【名称】         【类型 】      【说明】
  **           var_str          varchar2       要拆分的字符串
  **           var_split        varchar2       字符串分隔符
  ** 返 回 值：Result           t_ret_table    拆分后数组集合
  ** 摘    要：拆分字符串

  调用 举例：
  select * from table(split_str('2008-10-21','-'))
  ****************************************************/
  return t_ret_table is

  var_out     t_ret_table;
  var_tmp     varchar2(4000);
  var_element varchar2(4000);

begin
  var_tmp := var_str;
  var_out := t_ret_table();
  --如果存在匹配的分割符
  while instr(var_tmp, var_split) > 0 loop
    var_element := substr(var_tmp, 1, instr(var_tmp, var_split) - 1);
    var_tmp     := substr(var_tmp,
                          instr(var_tmp, var_split) + length(var_split),
                          length(var_tmp));
    --var_out.extend(1);
    var_out.extend;
    var_out(var_out.count) := var_element;
  end loop;

  --var_out.extend(1);
  var_out.extend;
  var_out(var_out.count) := var_tmp;
  return var_out;
end split_str;

然后就是再建立一个函数把分割得到的数组进行查询出他具有的权限代码,如下

 

create or replace function fn_group_permitcode(groupstr in varchar2)
  return varchar2 is
  v_ret varchar2(100);
begin
  select replace(substr(max(sys_connect_by_path(permitcode, ':')), 2),':',';')
    into v_ret
    from (select ig.groupcode,ig.permitcode, row_number() over(order by ig.groupcode) rn
            from i_permit_group ig
           where ig.groupcode in
                 (SELECT * from TABLE(split_str(groupstr, ';')))) a
   start with rn = 1
  connect by rn - 1 = prior rn;
  return v_ret;
end fn_group_permitcode;

 

以上这个的根据GROUP获取权限代码的函数,对于ROLE,需要首先得到ROLE所属的GROUP并上ROLE而外的权限,即可,时间关系.以后再补全.

 

然后用以下查询得到结果:(只对GROUP的权限进行了变换.ROLE的同理..)

select ip.users,ip.projectid,fn_group_permitcode(ip.groupcode),ip.rolecode,ip.permitcode from  i_project_permit ip;

 

得到..

USERS PROJECTID FN_GROUP_PERMITCODE(IP.GROUPCO ROLECODE PERMITCODE
U001 P00000001 P01;P02;P03;P04;P05;P06 R01;R02;R03; P01;P02;P03;
U001 P00000002 P03;P04;P07;P08 R02;R04; P02;P04;