MyBatis存储过程调用java

<parameterMap class="java.util.Map" id="countChargeMap">
<parameter property="merchantId" jdbcType="VARCHAR" javaType="java.lang.String" mode="IN"/>
<parameter property="payClassId" jdbcType="INTEGER" javaType="java.lang.Integer" mode="IN"/>
<parameter property="amount" jdbcType="DECIMAL" javaType="java.math.BigDecimal" mode="IN"/>
<parameter property="bankCharges" jdbcType="DECIMAL" javaType="java.math.BigDecimal" mode="IN"/>
<parameter property="dinpayCharge" jdbcType="DECIMAL" javaType="java.math.BigDecimal" mode="INOUT"/>
<parameter property="oneProxyCharge" jdbcType="DECIMAL" javaType="java.math.BigDecimal" mode="INOUT"/>
<parameter property="twoProxyCharge" jdbcType="DECIMAL" javaType="java.math.BigDecimal" mode="INOUT"/>
<parameter property="threeProxyCharge" jdbcType="DECIMAL" javaType="java.math.BigDecimal" mode="INOUT"/>
<parameter property="riskCharge" jdbcType="DECIMAL" javaType="java.math.BigDecimal" mode="INOUT"/>
<parameter property="proxy1Id" jdbcType="VARCHAR" javaType="java.lang.String" mode="INOUT"/>
<parameter property="proxy2Id" jdbcType="VARCHAR" javaType="java.lang.String" mode="INOUT"/>
<parameter property="proxy3Id" jdbcType="VARCHAR" javaType="java.lang.String" mode="INOUT"/>
</parameterMap>

<procedure id="callCharge" parameterMap="countChargeMap">
<![CDATA[
{call COUNTCHARGENOPROXY(?,?,?,?,?,?,?,?,?,?,?,?) }
]]>
</procedure>


CREATE OR REPLACE PROCEDURE COUNTCHARGENOPROXY(V_MERCHANTID IN VARCHAR2,--商家号
V_PAYCLASSID IN NUMBER,--支付类型
V_AMOUNT IN NUMBER,--交易金额
V_BANKCHAR IN NUMBER,--bank手续费
V_DINPAYCHARGE OUT NUMBER,--dinpay手续费
V_ONEPROXYCHARGE OUT NUMBER,--一级手续费
V_TWOPROXYCHARGE OUT NUMBER,--二级手续费
V_THREEPROXYCHARGE OUT NUMBER,-- 三级手续费
V_RISKCHARGE OUT NUMBER, --风险保证金
V_ONEPROXYID OUT VARCHAR2,
V_TWOPROXYID OUT VARCHAR2,
V_THREEPROXYID OUT VARCHAR2)

AS
BEGIN
DECLARE
V_CHARGE_DATE DATE;
V_CHARGETYPE VARCHAR2(2);
V_COUNT NUMBER(1,0); --是否计算分润（不等于0时计算）
V_PROXY2_RETURN_RATE NUMBER(6,4):=0;
V_PROXY3_RETURN_RATE NUMBER(6,4):=0;

BEGIN

-- 1.根据支付类型与商家号查询商家业务配置参数(手续费计算方式，费率，风险保证金收取方式，风险保证金值 )
--并计算dinpay总手续费 及 风险保证金
SELECT CHARGE_DATE,
CASE
WHEN CHARGE_TYPE='1' THEN CHARGE_VALUE
WHEN CHARGE_TYPE='0' OR CHARGE_TYPE='2' THEN CHARGE_VALUE * V_AMOUNT
WHEN CHARGE_TYPE='3' THEN (CHARGE_VALUE * V_AMOUNT)-V_BANKCHAR
END,
CASE
WHEN DEPOSIT_TYPE='0' THEN 0
WHEN CHARGE_TYPE='0' OR CHARGE_TYPE='2' OR CHARGE_TYPE='3' THEN V_AMOUNT*(1-CHARGE_VALUE)*DEPOSIT_VALUE
WHEN CHARGE_TYPE='1' THEN (V_AMOUNT-CHARGE_VALUE)*DEPOSIT_VALUE
END,
CHARGE_TYPE
INTO V_CHARGE_DATE,V_DINPAYCHARGE,V_RISKCHARGE,V_CHARGETYPE
FROM T_MERCHANT_PAYCLASS_CONFIG WHERE MERCHANT_ID=V_MERCHANTID AND PAYCLASS_ID=V_PAYCLASSID;
--查询三级代理商ID
SELECT PROXY_ID INTO V_THREEPROXYID FROM T_MERCHANT WHERE ID=V_MERCHANTID;
--查询是否需要计算分润
SELECT COUNT(*) INTO V_COUNT FROM T_PROXY_PAYCLASS_CONFIG WHERE PROXY_ID=V_THREEPROXYID AND PAYCLASS_ID=V_PAYCLASSID;

