下面存储过程原本是想根据传入的status值来更新对应列的name,代码如下:
drop table if exists test_tbl;
create table test_tbl (
name varchar(20),
status int(2)
);
insert into test_tbl values
('abc', 1),
('edf', 2),
('xyz', 3);
drop procedure IF EXISTS pro_test_1;
delimiter //
create procedure pro_test_1(in status int(2))
begin
update test_tbl set name = concat(name, '_new') where test_tbl.status = status;
end
//
delimiter ;
call pro_test_1(1);
select * from test_tbl;
调用上述存储过程后发现所有记录的name都被更新了,存储过程就只有一个update语句,而且执行了,问题出在where条件上,原意是where的第一个status为表的列名,第二个是参数,此处被存储过程都理解为参数,所以where条件永远是true。因此一定要注意列名和参数名相同问题,否则在执行delete或update时会酿成大祸。解决方法有3中,一是修改参数的名字,二是在字段前加上表名,第三种是update使用预处理语句的方式。代码如下:
第二种方法代码:
update test_tbl set name = concat(name, '_new') where status = status;
修改为:
update test_tbl set name = concat(name, '_new') where test_tbl.status = status;
第三种方法代码:
set @t = status;
PREPARE STMT FROM "
update test_tbl set name = concat(name, '_new') where status = ?
";
EXECUTE STMT USING @t;
另外定义游标时候可能也会出现这样的问题,解决方法跟上面类似,注意游标在申明前不能使用set,示例代码:
drop table if exists test_tbl;
create table test_tbl (
name varchar(20),
status int(2)
);
insert into test_tbl values
('abc', 1),
('edf', 2),
('xyz', 3);
drop procedure IF EXISTS pro_test_2;
delimiter //
create procedure pro_test_2(in status int(2))
begin
DECLARE done INT DEFAULT 0;
DECLARE na varchar(50) DEFAULT NULL;
DECLARE cur CURSOR FOR
select name from test_tbl where test_tbl.status = status;
-- 或
-- select name from test_tbl t where t.status = status;
-- 再或, 注意后面需要为 @s 赋值
-- select name from test_tbl where test_tbl.status = @s;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
-- set @s = status;
open cur;
REPEAT
FETCH cur INTO na;
if not done then
select na;
end if;
UNTIL done END REPEAT;
CLOSE cur;
end
//
delimiter ;
call pro_test_2(1);
(责任编辑:IT) |