mysql - 错误代码：1062。密钥“PRIMARY”的重复条目“2018-11-17 20:27:14”

Question

我有一个程序，我必须：

• 检查库存产品的数量，如果有足够的库存，则添加请求的数量，否则添加可用的数量；

• 从产品表中获取当前价格并将其用作单价；

• 从库存单位中扣除订购数量并调整库存单位

因此; 和

• 包含一个异常处理程序来处理重复的订单项目。您将需要决定这应该是 CONTINUE 还是 EXIT 处理程序。

这是我的程序：

DROP PROCEDURE IF EXISTS sp_AddOrderItem;
DELIMITER //

CREATE PROCEDURE sp_AddOrderItem(IN orderID int, IN productID int, IN quantity smallint, IN discount float, OUT  quantityAdded int)
BEGIN 

DECLARE o_done BOOL DEFAULT FALSE;

DECLARE addQuantity INT DEFAULT 0;

DECLARE checkStock INT DEFAULT 0;

DECLARE oldStock INT DEFAULT 0;

DECLARE orderID INT DEFAULT 0;

DECLARE productID INT DEFAULT 0;

DECLARE tableName Varchar(10);

DECLARE productCurrentPrice CURSOR FOR SELECT UnitPrice FROM Products WHERE     ProductID = productID ;

DECLARE EXIT HANDLER FOR 1062 SELECT 'Duplicate keys error encountered';

SET checkStock = (SELECT UnitsInStock FROM Products WHERE Products.ProductID = productID LIMIT 1);
SET oldStock = (SELECT Quantity FROM Order_Details WHERE OrderID = orderID AND ProductID = productID LIMIT 1);
SET orderID = (SELECT OrderId, ProductId FROM Order_Details WHERE ProductID = productID LIMIT 1);

IF checkStock >= quantity THEN

    SET addQuantity = oldStock + quantity;
    SET checkStock = oldStock - quantity;

    ELSE
        SET addQuantity = oldStock + checkStock;
        SET checkStock = 0;
        SET SQL_SAFE_UPDATES = 0;
        UPDATE IGNORE Order_Details SET Quantity = addQuantity WHERE OrderID = orderID AND ProductID = productID;
        UPDATE IGNORE Products SET UnitsInStock = checkStock WHERE ProductID = productID;

        SELECT orderID, productID, quantity, addQuantity, oldStock, checkStock;

END IF;

OPEN productCurrentPrice;
FETCH productCurrentPrice INTO orderDetailPrice;
CLOSE productCurrentPrice;

END // 
DELIMITER ;

这就是我得到的：错误代码：1062。键“PRIMARY”的重复条目“2018-11-17 20:27:14”

标签： mysql