时间戳

首页 > 解决方案 > 使用包含简单循环的 MySQL 游标出现奇怪的错误

mysql - 使用包含简单循环的 MySQL 游标出现奇怪的错误

问题描述

MySQL 游标出现奇怪的错误。可能是微不足道的，但我看不到发生了什么。

如果我使用“CALL cur();”运行它，这是错误 下面列出...：

错误代码：1054。“字段列表”中的未知列“完成”

这让我相信它指的是我的 Paper_Review 表。但是，Paper_Review 表仅包含以下列：

paperID、reviewerID、score、reviewSubmissionDate、reviewInvitationDate

如果我通过突出显示整个查询来运行它，我会得到一个完全不同的错误：

错误代码：1064。您的 SQL 语法有错误；检查与您的 MySQL 服务器版本相对应的手册，以在第 1 行的“END”附近使用正确的语法

这是我的代码：

DELIMITER $$
CREATE PROCEDURE cur()
BEGIN
DECLARE pScore INT;
DECLARE b BOOLEAN DEFAULT FALSE;
DECLARE aCursor CURSOR FOR SELECT pr.score FROM Paper_Review AS pr;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET b = TRUE;
OPEN aCursor;
myloop: LOOP
    FETCH aCursor INTO pScore;
    IF b THEN
        LEAVE myloop;
    END IF;
    IF pScore <> -1 THEN
        UPDATE Paper_Review AS pr
        SET pr.score = -1
        WHERE pr.reviewerID = "123" AND pr.reviewSubmissionDate < "2016-03-25";
    END IF;
END LOOP;
CLOSE aCursor;
END;

CALL cur();

创建表：

    CREATE TABLE Author(
aEmail VARCHAR(255) PRIMARY KEY,
fName VARCHAR(255),
lName VARCHAR(255),
bDate date,
city VARCHAR(255)
);

CREATE TABLE Reviewer(
rEmail VARCHAR(255) PRIMARY KEY,
phoneNumber VARCHAR(255),
lName VARCHAR(255),
fName VARCHAR(255),
city VARCHAR(255)
);

CREATE TABLE Paper(
paperID INT PRIMARY KEY,
title VARCHAR(255),
abstract VARCHAR(255),
submissionDate date
);

CREATE TABLE Author_Paper(
authorID INT PRIMARY KEY REFERENCES Author (aEmail),
paperID INT,
isContact BOOLEAN
);

ALTER TABLE Author_Paper
ADD FOREIGN KEY (paperID) REFERENCES Paper(paperID);

ALTER TABLE Author_Paper
MODIFY COLUMN authorID VARCHAR(255);

ALTER TABLE Author_Paper
ADD FOREIGN KEY (authorID) REFERENCES Author(aEmail);

CREATE TABLE Paper_Review(
paperID INT,
reviewerID VARCHAR(255),
score INT,
reviewSubmissionDate date,
reviewInvitationDate date
);

ALTER TABLE Paper_reviewpaper_review_ibfk_1
ADD FOREIGN KEY (paperID) REFERENCES Paper(paperID),
ADD FOREIGN KEY (reviewerID) REFERENCES Reviewer(rEmail);

INSERT INTO Author (aEmail, fName, lName, bDate, city) VALUES("abc", "s", "b", '1981-02-1', "City");
INSERT INTO Author (aEmail, fName, lName, bDate, city) VALUES("ghi", "r", "s", '1999-01-05', "City");
INSERT INTO Reviewer (rEmail, phoneNumber, lName,fName, city) VALUES ("123", "403-333-3333", "k", "b", "City");
INSERT INTO Paper(paperID, title, submissionDate) VALUES (5, "A Paper 5", "2016-02-2");
INSERT INTO Author_Paper(authorID, paperID, isContact) VALUES ("abc", "5", true);
INSERT INTO Paper_Review(paperID, reviewerID, score, reviewSubmissionDate, reviewInvitationDate) VALUES (5, "123", 6, "2016-02-20", "2016-02-15");

任何事情都会有所帮助！

谢谢你。

标签： mysqlmysql-workbenchcursors

解决方案

DELIMITER $$
CREATE PROCEDURE cur()
BEGIN
DECLARE pScore INT;
DECLARE b BOOLEAN DEFAULT FALSE;
DECLARE aCursor CURSOR FOR SELECT pr.score FROM Paper_Review AS pr;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET b = TRUE;
OPEN aCursor;
myloop: LOOP
    FETCH aCursor INTO pScore;
    IF b THEN
        LEAVE myloop;
    END IF;
    IF pScore <> -1 THEN
        UPDATE Paper_Review AS pr
        SET pr.score = -1
        WHERE pr.reviewerID = "123" AND pr.reviewSubmissionDate < "2016-03-25";
    END IF;
END LOOP;
CLOSE aCursor;
END;

-- finalize SP definition
$$
-- restore std. delimiter
DELIMITER ;

CALL cur();

小提琴

推荐阅读