mysql - 如何在mysql存储过程中分配默认值

Question

如何在 MySQL 的存储过程中分配默认值

DELIMITER $$

CREATE DEFINER=`ntc`@`%` PROCEDURE `MangerModule`()
BEGIN
    select (select sum(AmountRecevied) from collection) as TotalAmount,
           (select  @DayAmount := sum(AmountRecevied) as TotoalAmountperday 
            from collection  
            where  day(Date_Time)= day(CURRENT_DATE())) as Dayamount,

           (select  @MonthAmount :=sum(AmountRecevied) as TotoalAmountperMonth  
            from collection 
            where  date_time between DATE_FORMAT(NOW() ,'%Y-%m-01') 
                and LAST_DAY(now() - interval 0 month )) as monthamount,

           (select  @YearAmount := sum(AmountRecevied) as TotoalAmountperYear  
            from collection  
            where  year(Date_Time) =YEAR(CURRENT_DATE())) as yearamount;

END

Answer 1

使用IFNULL:

select (select IFNULL(sum(AmountRecevied), 0.0) from collection) as TotalAmount,
       (select  @DayAmount := IFNULL(sum(AmountRecevied), 0.0) as TotoalAmountperday 
        from collection  
        where  day(Date_Time)= day(CURRENT_DATE())) as Dayamount,

       (select  @MonthAmount := IFNULL(sum(AmountRecevied), 0.0) as TotoalAmountperMonth  
        from collection 
        where  date_time between DATE_FORMAT(NOW() ,'%Y-%m-01') 
            and LAST_DAY(now() - interval 0 month )) as monthamount,

       (select  @YearAmount := IFNULL(sum(AmountRecevied), 0.0) as TotoalAmountperYear  
        from collection  
        where  year(Date_Time) =YEAR(CURRENT_DATE())) as yearamount;