mysql - 仅执行今天尚未运行的 MySQL 存储过程

Question

这主要是对已经工作的存储过程的一种保险类型的补充，我似乎无法让代码工作。

我声明了 dt 并有一个 if 语句来检查 dt = date(now()) 但我正在做某事错误，因为这会引发错误。我也愿意采用更有效/更智能的方法来检查这一点。这就是我想出来的。

--/
CREATE PROCEDURE Matt.find_newwinners()
begin
    declare done int default false;
    declare a varchar(10);

    declare dt varchar(30);
    set dt = (select date(max(dateadded)) from dbo.newwins);

    declare cur1 cursor for select personoid from dbo.view_newwinners;
    declare continue handler for not found set done = true;

    open cur1;

    read_loop: loop
        fetch cur1 into a;

        if done then leave read_loop;
        end if;

        if dt = date(now()) then leave read_loop;
        /* would also be nice to print a reason why we are exiting */
        end if;

        insert ignore into dbo.newwins (personoid, dateadded) values (a, now());

    end loop;
    close cur1;
end

/

错误消息:

[Code: 1064, SQL State: 42000]  You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'declare cur1 cursor for select personoid from dbo.view_newwinners;
declare conti' at line 7
Code: 1064 SQL State: 42000 --- You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'declare cur1 cursor for select personoid from dbo.view_newwinners;
declare conti' at line 7

Answer 1

您收到的错误与 IF 语句无关，而是因为必须在 BEGIN 语句之后立即声明游标。参见 syntax error on DECLARE CURSOR FOR .

但是，您的过程可以简化，因此您根本不需要光标。只需使用 INSERT ... SELECT 将查询结果插入表中，并将 IF 放在查询周围。

--/
CREATE PROCEDURE Matt.find_newwinners()
begin

    declare dt DATE;
    set dt = (select date(max(dateadded)) from dbo.newwins);
    IF dt != TODAY() THEN 
        INSERT INTO dbo.newwins (personoid, dateadded)
        SELECT personoid, NOW()
        FROM dbo.view_newwinners;
    END IF

end

/

您甚至可以完全摆脱 IF:

INSERT INTO dbo.newwins (personoid, dateadded)
SELECT personoid, NOW()
FROM dbo.view_newwinners
CROSS JOIN (
    SELECT DATE(MAX(dateadded)) AS lastadded
    FROM dbo.newwins) AS x
WHERE lastadded != TODAY();