phpMyAdmin: 3.5.7
MySQL: 5.5.29我希望用户能够从数据库中的帖子中编译几个不同的列表。下面的函数应该用于将post添加到这样的列表中。它检查用户提供的列表id是否与给定的用户ID匹配,然后检查列表是否已满,然后将post添加到list。
DELIMITER //
CREATE FUNCTION addToFav(inputKid INT, inputUid INT, listID INT, listLimit INT)
RETURNS VARCHAR(300)
BEGIN
DECLARE resultMsg VARCHAR(300);
DECLARE listExists INT; SET listExists = (SELECT COUNT(*) FROM fav_lists WHERE fav_list_id=listID AND uid=inputUid AND active=1);
IF 1 > listExists THEN SET resultMsg = 'LIST NON-EXISTANT';
ELSE
DECLARE listSize INT;
SET listSize = (SELECT COUNT(*) FROM fav_links WHERE fav_list_id=listID AND active=1);
IF listSize = listLimit THEN SET resultMsg = 'LIST FULL';
ELSE IF listSize > listLimit THEN SET resultMsg ='dbErr: LIST OVER LIMIT';
ELSE
REPLACE INTO fav_links (kid, fav_list_id, active) VALUES (inputKid, listID, 1);
SET resultMsg ='SUCCESS';
END IF;
END IF;
RETURN resultMsg;
END //
DELIMITER ;我将这段代码输入phpMyAdmin的SQL控制台,它将返回
#1064 - 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 listSize INT; SET listSize = (SELECT COUNT(*) FROM fav_links W' at line 10现在,如果我将“错误”一行移动起来,代码如下所示:
DELIMITER //
CREATE FUNCTION addToFav(inputKid INT, inputUid INT, listID INT, listLimit INT)
RETURNS VARCHAR(300)
BEGIN
DECLARE listSize INT; SET listSize = (SELECT COUNT(*) FROM fav_links WHERE fav_list_id=listID AND active=1);
DECLARE resultMsg VARCHAR(300);
DECLARE listExists INT; SET listExists = (SELECT COUNT(*) FROM fav_lists WHERE fav_list_id=listID AND uid=inputUid AND active=1);
[...]此版本指向相同的错误,但在另一行:
#1064 - 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 resultMsg VARCHAR(300); DECLARE listExists INT; SET listExists = (SELEC' at line 6这使我相信,这个问题可能是由分隔符的错误使用引起的,但这就是我现在所掌握的全部。PhpMyAdmin在insertion字段之后有一个分隔符字段,我尝试将该字段保留为空,使用;和//填充,所有这些都不起作用。
是什么引起了这个问题,我该如何解决呢?
发布于 2014-03-06 09:55:10
您应该将变量声明保留在顶部。此外,我还自由地重写了一些代码:
DELIMITER //
CREATE FUNCTION addToFav(inputKid INT, inputUid INT, listID INT, listLimit INT)
RETURNS VARCHAR(300)
BEGIN
DECLARE resultMsg VARCHAR(300);
DECLARE listSize INT;
IF (NOT EXISTS (SELECT 1 FROM fav_lists WHERE fav_list_id=listID AND uid=inputUid AND active=1)) THEN
SET resultMsg = 'LIST NON-EXISTANT';
ELSE
SELECT COUNT(*) INTO listSize FROM fav_links WHERE fav_list_id=listID AND active=1;
IF (listSize = listLimit) THEN
SET resultMsg = 'LIST FULL';
ELSEIF (listSize > listLimit) THEN
SET resultMsg ='dbErr: LIST OVER LIMIT';
ELSE
BEGIN
REPLACE INTO fav_links (kid, fav_list_id, active) VALUES (inputKid, listID, 1);
SET resultMsg ='SUCCESS';
END;
END IF;
END IF;
RETURN resultMsg;
END //
DELIMITER ;如果您只想知道是否存在某种东西,请不要使用COUNT(*)。
发布于 2014-03-06 09:54:22
在MySQL中尝试下面的代码,同时使用ELSE Code there doesn't given space betweenandIF`。同时在顶部声明变量
DELIMITER //
CREATE FUNCTION addToFav(inputKid INT, inputUid INT, listID INT, listLimit INT) RETURNS VARCHAR(300)
BEGIN
DECLARE resultMsg VARCHAR(300);
DECLARE listExists INT;
DECLARE listSize INT;
SET listExists = (SELECT COUNT(*) FROM fav_lists WHERE fav_list_id=listID AND uid=inputUid AND active=1);
IF (1 > listExists) THEN
SET resultMsg = 'LIST NON-EXISTANT';
ELSE
SET listSize = (SELECT COUNT(*) FROM fav_links WHERE fav_list_id=listID AND active=1);
IF listSize = listLimit THEN
SET resultMsg = 'LIST FULL';
ELSEIF listSize > listLimit THEN
SET resultMsg ='dbErr: LIST OVER LIMIT';
ELSE
REPLACE INTO fav_links (kid, fav_list_id, active) VALUES (inputKid, listID, 1);
SET resultMsg ='SUCCESS';
END IF;
END IF;
RETURN resultMsg;
END //
DELIMITER ;https://stackoverflow.com/questions/22220601
复制相似问题