万能组织设计及实现
作者：童振华
说明
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