mysql xml 参数

最新推荐文章于 2021-12-16 17:42:47 发布

weixin_30740295

最新推荐文章于 2021-12-16 17:42:47 发布

阅读量144

点赞数

文章标签：数据库

原文链接：http://www.cnblogs.com/zisezhixin/p/5125671.html

版权

drop PROCEDURE IF EXISTS tt;
CREATE PROCEDURE tt(
para text,
OUT para1 DECIMAL(18, 6),
OUT para2 DECIMAL(18, 6)
)
BEGIN
DECLARE Count int;
DECLARE i int;

    DECLARE v_id int;
    DECLARE v_insurance_amount DECIMAL(18, 6);
    DECLARE v_rate DECIMAL(18, 6);

    SET i = 1;
    SET Count = ExtractValue(para, 'count(/list/com.xk.insalesystem.pojo.premiumCalculation.HouseholdPropertyAddtionalInsurance/id)');
    WHILE i <= Count DO
            SET v_id = ExtractValue(para, '/list/com.xk.insalesystem.pojo.premiumCalculation.HouseholdPropertyAddtionalInsurance[$i]/id');
            SET v_insurance_amount = ExtractValue(para, '/list/com.xk.insalesystem.pojo.premiumCalculation.HouseholdPropertyAddtionalInsurance[$i]/insurance_amount');
            SET v_rate = ExtractValue(para, '/list/com.xk.insalesystem.pojo.premiumCalculation.HouseholdPropertyAddtionalInsurance[$i]/rate');
            IF v_id = 1 THEN
                SET para1 = v_insurance_amount * v_rate;
            END IF;
            IF v_id = 2 THEN
                SET para2 = v_insurance_amount * v_rate;
            END IF;
        SET i = i + 1;
    END WHILE;
end;

call tt('<list>
<com.xk.insalesystem.pojo.premiumCalculation.HouseholdPropertyAddtionalInsurance>
    <id>1</id>
    <insurance_amount>1000</insurance_amount>
    <rate>0.35</rate>
    <premium_amount>0.0</premium_amount>
</com.xk.insalesystem.pojo.premiumCalculation.HouseholdPropertyAddtionalInsurance>
<com.xk.insalesystem.pojo.premiumCalculation.HouseholdPropertyAddtionalInsurance>
    <id>2</id>
    <insurance_amount>300</insurance_amount>
    <rate>0.4</rate>
    <premium_amount>0.0</premium_amount>
</com.xk.insalesystem.pojo.premiumCalculation.HouseholdPropertyAddtionalInsurance>
</list>', @a, @b);
SELECT @a, @b;

转载于:https://www.cnblogs.com/zisezhixin/p/5125671.html