调用 存储过程

 

 

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;