MySQL存储过程类似于光标声明中的合成文件

I am very green when it comes to MySQL Stored Procedures and i have been having trouble with selecting all payments received in the current month and looping through them to see which accounts did not pay the monthly fee, my problem seems to be with the LIKE statement in the declaration of the cursor. Here is the code:

我对MySQL存储过程非常环保，而且我一直无法选择当月收到的所有付款并循环查看哪些帐户没有支付月费，我的问题似乎与LIKE语句有关在光标的声明中。这是代码：

CREATE DEFINER=`wisper`@`%` PROCEDURE `process_rejections`()
BEGIN
    DECLARE cursor_ID INT(11);
    DECLARE account_id INT(11);
    DECLARE amount_paid DECIMAL(10,2);
    DECLARE date_paid DATETIME;
    DECLARE cur_year INT DEFAULT (SELECT YEAR(CURRENT_DATE()));
    DECLARE cur_month INT DEFAULT (SELECT MONTH(CURRENT_DATE()));
    DECLARE comp_date VARCHAR(10) DEFAULT (SELECT CONCAT(cur_year,'-',cur_month));
    DECLARE done INT DEFAULT FALSE;

    DECLARE cursor_i CURSOR FOR 
    SELECT `id`, `account_id`, `amount_paid`, `date` 
    FROM `payments_received` 
    WHERE `date`LIKE CONCAT('%',@comp_date,'%');

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    OPEN cursor_i;
    read_loop: LOOP
        FETCH cursor_i INTO cursor_ID, account_id, amount_paid, date_paid;
            IF done THEN
                LEAVE read_loop;
            END IF;
            //Do Stuff here like change the status of the account
        END LOOP;
    CLOSE cursor_i;
END

IF i remove this:

如果我删除此：

WHERE `date` LIKE CONCAT('%',@comp_date,'%');

Everything works but it obviously selects * payments received instead of those that occured during ,for example, Aug 2017. I do not want to select * because over time there will be hundreds of thousands of rows in the specific table and i do not want the overhead to become too much. I have also tested this:

一切正常，但它明显选择*付款而不是在2017年8月期间发生的那些。我不想选择*因为随着时间的推移，特定表格中将有数十万行，我不希望开销太大了。我也测试了这个：

WHERE `date` LIKE '2017-08-11';

Which also does not work. During debuggin i also tried to use a specifi date like this:

哪个也行不通。在debuggin期间，我也尝试使用如下的特定日期：

WHERE `date` = '2017-08-11';

And everything worked nicely but it is obviously just to test and see if the WHERE cluase was syntactically correct inside the cursor declaration.

一切都运行良好，但显然只是测试并查看WHERE cluase在游标声明中是否语法正确。

I am at a lost here and would appreciate some assistance from you guy's.

我在这里迷路了，并希望得到你们的一些帮助。

Thank You in advance.

先谢谢你。

2 个解决方案

#1

You've debugged your code well, and you've isolated the problem successfully. It's not the LIKE itself, or the fact it is a stored procedure that's tripping you up.

您已经很好地调试了代码，并且已经成功地隔离了问题。这不是LIKE本身，或者它是一个绊倒你的存储过程。

Problem: You're using a session variable (the variable is prepended with an @), not the stored procedure variable declared a few lines earlier.

问题：您正在使用会话变量（该变量前缀为@），而不是之前几行声明的存储过程变量。

Solution: Drop the @ (on comp_date):

解决方案：删除@（在comp_date上）：

WHERE `date` LIKE CONCAT('%', comp_date, '%');

Assuming the session variable isn't set, I believe you'll be getting NULL, which won't work. Thus the symptom you're seeing.

假设没有设置会话变量，我相信你会得到NULL，这是行不通的。因此，你看到的症状。

There's some useful explanation on another SO question here: MySQL: @variable vs. variable. Whats the difference?

关于另一个SO问题有一些有用的解释：MySQL：@variable vs. variable。有什么不同？

#2

I have found the problem, turns out the comp_date looks like this 2017-8 instead of using leading zeros like this 2017-08, here is the finished code:

我发现了问题，原来comp_date看起来像2017-8而不是像2017-08这样的前导零，这里是完成的代码：

PS: Yes i feel like an idiot for not testing the values that i submit in the statement, but maybe someone else also struggles with it and this can help them.

PS：是的，我觉得自己是一个白痴，因为我没有测试我在声明中提交的价值观，但也许其他人也在努力解决它，这可以帮助他们。

CREATE DEFINER=`wesley`@`%` PROCEDURE `process_rejections`()
BEGIN
    DECLARE cursor_ID INT(11);
    DECLARE account_id INT(11);
    DECLARE amount_paid DECIMAL(10,2);
    DECLARE date_paid DATETIME;

    DECLARE comp_date VARCHAR(10) DEFAULT 
    CONCAT(YEAR(CURRENT_DATE()), '-', LPAD(MONTH(CURRENT_DATE()), 2, '0'));

    DECLARE done INT DEFAULT FALSE;
    DECLARE cursor_i CURSOR 
    FOR SELECT `id`, `account_id`, `amount_paid`, `date` 
    FROM `payments_received` 
    WHERE `date` 
    LIKE CONCAT('%', comp_date,'%');

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    OPEN cursor_i;
    read_loop: LOOP
    FETCH cursor_i INTO cursor_ID, account_id, amount_paid, date_paid;
            IF done THEN
                LEAVE read_loop;
            END IF;
        //Do stuff here like changing account status
        END LOOP;
    CLOSE cursor_i;
END

Note how i declare comp_date to account for leading zeros.

请注意我如何声明comp_date来计算前导零。

#1