mysql - 在数据库中添加过程

Question

我正在尝试创建一个过程，使用 mySQL 同时将数据添加到两个表中，但我没有太多在数据库中使用 if/else 语句的经验，所以有人可以告诉我如何修复我的问题吗？代码

这里是属性:

Customer information: ID,Name, Address, Telephone.

Staff information: ID, Name, Phone

Camera/Len Type: ID,Name

Camera: ID, typeID, Model, Status, Color, Hiring Price (per day), Paying Price (in case of loss).

Len: ID, TypeID, Model, Status, Hiring Price (per day), Paying Price (in case of loss).

Order orderID, customerID, employeeID, hireDate, returnDate

OrderDetail: orderID, cameraID, lenID, cameraQuantity, lenQuantity, Status(paid/not paid), pStatus(Lost/Ok)

这是程序代码:

 DELIMITER $$
 Create Procedure addOrder(IN cust_id int, IN camera_id int, IN len_id int, 
 IN emp_id int, IN c_quantity int,IN l_quantity int,
 IN status char(30), IN p_Status char(30), IN hire_date date, IN return_date 
 date)
 begin
 Declare order_id int;
 Declare hireTime int;
 Declare totalC int;
 Declare totalL int;
 Declare totalP int;

 Select order_id = max(orderID) from orders;
 if orderID is null then set order_id = 1;

 Set hireTime = DATEDILL(hire_date, return_date);
 if hire_date == return_date then set hireTime = 1;

 if p_Status = "Lost" then set
    select totalC = hireTime*c_quantity*hPrice + pPrice from Camera as C
    inner join orderDetail as OD on C.camID = OD.camera_id inner join Orders 
    as O on O.orderID = OD.order_id;

    select totalL = hireTime*l_quantity*hPrice + pPrice from Lens as L
    inner join orderDetail as OD on L.lenID = OD.len_id inner join Orders as 
    O on O.orderID = OD.order_id;

    Set totalP = totalL + totalC;
else set
    select totalC = hireTime*c_quantity*hPrice + pPrice from Camera as C
    inner join orderDetail as OD on C.camID = OD.camera_id inner join Orders 
    as O on O.orderID = OD.order_id;

    select totalL = hireTime*l_quantity*hPrice + pPrice from Lens as L
    inner join orderDetail as OD on L.lenID = OD.len_id inner join Orders as 
    O on O.orderID = OD.order_id;

    Set totalP = totalL + totalC;
end if;

insert into Orders(custID, empID, hireDate, returnDate) 
    values (cust_id, emp_id,hire_date, return_date);

insert into orderDetail(orderID, cameraID, lenID, totalPrice, camQuantity, 
lenQuantity, status, pStatus) values (order_id,camera_id, len_id, totalP, 
c_quantity, l_quantity, status, p_Status);
end $$
DELIMITER 

Answer 1

我认为您需要使用 View 表来添加使用存储过程之后的所有表。我给你举了一个例子，我想你能明白。

CREATE PROCEDURE [dbo].[sp_CMIDByID] @UID INT, @TID INT
AS
BEGIN
DECLARE @CMID INT

IF(@TID IS NULL AND @UID IS NOT NULL)
BEGIN
    SELECT @TID = TID, @CMID = CMID FROM tbl_U WHERE UID = @UID     
END


IF (@CMID IS NULL AND @TID IS NOT NULL)
BEGIN
    SELECT @CMID = CMID FROM tbl_T WHERE TID = @TID
END

SELECT @CMID
END