mysql - MariaDB：我的存储过程没有返回任何行

Question

我已经在 mariaDB 中创建了带有参数的存储过程。当我调用存储过程时，它返回空行，尽管每个表都包含许多数据。这是我拨打电话时的结果：
call sp_rpt_trans('2020','10');
/* 受影响的行：0 找到的行：0 警告：0 1 次查询的持续时间：0.141 秒。*/

下面是我的代码：

DELIMITER //

CREATE PROCEDURE sp_rpt_trans (_year VARCHAR(10),_month VARCHAR(10))

BEGIN

create TEMPORARY table if not exists rpt_trans1 ( 
   category varchar(100) NULL,
   pos VARCHAR(40) NULL,
   id VARCHAR(40) NULL,
   description VARCHAR(100) NULL,
   created_on datetime NULL,    
   handling_fee_amt DECIMAL(22,6) NULL,
   amt DECIMAL(22,6) NULL,
   outlet_commission DECIMAL(22,6) NULL,
   weight DECIMAL(10,2) NULL
);

insert into rpt_trans1 (
category, pos, id, description, created_on, handling_fee_amt, amt, outlet_commission, weight)

SELECT
    *
FROM
    (
        SELECT
            'courier' AS category,
            pt.id pos,
            o.id,
            o.description,
            pt.created_on,
            ct.handling_fee_amt,
            ct.cour_amt AS amt,
            ct.outlet_commission,
            ct.weight
        from
            pos_trans pt
            inner join mgt_outlet o on o.id = pt.mgt_outlet_id
            and date_format(pt.created_on, '%Y') = _year
            AND date_format(pt.created_on, '%m') = _month
            inner join pos_trans_dt ptd on pt.id = ptd.pos_trans_id
            inner join cour_trans ct on ct.id = ptd.cour_trans_id
            AND ct.mgt_outlet_id = o.id
        UNION ALL
        SELECT
            'bill' AS category,
            pt.id pos,
            o.id,
            o.description,
            pt.created_on,
            bt.handling_fee_amt,
            bt.amount AS amt,
            bt.outlet_commission,
            0 as weight
        from
            pos_trans pt
            inner join mgt_outlet o on o.id = pt.mgt_outlet_id
            and date_format(pt.created_on, '%Y') = _year
            AND date_format(pt.created_on, '%m') = _month
            inner join pos_trans_dt ptd on pt.id = ptd.pos_trans_id
            inner join bill_trans bt on bt.id = ptd.bill_trans_id
        UNION ALL
        SELECT
            'insurance' AS category,
            pt.id pos,
            o.id,
            o.description,
            pt.created_on,
            0 AS handling_fee_amt,
            ins.amount AS amt,
            ins.outlet_commision,
            0 as weight
        from
            pos_trans pt
            inner join mgt_outlet o on o.id = pt.mgt_outlet_id
            and date_format(pt.created_on, '%Y') = _year
            AND date_format(pt.created_on, '%m') = _month
            inner join pos_trans_dt ptd on pt.id = ptd.pos_trans_id
            inner join ins_trans ins on ins.id = ptd.ins_trans_id
    ) com1
where
    date_format(created_on, '%Y') = _year
    AND date_format(created_on, '%m') = _month
ORDER BY
    created_on ;

TRUNCATE TABLE rpt_trans1 ;
DROP TEMPORARY TABLE if exists rpt_trans1 ;

END //

DELIMITER ;

原因是什么？请指教。谢谢。

标签： mysqldatabasestored-proceduresmariadbunion