MySql变量名和字段重名导致存储过程执行错误

最新推荐文章于 2024-05-19 03:18:57 发布

System.gc(me)

最新推荐文章于 2024-05-19 03:18:57 发布

阅读量134

点赞数

分类专栏：数据库文章标签： mysql 数据库

本文链接：https://blog.csdn.net/TinasheYGH/article/details/131823560

版权

数据库专栏收录该内容

2 篇文章 0 订阅

订阅专栏

MySql变量名和字段重名导致存储过程执行错误

最近项目上遇到一个存储过程

CREATE DEFINER=`epoint_root_cs`@`%` PROCEDURE `Proc_PingBiao_CalcBJInfo`(IN `inBJGuid` varchar(50))
    SQL SECURITY INVOKER
BEGIN

	DECLARE TotalPrice DECIMAL(18, 2);

	    UPDATE  PB_TB_BaoJiaHistroyDetail

        SET     TotalPrice = IFNULL(UnitPrice, 0) * IFNULL(GeShu, 0)

        WHERE   BJGuid = inBJGuid;

          commit;


        UPDATE  PB_TB_BaoJiaHistroyDetail

        SET     TotalPrice = IFNULL(( SELECT    SUM(IFNULL(b.TotalPrice, 0))

                                      FROM      (select TotalPrice,JieGou,BJGuid from PB_TB_BaoJiaHistroyDetail) b

                                      where     b.JieGou LIKE CONCAT_WS(PB_TB_BaoJiaHistroyDetail.JieGou,'.%')

                                                AND b.BJGuid = inBJGuid

                                    )

                                    , PB_TB_BaoJiaHistroyDetail.TotalPrice)

        where   BJGuid = inBJGuid

                AND LOCATE('.', JieGou) = 0;

                  commit;


  UPDATE  PingBiao_BaoJiaHistroy

        SET     BJTotal = IFNULL(( SELECT  SUM(IFNULL(TotalPrice, 0))

                            FROM    PB_TB_BaoJiaHistroyDetail

                            WHERE   LOCATE('.', JieGou) = 0

                                    AND BJGuid = inBJGuid

                          ),-1),

                FinalBaoJia=( SELECT  SUM(IFNULL(TotalPrice, 0))

                            FROM    PB_TB_BaoJiaHistroyDetail

                            WHERE   LOCATE('.', JieGou) = 0

                                    AND BJGuid = inBJGuid

                          )

        WHERE   BJGuid = inBJGuid;

          commit;



END

执行存储过程不会报错但totalprice 始终为0
仔细检查 update 中的TotalPrice 与定义变量totalprice 重名
mysql情况下优先取变量中的值所以一直取得是null
update中其实想取的是表中的totalprice

 UPDATE  PingBiao_BaoJiaHistroy

        SET     BJTotal = IFNULL(( SELECT  SUM(IFNULL(PB_TB_BaoJiaHistroyDetail.TotalPrice, 0))

                            FROM    PB_TB_BaoJiaHistroyDetail

                            WHERE   LOCATE('.', JieGou) = 0

                                    AND BJGuid = inBJGuid

                          ),-1),

                FinalBaoJia=( SELECT  SUM(IFNULL(PB_TB_BaoJiaHistroyDetail.TotalPrice, 0))

                            FROM    PB_TB_BaoJiaHistroyDetail

                            WHERE   LOCATE('.', JieGou) = 0

                                    AND BJGuid = inBJGuid

                          )

        WHERE   BJGuid = inBJGuid;

          commit;