mysql数据库存储过程游标循环，提前退出

时间:2019-02-18 12:44 来源:linux.it.net.cn 作者:IT

mysql游标遇到null提前退出
游标循环中出现 select into 赋值为 null 的时候，循环会提前退出，解决方法有三种
1不用select into
2select aa into bb，aa改为count（aa），之后的代码由判断null改为判断0
2当赋值结果为null的时候，会自动修改循环触底的标志，所以手动在循环底部增加 set b = 1；

BEGIN
/**
    功能：平台收益计算
    参数：结算周期
    作者：高广金 2015-10-13
**/

/*产品标示,渠道标示,外部产品代码*/
DECLARE
    channelId,
    productId,
    outerProductId VARCHAR (64);

/*申购量,赎回量,持仓量*/
DECLARE
    purchaseVolume,
    redeemVolume,
    positions,
counts,
   _error,noResult INT DEFAULT 0;

/*申购金额,赎回金额,持仓金额,收益率,收益金额,结息金额,盈亏金额*/
DECLARE
    purchaseAmount,
    redeemAmount,
    amounts,
    yieldRate,
    incomeAmount,
allocatedAmount,
    settleAmount,
    profitLossAmount DOUBLE DEFAULT 0;

DECLARE
    cur CURSOR FOR SELECT
        a.channel_id,
        a.product_id,
        sum(a.purchase_volume),
        sum(a.purchase_amount),
        sum(a.redeem_volume),
        sum(a.redeem_amount),
        sum(a.position),
        sum(a.amount)
    FROM
        c_daily_user_income a
    WHERE
        a.settle_date = settledate
    GROUP BY
        a.settle_date,
        a.channel_id,
    a.sys_id,
        a.product_id;

DECLARE
    CONTINUE HANDLER FOR NOT FOUND
SET noResult = 1;
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION,SQLWARNING set _error=1;

OPEN cur;
start transaction;

FETCH cur INTO channelId,productId,purchaseVolume,purchaseAmount, redeemVolume,redeemAmount,
    positions,amounts;

REPEAT
    SELECT COUNT(1) INTO counts FROM c_channel_settle_param ccsp WHERE ccsp.settle_date=settledate AND ccsp.product_id=productId AND ccsp.channel_id=channelId;
/*获取外部产品id*/
     IF counts>0 then
SELECT ccsp.product_origin INTO outerProductId FROM c_channel_settle_param ccsp WHERE ccsp.settle_date=settledate AND ccsp.product_id=productId AND ccsp.channel_id=channelId;
END IF;

SELECT COUNT(1) INTO counts FROM c_financial_account_flow cfaf WHERE cfaf.settle_date=settledate AND cfaf.out_produte_id=outerProductId AND cfaf.trans_type='4';
IF counts>0 then
   /*结息金额计算,在理财账户（B账户）流水表 */
     SELECT cfaf.amount INTO settleAmount FROM c_financial_account_flow cfaf WHERE cfaf.settle_date=settledate AND cfaf.out_produte_id=outerProductId AND cfaf.trans_type='4';
END IF;

SELECT COUNT(1) INTO counts from c_financial_income_data cfid WHERE cfid.settle_date=settledate AND cfid.out_product_id=outerProductId;
/*总行理财产品收益数据c_financial_income_data查询出收益率*/
/*平台固定收益=持仓金额*实际收益率 */
/*收益金额income_amount=（平台固定收益+结息金额）*/
IF counts>0 then
    SELECT (cfid.yield_rate*amounts+settleAmount) INTO incomeAmount from c_financial_income_data cfid WHERE cfid.settle_date=settledate AND cfid.out_product_id=outerProductId;
END IF;

SELECT COUNT(1) INTO counts FROM c_product_income cpi WHERE cpi.settle_date=settledate AND cpi.product_id=productId;
/*
分配金额=实际支付渠道金额=c_daily_user_income 的amount * 产品收益表 c_product_income（yield_rate）
*/
IF counts>0 then
SELECT (cpi.yield_rate*amount) INTO allocatedAmount FROM c_product_income cpi WHERE cpi.settle_date=settledate AND cpi.product_id=productId;
END IF;
/*盈亏金额=收益金额-渠道实际支付金额*/
SET profitLossAmount=incomeAmount-allocatedAmount;

/*插入*/
INSERT INTO c_platform_income (settle_date,sys_id,outer_product_id,purchase_volume,purchase_amount,redeem_volume,redeem_amount,`position`,amount,yield_rate,income_amount,settle_amount,allocated_amount,profit_loss_amount)
    values(settledate,'1',outerProductId,purchaseVolume,purchaseAmount,redeemVolume,redeemAmount,positions,amounts,0,incomeAmount,settleAmount,allocatedAmount,profitLossAmount);

FETCH cur INTO channelId,productId,purchaseVolume,purchaseAmount, redeemVolume,redeemAmount,
    positions,amounts;

UNTIL noResult = 1
END REPEAT;
CLOSE cur;
IF _error=0 THEN
COMMIT;
   else
ROLLBACK;
   end if;
END

(责任编辑：IT)