存储过程中的递归相关知识与例子

递归的讲解 

 

WITH RPL (PID, ID, name) AS       
(       
SELECT ROOT.PID, ROOT.ID, ROOT.Name       
FROM regr ROOT       
WHERE ROOT.PID = 8  
UNION ALL      
SELECT CHILD.PID, CHILD.ID, CHILD.Name
FROM RPL PARENT, regr CHILD
WHERE PARENT.ID = CHILD.PID
)
SELECT DISTINCT PID, ID, Name       
FROM RPL
ORDER BY PID, ID, Name

让我们研究这个查询的组件：

RPL 作为一个具有以下三列的虚拟表：PID、ID 和 name。

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

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

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

 

 

 关于partition by  的讲解

1. select type,qty from test;   
2. --------   
3.   
4. 1   3  
5. 1   5  
6. 2   7  
7. 2   9  
8. 3   4  
9. 3   6  
10. 3   8  
11. select type,qty,to_char(row_number() over(partition by type order by qty))||'/'||to_char(count(*) over(partition by type)) as cnt2 from test   
12. ----------------   
13. 1  3  1/2  
14. 1  5  2/2  
15. 2  7  1/2  
16. 2  9  2/2  
17. 3  4  1/3  
18. 3  6  2/3  
19. 3  8  3/3  
20.   
21. 关于partition by   
22.   
23. 这些都是分析函数，好像是8.0以后才有的 row_number()和rownum差不多，功能更强一点（可以在各个分组内从1开时排序） rank()是跳跃排序，有两个第二名时接下来就是第四名（同样是在各个分组内） dense_rank()l是连续排序，有两个第二名时仍然跟着第三名。相比之下row_number是没有重复值的 lag（arg1,arg2,arg3): arg1是从其他行返回的表达式 arg2是希望检索的当前行分区的偏移量。是一个正的偏移量，时一个往回检索以前的行的数目。 arg3是在arg2表示的数目超出了分组的范围时返回的值。   
24. row_number() over(partition by ... order by ...)   
25. rank() over(partition by ... order by ...)   
26. dense_rank() over(partition by ... order by ...)   
27. count() over(partition by ... order by ...)   
28. max() over(partition by ... order by ...)   
29. min() over(partition by ... order by ...)   
30. avg() over(partition by ... order by ...)   
31. first_value() over(partition by ... order by ...)   
32. last_value() over(partition by ... order by ...)   
33. lag() over(partition by ... order by ...)   
34. lead() over(partition by ... order by ...)

整个实例参考：

 

DROP PROCEDURE ACCOUNT.GET_ALL_CLIENT_MERGE; CREATE PROCEDURE ACCOUNT.GET_ALL_CLIENT_MERGE() SPECIFIC GET_ALL_CLIENT_MERGE RESULT SETS 1 READS SQL DATA NOT DETERMINISTIC NULL CALL LANGUAGE SQL BEGIN DECLARE C1 CURSOR WITH RETURN FOR WITH CLIENT1(ACRONYM,ENTITY,NAME,ROWNUM1) AS ( SELECT ACRONYM,ENTITY,NAME,ROWNUMBER() OVER(PARTITION BY ACRONYM) FROM (SELECT RTRIM(M.CLIENT_ACRONYM)AS ACRONYM,RTRIM(C.NMR_ENT_CD) AS ENTITY,RTRIM(C.NMR_CLIENT_NM) AS NAME FROM ACCOUNT.MSTR_ACRONYM M,ACCOUNT.NMR_CLIENT C,ACCOUNT.NMR_CLIENT_MSTR_XREF CX WHERE M.MACCT_MSTR_ID=CX.MACCT_MSTR_ID AND C.NMR_CLIENT_ID=CX.NMR_CLIENT_ID AND C.NMR_ENT_CD=CX.NMR_ENT_CD AND CX.STATUS !='I')),CLIENT2(ACRONYM,ENTITY,NAME,ROWNUM1) AS(SELECT ACRONYM,CAST(ENTITY AS VARCHAR(100)),CAST(NAME AS VARCHAR(1000)),ROWNUM1 FROM CLIENT1 WHERE ROWNUM1 =1 UNION ALL SELECT A.ACRONYM,CAST(A.ENTITY||'|'||B.ENTITY AS VARCHAR(100)),CAST(A.NAME||'|'||B.NAME AS VARCHAR(1000)),A.ROWNUM1+1 FROM CLIENT2 A,CLIENT1 B WHERE A.ACRONYM=B.ACRONYM AND A.ROWNUM1 = B.ROWNUM1-1) SELECT ACRONYM,ENTITY,NAME AS CLIENT_NAME FROM CLIENT2 X WHERE X.ROWNUM1=(SELECT MAX(ROWNUM1) FROM CLIENT2 Y WHERE X.ACRONYM=Y.ACRONYM) ORDER BY ACRONYM; OPEN C1; END