mysql存储过程的异常

2012-05-04

mysql存储过程的错误下面是我在MYSQL中通过存储过程往中间表插数据

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 ANDDATE_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 '未回款'

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 ANDDATE_FORMAT(b.orderform_date,'%Y-%m') = totalTime

GROUP BY a.payee_id;
查出来的结果是正确的：
+--------------------------------------+--------+--------+--------+
| 回款人ID | 应回款 | 已回款 | 未回款 |
+--------------------------------------+--------+--------+--------+
| 2672ffd2-6c69-4df5-825a-8401b01c6623 | 4350 | 4350 | 0 |
| a25d368d-89e6-45e3-af55-553e2d071fe7 | 4800 | 3500 | 1300 |
| ea2a73b4-631f-4454-b7fe-bec5a4e484e3 | 5200 | 3000 | 2200 |
+--------------------------------------+--------+--------+--------+

但是我call sp_return_money('2012-03');的时候，在中间表插入的3个字段should_return_money 、alread_return_money 、remain_return_money三个字段全部是0（cmd里面结果太长，未贴上）。正常情况下，应该是有上面3条数据的。

求大神帮我解决

[解决办法]
加入下面语句，以确认传入的参数正确。

SQL code

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;    select tenantId,deptId,employeeId,totalTime;  -- 确认传入变量正确        -- 打开游标    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
[解决办法]
只有分步调试，在每个有WHERE的SQL语句后加上
set done=1，在游标循环中加上SELECT 变量名，看看错误在什么地方
[解决办法]
分步调试

热点排行

Mysql

mysql存储过程的异常