oracle递归转db2

说明：DB2用WITH…AS形式的虚拟表来实现oracle中START WITH…CONNECT BY字句的递归功能。

1、先回头看下oracle的start with字句

部门id
父部门id(所属部门id)
部门名称
DEPTID PAREDEPTID NAME
NUMBER NUMBER CHAR (40 Byte)
a、通过子节点向根节点追溯

SELECT * FROM DEPT START WITH DEPTID = 80 CONNECT BY PRIOR PAREDEPTID = DEPTID;

b、通过根节点遍历子节点

SELECT * FROM DEPT START WITH DEPTID = 0 CONNECT BY PRIOR DEPTID = PAREDEPTID;

c、通过level关键字查询所在层次

SELECT A.*,LEVEL FROM DEPT A START WITH PAREDEPTID = 0 CONNECT BY PRIOR DEPTID = PAREDEPTID;

PS:start with …connect by 的用法， start with 后面所跟的就是就是递归的种子。

递归的种子也就是递归开始的地方 connect by 后面的"prior" 如果缺省：则只能查询到符合条件的起始行，并不进行递归查询；

connect by prior 后面所放的字段是有关系的，它指明了查询的方向。

2、DB2中WITH的用法

 上面这张表(DB2中对应NUMBER类型的字段需修改成smallint,integer或者bigint) --从根节点开始遍历

WITH REF_TB (DEPTID,PAREDEPTID,NAME ) AS        
(        
SELECT ROOT.DEPTID, ROOT.PAREDEPTID, ROOT.NAME       
FROM DEPT ROOT        
WHERE ROOT.PAREDEPTID = 0
UNION ALL       
SELECT CHILD.DEPTID, CHILD.PAREDEPTID, CHILD.NAME       
FROM REF_TB PARENT, DEPT CHILD
WHERE PARENT.DEPTID = CHILD.PAREDEPTID
)
SELECT DISTINCT DEPTID,PAREDEPTID,NAME        
FROM REF_TB
ORDER BY DEPTID,PAREDEPTID,NAME;

这个查询的组件：

REF_TB作为一个具有以下三列的虚拟表：DEPTID,PAREDEPTID,NAME。

1.WITH 子句内的第一个 SELECT 语句是初始化表。它只执行一次。它的结果形成虚拟表的初始内容以作为递归的种子。在上面的示例中，种子是 PAREDEPTID 为 0 的一行或多行。

2. SELECT 语句执行多次。将种子作为输入（JOIN 中的辅助表）传递给第二个 SELECT 语句以产生下一个行集合。将 JOIN 的结果添加（UNION ALL）到虚拟表的当前内容中，并放回到其中以形成用于下一次传递的输入。只要有行产生，这个过程就会继续。

如果期望，虚拟表上最后的 SELECT 允许我们选择递归查询所产生的所有行或仅部分行。