声明光标的语法错误。

I dont't understand why im getting syntax error on my sp code below. Can anyone help me figure this out?

我不明白为什么我的sp代码会出现语法错误。有人能帮我解决这个问题吗?

SQL Error (1064):

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 CUR1 CURSOR FOR SELECT pc.prospectus_courses_id FROM prereq_cou' at line 8

在SQL语法中有一个错误;请检查与您的MySQL服务器版本对应的手册，以便正确的语法使用near 'DECLARE CUR1光标for SELECT pc。在第8行预注册的勘探者。

DELIMITER $$
DROP PROCEDURE IF EXISTS get_prereqs3$$
CREATE PROCEDURE get_prereqs3(IN prosp_courses_id SMALLINT(5))
BEGIN
    DECLARE done  int DEFAULT FALSE;
    DECLARE required SMALLINT(5) default 0; 
    DECLARE to_search SMALLINT(5) default 0; 
    DROP TABLE IF EXISTS tmp_list;
    CREATE TABLE tmp_list(courses_id SMALLINT(5), courses_id_req SMALLINT(5)) ENGINE = MEMORY;
    DECLARE CUR1 CURSOR FOR SELECT pc.prospectus_courses_id 
            FROM prereq_courses     pc          
            JOIN prerequisites      pr on (pr.id = pc.prerequisites_id)
            JOIN prospectus_courses ps on (ps.id = pr.prospectus_courses_id)
            WHERE ps.id = to_search 
    MAIN_LOOP: LOOP 
        DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
        OPEN cur1;
        FETCH cur1 INTO required;

        IF done THEN
            CLOSE cur1;
            LEAVE main_loop;
        ELSE
            insert into tmp_list values (to_search, required);
            set to_search = required;
            iterate main_loop;
        END IF;
    END LOOP;
    select  c.course_code 
        from tmp_list           t
        join prospectus_courses pc on pc.id = t.courses_id_req
        join courses            c  on c.id  = pc.courses_id ;
    drop table tmp_list;
END$$
DELIMITER ;

2 个解决方案

#1

Declarations have to be right after a BEGIN block. In your case just move the DECLARE cur1 CURSOR and DECLARE CONTINUE HANDLER.. two lines up.

声明必须在开始块之后才正确。在您的情况中，只需移动声明cur1光标并声明CONTINUE HANDLER。两条线。

Sometimes you want to declare a variable or cursor later in the code, for example only, if a condition is met.

有时，您需要在代码中稍后声明一个变量或游标，例如，如果满足条件的话。

In this case you can wrap the block with a nested BEGIN .. END again.

在这种情况下，您可以用嵌套的开始来包装块。结束了。

http://dev.mysql.com/doc/refman/5.5/en/begin-end.html and http://dev.mysql.com/doc/refman/5.5/en/declare.html

http://dev.mysql.com/doc/refman/5.5/en/begin-end.html和http://dev.mysql.com/doc/refman/5.5/en/declare.html

DECLARE is permitted only inside a BEGIN ... END compound statement and must be at its start, before any other statements.

声明只允许在开始时…结束复合语句，必须在它开始之前，在任何其他语句之前。

Also you are declaring CUR1 but using cur1.

同时你也宣布CUR1，但使用CUR1。

#2

Is there no need semicolon?

不需要分号吗?

WHERE ps.id = to_search;
                       ^___________

#1