如何使用存储过程进行更新、删除和选择?
问题描述:
当我尝试使用更新、删除或选择时,代码会执行所有行中的命令.
When I try to use an update, delete or select, the code executes the commands in all rows.
DELIMITER $$
CREATE PROCEDURE update_cinema
(IN NOME_FANTASIA varchar(100), IN LOGRADOURO varchar(100),IN CIDADE varchar(100), IN ESTADO varchar(100),IN LOTACAO int, in ID_CINEMA int)
BEGIN
UPDATE tb_cinema
SET NOME_FANTASIA = nome_fantasia , LOGRADOURO=LOGRADOURO, CIDADE= cidade, ESTADO= estado, LOTACAO= lotacao
WHERE id_cinema = id_cinema;
END $$
DELIMITER ;
CALL UPDATE_CINEMA ('Muda', 'Por favor', 'da certo', 'MS', 400, 16)
DELIMITER $$
CREATE PROCEDURE SELECT_cinema
(IN NOME_FANTASIA varchar(100))
BEGIN
SELECT * from tb_cinema
where nome_fantasia = nome_fantasia;
END $$
DELIMITER ;
DELIMITER $$
CREATE PROCEDURE DELETE_CINEMA
(IN NOME_FANTASIA varchar(100))
BEGIN
DELETE from tb_cinema
where nome_fantasia = nome_fantasia;
END $$
DELIMITER ;
call DELETE_cinema ('test_Select')
答
您需要给出与实际列名不同的参数名.否则,名称是指参数还是列名是不明确的.结果,where
条件变成空操作,就像 set
赋值一样.
You need give the parameter names that are different from the actual column names. Otherwise, it is ambiguous whether the name refer to the parameter or the column name. As a consequence, the where
conditions become no-ops, just like the set
assignments.
这是更新过程的示例.我在所有参数名称前加上 p_
以消除歧义:
Here is an example for the update procedure. I prefixed all parameter names with p_
to remove the ambiguity:
create procedure update_cinema(
in p_nome_fantasia varchar(100),
in p_logradouro varchar(100),
in p_cidade varchar(100),
in p_estado varchar(100),
in p_lotacao int,
in p_id_cinema int
)
begin
update tb_cinema
set
nome_fantasia = p_nome_fantasia,
logradouro = p_logradouro,
cidade = p_cidade,
estado = p_estado,
lotacao = p_lotacao
where id_cinema = p_id_cinema;
end $$
delimiter ;