问题描述
请帮忙看一下为什么会报错,小弟首次编写这种类型的存储过程:delimiter $$create procedure mydb()begindeclare dbname varchar(50);declare stop int default 0;declare cur cursor for (select name from name); //name表中存放的是数据库的名称 declare CONTINUE HANDLER FOR SQLSTATE ’02000’ SET stop = null;open cur;fetch cur into dbname; //遍历数据库的名称while ( stop is not null) do
begindeclare name varchar(200);declare stop1 int default 0;declare cur1 cursor for (select prounit_name from dbname.prounit); //获取prounit表中prounit_name字段内容declare CONTINUE HANDLER FOR SQLSTATE ’02000’ SET stop1 = null;open cur1;fetch cur1 into name;while ( stop1 is not null) do update prounit set name=’abc’; fetch cur1 into name;end while;close cur1;end ;
fetch cur into dbname;end while;close cur;end ;delimiter $$
在执行存储过程error:ERROR 1146 (42S02): Table ’dbname.prounit’ doesn’t exist
why?????
问题解答
回答1：提示很明显，表不存在declare dbname varchar(50);名字覆盖了？
回答2：说几个语法问题吧.
while ( stop is not null) do中的stop应该游标dbname才对,对游标进行判断.不然对stop没有引用过
最后一个end;,前面你定义了delimiter $$`以`$$为结束符,应该改为end$$,最后不要忘了结尾改成delimiter ;
