oracle 中 parent_id = null 的变通实现

今有一表

node

id	编号
parent_id	父节点
name	名称

自联接, 若没有父节点, parent_Id 的取值为 NULL, 而不能是 0, 因为不存在编号为 0 的节点.

SELECT * FROM node 

START WITH parent_id IS NULL

CONNECT BY PRIOR id=parent_id

可以按层次查出所有节点.

现在,如果要查从 1 的子节点,查询形如

SELECT * FROM node

START WITH parent_id = 1

CONNECT BY PRIOR id = parent_id

考虑提供参数化查询,  则此处 1 和 NULL 应为变量.

SELECT * FROM node

START WITH parent_id = :parent_id

CONNECT BY PRIOR id = parent_id

这时,提供参数为 NULL, 会发现查不出任何记录. 原因是在 Oracle 的世界观里, NULL 是无法比较的, 因为 NULL <> NULL.

使用 IN 能不能解决问题呢? 因为 IN 其实是对 (,,,) 中的各个散值进行遍历比较, 效果和 = 是一样的.写成 IN( NULL) 也查不出记录.

经过对 NULL 的多方了解, 我发现, NULL - <N> = NULL, 也就是说, NULL 减任何数都是 NULL. 据此悟出如下方法:

SELECT * FROM node

START WITH nvl(parent_id - :parent_id / 2, 0) = :parent_id / 2

CONNECT BY PRIOR id = parent_id

当提供 0 作为参数时, 相当于 null.

该方法的重点是表达式 START WITH nvl(parent_id - :parent_id / 2, 0) = :parent_id / 2

当给定参数为 0 时, 右边值为 0 , 要求左边值为 0 条件才满足, 而 nvl(x, 0) = 0 的 x 仅能取 NULL 和 0.

考虑到 X 为 parent_id - 0, 故仅当 parent_id  为 NULL 时,等式才成立.

当给定参数为非 0 数字 n 时, 等式成立的前提是 nvl (x, 0) = <n / 2>, 此时, x 为 NULL 不能使等式成立.

亦即,仅当 parent_id - parent_id / 2 = parent / 2 时,等式才会成立.