使用存储过程插入和更新

问题描述：

我的存储过程有问题(在 MySQL 上).如果记录存在，我需要表的数据更新过程，否则插入记录.我有:

Have a problem with my stored procedure (on MySQL). I need the data update process of the table if the record exists, else insert the record. I have:

DELIMITER //

CREATE PROCEDURE saveorUpdate(in product varchar(30), price int, stock int, active varchar(5))

BEGIN

DECLARE id int;

SELECT id_pro FROM products WHERE product=product into id;

IF(id_pro=id)THEN

UPDATE products SET product=product, price=price, stock=stock, active=active 
WHERE id_pro=id;

ELSE

INSERT INTO products (product, price, stock, active) VALUES 
(product, price, stock, active);

END IF;
END

有什么想法吗?

答

由于您的数据库是 MySQL，您可以只使用 INSERT INTO ... ON DUPLICATE KEY 语法并废弃存储过程的内容.

As your database is MySQL you could just use the INSERT INTO ... ON DUPLICATE KEY syntax and scrap the stored procedure stuff.

INSERT INTO products (
  product, price, stock, active
) VALUES (
  $product, $price, $stock, $active
) ON DUPLICATE KEY UPDATE
  product=VALUES(product)
 , price=VALUES(price)
 , stock=VALUES(stock)
 , active=VALUES(active)

只是一个想法.可以更简洁、更快速地编写替代方案.

Just an idea. Could be a cleaner and faster to write alternative.

希望有帮助

使用存储过程插入和更新

相关推荐