本文共 3867 字，大约阅读时间需要 12 分钟。

create table tb_menu( id number(10) not null, --主键id title varchar2(50), --标题 parent number(10) --parent id);--父菜单insert into tb_menu(id, title, parent) values(1, '父菜单1',null);insert into tb_menu(id, title, parent) values(2, '父菜单2',null);insert into tb_menu(id, title, parent) values(3, '父菜单3',null);insert into tb_menu(id, title, parent) values(4, '父菜单4',null);insert into tb_menu(id, title, parent) values(5, '父菜单5',null);--一级菜单insert into tb_menu(id, title, parent) values(6, '一级菜单6',1);insert into tb_menu(id, title, parent) values(7, '一级菜单7',1);insert into tb_menu(id, title, parent) values(8, '一级菜单8',1);insert into tb_menu(id, title, parent) values(9, '一级菜单9',2);insert into tb_menu(id, title, parent) values(10, '一级菜单10',2);insert into tb_menu(id, title, parent) values(11, '一级菜单11',2);insert into tb_menu(id, title, parent) values(12, '一级菜单12',3);insert into tb_menu(id, title, parent) values(13, '一级菜单13',3);insert into tb_menu(id, title, parent) values(14, '一级菜单14',3);insert into tb_menu(id, title, parent) values(15, '一级菜单15',4);insert into tb_menu(id, title, parent) values(16, '一级菜单16',4);insert into tb_menu(id, title, parent) values(17, '一级菜单17',4);insert into tb_menu(id, title, parent) values(18, '一级菜单18',5);insert into tb_menu(id, title, parent) values(19, '一级菜单19',5);insert into tb_menu(id, title, parent) values(20, '一级菜单20',5);--二级菜单insert into tb_menu(id, title, parent) values(21, '二级菜单21',6);insert into tb_menu(id, title, parent) values(22, '二级菜单22',6);insert into tb_menu(id, title, parent) values(23, '二级菜单23',7);insert into tb_menu(id, title, parent) values(24, '二级菜单24',7);insert into tb_menu(id, title, parent) values(25, '二级菜单25',8);insert into tb_menu(id, title, parent) values(26, '二级菜单26',9);insert into tb_menu(id, title, parent) values(27, '二级菜单27',10);insert into tb_menu(id, title, parent) values(28, '二级菜单28',11);insert into tb_menu(id, title, parent) values(29, '二级菜单29',12);insert into tb_menu(id, title, parent) values(30, '二级菜单30',13);insert into tb_menu(id, title, parent) values(31, '二级菜单31',14);insert into tb_menu(id, title, parent) values(32, '二级菜单32',15);insert into tb_menu(id, title, parent) values(33, '二级菜单33',16);insert into tb_menu(id, title, parent) values(34, '二级菜单34',17);insert into tb_menu(id, title, parent) values(35, '二级菜单35',18);insert into tb_menu(id, title, parent) values(36, '二级菜单36',19);insert into tb_menu(id, title, parent) values(37, '二级菜单37',20);--三级菜单insert into tb_menu(id, title, parent) values(38, '三级菜单38',21);insert into tb_menu(id, title, parent) values(39, '三级菜单39',22);insert into tb_menu(id, title, parent) values(40, '三级菜单40',23);insert into tb_menu(id, title, parent) values(41, '三级菜单41',24);insert into tb_menu(id, title, parent) values(42, '三级菜单42',25);insert into tb_menu(id, title, parent) values(43, '三级菜单43',26);insert into tb_menu(id, title, parent) values(44, '三级菜单44',27);insert into tb_menu(id, title, parent) values(45, '三级菜单45',28);insert into tb_menu(id, title, parent) values(46, '三级菜单46',28);insert into tb_menu(id, title, parent) values(47, '三级菜单47',29);insert into tb_menu(id, title, parent) values(48, '三级菜单48',30);insert into tb_menu(id, title, parent) values(49, '三级菜单49',31);insert into tb_menu(id, title, parent) values(50, '三级菜单50',31);

connect by 是结构化查询中用到的，其基本语法是：

select … from tablename
start with 条件1
connect by 条件2
where 条件3;

简单说来是将一个树状结构存储在一张表里，比如一个表中存在两个字段:org_id、parent_id。那么通过表示每一条记录的parent是谁，就可以形成一个树状结构。用上述语法的查询可以取得这棵树的所有记录。

条件1 是根结点的限定语句，当然可以放宽限定条件，以取得多个根结点，实际就是多棵树。条件2 是连接条件，其中用PRIOR表示上一条记录，比如 CONNECT BY PRIOR org_id = parent_id;       就是说上一条记录的org_id 是本条记录的parent_id，即本记录的父亲是上一条记录。条件3 是过滤条件，用于对返回的所有记录进行过滤。

在扫描树结构表时，需要依此访问树结构的每个节点，一个节点只能访问一次，其访问的步骤如下：

第一步： 从根节点开始；
第二步： 访问该节点；
第三步： 判断该节点有无未被访问的子节点，若有，则转向它最左侧的未被访问的子节，并执行第二步，否则执行第四步；
第四步： 若该节点为根节点，则访问完毕，否则执行第五步；
第五步： 返回到该节点的父节点，并执行第三步骤。
总之：扫描整个树结构的过程也即是中序遍历树的过程。