如何编写存储过程以在多个表中插入值

How do I write a stored procedure to add a person record with multiple addresses?

如何编写存储过程以添加具有多个地址的人员记录?

It is easy if the person has only one address but I'm not sure how to write a stored procedure to add a person with multiple addresses. Here is the stored procedure to add a person with one address:

如果此人只有一个地址,但我不确定如何编写存储过程来添加具有多个地址的人,这很容易。以下是添加具有一个地址的人员的存储过程:

 DELIMITER $$

 CREATE PROCEDURE `log`.`spAddPerson` (
 IN personID INT,
 IN personName VARCHAR(100),
 IN addressLine1 VARCHAR(45),
 IN addressLine2 VARCHAR(45),
 IN myCity VARCHAR(45),
 IN myState VARCHAR(45),
 IN myCountry VARCHAR(45)

)

 BEGIN
 DECLARE EXIT HANDLER FOR SQLEXCEPTION 
 BEGIN
      ROLLBACK;
 END;

 START TRANSACTION;

 INSERT INTO person VALUES(personID,personName);
 -- addressid is automatically generated
 INSERT INTO address(Line1, Line2,City,State,Country) VALUES
 (addressLine1, addressLine2, myCity,myState, myCountry);

 INSERT INTO personAddress(personID, last_insert_id());

 COMMIT;

 END

The above code works fine. However, I do not know how to handle a person with multiple addresses without writing a separate stored procedure. Is there a simple way to do this?

上面的代码工作正常。但是,我不知道如何处理具有多个地址的人而不编写单独的存储过程。有一个简单的方法吗?

2 个解决方案

#1

You cannot pass a variable number of variables to a procedure, nor a non-scalar type.

您不能将可变数量的变量传递给过程,也不能传递非标量类型。

A possible trick would be building a temporary table with the addresses before calling this procedure. Either the temporary table is pre-determined, or pass its name as a VARCHAR parameter(and use it to build dynamic SQL statements). Eg:

在调用此过程之前,可能的技巧是使用地址构建临时表。临时表是预先确定的,或者将其名称作为VARCHAR参数传递(并使用它来构建动态SQL语句)。例如:

CREATE PROCEDURE spAddPerson (tmp_table VARCHAR(10), ...)
BEGIN
    ...
    PREPARE s AS CONCAT(
        'INSERT INTO address (line1, ...) VALUES SELECT * FROM ', tmp_table
    );
    EXECUTE s;
    ...
END


-- use it like this
CREATE TEMPORARY TABLE tmp_addresses (line1 VARCHAR(255), ...);
INSERT INTO tmp_addresses VALUES ('1 Cherry Lane'), ... ;
CALL spAddPerson ('tmp_addresses', ...);

However, I would rather split the action in two parts. Do you really want to prevent the creation of the person altogether if its address creation fails? And even then, wouldn't you want to advise your user why the transaction failed (user creation or address creation)?

但是,我宁愿将行动分为两部分。如果地址创建失败,你真的想完全阻止这个人的创造吗?即便如此,您是否想要告知您的用户交易失败的原因(用户创建或地址创建)?

I would rather treat these two exceptions separately at the application level:

我宁愿在应用程序级别单独处理这两个例外:


    issue a "START TRANSATION"
    try to insert a person (call stored proc 1)
    if it failed, rollback and notify user
    for each address
        try to insert an address (call stored proc 2)
        if it failed, rollback and notify user
    issue a "COMMIT"

#2

>     DECLARE @LAST_INSERT_ID INT
>     DECLARE @EXECUTION_OK char(1)
>     SET @EXECUTION_OK = 1
>     
>     insert into base_table(imgPath,store,apparelType) values (imgPath,store,apparelType)
>     
>         SELECT @LAST_INSERT_ID = SCOPE_IDENTITY()
>         
>         insert into data_table(cvID,color) values (@LAST_INSERT_ID, color)
>         GO
>         
>         If exists( Select cvID from data_table where cvID= @LAST_INSERT_ID)
>         Begin
>         @EXECUTION_OK = 0
>         End

#1