mysql存储过程的错误
下面是我在MYSQL中通过存储过程往中间表插数据
==============================================================================================================
在这个存储过程中，我通过表esale_total_employeeinfo把租户ID、部门ID、销售ID查出来，然后调用sp_return_money_sum，将这几个参数传进去
CREATE DEFINER=`root`@`127.0.0.1` PROCEDURE `sp_return_money`(inputTime varchar(255))
BEGIN
DECLARE tenantId VARCHAR(255); — 租户ID
DECLARE deptId VARCHAR(255); — 部门ID
DECLARE employeeId VARCHAR(255); — 销售ID
DECLARE done int DEFAULT 0;
DECLARE statisticalTime varchar(255); — 统计年月 YYYY-MM
DECLARE return_money_cursor CURSOR FOR
SELECT tenant_id, dept_id, employee_id FROM esale_total_employeeinfo;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;
IF inputTime=’all’ THEN
SET statisticalTime = DATE_FORMAT(now(),’%Y-%m’);
ELSE
set statisticalTime = inputTime;
END IF;
— 打开游标
OPEN return_money_cursor;
WHILE done <> 1 DO
FETCH return_money_cursor INTO tenantId, deptId, employeeId;
CALL sp_return_money_sum(tenantId, deptId, employeeId, statisticalTime);
END WHILE;
CLOSE return_money_cursor;
END
在这个存储过程中，我接收上面存储过程传过来的参数。通过表esale_tb_returned_money 和表esale_tb_orderform 把回款人ID、应回款、已回款等我需要的信息查出来，并赋值给我定义的变量，然后将查出来的这些值插入到中间表esale_total_return_money_employee 中。
CREATE DEFINER=`root`@`127.0.0.1` PROCEDURE `sp_return_money_sum`(tenantId varchar(255),deptId varchar(255),employeeId varchar(255), totalTime varchar(255))
BEGIN
DECLARE return_money_men VARCHAR(255); — 回款人ID
DECLARE must_money DOUBLE DEFAULT 0.00; — 应回款
DECLARE alread_money DOUBLE DEFAULT 0.00; — 已回款
DECLARE no_money DOUBLE DEFAULT 0.00; — 未回款
DECLARE done INT DEFAULT 0;
DECLARE icount INT;
DECLARE data_status INT DEFAULT 1;
DECLARE return_money_sum CURSOR FOR
SELECT a.payee_id AS ‘回款人ID’,SUM(b.amount) AS ‘应回款’,SUM(a.current_returned_money) AS ‘已回款’,SUM(b.amount) – SUM(a.current_returned_money) AS ‘未回款’
FROM
esale_tb_returned_money a, — 回款表
esale_tb_orderform b — 订单表
WHERE a.orderform_id = b.orderform_number AND a.data_status = 1 AND b.data_status = 1 AND b.orderform_status = 1 AND b.employee_id = employeeId AND DATE_FORMAT(b.orderform_date,’%Y-%m’) = totalTime
GROUP BY a.payee_id;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;
— 打开游标
OPEN return_money_sum;
WHILE done<>1 DO
FETCH return_money_sum INTO return_money_men, must_money, alread_money, no_money;
SET @icount = (SELECT COUNT(*) FROM esale_total_return_money_employee WHERE tenant_id = tenantId AND dept_id = deptId AND employee_id = employeeId AND statistical_time = totalTime);
IF @icount = 0 THEN
INSERT INTO esale_total_return_money_employee (tenant_id, dept_id, should_return_money, alread_return_money, return_money_men, remain_return_money, statistical_time, employee_id)
VALUES (tenantId, deptId, must_money, alread_money, return_money_men, no_money, totalTime, employeeId);
ELSE
UPDATE esale_total_return_money_employee SET should_return_money = must_money, alread_return_money = alread_money, remain_return_money = no_money
WHERE tenant_id = tenantId AND dept_id = deptId AND employee_id = employeeId AND statistical_time = totalTime;
END IF;
END WHILE;
— 更新销售人员名称
UPDATE esale_total_return_money_employee a LEFT JOIN esale_total_employeeinfo b ON a.employee_id = b.employee_id SET a.return_money_men = b.employee_name;
CLOSE return_money_sum;
END
我单独运行上面的
SELECT a.payee_id AS ‘回款人ID’,SUM(b.amount) AS ‘应回款’,SUM(a.current_returned_money) AS ‘已回款’,SUM(b.amount) – SUM(a.current_returned_money) AS ‘未回款’
