CREATE OR REPLACE PROCEDURE P_BLOOD_ANALYSE(P_INST_ID VARCHAR2,P_SESSION_ID VARCHAR2,P_ANALYSE_TYPE INT,P_CREATE_TIME NUMBER)isMAX_LEVEL INT;V_ALY_LAYER INT;V_INSERT_COUNT INT;BEGINMAX_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_TIMEFROM 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_TIMEFROM T_MD_ETL_INST F, T_ALY_IL_RESULT RWHERE 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;
转载于:https://www.cnblogs.com/zhouchunDIY/p/8377423.html
