mysql存储过程的坑(关于declare与具体实现顺序)

先上代码

BEGINDECLARE hotelId BIGINT DEFAULT 0;DECLARE done int DEFAULT FALSE;DECLARE tbtypeId BIGINT DEFAULT 0;SELECT id into tbtypeId from dbbanquet.tbtype where fname = '早餐'; /* 声明游标 */declare myCursor cursor for SELECT hotel_id from kzsteb.hotel where hotel_id not in (SELECT distinct fhid from dbbanquet.tbtype);/* 当游标到达尾部时，mysql自动设置done=1 */ declare continue handler for not found set done=true;open myCursor;read_loop:LOOPfetch myCursor into hotelId;if done = true then leave read_loop;end if;insert into dbbanquet.tbbanquet(fname,funit,funitprice,ftype,findex,fmanagement,fhid,fcu,fcid,fctime,fmu,fmid,fmtime)VALUES('包价早餐','*',0,tbtypeId,1,0,hotelId,'',1,NOW(),'',0,NOW());end loop;close myCursor;END

错误原因：
存储过程的所有关于数据库增删改查的操作必须在所有 declarre声明完所有 变量 ,包括游标等 之后再执行,
否则该存储过程将无法运行
正确方法将SELECT id into tbtypeId from dbbanquet.tbtype where fname = '早餐';
该句放到 declare continue handler for not found set done=atrue;之后