我创建了一个存储过程,需要从投诉表中获取所有记录,然后循环查看,找出谁对特定投诉发表了评论。您可以假设它是一个博客应用程序,其中一个博客可以有多个评论。我在中遇到语法错误
Declare cur CURSOR statement,
可能是什么问题,或者我错过了什么。
这是下面的消息,我收到
错误代码:1064您的SQL语法有错误;查看与MySQL服务器版本对应的手册,了解在第22行的"declare cur CURSOR for SELECT id,user_id FROM complaint3 WHERE user_id IN(SELEC("附近使用的正确语法
以下是我的程序
DELIMITER $$
CREATE PROCEDURE `myDB`.`ShowResult`(user_id INT, hours INT,L_Complaint_id INT)
BEGIN
DECLARE complaint_count INT;
DECLARE 24Hrs_count INT;
DECLARE 48Hrs_count INT;
DECLARE Gr_48Hrs_count INT;
DECLARE notCommented INT;
DECLARE agentName VARCHAR(100);
DECLARE v_agentId INT;
DECLARE v_userId INT;
DECLARE lastUserCommentDate DATETIME;
DECLARE lastInternalUserCommentDate DATETIME;
DECLARE tempDate INT;
DECLARE v_complaint_id INT;
SET 24Hrs_count =0;
SET 48Hrs_count =0;
SET Gr_48Hrs_count =0;
SET notCommented =0;
SET v_complaint_id =0;
DECLARE cur CURSOR FOR SELECT id,user_id FROM complaint3 WHERE user_id IN( SELECT id FROM user3 WHERE user_type=0);
CREATE TEMPORARY TABLE IF NOT EXISTS resultTable (
id MEDIUMINT NOT NULL AUTO_INCREMENT,t_agentId INT,t_agentName VARCHAR(1000),t_24HrsCount INT,t_48HrsCount INT,t_Gr48HrsCount INT,
t_nullCount INT,PRIMARY KEY (id)) ENGINE = MEMORY;
SELECT COUNT(DISTINCT(id)) INTO complaint_count FROM complaint3 WHERE user_id IN(SELECT id FROM user3 WHERE user_type=0);
OPEN cur;
insert_loop: LOOP
IF complaint_count > 0 THEN
FETCH cur INTO complaint_id,user_id;
SELECT created_at INTO lastUserCommentDate FROM complaint3_diary WHERE complaint_id=v_complaint_id AND user_id = v_user_id ORDER BY id DESC LIMIT 1;
SELECT assigned_to INTO v_agentId FROM assignment3 WHERE complaint_id=v_complaint_id AND a.expire_at IS NULL;
SELECT NAME INTO agentName FROM user3 WHERE id=v_agentId;
SELECT created_at INTO lastInternalUserCommentDate FROM complaint3_diary WHERE complaint_id=v_complaint_id AND user_id = v_agentId ORDER BY id DESC LIMIT 1;
SELECT TIMESTAMPDIFF(HOUR, lastInternalUserCommentDate, lastUserCommentDate) INTO tempDate;
IF (tempDate >0 && tempDate <= 24) THEN
SET 24Hrs_count =1;
ELSEIF (tempDate >24 && tempDate <= 48) THEN
SET 48Hrs_count = 1;
ELSEIF (tempDate >48) THEN
SET Gr_48Hrs_count = 1;
ELSE
SET notCommneted = 1;
END IF;
INSERT INTO resultTable(t_agentId,t_agentName,t_24HrsCount,t_48HrsCount,t_Gr48HrsCount,t_nullCount) VALUES(v_agentId,agentName,24Hrs_count,48Hrs_count,Gr_48Hrs_count,notCommneted);
ELSE
LEAVE insert_loop;
END IF;
SET complaint_count = complaint_count - 1;
END LOOP;
CLOSE cur;
SELECT t_agentId,t_agentName,COUNT(t_24HrsCount),COUNT(t_48HrsCount),COUNT(t_Gr48HrsCount),COUNT(t_nullCount) FROM resultTable GROUP BY agentId;
END$$
分隔符;
根据关于DECLARE
的文档
DECLARE
只允许在BEGIN ... END
复合语句内部,并且必须在其开头,在任何其他语句之前
在您的代码中,语句
DECLARE cur CURSOR FOR
SELECT id, user_id
FROM complaint3
WHERE user_id IN( SELECT id FROM user3 WHERE user_type = 0 );
没有遵循声明顺序规则。这就是错误。
更改部分代码如下:
BEGIN
DECLARE complaint_count INT;
DECLARE 24Hrs_count INT;
DECLARE 48Hrs_count INT;
DECLARE Gr_48Hrs_count INT;
DECLARE notCommented INT;
DECLARE agentName VARCHAR(100);
DECLARE v_agentId INT;
DECLARE v_userId INT;
DECLARE lastUserCommentDate DATETIME;
DECLARE lastInternalUserCommentDate DATETIME;
DECLARE tempDate INT;
DECLARE v_complaint_id INT;
DECLARE cur CURSOR FOR
SELECT id, user_id
FROM complaint3
WHERE user_id IN( SELECT id FROM user3 WHERE user_type = 0 );
SET 24Hrs_count =0;
SET 48Hrs_count =0;
SET Gr_48Hrs_count =0;
SET notCommented =0;
SET v_complaint_id =0;