linux 调用mysql存储过程_linux - mysql 存储过程问题调用时候报错

问 题

请帮忙看一下为什么会报错,小弟首次编写这种类型的存储过程:

delimiter $$

create procedure mydb()

begin

declare 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

begin

declare 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?????

解决方案

说几个语法问题吧.

while ( stop is not null) do中的stop应该游标dbname才对,对游标进行判断.不然对stop没有引用过

最后一个end;,前面你定义了delimiter $$`以`$$为结束符,应该改为end$$,最后不要忘了结尾改成delimiter ;

扫一扫关注IT屋

微信公众号搜索 “ IT屋 ” ，选择关注与百万开发者在一起