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

解决方案

说几个语法问题吧.

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

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

这篇关于linux - mysql 存储过程问题调用时候报错的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！