oracle角色关联查询

电玩女神 2022-07-10 08:58 289阅读 0赞

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

大致信息如下:

**表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;