1 存储子程序

今天在我们的项目经理肖老大带领下（做一个管理系统的分级权限）
      我们小组讨论了一个分级权限的问题，确实业务复杂了，想了几种方案，

说到权限管理，我也来项目总结一下 ，知识浅薄，希望javaEye众多高手和师兄们指点一下！（当然我总结的是数据库方面的）


当然 我随便举一个超市管理系统的例子吧！


数据库分析的前提条件
1、掌握数据库关系理论
   关系DB=实体+关系=PK+FK
   关系 = 一对一(11,11) + 一对多(1M，11) + 多对多(1M，1M)
   范式(3NF)
   1NF = 不能将很多信息放一个字段(列不可再分)；每个表都要有主键(每一行都必须不同)；
   2NF =  找实体时，只能找实体的固有属性(非PK列必须依赖于PK列)
           找学生实体，只能找姓名，性别等，不能找班级信息。
   3NF = 非PK列之间必须相互独立
           找员工，出生日期与年龄不要在同一个表，或者出生日期不与生肖在同一个表。
2、熟悉PD建模(CDM→PDM→代码)


任务：根据关系型，找实体，找关系

步骤
1、建立ER模型(CDM，概念数据模型)：找实体，找关系
   1NF：每个实体必须有标识属性→每个实体必须有主键。
2、







系统中的人员分类

-------------------------------------权限系统(后台系统)
一、操作员：能够登录并操作的人员

思考：
案例：张三(操作员) 能不能  增加商品(权限)？
权限系统中，通常不让人直接接触 权限。而是： 管理员(岗位，角色，权限组) 才能 增加商品(权限)
于是：先让 张三 当上 管理员，张三就能 增加商品(权限)

实体分析：操作员、角色、权限

岗位：确定某人 当什么官，是什么职位
权限：确定 能做什么事

组合：什么岗位 才能 做什么事
不是：什么人 能做什么事
分析：
直接设计：
100人  ，10件事： 都能做：100*10 = 1000次(条记录)，不方便更改 ，多1件事情，增加100条
间接设计：
100人，10件事，1个岗位：1+100*1 = 101条记录，方便更改，多1件事情，增加1条

  一定要有岗位！


完成ER模型设计(与具体的数据库无关)
转化为 PDM(必须选择一种具体的数据库)，因为实际是生成create table代码。
    Oracle：必须通过设置去除 双引号！


实用招数：通过sql脚本生成PDM，再转为CDM


深入权限系统：分级权限

班主任：有 带班和使用打印机 权限
可以 给 班长(岗位) 设置 带班权限
设计？

-------------------------------------业务系统

二、用户数据



====================================================================


1、增加操作员、岗位、权限

流程1：为新用户(6,f)设置岗位(12)。
流程2：为f用户添加新的岗位，此时不能出现已经拥有的岗位
INSERT INTO t_sys_users  (u_id, u_name, u_pwd)
VALUES   (6, 'f', '6666');

--添加新的岗位，已有的岗位要求不要出现
INSERT INTO t_gw_user
  (gu_id, u_id, gw_id, gu_date)
VALUES
  (v_gu_id, v_u_id, v_gw_id, v_gu_date);
 
--查询某人已有的岗位
SELECT gu.gw_id
FROM t_sys_users u
     LEFT JOIN t_gw_user gu ON u.u_id=gu.u_id
WHERE u.u_id=1
--查询某人没有的岗位
SELECT * FROM t_gw gw
WHERE gw_id NOT IN (
  SELECT gw_id
  FROM t_sys_users u
       LEFT JOIN t_gw_user gu ON u.u_id=gu.u_id
  WHERE u.u_id=1
)
--改用not exists(不方便)

--换用新的人员 6
SELECT * FROM t_gw gw
WHERE gw_id NOT IN (
  SELECT gw_id
  FROM t_sys_users u
       LEFT JOIN t_gw_user gu ON u.u_id=gu.u_id
  WHERE u.u_id=6
)
--why 无数据？
SELECT * FROM t_gw gw WHERE gw_id NOT IN (10)

--null?
SELECT * FROM t_gw gw
WHERE gw_id NOT IN (
  SELECT nvl(gw_id,-1)
  FROM t_sys_users u
       LEFT JOIN t_gw_user gu ON u.u_id=gu.u_id
  WHERE u.u_id=6
)

--操作员 选中 10,11,为6号员工增加2个岗位
--从网页分别获取10,11,组成2个insert语句
INSERT INTO t_gw_user  (gu_id, u_id, gw_id, gu_date)
VALUES  (seq1.nextval, 6, 10, SYSDATE);
INSERT INTO t_gw_user  (gu_id, u_id, gw_id, gu_date)
VALUES  (seq1.nextval, 6, 11, SYSDATE);

--用户u_id=6的登录，session中保存此信息。
--打开一个网页，网页的权限id为101。是否有权限打开？
--1/2种：看sql是否返回记录
SELECT * FROM v_user_right
WHERE u_id=6 AND r_id=101
--用left join改写

