使用存储过程插入和更新 [英] insert and update with stored procedure

问题描述

我的存储过程有问题(在 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.

希望有帮助

这篇关于使用存储过程插入和更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！