多级菜单数据表应用

数据表的建立

-- Create table
create table CATEGORY
(
  ID          NUMBER(8) not null,--表主键
  NAME        VARCHAR2(200),--分类名称
  DESCRIPTION VARCHAR2(200),--分类描述
  PARENT_ID   NUMBER(8),--上一级分类的ID（父id，自身为外键）
  LEVELS      NUMBER(2)--分类所属于的等级
)
tablespace USERS
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );
-- Create/Recreate primary, unique and foreign key constraints 
alter table CATEGORY
  add constraint CATEGORY_PK primary key (ID)
  using index 
  tablespace USERS
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );
alter table CATEGORY
  add constraint CATEGORY_FK foreign key (PARENT_ID)
  references CATEGORY (ID);

插入数据

insert into category (ID, NAME, DESCRIPTION, PARENT_ID, LEVELS)
values (1, '大家电', '', null, 1);

insert into category (ID, NAME, DESCRIPTION, PARENT_ID, LEVELS)
values (2, '手机数码', '', null, 1);

insert into category (ID, NAME, DESCRIPTION, PARENT_ID, LEVELS)
values (3, '电脑产品', '', null, 1);

insert into category (ID, NAME, DESCRIPTION, PARENT_ID, LEVELS)
values (4, '日用百货', '', null, 1);

insert into category (ID, NAME, DESCRIPTION, PARENT_ID, LEVELS)
values (5, '家用电器', '', 1, 2);

insert into category (ID, NAME, DESCRIPTION, PARENT_ID, LEVELS)
values (6, '生活电器', '', 1, 2);

insert into category (ID, NAME, DESCRIPTION, PARENT_ID, LEVELS)
values (7, '家具用品', '', 4, 2);

insert into category (ID, NAME, DESCRIPTION, PARENT_ID, LEVELS)
values (8, '洗衣机', '', 5, 3);

自关联