Oracle 查询父级以及以下的数据 & 删除父级以及以下的数据

1.  语法格式
        Select * from …. Where [结果过滤条件语句]
                       start with  [and起始条件过滤语句]
                       connect by prior [and中间记录过滤条件语句]
2.  查找所有下级
        例如： select * from tbl_test start with id=1 connect by prior id=pid

        如此： select t.*, t.rowid  from CM_BUSINESS_CODE t  

                        start with t.business_code ='111'  
                        connect by prior t.business_code = t.super_business_code
注意：此sql能查找id=1的数据的所有下级，写sql语句时要注意，因为是从id开始查找下级，所以connect by prior 子句的条件是         id=pid
3.  查找所有上级
       select * from tbl_test start with id=5 connect by prior pid=id
因为是从id开始查找上级，所以connect by prior 子句的条件是pid=d

 

4. 删除父级以及以下的数据

delete from
CM_BUSINESS_CODE
where business_code
in
(  select business_code  from CM_BUSINESS_CODE t  start with t.business_code =#{businessCode}
connect by prior t.business_code = t.super_business_code)