存储过程+游标+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)) 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
错误
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
//
