start with ... connect by ... prior 实例

创建表的脚本及测试数据
CREATE TABLE TABLE1
(
  UUID VARCHAR2(20 BYTE),
  XUHAO VARCHAR2(20 BYTE),
  PRICE NUMBER(10,2)
)

Insert into TABLE1
  (UUID, XUHAO, PRICE)
 Values
  ('001', '3', 140);
Insert into TABLE1
  (UUID, XUHAO, PRICE)
 Values
  ('002', '3.1', 140);
Insert into TABLE1
  (UUID, XUHAO, PRICE)
 Values
  ('003', '3.1.1', 50);
Insert into TABLE1
  (UUID, XUHAO, PRICE)
 Values
  ('004', '3.1.1.1', 20);
Insert into TABLE1
  (UUID, XUHAO, PRICE)
 Values
  ('005', '3.1.1.2', 30);
Insert into TABLE1
  (UUID, XUHAO, PRICE)
 Values
  ('006', '3.1.2', 90);
Insert into TABLE1
  (UUID, XUHAO, PRICE)
 Values
  ('007', '3.1.2.1', 40);
Insert into TABLE1
  (UUID, XUHAO, PRICE)
 Values
  ('008', '3.1.2.2', 50);
Insert into TABLE1
  (UUID, XUHAO, PRICE)
 Values
  ('009', '4', 136);
Insert into TABLE1
  (UUID, XUHAO, PRICE)
 Values
  ('010', '4.1', 70);
Insert into TABLE1
  (UUID, XUHAO, PRICE)
 Values
  ('011', '4.2', 66);
Insert into TABLE1
  (UUID, XUHAO, PRICE)
 Values
  ('012', '5', 90);
COMMIT;

得到所叶子及没有叶子的节点：

select * from table1 a 
where connect_by_isleaf=1 
start with length(xuhao)=1 
connect by xuhao like prior xuhao||'.%' 
  and xuhao not like prior xuhao||'.%.%'