如何使用存储过程中的insert语句生成的值？

I have a process which requires that I insert new records to multiple tables and I thought I'd try using a stored procedure as I am developing in PHP 5.3.5 and using the MySql Server 5.5.8.

我有一个进程,要求我将新记录插入到多个表中,我想我会尝试使用存储过程,因为我在PHP 5.3.5中开发并使用MySql Server 5.5.8。

Created a simple Insert procedure but I wanted to obtain the value of the recently added id so that it could be used for a subsequent insert.

创建了一个简单的Insert过程但我想获取最近添加的id的值,以便它可以用于后续插入。

The id values of the table are set to auto_increment, and are bigint(20). The insert statement works, but when I try to use the DECLARE statement I obtain the following error:

表的id值设置为auto_increment,并且是bigint(20)。 insert语句有效,但是当我尝试使用DECLARE语句时,我得到以下错误:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your >MySQL server version for the right syntax to use near 'DECLARE @i BIGINT(20); END' at line >8

#1064 - 您的SQL语法有错误;检查与您的> MySQL服务器版本对应的手册,以便在'DECLARE @i BIGINT(20)附近使用正确的语法;结束'在线> 8

THe stored procedure is as follows:

存储过程如下:

CREATE PROCEDURE test(
   IN a VARCHAR(255),
   IN b VARCHAR(255),
   IN c BIGINT(20)
)
BEGIN
   INSERT INTO tableA (datetime_created) VALUE (NOW());
   DECLARE @i BIGINT(20);   // or BIGINT(20);
   SET @i = SELECT id FROM tableA ORDER BY DESC LIMIT 1;
   INSERT INTO tableB(attr1, attr2, attr3, attr4)  VALUES (a, b, c, @i);
END

I have removed all statements, and when I build what I would think is a simple stored procedure, the error appears as soon as the second line is added. Any help would be greatly appreciated for I have been searching and attempting to resolve this but I have not found a solution as of yet.

我删除了所有语句,当我构建我认为是一个简单的存储过程时,只要添加第二行就会出现错误。任何帮助将非常感谢因为我一直在寻找并试图解决这个问题,但我还没有找到解决方案。

2 个解决方案

#1

There are several problems:

有几个问题:

variable declarations should come first

变量声明应该首先出现

using DECLARE you meant to use a local (stored procedure level) variable instead of user variable. For that don't use @ in front of a variable name.

使用DECLARE你意味着使用本地(存储过程级别)变量而不是用户变量。为此,不要在变量名前面使用@。

there is a typo in first INSERT. It should be VALUES instead of VALUE

在第一次INSERT中有一个拼写错误。它应该是VALUES而不是VALUE

use LAST_INSERT_ID() function to retrieve a value of an auto_incremented column for a row that has been just inserted instead of

使用LAST_INSERT_ID()函数检索刚刚插入的行的auto_incremented列的值,而不是

Your code might look something like this

您的代码可能看起来像这样

DELIMITER $$
CREATE PROCEDURE test(
   IN a VARCHAR(255),
   IN b VARCHAR(255),
   IN c BIGINT(20)
)
BEGIN
   DECLARE i BIGINT(20);   // or BIGINT(20);

   INSERT INTO tableA (datetime_created) VALUES (NOW());
   SET i = LAST_INSERT_ID();
   INSERT INTO tableB(attr1, attr2, attr3, attr4)  VALUES (a, b, c, i);
END$$
DELIMITER ;

In this particular case you don't need a variable at all. A revised version of your SP might look like this

在这种特殊情况下,您根本不需要变量。 SP的修订版可能如下所示

DELIMITER $$
CREATE PROCEDURE test(
   IN a VARCHAR(255),
   IN b VARCHAR(255),
   IN c BIGINT(20)
)
BEGIN
   INSERT INTO tableA (datetime_created) VALUES (NOW());
   INSERT INTO tableB(attr1, attr2, attr3, attr4)  VALUES (a, b, c, LAST_INSERT_ID());
END$$
DELIMITER ;

Here is SQLFiddle demo for both versions

这是两个版本的SQLFiddle演示

#2

You have to set delimiter to something else then ;

你必须将分隔符设置为其他内容;

As you are probably using phpMyAdmin, there's an input field for that under the main textarea. The standard is ;, use eg ## instead.

由于您可能正在使用phpMyAdmin,因此在主textarea下有一个输入字段。标准是;,使用例如##代替。

#1