oracle 树形菜单设计及查询

建表语句：

-- Create table
create table T_SYS_RESOURCE
(
  resourceid   VARCHAR2(30) not null,
  systemid     VARCHAR2(30),
  resourcename VARCHAR2(50),
  description  VARCHAR2(100),
  link         VARCHAR2(200),
  parentid     VARCHAR2(30),
  seqno        NUMBER default 0 not null,
  resourcecode VARCHAR2(30)
)
tablespace TEST  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
-- Add comments to the columns
comment on column T_SYS_RESOURCE.resourcename
  is '资源名称';
comment on column T_SYS_RESOURCE.description
  is '资源描述';
comment on column T_SYS_RESOURCE.seqno
  is '同级菜单内序号';
-- Create/Recreate primary, unique and foreign key constraints
alter table T_SYS_RESOURCE
  add constraint PK_T_SYS_RESOURCE primary key (RESOURCEID)
  using index
  tablespace TEST  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
-- Create/Recreate indexes
create index IDX_RES_SYSTEMID on T_SYS_RESOURCE (SYSTEMID)
  tablespace TEST  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );

 

select level, r.resourceid, r.resourcename, r.link
  from sys_resource r
connect by prior r.resourceid = r.parentid;

 

insert into t_sys_resource (RESOURCEID, SYSTEMID, RESOURCENAME, DESCRIPTION, LINK, PARENTID, SEQNO, RESOURCECODE)
values ('201403141958010001', '201403091958010020', '类别管理', '', 'category', '201403091958010028', 2, 'category');

insert into t_sys_resource (RESOURCEID, SYSTEMID, RESOURCENAME, DESCRIPTION, LINK, PARENTID, SEQNO, RESOURCECODE)
values ('201403171255180001', '201403091958010020', '系统管理', '', '', '', 0, '');

insert into t_sys_resource (RESOURCEID, SYSTEMID, RESOURCENAME, DESCRIPTION, LINK, PARENTID, SEQNO, RESOURCECODE)
values ('201403171255180002', '201403091958010020', '机构管理', '', '', '201403171255180001', 1, '');

insert into t_sys_resource (RESOURCEID, SYSTEMID, RESOURCENAME, DESCRIPTION, LINK, PARENTID, SEQNO, RESOURCECODE)
values ('201403171255180003', '201403091958010020', '机构设置', '', 'unit', '201403171255180002', 1, 'unit');

insert into t_sys_resource (RESOURCEID, SYSTEMID, RESOURCENAME, DESCRIPTION, LINK, PARENTID, SEQNO, RESOURCECODE)
values ('201403171255180004', '201403091958010020', '部门设置', '', 'department', '201403171255180002', 2, 'department');

insert into t_sys_resource (RESOURCEID, SYSTEMID, RESOURCENAME, DESCRIPTION, LINK, PARENTID, SEQNO, RESOURCECODE)
values ('201403171255180005', '201403091958010020', '员工设置', '', 'employee', '201403171255180002', 3, 'employee');

insert into t_sys_resource (RESOURCEID, SYSTEMID, RESOURCENAME, DESCRIPTION, LINK, PARENTID, SEQNO, RESOURCECODE)
values ('201403091958010021', '201403091958010020', '基础数据管理', '', '', '', 1, '');

insert into t_sys_resource (RESOURCEID, SYSTEMID, RESOURCENAME, DESCRIPTION, LINK, PARENTID, SEQNO, RESOURCECODE)
values ('201403091958010024', '201403091958010020', '货运站管理', '', '', '201403091958010021', 1, '');

insert into t_sys_resource (RESOURCEID, SYSTEMID, RESOURCENAME, DESCRIPTION, LINK, PARENTID, SEQNO, RESOURCECODE)
values ('201403091958010025', '201403091958010020', '货运站设置', '', 'a', '201403091958010024', 1, '');

insert into t_sys_resource (RESOURCEID, SYSTEMID, RESOURCENAME, DESCRIPTION, LINK, PARENTID, SEQNO, RESOURCECODE)
values ('201403091958010026', '201403091958010020', '库区设置', '', 'warehouse', '201403091958010024', 2, 'warehouse');

insert into t_sys_resource (RESOURCEID, SYSTEMID, RESOURCENAME, DESCRIPTION, LINK, PARENTID, SEQNO, RESOURCECODE)
values ('201403091958010022', '201403091958010020', '储区设置', '', 'stockarea', '201403091958010024', 3, '');

insert into t_sys_resource (RESOURCEID, SYSTEMID, RESOURCENAME, DESCRIPTION, LINK, PARENTID, SEQNO, RESOURCECODE)
values ('201403091958010027', '201403091958010020', '货位设置', '', 'bin', '201403091958010024', 4, '');

insert into t_sys_resource (RESOURCEID, SYSTEMID, RESOURCENAME, DESCRIPTION, LINK, PARENTID, SEQNO, RESOURCECODE)
values ('201403091958010028', '201403091958010020', '货主管理', '', '', '201403091958010021', 2, '');

insert into t_sys_resource (RESOURCEID, SYSTEMID, RESOURCENAME, DESCRIPTION, LINK, PARENTID, SEQNO, RESOURCECODE)
values ('201403091958010031', '201403091958010020', '货主设置', '', 'b1', '201403091958010028', 1, '');

insert into t_sys_resource (RESOURCEID, SYSTEMID, RESOURCENAME, DESCRIPTION, LINK, PARENTID, SEQNO, RESOURCECODE)
values ('201403091958010033', '201403091958010020', '供应商设置', '', 'supplier', '201403091958010028', 2, 'supplier');

insert into t_sys_resource (RESOURCEID, SYSTEMID, RESOURCENAME, DESCRIPTION, LINK, PARENTID, SEQNO, RESOURCECODE)
values ('201403091958010023', '201403091958010020', '物资品类设置', '', 'b2', '201403091958010028', 3, '');

insert into t_sys_resource (RESOURCEID, SYSTEMID, RESOURCENAME, DESCRIPTION, LINK, PARENTID, SEQNO, RESOURCECODE)
values ('201403091958010032', '201403091958010020', '物资品规设置', '', 'b3', '201403091958010028', 4, '');

insert into t_sys_resource (RESOURCEID, SYSTEMID, RESOURCENAME, DESCRIPTION, LINK, PARENTID, SEQNO, RESOURCECODE)
values ('201403091958010035', '201403091958010020', '作业管理', '', '', '', 2, '');

insert into t_sys_resource (RESOURCEID, SYSTEMID, RESOURCENAME, DESCRIPTION, LINK, PARENTID, SEQNO, RESOURCECODE)
values ('201403091958010029', '201403091958010020', '入库管理', '', '', '201403091958010035', 1, '');

insert into t_sys_resource (RESOURCEID, SYSTEMID, RESOURCENAME, DESCRIPTION, LINK, PARENTID, SEQNO, RESOURCECODE)
values ('201403091958010030', '201403091958010020', '入库单管理', '', 'b4', '201403091958010029', 1, '');

insert into t_sys_resource (RESOURCEID, SYSTEMID, RESOURCENAME, DESCRIPTION, LINK, PARENTID, SEQNO, RESOURCECODE)
values ('201403091958010036', '201403091958010020', '入库调度', '', 'b5', '201403091958010029', 2, '');