[转载]Oracle 递归的写法(start with) 以及where条件作用域

数据库里有一个关于地域信息的表,可以叫它tableA,此表里有三个字段分别是 parent_area_code、area_code、isvalid, 有一段sql的功能是查找指定地域code的所有父类地域,sql如下,
这是一个典型的递归的写法:

select area_code
from tableA
where isvalid=1
start with area_code = 5002002753

注意这个isvalid,起初这个表里没有无效的数据,因此这个写法还没有造成什么杀伤力。 某一天这个语句执行时报错:

ORA-01436用户数据中的CONNECT BY循环
(个人理解是tableA本身是个树状图,如果这个树状图的数据里出现了死循环,例如 a的父类为b,b的父类为a,就会报这个异常)

一开始从数据入手,发现有确实有会造成死循环的数据出现,可是它们的isvalid字段的值都为0,可是上面的sql里面明明过滤掉了isvalid=0的数据,怎么可能呢?于是又跑去看了一遍oracle递归语句的用法,看到了这一段:

还可以加where条件

我上面说了可以把start with ,connect 假装看成where 条件一样.所以在这个sql语句其他地方还可以加其他where 语句,可以看成与递归查询无关.只是对整个结果起过滤作用
真相大白,把where条件的作用域搞错了,它并不是作用于递归查询中,而是在递归结束之后才起作用
而那些isvalid=0的有问题的数据,已经在递归中造成了死循环,所以这个where条件显然写的位置不对。这是我最后修改后的sql语句,至此,问题解决。

        select area_code
        from (select * from tableA where isvalid=1)
        start with area_code = 5002002753
        connect by  prior parent_area_code = area_code

此外还可以使用nocycle关键字

        select area_code
        from (select * from tableA where isvalid=1)
        start with area_code = 5002002753
        connect by nocycle prior parent_area_code = area_code

即:开始的递归查询时出现循环圈时.用nocycle关键字指定忽略环.

转自:Oracle 递归的写法(start with) 以及where条件作用域

Last modification:June 24th, 2019 at 10:32 am
If you think my article is useful to you, please feel free to appreciate