Untitled
unknown
plain_text
a year ago
1.1 kB
1
Indexable
Never
CREATE DEFINER=`root`@`localhost` PROCEDURE `update_order_customer`( IN p_OrderCustomerId INT, IN p_StaffID VARCHAR(10), IN p_StaffIdDeliver VARCHAR(10), IN p_Status VARCHAR(50) ) BEGIN DECLARE v_Staff varchar(10); DECLARE v_StaffDeliver varchar(10); START TRANSACTION; SELECT StaffId INTO v_Staff FROM staff WHERE StaffId = p_StaffID; SELECT StaffId INTO v_StaffDeliver FROM staff WHERE StaffId = p_StaffIdDeliver; IF (v_Staff is not null && v_StaffDeliver is not null) THEN UPDATE customerorder SET StaffId = p_StaffID, StaffIdDeliver = p_StaffIdDeliver, Status = p_Status Where customerorder.CustomerOrderId = p_OrderCustomerId; IF (p_Status = 'Cancel') THEN UPDATE Toy T INNER JOIN CustomerOrderDetail COD ON COD.SKU = T.SKU AND COD.CustomerOrderId = p_OrderCustomerId SET T.InventoryNumber = T.InventoryNumber + COD.Quantity; END IF; COMMIT; ELSE ROLLBACK; END IF; SELECT v_Staff is not null as StaffValid, v_StaffDeliver is not null as StaffDeliverValid; END