在MySQL中调用存储过程

I can't find this answer anywhere, but can you call a Stored Procedure from another Stored Procedure in MySQL? I want to get the Identity Value back and use it in the parent Stored Procedure. We can't use FUNCTIONS anymore!

我在任何地方都找不到这个答案，但是你能调用MySQL中另一个存储过程的存储过程吗?我想要取回标识值并在父存储过程中使用它。我们再也不能用函数了!

2 个解决方案

#1

CREATE PROCEDURE innerproc(OUT param1 INT)
BEGIN
 insert into sometable;
 SELECT LAST_INSERT_ID() into param1 ;
END
-----------------------------------
CREATE PROCEDURE outerproc()
BEGIN
CALL innerproc(@a);
// @a gives you the result of innerproc
SELECT @a INTO variableinouterproc FROM dual;
END

OUT parameters should help you in getting the values back to the calling procedure.Based on that the solution must be something like this.

OUT参数应该可以帮助您将值返回到调用过程。基于此，解应该是这样的。

#2

To call another procedure, use CALL: ex: Call SP1(parm1, parm2);

要调用另一个过程，使用call: ex: call SP1(parm1, parm2);

To get identity, did you try checking out LAST_INSERT_ID(); You would do something like SELECT LAST_INSERT_ID() after your SP call.

要获得标识，是否尝试检查LAST_INSERT_ID();您可以在SP调用之后选择LAST_INSERT_ID()。

Here's a complete, tested example:

这里有一个完整的测试示例:

DELIMITER $$

CREATE TABLE animals (
     id MEDIUMINT NOT NULL AUTO_INCREMENT,
     name CHAR(30) NOT NULL,
     PRIMARY KEY (id)
) $$

CREATE PROCEDURE sp1()
BEGIN
  insert into animals (name) values ('bear');
END $$

CREATE PROCEDURE sp2()
BEGIN
  call sp1;
  select last_insert_id();
END $$

call sp2;

#1

CREATE PROCEDURE innerproc(OUT param1 INT)
BEGIN
 insert into sometable;
 SELECT LAST_INSERT_ID() into param1 ;
END
-----------------------------------
CREATE PROCEDURE outerproc()
BEGIN
CALL innerproc(@a);
// @a gives you the result of innerproc
SELECT @a INTO variableinouterproc FROM dual;
END