以存储过程实现比对不同表的金额，提升测试效率

工作中经常要比对承保系统和再保系统的金额，写了以下的存储过程以提高测试效率，比对涉及出单和批单的数据，涉及表 承保的是 T_POLICY_COVERAGE ，

再保的表是：T_RI_POLICY_INTERFACE和T_RI_RISKUNIT_INTERFACE

CREATE OR REPLACE PROCEDURE TEST_WANG(NU IN VARCHAR2) IS

--通过&取输入的值    
N1          NUMBER;
N2          NUMBER;
N3          NUMBER;
ENDORSE_NUM NUMBER;
/*   pnum number := #*/
BEGIN
-----------------------------------判断出单时再保的保额、保费是否与承保一致----------
SELECT COUNT(*)
INTO N1
FROM ((
---------------比对T_POLICY_COVERAGE 与  T_RI_POLICY_INTERFACE的保额、保费
SELECT SUM(T.INSURED_AMOUNT) AS 保额,
SUM(T.TOTAL_ACTUAL_PREMIUM) AS 复核保费
FROM AUTO_OPR.T_POLICY_COVERAGE T
WHERE T.POLICY_NO = NU
MINUS
SELECT SUM(T.SUM_INSURED) AS 保额, SUM(T.PREMIUM) AS 保费
FROM REIN_OPR.T_RI_RISKUNIT_INTERFACE T,
REIN_OPR.T_RI_POLICY_INTERFACE   T2
WHERE T.RI_POLICY_ID = T2.RI_POLICY_ID
AND T2.POLICY_NO = NU
AND T2.TIMES = '0') UNION
(
---------------比对T_POLICY_COVERAGE 与  T_RI_RISKUNIT_INTERFACE的保额、保费
SELECT SUM(T.INSURED_AMOUNT) AS 保额,
SUM(T.TOTAL_ACTUAL_PREMIUM) AS 保费
FROM AUTO_OPR.T_POLICY_COVERAGE T
WHERE T.POLICY_NO = NU
MINUS
SELECT SUM(T.SUM_INSURED) AS 保额, SUM(T.PREMIUM) AS 保费
FROM REIN_OPR.T_RI_RISKUNIT_INTERFACE T,
REIN_OPR.T_RI_POLICY_INTERFACE   T2
WHERE T.RI_POLICY_ID = T2.RI_POLICY_ID
AND T2.POLICY_NO = NU
AND T2.TIMES = '0'));


IF N1 = 0 THEN
DBMS_OUTPUT.PUT_LINE('出单的再保数据正确');
ELSE
DBMS_OUTPUT.PUT_LINE('出单的再保数据不正确');
END IF;


-----------------------------------判断批单时T_RI_POLICY_INTERFACE的保额，保费，是否与承保一致----------  
SELECT COUNT(*)
INTO ENDORSE_NUM
FROM AUTO_OPR.T_ENDORSE_COVERAGE
WHERE POLICY_NO = NU;
IF ENDORSE_NUM = 0 THEN
DBMS_OUTPUT.PUT_LINE('承保没有批单数据');
ELSE

SELECT COUNT(*)
INTO N2
FROM (SELECT *
FROM (SELECT SUM(T.INSURED_AMOUNT) AS 保额,
SUM(T.TOTAL_ACTUAL_PREMIUM) AS 保费
FROM AUTO_OPR.T_ENDORSE_COVERAGE T
WHERE POLICY_NO = NU
GROUP BY ENDORSE_ID
ORDER BY ENDORSE_ID ASC)
MINUS
SELECT *
FROM (SELECT SUM(T.SUM_INSURED) AS 保额,
SUM(T.PREMIKUM) AS 保费
FROM REIN_OPR.T_RI_POLICY_INTERFACE T
WHERE T.POLICY_NO = NU
AND T.TIMES NOT IN ('0')
GROUP BY T.TIMES
ORDER BY T.TIMES ASC));

IF N2 = 0 THEN
DBMS_OUTPUT.PUT_LINE('批单T_RI_POLICY_INTERFACE的保额、保费正确');
ELSE
DBMS_OUTPUT.PUT_LINE('批单T_RI_POLICY_INTERFACE的保额、保费不正确');
END IF;

-----------------------------------判断批单时再保T_RI_RISKUNIT_INTERFACE的保额、保额变化，保费，保费变化是否与承保一致---------- 
SELECT COUNT(*)
INTO N3
FROM (SELECT *
FROM (SELECT SUM(T.INSURED_AMOUNT) AS 保额,
SUM(T.ENDORSE_AMOUNT) AS 保额变化,
SUM(T.TOTAL_ACTUAL_PREMIUM) AS 保费,
SUM(T.CHECK_ENDORSE_PREMIUM) AS 保费变化
FROM AUTO_OPR.T_ENDORSE_COVERAGE T
WHERE POLICY_NO = NU
GROUP BY ENDORSE_ID
ORDER BY ENDORSE_ID ASC)
MINUS
SELECT *
FROM (SELECT SUM(T.SUM_INSURED) AS 保额,
SUM(T.SUM_INSURED_CHANGED) AS 保额变化,
SUM(T.PREMIUM) AS 保费,
SUM(T.PREMIUM_CHANGED) AS 保费变化
FROM REIN_OPR.T_RI_RISKUNIT_INTERFACE T,
REIN_OPR.T_RI_POLICY_INTERFACE   T2
WHERE T.RI_POLICY_ID = T2.RI_POLICY_ID
AND T2.POLICY_NO = NU
AND T2.TIMES NOT IN ('0')
GROUP BY T2.TIMES
ORDER BY T2.TIMES ASC));

IF N3 = 0 THEN
DBMS_OUTPUT.PUT_LINE('批单T_RI_RISKUNIT_INTERFACE的保额、保额变化，保费，保费变化正确');
ELSE
DBMS_OUTPUT.PUT_LINE('批单T_RI_RISKUNIT_INTERFACE的保额、保额变化，保费，保费变化不正确');
END IF;

END IF;
IF N3 = 0 AND N2 = 0 AND N1 = 0 OR (ENDORSE_NUM = 0 AND N1 = 0) THEN
DBMS_OUTPUT.PUT_LINE('PASS');
ELSE
DBMS_OUTPUT.PUT_LINE('FAIL');
END IF;


END TEST_WANG;
/