MySql存储过程循环游标-语法错误 [英] MySql Stored Procedure Loop Cursor - Syntax error

问题描述

语法错误在哪里?

DECLARE irid INT DEFAULT 0;
DECLARE tmp_joinid INT DEFAULT 0;
DECLARE loopjoins_eof INT DEFAULT FALSE;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET loopjoins_eof = TRUE;
START TRANSACTION;

    SET irid = (SELECT id FROM `tables` WHERE `adapter_id`=_aid AND `view_id`=_vid AND `name`=_tname);
    IF irid IS NOT NULL THEN

        DECLARE cur0 CURSOR FOR SELECT `joins`.`id` FROM `joins` WHERE `table_left_id`=irid OR `table_right_id`=irid;
        OPEN cur0;
            loopjoins: LOOP
                FETCH cur0 INTO tmp_joinid;
                IF loopjoins_eof THEN
                    LEAVE loopjoins;
                END IF;
                -- Lösche Join-Columns
                DELETE FROM `join_columns` WHERE `join_id`=tmp_joinid;
            END LOOP loopjoins;
        CLOSE cur0;

    END IF;
COMMIT;
SELECT irid;

1064-您的SQL语法有错误；在第12行

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 cur0 CURSOR FOR SELECT joins.id FROM joins WHERE table_left_id=i' at line 12

谢谢

推荐答案

来自

游标声明必须出现在处理程序声明之前之前以及变量和条件声明之后.

Cursor declarations must appear before handler declarations and after variable and condition declarations.

这篇关于MySql存储过程循环游标-语法错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！