代码如下,运行时 显示我变量b_column_value未定义
ERROR 1327 : Undeclared variable: b_column_value t_id w_id bname 是从外面传入的
begin
/*基础变量声明*/
DECLARE u_id int;
DECLARE r_id int;
DECLARE t_year int;DECLARE b_name varchar(255);
DECLARE b_baifen decimal(10,2);
DECLARE b_quan decimal(10,6);
DECLARE b_value int;
DECLARE b_min decimal(10,4);
DECLARE b_max decimal(10,4);
DECLARE b_column_name varchar(255);
DECLARE b_table_name varchar(255);
DECLARE b_column_value decimal(10,4);DECLARE result decimal(10,6) default 0.000000;
DECLARE done int(10);
DECLARE cursor_name CURSOR FOR select bin_baifen,bin_quan,bin_value,min_value,max_value,bin_column_name,bin_table_name from jbfx_yinsu where bin_name=bname;
DECLARE continue handler for not found set done=1;
open cursor_name;
loop_label:LOOP
fetch cursor_name into b_baifen,b_quan,b_value,b_min,b_max,b_column_name,b_table_name;
if done =1 then
leave loop_label;
else
set @str = concat('select ',b_column_name,' into b_column_value from ',b_table_name,' where wenjuan_id =',w_id,' and tijian_id = ',t_id);
prepare stmt1 from @str;
execute stmt1;
deallocate prepare stmt1;
if b_value != null then
if b_value = b_column_value then
set result = result+b_baifen*b_quan;
end if;
elseif (b_min != null) AND (b_max != null) then
if (b_column_value>= b_min) AND (b_column_value < b_max) then
set result = result+b_baifen*b_quan;
end if;
end if;
end if;
END LOOP;
close cursor_name;
select result;
end
ERROR 1327 : Undeclared variable: b_column_value t_id w_id bname 是从外面传入的
begin
/*基础变量声明*/
DECLARE u_id int;
DECLARE r_id int;
DECLARE t_year int;DECLARE b_name varchar(255);
DECLARE b_baifen decimal(10,2);
DECLARE b_quan decimal(10,6);
DECLARE b_value int;
DECLARE b_min decimal(10,4);
DECLARE b_max decimal(10,4);
DECLARE b_column_name varchar(255);
DECLARE b_table_name varchar(255);
DECLARE b_column_value decimal(10,4);DECLARE result decimal(10,6) default 0.000000;
DECLARE done int(10);
DECLARE cursor_name CURSOR FOR select bin_baifen,bin_quan,bin_value,min_value,max_value,bin_column_name,bin_table_name from jbfx_yinsu where bin_name=bname;
DECLARE continue handler for not found set done=1;
open cursor_name;
loop_label:LOOP
fetch cursor_name into b_baifen,b_quan,b_value,b_min,b_max,b_column_name,b_table_name;
if done =1 then
leave loop_label;
else
set @str = concat('select ',b_column_name,' into b_column_value from ',b_table_name,' where wenjuan_id =',w_id,' and tijian_id = ',t_id);
prepare stmt1 from @str;
execute stmt1;
deallocate prepare stmt1;
if b_value != null then
if b_value = b_column_value then
set result = result+b_baifen*b_quan;
end if;
elseif (b_min != null) AND (b_max != null) then
if (b_column_value>= b_min) AND (b_column_value < b_max) then
set result = result+b_baifen*b_quan;
end if;
end if;
end if;
END LOOP;
close cursor_name;
select result;
end
解决方案 »
- 发我一个我自己写的基于Linux和MySQL的邮件服务器
- 求一个MySql存储过程,小弟以前没写过存储过程
- 关于PostgreSQL性能优化的问题
- 如何利用mysql select产生新表?
- 我的MYSQL中文乱码的解决办法
- 在mysql下,如何将datetime类型转换为long int?
- mysql的复制问题
- 求助如何访问内网的MySQL Server?
- msSQL,mysql,sql server这三个东西是指一个东西吗?
- mysql中表a和表b合并为表c,再将表c恢复至表a,请问如何处理较好
- 求助 本机怎样从sqlserver把数据库导入mysql 程序或者sql都行!
- 如何将字段的数据转换为查找语言
....因为你的PREPARE执行的SQL语句并不是在存储过程的变量空间内。