--用最少的表
--1小步
SELECT  *
FROM t_sys_users u JOIN t_gw_user  gu ON u.u_id = gu.u_id
WHERE u.u_id = 6 
--2小步
SELECT  *
FROM t_sys_users u JOIN t_gw_user  gu ON u.u_id = gu.u_id
                    JOIN t_right_gw gw ON gw.gw_id = gu.gw_id
WHERE u.u_id = 6  AND  gw.r_id = 101;
--java
SELECT  'x'
FROM t_sys_users u JOIN t_gw_user  gu ON u.u_id = gu.u_id
                    JOIN t_right_gw gw ON gw.gw_id = gu.gw_id
WHERE u.u_id = ?  AND  gw.r_id = ?;

--角色配置表:u_id,gw_id  权限配置表gw_id r_id
--现在其实是：通过 u_id找r_id，最少2个表
SELECT *
FROM t_gw_user a JOIN t_right_gw b ON a.gw_id = b.gw_id
WHERE a.u_id = 6 AND b.r_id= 101

--2/2种：函数

SELECT tt_fu(6,101) x FROM dual;
SELECT tt_fu(6,102) x FROM dual;
--网页保存 权限ID 还是 角色ID？
--答案：权限ID

--思路1：可以将网页路径保存到网页权限表中。
--思路2：可以与网页与权限配置写到XML中
CREATE TABLE t_page_right(
pr_id INT PRIMARY KEY,
page_path varchar2(1000),
page_title VARCHAR2(1000),
r_id INT,
FOREIGN KEY(r_id) REFERENCES t_right(r_id)
);


--6号操作员 打开网页/teach_oracle.jsp，是否有权限？


--1号员工为7号员工增加？
--一种：直接增加权限 (权限可以不在任何岗位中，则必须新建
--新的岗位，岗位创建人必须指明是1号，也可以指明新岗位
--是私有还是公有。

--设计： 1号=老师岗位+班主任岗位=讲课+打印机+带班+打印机
--7号员工 = 讲课+带班  = 新岗位
--操作流程：
--1号员工必须先建立新岗位(检测相同岗位是否存在)
--新岗位赋值给7号员工
--函数：
--1员工怎么知道要新建岗位？

--是否要保证相同的权限不能有多个岗位？

--在工作中，先设计原型，其它人来实现。用包和包体
--表名太长，解决：用同义词
CREATE OR REPLACE PACKAGE pkg_right IS
  PROCEDURE sp_set_my_right(
     sj_u_id t_sys_users.u_id%TYPE,--上级
     xj_u_id t_sys_users.u_id%TYPE,--下级
     r_id  t_right.r_id%TYPE --必须是上级所拥有的权限
);

END;
--逻辑：
--1.r_id必须是上级所拥有的权限





CREATE OR REPLACE PROCEDURE pro_changeright(
   p_sup_u_id t_sys_users.u_id%TYPE, --分配权限的人
   p_sub_u_id t_sys_users.u_id%TYPE,--下级
   p_r_id     t_right.r_id%TYPE,       --分配的根限id
   p_gw_name  t_gw.gw_name%TYPE
)
IS
   v_count NUMBER;
   v_gw_id NUMBER;
   v_rg_id NUMBER;
   v_gu_id NUMBER;
BEGIN
   --查询该分配者是否有该权
   SELECT COUNT(r_id) INTO v_count FROM t_gw_user a JOIN t_right_gw b ON a.gw_id = b.gw_id WHERE a.u_id = p_sup_u_id AND b.r_id = p_r_id ORDER BY a.u_id;
   dbms_output.put_line(v_count);

   IF(v_count < 1 ) THEN
       raise_application_error(-20001,'分配者没有该权限');
   END IF;

   --确定新岗位Id,其实这里应该使用序列
   SELECT MAX(gw_id)+1 INTO v_gw_id FROM t_gw;
   --确定rg_id
   SELECT MAX(rg_id)+1 INTO v_rg_id FROM t_right_gw;
   --确定gu_id
   SELECT MAX(gu_id)+1 INTO v_gu_id FROM t_gw_user;
   --以上应该使用列完成...
  
   --插入该岗
   INSERT INTO t_gw(gw_id,gw_name) VALUES(v_gw_id,p_gw_name);

   --配置该岗位与权限
   INSERT INTO t_right_gw(rg_id,r_id,gw_id) VALUES(v_rg_id,p_r_id,v_gw_id);

   --配置该操作员与岗
   INSERT INTO t_gw_user(gu_id,u_id,gw_id) VALUES(v_gu_id,p_sub_u_id,v_gw_id);

END;

当然这只是我的一点小小的看法！有好的方法请大家指点！
确实，一定设计上出了问题，将1导致业务逻辑变得更复杂化!


下面是我的测试用的代码!