IF SYSDATE>=V_CHARGE_DATE AND V_COUNT>0 THEN --已过免手续费期
BEGIN
IF V_CHARGETYPE = '2' THEN --2表示固定费率 代理商收费方式根据商家收费方式来定
BEGIN
-- 计算三级手续费 总手续费-总交易额*3级固定费率
SELECT V_DINPAYCHARGE-TP.RETURN_FIXED*V_AMOUNT,T.PARENT_ID
INTO V_THREEPROXYCHARGE,V_TWOPROXYID
FROM T_PROXY_PAYCLASS_CONFIG TP,T_PROXY T
WHERE TP.PROXY_ID=T.ID AND TP.PROXY_ID=V_THREEPROXYID AND TP.PAYCLASS_ID=V_PAYCLASSID;
-- 计算二级手续费 总手续费-总交易额*2级固定费率-3级代理手续费；
SELECT V_DINPAYCHARGE-TP.RETURN_FIXED*V_AMOUNT-V_THREEPROXYCHARGE,T.PARENT_ID
INTO V_TWOPROXYCHARGE,V_ONEPROXYID
FROM T_PROXY_PAYCLASS_CONFIG TP,T_PROXY T WHERE T.ID=TP.PROXY_ID AND TP.PROXY_ID=V_TWOPROXYID AND TP.PAYCLASS_ID=V_PAYCLASSID;
-- 计算一级手续费 1级代理手续费=总手续费-总交易额*1级固定费率 - 2级代理手续费-3级代理手续费；
SELECT V_DINPAYCHARGE-TP.RETURN_FIXED*V_AMOUNT-V_THREEPROXYCHARGE-V_TWOPROXYCHARGE
INTO V_ONEPROXYCHARGE
FROM T_PROXY_PAYCLASS_CONFIG TP,T_PROXY T WHERE T.ID=TP.PROXY_ID AND TP.PROXY_ID=V_ONEPROXYID AND TP.PAYCLASS_ID=V_PAYCLASSID;
END;
ELSIF V_CHARGETYPE='0' OR V_CHARGETYPE='1' OR V_CHARGETYPE='3' THEN -- 当商家为返点或者 按笔收时 代理商按返点算法来计算手续费
BEGIN
-- 计算三级手续费 3级代理手续费=总手续费*3级返利点；
SELECT V_DINPAYCHARGE*TP.RETURN_RATE,TP.RETURN_RATE,T.PARENT_ID
INTO V_THREEPROXYCHARGE,V_PROXY3_RETURN_RATE,V_TWOPROXYID
FROM T_PROXY_PAYCLASS_CONFIG TP,T_PROXY T
WHERE TP.PROXY_ID=T.ID AND TP.PROXY_ID=V_THREEPROXYID AND TP.PAYCLASS_ID=V_PAYCLASSID;
-- 计算二级手续费 2级代理手续费=总手续费*（2级返利点-3级返利点）
SELECT V_DINPAYCHARGE*(TP.RETURN_RATE-V_PROXY3_RETURN_RATE),TP.RETURN_RATE,T.PARENT_ID
INTO V_TWOPROXYCHARGE,V_PROXY2_RETURN_RATE,V_ONEPROXYID
FROM T_PROXY_PAYCLASS_CONFIG TP,T_PROXY T WHERE T.ID=TP.PROXY_ID AND TP.PROXY_ID=V_TWOPROXYID AND TP.PAYCLASS_ID=V_PAYCLASSID;
-- 计算一级手续费 1级代理手续费=总手续费*（1级返利点-2级返利点）
SELECT V_DINPAYCHARGE*(TP.RETURN_RATE-V_PROXY2_RETURN_RATE)
INTO V_ONEPROXYCHARGE
FROM T_PROXY_PAYCLASS_CONFIG TP,T_PROXY T WHERE T.ID=TP.PROXY_ID AND TP.PROXY_ID=V_ONEPROXYID AND TP.PAYCLASS_ID=V_PAYCLASSID;
END;
END IF;

END;
ELSIF SYSDATE>=V_CHARGE_DATE AND V_COUNT<=0 THEN --代理商不参与分润
BEGIN
SELECT PARENT_ID INTO V_TWOPROXYID FROM T_PROXY WHERE ID=V_THREEPROXYID AND PROXY_LEVEL=3;
SELECT PARENT_ID INTO V_ONEPROXYID FROM T_PROXY WHERE ID=V_TWOPROXYID AND PROXY_LEVEL=2;
V_ONEPROXYCHARGE := 0;
V_TWOPROXYCHARGE := 0;
V_THREEPROXYCHARGE := 0;
END;
ELSE
BEGIN
SELECT PARENT_ID INTO V_TWOPROXYID FROM T_PROXY WHERE ID=V_THREEPROXYID AND PROXY_LEVEL=3;
SELECT PARENT_ID INTO V_ONEPROXYID FROM T_PROXY WHERE ID=V_TWOPROXYID AND PROXY_LEVEL=2;
V_DINPAYCHARGE := 0;
V_ONEPROXYCHARGE := 0;
V_TWOPROXYCHARGE := 0;
V_THREEPROXYCHARGE := 0;
END;
END IF;
END;
END;