一 、 建表和插入数据
prompt PL/SQL Developer import fileprompt Created on 2013年5月15日 by zx
set feedback off
set define off
prompt Creating T_MENU_PRIVILEGE...
create table T_MENU_PRIVILEGE
(
PKID NUMBER(16) not null,
ROLE_ID VARCHAR2(16),
MENU_ID NUMBER(16),
PRIVILEGE_ID CHAR(3)
)
;
alter table T_MENU_PRIVILEGE
add constraint PK_T_MENU_PRIVILEGE primary key (PKID);
prompt Creating T_PRIVILEGE...
create table T_PRIVILEGE
(
PRIVILEGE_ID CHAR(3) not null,
PRIVILEGE_TYPE VARCHAR2(8)
)
;
alter table T_PRIVILEGE
add constraint PK_T_PRIVILEGE primary key (PRIVILEGE_ID);
prompt Creating T_ROLE...
create table T_ROLE
(
ROLE_ID VARCHAR2(16) not null,
ROLE_DESCR VARCHAR2(32)
)
;
alter table T_ROLE
add constraint PK_T_ROLE primary key (ROLE_ID);
prompt Creating T_SYS_MENU...
create table T_SYS_MENU
(
MENU_ID NUMBER(16) not null,
MENU_DESCR VARCHAR2(32),
MENU_URL VARCHAR2(255),
MENU_PID VARCHAR2(16)
)
;
alter table T_SYS_MENU
add constraint PK_T_SYS_MENU primary key (MENU_ID);
prompt Creating T_USERS...
create table T_USERS
(
USER_ID VARCHAR2(16) not null,
PASSWORD CHAR(8),
USERNAME VARCHAR2(16)
)
;
alter table T_USERS
add constraint PK_T_USERS primary key (USER_ID);
prompt Creating T_USER_ROLE...
create table T_USER_ROLE
(
USER_ID VARCHAR2(16) not null,
ROLE_ID VARCHAR2(16) not null
)
;
alter table T_USER_ROLE
add constraint PK_T_USER_ROLE primary key (USER_ID, ROLE_ID);
prompt Deleting T_USER_ROLE...
delete from T_USER_ROLE;
commit;
prompt Deleting T_USERS...
delete from T_USERS;
commit;
prompt Deleting T_SYS_MENU...
delete from T_SYS_MENU;
commit;
prompt Deleting T_ROLE...
delete from T_ROLE;
commit;
prompt Deleting T_PRIVILEGE...
delete from T_PRIVILEGE;
commit;
prompt Deleting T_MENU_PRIVILEGE...
delete from T_MENU_PRIVILEGE;
commit;
prompt Loading T_MENU_PRIVILEGE...
insert into T_MENU_PRIVILEGE (PKID, ROLE_ID, MENU_ID, PRIVILEGE_ID)
values (6, 'user', 105, '106');
insert into T_MENU_PRIVILEGE (PKID, ROLE_ID, MENU_ID, PRIVILEGE_ID)
values (5, 'user', 105, '105');
insert into T_MENU_PRIVILEGE (PKID, ROLE_ID, MENU_ID, PRIVILEGE_ID)
values (4, 'user', 105, '104');
insert into T_MENU_PRIVILEGE (PKID, ROLE_ID, MENU_ID, PRIVILEGE_ID)
values (3, 'user', 105, '103');
insert into T_MENU_PRIVILEGE (PKID, ROLE_ID, MENU_ID, PRIVILEGE_ID)
values (2, 'user', 105, '102');
insert into T_MENU_PRIVILEGE (PKID, ROLE_ID, MENU_ID, PRIVILEGE_ID)
values (1, 'user', 105, '101');
insert into T_MENU_PRIVILEGE (PKID, ROLE_ID, MENU_ID, PRIVILEGE_ID)
values (9, 'admin', 106, '103');
insert into T_MENU_PRIVILEGE (PKID, ROLE_ID, MENU_ID, PRIVILEGE_ID)
values (8, 'admin', 106, '102');
insert into T_MENU_PRIVILEGE (PKID, ROLE_ID, MENU_ID, PRIVILEGE_ID)
values (7, 'admin', 102, '101');
insert into T_MENU_PRIVILEGE (PKID, ROLE_ID, MENU_ID, PRIVILEGE_ID)
values (11, 'admin', 103, '102');
insert into T_MENU_PRIVILEGE (PKID, ROLE_ID, MENU_ID, PRIVILEGE_ID)
values (10, 'admin', 104, '103');
commit;
prompt 11 records loaded
prompt Loading T_PRIVILEGE...
insert into T_PRIVILEGE (PRIVILEGE_ID, PRIVILEGE_TYPE)
values ('103', '修改');
insert into T_PRIVILEGE (PRIVILEGE_ID, PRIVILEGE_TYPE)
values ('102', '删除');
insert into T_PRIVILEGE (PRIVILEGE_ID, PRIVILEGE_TYPE)
values ('101', '增加');
insert into T_PRIVILEGE (PRIVILEGE_ID, PRIVILEGE_TYPE)
values ('104', '查询');
insert into T_PRIVILEGE (PRIVILEGE_ID, PRIVILEGE_TYPE)
values ('106', '报表');
insert into T_PRIVILEGE (PRIVILEGE_ID, PRIVILEGE_TYPE)
values ('105', '打印');
commit;
prompt 6 records loaded
prompt Loading T_ROLE...
insert into T_ROLE (ROLE_ID, ROLE_DESCR)
values ('admin', 'IT管理部');
insert into T_ROLE (ROLE_ID, ROLE_DESCR)
values ('gmanager', '老总');
insert into T_ROLE (ROLE_ID, ROLE_DESCR)
values ('manager', '部门经理');
insert into T_ROLE (ROLE_ID, ROLE_DESCR)
values ('user', '普通用户');
commit;
prompt 4 records loaded
prompt Loading T_SYS_MENU...
insert into T_SYS_MENU (MENU_ID, MENU_DESCR, MENU_URL, MENU_PID)
values (105, '系统管理', null, '0');
insert into T_SYS_MENU (MENU_ID, MENU_DESCR, MENU_URL, MENU_PID)
values (104, '年报', 'xxx.do', '101');
insert into T_SYS_MENU (MENU_ID, MENU_DESCR, MENU_URL, MENU_PID)
values (103, '季报', 'xxx.do', '101');
insert into T_SYS_MENU (MENU_ID, MENU_DESCR, MENU_URL, MENU_PID)
values (102, '月报', 'xxx.do', '101');
insert into T_SYS_MENU (MENU_ID, MENU_DESCR, MENU_URL, MENU_PID)
values (101, '报表查询', null, '0');
insert into T_SYS_MENU (MENU_ID, MENU_DESCR, MENU_URL, MENU_PID)
values (106, '用户管理', null, '105');
insert into T_SYS_MENU (MENU_ID, MENU_DESCR, MENU_URL, MENU_PID)
values (111, '删除角色', 'adelRole', '109');
insert into T_SYS_MENU (MENU_ID, MENU_DESCR, MENU_URL, MENU_PID)
values (110, '新增角色', 'addRole.do', '109');
insert into T_SYS_MENU (MENU_ID, MENU_DESCR, MENU_URL, MENU_PID)
values (109, '角色管理', null, '105');
insert into T_SYS_MENU (MENU_ID, MENU_DESCR, MENU_URL, MENU_PID)
values (108, '删除用户', 'delUser.do', '106');
insert into T_SYS_MENU (MENU_ID, MENU_DESCR, MENU_URL, MENU_PID)
values (107, '新增用户', 'addUser.do', '106');
commit;
prompt 11 records loaded
prompt Loading T_USERS...
insert into T_USERS (USER_ID, PASSWORD, USERNAME)
values ('Andy', '12345678', null);
insert into T_USERS (USER_ID, PASSWORD, USERNAME)
values ('Danzel', '12345678', null);
insert into T_USERS (USER_ID, PASSWORD, USERNAME)
values ('Jim', '12345678', null);
insert into T_USERS (USER_ID, PASSWORD, USERNAME)
values ('Sally', '12345678', null);
insert into T_USERS (USER_ID, PASSWORD, USERNAME)
values ('Sammy', '12345678', null);
insert into T_USERS (USER_ID, PASSWORD, USERNAME)
values ('Tonney', '12345678', null);
insert into T_USERS (USER_ID, PASSWORD, USERNAME)
values ('Shawn', '12345678', null);
commit;
prompt 7 records loaded
prompt Loading T_USER_ROLE...
insert into T_USER_ROLE (USER_ID, ROLE_ID)
values ('Danzel', 'admin');
insert into T_USER_ROLE (USER_ID, ROLE_ID)
values ('Jim', 'admin');
insert into T_USER_ROLE (USER_ID, ROLE_ID)
values ('Danzel', 'manager');
insert into T_USER_ROLE (USER_ID, ROLE_ID)
values ('Sammy', 'user');
insert into T_USER_ROLE (USER_ID, ROLE_ID)
values ('Tonney', 'user');
commit;
prompt 5 records loaded
set feedback on
set define on
prompt Done.
二 、查出菜单树
SELECT * FROM T_SYS_MENU
START WITH MENU_PID='0'
CONNECT BY PRIOR MENU_ID=MENU_PID
ORDER BY MENU_ID
SELECT DISTINCT M.MENU_PID,m.menu_id,m.menu_descr,m.menu_url,m.menu_pid,p.privilege_id,p.privilege_type FROM
T_MENU_PRIVILEGE MP,
T_SYS_MENU M,
T_PRIVILEGE P,
T_USER_ROLE R
WHERE MP.ROLE_ID = R.ROLE_ID
AND MP.ROLE_ID = R.ROLE_ID
AND MP.MENU_ID = M.MENU_ID
AND R.USER_ID = 'Jim'
AND M.MENU_PID ='101'
CONNECT BY PRIOR M.MENU_ID=M.MENU_PID
ORDER BY M.MENU_ID
select distinct m.menu_id, m.menu_descr, m.menu_url, m.menu_pid from
t_menu_privilege mp ,
t_sys_menu m,
t_privilege p,
t_user_role r
where
mp.privilege_id=p.privilege_id
and mp.role_id=r.role_id
and mp.menu_id=m.menu_id
and r.user_id='Danzel'
START WITH MENU_PID='0'
CONNECT BY PRIOR M.MENU_ID=M.MENU_PID
order by M.MENU_ID
/////////
新建数据库
prompt PL/SQL Developer import file
prompt Created on 2013年5月15日 by apple
set feedback off
set define off
prompt Deleting T_USER_ROLE...
delete from T_USER_ROLE;
commit;
prompt Deleting T_USERS...
delete from T_USERS;
commit;
prompt Deleting T_SYS_MENU...
delete from T_SYS_MENU;
commit;
prompt Deleting T_ROLE...
delete from T_ROLE;
commit;
prompt Deleting T_PRIVILEGE...
delete from T_PRIVILEGE;
commit;
prompt Deleting T_MENU_PRIVILEGE...
delete from T_MENU_PRIVILEGE;
commit;
prompt Loading T_MENU_PRIVILEGE...
insert into T_MENU_PRIVILEGE (PKID, ROLE_ID, MENU_ID, PRIVILEGE_ID)
values (6, 'user', 105, '106');
insert into T_MENU_PRIVILEGE (PKID, ROLE_ID, MENU_ID, PRIVILEGE_ID)
values (5, 'user', 105, '105');
insert into T_MENU_PRIVILEGE (PKID, ROLE_ID, MENU_ID, PRIVILEGE_ID)
values (4, 'user', 105, '104');
insert into T_MENU_PRIVILEGE (PKID, ROLE_ID, MENU_ID, PRIVILEGE_ID)
values (3, 'user', 105, '103');
insert into T_MENU_PRIVILEGE (PKID, ROLE_ID, MENU_ID, PRIVILEGE_ID)
values (2, 'user', 105, '102');
insert into T_MENU_PRIVILEGE (PKID, ROLE_ID, MENU_ID, PRIVILEGE_ID)
values (1, 'user', 105, '101');
insert into T_MENU_PRIVILEGE (PKID, ROLE_ID, MENU_ID, PRIVILEGE_ID)
values (9, 'admin', 106, '103');
insert into T_MENU_PRIVILEGE (PKID, ROLE_ID, MENU_ID, PRIVILEGE_ID)
values (8, 'admin', 106, '102');
insert into T_MENU_PRIVILEGE (PKID, ROLE_ID, MENU_ID, PRIVILEGE_ID)
values (7, 'admin', 102, '101');
insert into T_MENU_PRIVILEGE (PKID, ROLE_ID, MENU_ID, PRIVILEGE_ID)
values (11, 'admin', 103, '102');
insert into T_MENU_PRIVILEGE (PKID, ROLE_ID, MENU_ID, PRIVILEGE_ID)
values (10, 'admin', 104, '103');
commit;
prompt 11 records loaded
prompt Loading T_PRIVILEGE...
insert into T_PRIVILEGE (PRIVILEGE_ID, PRIVILEGE_TYPE)
values ('103', '修改');
insert into T_PRIVILEGE (PRIVILEGE_ID, PRIVILEGE_TYPE)
values ('102', '删除');
insert into T_PRIVILEGE (PRIVILEGE_ID, PRIVILEGE_TYPE)
values ('101', '增加');
insert into T_PRIVILEGE (PRIVILEGE_ID, PRIVILEGE_TYPE)
values ('104', '查询');
insert into T_PRIVILEGE (PRIVILEGE_ID, PRIVILEGE_TYPE)
values ('106', '报表');
insert into T_PRIVILEGE (PRIVILEGE_ID, PRIVILEGE_TYPE)
values ('105', '打印');
commit;
prompt 6 records loaded
prompt Loading T_ROLE...
insert into T_ROLE (ROLE_ID, ROLE_DESCR)
values ('admin', 'IT管理部');
insert into T_ROLE (ROLE_ID, ROLE_DESCR)
values ('gmanager', '老总');
insert into T_ROLE (ROLE_ID, ROLE_DESCR)
values ('manager', '部门经理');
insert into T_ROLE (ROLE_ID, ROLE_DESCR)
values ('user', '普通用户');
commit;
prompt 4 records loaded
prompt Loading T_SYS_MENU...
insert into T_SYS_MENU (MENU_ID, MENU_DESCR, MENU_URL, MENU_PID, LFT, RGT)
values (105, '系统管理', null, '0', 10, 23);
insert into T_SYS_MENU (MENU_ID, MENU_DESCR, MENU_URL, MENU_PID, LFT, RGT)
values (104, '年报', 'xxx.do', '101', 7, 8);
insert into T_SYS_MENU (MENU_ID, MENU_DESCR, MENU_URL, MENU_PID, LFT, RGT)
values (103, '季报', 'xxx.do', '101', 5, 6);
insert into T_SYS_MENU (MENU_ID, MENU_DESCR, MENU_URL, MENU_PID, LFT, RGT)
values (102, '月报', 'xxx.do', '101', 3, 4);
insert into T_SYS_MENU (MENU_ID, MENU_DESCR, MENU_URL, MENU_PID, LFT, RGT)
values (101, '报表查询', null, '0', 2, 9);
insert into T_SYS_MENU (MENU_ID, MENU_DESCR, MENU_URL, MENU_PID, LFT, RGT)
values (106, '用户管理', null, '105', 11, 16);
insert into T_SYS_MENU (MENU_ID, MENU_DESCR, MENU_URL, MENU_PID, LFT, RGT)
values (111, '删除角色', 'adelRole', '109', 20, 21);
insert into T_SYS_MENU (MENU_ID, MENU_DESCR, MENU_URL, MENU_PID, LFT, RGT)
values (110, '新增角色', 'addRole.do', '109', 18, 19);
insert into T_SYS_MENU (MENU_ID, MENU_DESCR, MENU_URL, MENU_PID, LFT, RGT)
values (109, '角色管理', null, '105', 17, 22);
insert into T_SYS_MENU (MENU_ID, MENU_DESCR, MENU_URL, MENU_PID, LFT, RGT)
values (108, '删除用户', 'delUser.do', '106', 14, 15);
insert into T_SYS_MENU (MENU_ID, MENU_DESCR, MENU_URL, MENU_PID, LFT, RGT)
values (107, '新增用户', 'addUser.do', '106', 12, 13);
insert into T_SYS_MENU (MENU_ID, MENU_DESCR, MENU_URL, MENU_PID, LFT, RGT)
values (0, '菜单', null, null, 0, 24);
commit;
prompt 12 records loaded
prompt Loading T_USERS...
insert into T_USERS (USER_ID, PASSWORD, USERNAME)
values ('Andy', '12345678', null);
insert into T_USERS (USER_ID, PASSWORD, USERNAME)
values ('Danzel', '12345678', null);
insert into T_USERS (USER_ID, PASSWORD, USERNAME)
values ('Jim', '12345678', null);
insert into T_USERS (USER_ID, PASSWORD, USERNAME)
values ('Sally', '12345678', null);
insert into T_USERS (USER_ID, PASSWORD, USERNAME)
values ('Sammy', '12345678', null);
insert into T_USERS (USER_ID, PASSWORD, USERNAME)
values ('Tonney', '12345678', null);
insert into T_USERS (USER_ID, PASSWORD, USERNAME)
values ('Shawn', '12345678', null);
commit;
prompt 7 records loaded
prompt Loading T_USER_ROLE...
insert into T_USER_ROLE (USER_ID, ROLE_ID)
values ('Danzel', 'admin');
insert into T_USER_ROLE (USER_ID, ROLE_ID)
values ('Danzel', 'manager');
insert into T_USER_ROLE (USER_ID, ROLE_ID)
values ('Jim', 'admin');
insert into T_USER_ROLE (USER_ID, ROLE_ID)
values ('Sammy', 'user');
insert into T_USER_ROLE (USER_ID, ROLE_ID)
values ('Tonney', 'user');
commit;
prompt 5 records loaded
set feedback on
set define on
prompt Done.
用左右实现菜单树
--用ft rgt 实现菜单树
SELECT n.menu_id,n.menu_descr,n.menu_url,n.lft,n.rgt ,
(COUNT(p.menu_id)-1) menuLevel
FROM T_SYS_MENU N,T_SYS_MENU P
where n.lft between p.lftand p.rgt
group by n.menu_id,n.menu_descr,n.menu_url,n.lft,n.rgt
order by n.lft
--插入周报在月报前面插入新的子节点周报
SELECT T.LFT FROM T_SYS_MENU TWHERE T.MENU_ID=101 ;
UPDATE T_SYS_MENU TSET T.LFT=t.Lft+2WHERE T.LFT>2 ;
UPDATE T_SYS_MENU TSET T.RGT=T.RGT+2WHERE T.RGT>2 ;
INSERT INTO T_SYS_MENU T (T.MENU_ID,T.MENU_DESCR,T.MENU_URL,T.LFT,T.RGT)VALUES('202','周报,'shougong.do',2+1,2+2) ;
commit;
--插入新的节点插入同报表相同的子节点
SELECT T.RGT FROM T_SYS_MENU TWHERE T.MENU_ID = 101;
UPDATE T_SYS_MENU TSET T.RGT=T.RGT+2WHERE T.RGT>11;
UPDATE T_SYS_MENU TSET T.LFT=T.LFT+2WHERE T.LFT>11 ;
INSERT INTO T_SYS_MENU T (T.MENU_ID,T.MENU_DESCR,T.MENU_URL,T.LFT,T.RGT)VALUES('201','保单审核,'',11+1,11+2) ;
--在保单审核里面插入子节点插入新的子节点手工审核
SELECT T.LFT FROM T_SYS_MENU TWHERE T.MENU_ID=201 ;
UPDATE T_SYS_MENU TSET T.LFT=t.Lft+2WHERE T.LFT>12 ;
UPDATE T_SYS_MENU TSET T.RGT=T.RGT+2WHERE T.RGT>12 ;
INSERT INTO T_SYS_MENU T (T.MENU_ID,T.MENU_DESCR,T.MENU_URL,T.LFT,T.RGT)VALUES('203','手工审核,'shougongshenhe.do',12+1,12+2) ;
commit;
--删除节点
SELECT T.LFT,T.RGT ,T.RGT-T.LFT+1 WIDTHFROM T_SYS_MENU TWHERE T.MENU_ID =201;
DELETE FROM T_SYS_MENU TWHERE T.LFT BETWEEN12 AND15;
UPDATE T_SYS_MENU TSET T.RGT=T.RGT-4WHERE T.RGT>15;
UPDATE T_SYS_MENU TSET T.LFT=T.LFT-4WHERE T.LFT>15;
COMMIT ;