万能组织设计及实现

作者:童振华




说明

1)        系统ID:用于标记归属哪个系统,以适应多系统。比如角色,权限,考虑到不同系统,在组织机构的划分上有不同的分法也加上。

2)        用户与部门的关系:一般要求一个用户只属于一个部门。比如一个人即是领导,又是书记,那么这种关系应该由职务表来体现。如果有一个用户即属于A部门,又属于B部门,那么可以再加一个ID做为主键。登录时让用户选择登录部门。

3)        权限:主要基于角色,考虑到灵活性同时提供特权表。最后用户的权限由用户权限视图来体现。

Select 用户ID,权限ID from  用户角色表 PR,角色权限表 RR

   where PR.角色ID=RR.角色ID

UNION

Select 用户ID,权限ID from 用户权限表

4)        用户具体信息:由用户信息表来实现。

基于Oracle的实现
ORACLE是一个优秀的关系型数据库,它的数据可以直接呈现出树型结构的联系。

1. 树结构的描述

树结构的数据存放在表中,数据之间的层次关系即父子关系,通过表中的列与列间的关系来描述,如部门表中的ID和父ID。ID表示该部门的编号,父ID表示该部门的上级部门编号。在表的每一行中都有一个表示父节点的MGR(除根节点外),通过每个节点的父节点,就可以确定整个树结构。

在SELECT命令中使用CONNECT BY 和START WITH 子句可以查询表中的树型结构关系。其命令格式如下:

SELECT 。。。

CONNECT BY {PRIOR 列名1=列名2|列名1=PRIOR 裂名2}

[START WITH];

其中:CONNECT BY子句说明每行数据将是按层次顺序检索,并规定将表中的数据连入树型结构的关系中。PRIORY运算符必须放置在连接关系的两列中某一个的前面。对于节点间的父子关系,PRIOR运算符在一侧表示父节点,在另一侧表示子节点,从而确定查找树结构是的顺序是自顶向下还是自底向上。在连接关系中,除了可以使用列名外,还允许使用列表达式。START WITH 子句为可选项,用来标识哪个节点作为查找树型结构的根节点。若该子句被省略,则表示所有满足查询条件的行作为根节点。

例子1:组织树

SQL>  select DEPT_ID,DEPT_NAME,PARENTID from dept

     CONNECT BY PARENTID=PRIOR  DEPT_ID start with dept_id=1;

 

   DEPT_ID DEPT_NAME                                            PARENTID

---------- -------------------------------------------------- ----------

         1 执法系统                                                    0

         2 市局                                                        1

        30 市局待分配                                                  2

         3 上城区局                                                    1

        20 上城待分配                                                  3

         4 下城区局                                                    1

        21 下城待分配                                                  4

         5 江干区局                                                    1

        22 江干待分配                                                  5

         6 拱墅区局                                                    1

        23 拱墅待分配                                                  6

 

例子2:查找上级组织

SQL>  select DEPT_ID,DEPT_NAME,PARENTID from dept

     CONNECT BY PRIOR PARENTID=  DEPT_ID start with dept_id=20;

 

   DEPT_ID DEPT_NAME                                            PARENTID

---------- -------------------------------------------------- ----------

        20 上城待分配                                                  3

         3 上城区局                                                    1

         1 执法系统                                                    0

注意:请注意例子1与例子2中PRIOR的位置

 

例子3:选取一个结点,列出本组织下的所有用户

select * from (--去因为左连接造成的空记录

select * from  (--建树

select d.dept_id,--建组织表

        d.dept_name,

        d.parentid,

        p.person_id,

        p.person_name

   from dept d,

        person_dept pd,

        person p

  where pd.dept_id(+) = d.dept_id and p.person_id(+)=pd.person_id )--建组织表

   CONNECT BY PRIOR  dept_id=parentid

    START WITH dept_id=20 -- 这里输入结点ID

) where person_id<>0

 

结果:

DEPT_ID,DEPT_NAME,PARENTID,PERSON_ID,PERSON_NAME

20,上城待分配,3,10968,李XX

20,上城待分配,3,10266,高XX

20,上城待分配,3,10268,李AB

20,上城待分配,3,10269,童XX

20,上城待分配,3,10410,祝XX

20,上城待分配,3,10413,陈XX

20,上城待分配,3,10416,任X

20,上城待分配,3,10420,金XX

下面解释一下上述的SQL

其中

select d.dept_id,--建组织表

        d.dept_name,

        d.parentid,

        p.person_id,

        p.person_name

   from dept d,

        person_dept pd,

        person p

  where pd.dept_id(+) = d.dept_id and p.person_id(+)=pd.person_id )--建组织树

然后外面加上

select * from  (--建树  和CONNECT BY PRIOR  dept_id=parentid

    START WITH dept_id=20 -- 这里输入结点ID

进行截支,

然后再套一层select消去因为左连接造成的空记录

如果觉得上述的SQL难以理解的话,我们也可通过视图来分步实现

create or replace view TT as

select dept.dept_id,

        dept.dept_name,

        dept.parentid,

        person_dept.person_id

   from dept,

        person_dept

  where person_dept.dept_id = dept.dept_id;

 

SELECT * FROM TT

CONNECT BY PRIOR  dept_id=   parentid

      START WITH dept_id= 2;

 

例子4:使用level改变结果的显示方式

SELECT dept.dept_id,RPAD(' ',(LEVEL-1)*3) ||dept.dept_name,dept.parentid,dept.sts  FROM dept where sts='A'

     CONNECT BY  PRIOR dept_id=  parentid

      START WITH dept_id= 1

 

DEPT_ID,RPAD('',(LEVEL-1)*3)||DEPT.DEP,PARENTID,STS

2,   市局,1,A

30,      市局待分配,2,A

3,   上城区局,1,A

20,      上城待分配,3,A

52,         上城一中队,20,A

4,   下城区局,1,A

21,      下城待分配,4,A

5,   江干区局,1,A

22,      江干待分配,5,A

posted on 2005-03-29 08:16  使名扬  阅读(2859)  评论(3编辑  收藏  举报