MySQL / MariaDB疑难解答存储过程语法错误

I am trying to write the following stored procedure but I keep getting a syntax error, which I've included under the SP.

我正在尝试编写以下存储过程但我不断收到语法错误,我已将其包含在SP下。

DELIMITER $$
CREATE DEFINER=`root`@`%` PROCEDURE `test`(categoryID int, userID int)
BEGIN
    DECLARE vbUserId INT DEFAULT ( SELECT userfield.field6 FROM userfield WHERE userfield.field6 = userID );
    DECLARE m_forumId, m_numOfPosts, m_numOfThreads, m_hasChildren, m_isPrivate INT;
    DECLARE m_forumName VARCHAR(100);
    DECLARE lastRow INT DEFAULT 0;

    CREATE TEMPORARY TABLE tmp engine=memory AS (select forumid,replace(replace(title_clean,'&amp;','&'),'&quot;','') as forumName,replycount as NumOfPosts, threadcount as NumOfThreads, 0 as hasChildren, showprivate as isprivate from forum where parentid=categoryID and displayorder!=0 and options&1=1 order by displayorder);

    DECLARE cur_forums CURSOR FOR select * from tmp;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET lastRow = 1;
    OPEN cur_forums;
    iterate_forums: LOOP
        FETCH cur_forums INTO m_forumId, m_forumName, m_numOfPosts, m_numOfThreads, m_hasChildren, m_isPrivate;             
        IF lastRow = 1 THEN LEAVE iterate_forums;
        IF (m_isPrivate = 1)
            SELECT CONCAT('Private: ', m_isPrivate);
        END IF
    END LOOP iterate_forums;
    CLOSE cur_forums;
    DROP TEMPORARY TABLE IF EXISTS tmp;

END$$

Here is the error I receive when I try to import this into a db:

这是我尝试将其导入db时收到的错误:

ERROR 1064 (42000) at line 2: 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 cur_forums CURSOR FOR select * from tmp;
        DECLARE CONTINUE HANDLER FOR N' at line 10

I've read the documentation and looked over previous SO questions, and also tried commenting out certain lines or running them on their own, but still can't figure out what I'm doing wrong.

我已阅读文档并查看以前的SO问题,并尝试评论某些行或自行运行它们,但仍然无法弄清楚我做错了什么。

3 个解决方案

#1

Chuck tmp and the cursor. Instead, have simply

查克tmp和光标。相反,简单地说

select "Private: 1"
    FROM forum
    WHERE EXISTS ( SELECT *
           from forum
           where parentid=categoryID
             and displayorder!=0
             and options&1=1
             AND showprivate = 1 );

vbUserId seems to be unused; get rid of it.

vbUserId似乎未被使用;摆脱它。

#2

change the order like: 1 declare, then open

改变顺序如下:1声明,然后打开

....
    DECLARE vbUserId INT DEFAULT ( SELECT userfield.field6 FROM userfield WHERE userfield.field6 = userID );
    DECLARE m_forumId, m_numOfPosts, m_numOfThreads, m_hasChildren, m_isPrivate INT;
    DECLARE m_forumName VARCHAR(100);
    DECLARE lastRow INT DEFAULT 0;
    DECLARE  cur_forums CURSOR FOR select * from tmp;

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET lastRow = 1;

    OPEN cur_forums;

    CREATE TEMPORARY TABLE tmp engine=memory AS (select forumid,replace(replace(title_clean,'&amp;','&'),'&quot;','') as .....
...

#3

Fixed it by re-writing as so:

通过重写来修复它:

DELIMITER $$
CREATE DEFINER=`root`@`%` PROCEDURE `test`(categoryID int, userID int)
BEGIN
        DECLARE vbUserId INT DEFAULT (
               SELECT userfield.field6 FROM userfield
                   WHERE userfield.field6 = userID );
        DECLARE m_forumId, m_numOfPosts, m_numOfThreads,
                m_hasChildren, m_isPrivate INT;
        DECLARE m_forumName VARCHAR(100);
        DECLARE lastRow INT DEFAULT FALSE;

        DECLARE curForums CURSOR FOR select * from tmp;
        DECLARE CONTINUE HANDLER FOR NOT FOUND SET lastRow = TRUE;

        CREATE TEMPORARY TABLE tmp engine=memory AS (
            select forumid,
                   replace(replace(title_clean,'&amp;','&'),'&quot;','')
                             as forumName,
                   replycount as NumOfPosts,
                   threadcount as NumOfThreads,
                   0 as hasChildren,
                   showprivate as isprivate
               from forum
               where parentid=categoryID
                 and displayorder!=0
                 and options&1=1
               order by displayorder);

        OPEN curForums;
        iterateForums: LOOP
                FETCH curForums INTO m_forumId, m_forumName,
                                     m_numOfPosts, m_numOfThreads,
                                     m_hasChildren, m_isPrivate;
                IF lastRow THEN 
                    LEAVE iterateForums;
                END IF;
                IF m_isPrivate = 1 THEN 
                    SELECT (m_isPrivate);
                END IF;                 
        END LOOP;
        CLOSE curForums;

        DROP TEMPORARY TABLE IF EXISTS tmp;

END$$
DELIMITER ;

#1