尝试在mysql中创建一个带有游标的事件

slmsl1lt 于 2021-06-23 发布在 Mysql

关注(0)|答案(2)|浏览(290)

我想用光标创建一个事件。但是下面有个错误
错误：

ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 3

代码：

create event api_5 ON SCHEDULE EVERY 360 SECOND DO
BEGIN
DECLARE lc_current_time DATETIME;
DECLARE unuse_count INT;
DECLARE auto_assign TINYINT;
DECLARE total_sum INT;
DECLARE check_count INT;
SET lc_current_time = CONVERT_TZ(NOW(), @@session.time_zone, '+0:00');

DECLARE select_cursor CURSOR FOR SELECT unuse_count,auto_assign,total_sum,check_count from new_api;

OPEN select_cursor;
LOOP
     FETCH select_cursor INTO unuse_count,auto_assign,total_sum,check_count
     if(unuse_count <= check_count){
        insert into api_report(date,new_api_id,api_name,type,status,email,customer_id,unuse_count,check_count) values (lc_current_time,5,'abc',4,1,'abc@gmail.com',check_count,unuse_count);
        }
     END;
END LOOP;
CLOSE select_cursor;
END;

任何人都想知道为什么它会出现在第三行。有一些限制，比如我们不能多次改变。我是第一次尝试。所以请忽略任何错误。
注意：insert query和select query都可以正常工作。
第二个错误：

ERROR 1064 (42000) at line 2: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DECLARE select_cursor CURSOR FOR SELECT unuse_count,auto_assign,total_sum,check_' at line 10

mysql

来源：https://stackoverflow.com/questions/50542999/try-to-create-an-event-with-cursor-in-mysql

2条答案

按热度按时间

xxe27gdn1#

答案如下：
修复：
未添加分隔符。它给出了第一个错误，所以在开头和结尾都添加了分隔符。
没有完成，所有变量声明都开始使用它。作为回答，我把 SET lc_current_time = CONVERT_TZ(NOW(), @@session.time_zone, '+0:00'); 在光标之后。 If condition 未按mysql语法添加
工作方案：

delimiter $$
create event api_6 ON SCHEDULE EVERY 360 SECOND DO
BEGIN
DECLARE lc_current_time DATETIME;
DECLARE unuse_count INT;
DECLARE auto_assign TINYINT;
DECLARE total_sum INT;
DECLARE check_count INT;
DECLARE select_cursor CURSOR FOR SELECT unuse_count,auto_assign,total_sum,check_count from new_api;
SET lc_current_time = CONVERT_TZ(NOW(), @@session.time_zone, '+0:00');
OPEN select_cursor;
LOOP
     FETCH select_cursor INTO unuse_count,auto_assign,total_sum,check_count;
        IF unuse_count <= check_count THEN
          insert into api_report(date,new_api_id,api_name,type,status,email,customer_id,unuse_count,check_count) values (lc_current_time,5,'abc',4,1,'abc@gmail.com',check_count,unuse_count);
        END IF;
END LOOP;
CLOSE select_cursor;
END$$
delimiter ;

赞(0）回复(0）举报 2021-06-23

n53p2ov02#

如果尚未使用分隔符，请尝试以下操作：

delimiter $$
create event api_5 ON SCHEDULE EVERY 360 SECOND DO
BEGIN
DECLARE lc_current_time DATETIME;
DECLARE unuse_count INT;
DECLARE auto_assign TINYINT;
DECLARE total_sum INT;
DECLARE check_count INT;
SET lc_current_time = CONVERT_TZ(NOW(), @@session.time_zone, '+0:00');

DECLARE select_cursor CURSOR FOR SELECT unuse_count,auto_assign,total_sum,check_count from new_api;

OPEN select_cursor;
LOOP
     FETCH select_cursor INTO unuse_count,auto_assign,total_sum,check_count
     if(unuse_count <= check_count){
        insert into api_report(date,new_api_id,api_name,type,status,email,customer_id,unuse_count,check_count) values (lc_current_time,5,'abc',4,1,'abc@gmail.com',check_count,unuse_count);
        }
     END;
END LOOP;
CLOSE select_cursor;
END
delimiter ;

赞(0）回复(0）举报 2021-06-23

我来回答

尝试在mysql中创建一个带有游标的事件

2条答案

相关问题

热门标签

最新问答