存储过程+游标+While的有关问题

存储过程+游标+While的问题
环境：Windows Server 2003 Enterprise Edition
PHPnow 1.4.4（内置phpMyAdmin - 2.11.4和MySQL-5.0.51a）
我还不怎么熟悉MySql，我搜了论坛的相关帖子，问题依旧。望各位大神不吝赐教 ！

以下代码在phpMyAdmin中运行出错。

   

SQL code

create procedure wf_getjournalname(in u_name varchar(32),out v_re varchar(255))BEGINdeclare cra cursor for select setting_value from journal_settings;OPEN cra;FETCH NEXT from cra into @v_re;WHILE @@FETCH_STATUS = 0BEGINFETCH NEXT from cra into @v_re;END;close cra;deallocate cra;    end    

 



错误
SQL 查询:  

CREATE PROCEDURE wf_getjournalname( IN u_name varchar( 32 ) , out v_re varchar( 255 ) ) BEGIN declare cra cursor FOR SELECT setting_value
FROM journal_settings;

OPEN cra;

FETCH NEXT FROM cra INTO @v_re ;

WHILE@ @FETCH_STATUS =0 BEGIN FETCH NEXT FROM cra INTO @v_re ;

END ;

close cra;

deallocate cra;

END 

MySQL 返回： 

#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 '@v_re;
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT from cra into @v_re;
END;
' at line 5 




[解决办法]
delimiter $$
create procedure wf_getjournalname(in u_name varchar(32),out v_re varchar(255))
BEGIN
declare cra cursor for select setting_value from journal_settings;
OPEN cra;
FETCH NEXT from cra into @v_re;
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT from cra into @v_re;
END;
close cra;
deallocate cra;
end $$
delimiter ;
[解决办法]
在你的phpMyAdmin中设置一下 delimiter, 否则phpMyAdmin看到；分号就提交了你的语句。
[解决办法]
在你的phpMyAdmin中设置一下 delimiter, 否则phpMyAdmin看到；分号就提交了你的语句。

在你的 phpMyAdmin 界面上，在SQL查询框下有一个 文本框DELIMITER来设置。你可以设置为 //
然后


create procedure wf_getjournalname(in u_name varchar(32),out v_re varchar(255))
BEGIN
declare cra cursor for select setting_value from journal_settings;
OPEN cra;
FETCH NEXT from cra into @v_re;
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT from cra into @v_re;
END;
close cra;
deallocate cra;
end
//

查看更多 下一篇