如何使用存储过程进行更新、删除和选择? [英] how to do an update, delete and select using Stored Procedure?

问题描述

当我尝试使用更新、删除或选择时，代码会执行所有行中的命令.

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)

更新在 16,17 和 18 执行，而不是仅在 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 ;

这篇关于如何使用存储过程进行更新、删除和选择?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！