以下是我出错的代码:
delimiter $$
drop procedure if exists show_all;
create procedure show_all()
begin
declare bname text;
declare eno text;
declare done int default 0;
declare bname_cur cursor for
select temp.bname as info
from (select distinct bank.bname from bank,loan where loan.bno=bank.bno) as temp;
declare CONTINUE HANDLER FOR NOT FOUND SET done=1;
open bname_cur;
rowloop:Loop
if done=1 then leave rowloop;
end if;
fetch bname_cur into bname;
select legalentity.ename, ldate, lamount, lterm
from legalentity,loan
where legalentity.eno=loan.eno and loan.bname=bname;
end loop rowloop;
close bname_cur;
end $$
delimiter ;
运行的时候,MySQL报错:Error Code: 2014 Commands out of sync; you can’t run this command now
修改后的代码
delimiter $$
drop procedure if exists show_all $$ /*替换成分隔符*/
create procedure show_all()
begin
declare bname text;
declare eno text;
declare done int default 0;
declare bname_cur cursor for
select temp.bname as info
from (select distinct bank.bname from bank,loan where loan.bno=bank.bno) as temp;
declare CONTINUE HANDLER FOR NOT FOUND SET done=1;
open bname_cur;
rowloop:Loop
if done=1 then leave rowloop;
end if;
fetch bname_cur into bname;
select legalentity.ename, ldate, lamount, lterm
from legalentity,loan
where legalentity.eno=loan.eno and loan.bname=bname;
end loop rowloop;
close bname_cur;
end $$
delimiter ;
解决办法:根据Stack Overflow的建议,将drop procedure那一行结尾处加上分隔符(此处为$$)即可。