调用存储过程

最新推荐文章于 2022-03-22 09:21:30 发布

dianjixiao4363

最新推荐文章于 2022-03-22 09:21:30 发布

阅读量123

点赞数

原文链接：http://www.cnblogs.com/zhouchunDIY/p/8377423.html

版权

本文介绍了一个名为P_BLOOD_ANALYSE的存储过程，该过程用于递归地分析实例之间的血缘关系，并将结果存储在T_ALY_IL_RESULT表中。通过设定最大层级和初始条件，此过程可以有效地遍历和记录实体间的多层次关联。

摘要由CSDN通过智能技术生成

CREATE OR REPLACE PROCEDURE P_BLOOD_ANALYSE(P_INST_ID VARCHAR2,P_SESSION_ID VARCHAR2,P_ANALYSE_TYPE INT,P_CREATE_TIME NUMBER)
is
MAX_LEVEL INT;
V_ALY_LAYER INT;
V_INSERT_COUNT INT;
BEGIN

MAX_LEVEL := 20;
V_ALY_LAYER := 0;
V_INSERT_COUNT := 0;

INSERT INTO T_ALY_IL_RESULT
(SESSION_ID,UP_INST_ID,DOWN_INST_ID,ALY_LAYER,CREATE_TIME)
SELECT P_SESSION_ID,TARGET_ID,SOURCE_ID,V_ALY_LAYER,P_CREATE_TIME
FROM T_MD_ETL_INST F WHERE F.TARGET_ID = P_INST_ID;
V_INSERT_COUNT := V_INSERT_COUNT + SQL%ROWCOUNT;

WHILE V_ALY_LAYER <= MAX_LEVEL AND V_INSERT_COUNT > 0 LOOP
V_INSERT_COUNT := 0;
V_ALY_LAYER := V_ALY_LAYER + 1;
INSERT INTO T_ALY_IL_RESULT(SESSION_ID,UP_INST_ID,DOWN_INST_ID,ALY_LAYER,CREATE_TIME)
SELECT DISTINCT P_SESSION_ID,TARGET_ID,SOURCE_ID,V_ALY_LAYER,P_CREATE_TIME
FROM T_MD_ETL_INST F, T_ALY_IL_RESULT R
WHERE F.TARGET_ID = R.DOWN_INST_ID AND R.SESSION_ID = P_SESSION_ID AND R.ALY_LAYER = V_ALY_LAYER - 1 AND NOT EXISTS (
SELECT 1 FROM T_ALY_IL_RESULT R2 WHERE R2.SESSION_ID = P_SESSION_ID AND R2.UP_INST_ID = F.TARGET_ID AND R2.DOWN_INST_ID = F.SOURCE_ID)
AND NOT EXISTS (
SELECT 1 FROM T_ALY_IL_RESULT R2 WHERE R2.SESSION_ID = P_SESSION_ID AND R2.UP_INST_ID = F.SOURCE_ID AND R2.DOWN_INST_ID = F.TARGET_ID);
V_INSERT_COUNT := V_INSERT_COUNT + SQL%ROWCOUNT;
END LOOP;
END P_BLOOD_ANALYSE;