MySQL如何从[存储过程]插入到[temp表]中

This is very similar to question 653714, but for MySQL instead of SQL Server.

这与问题653714非常相似，但是对于MySQL而不是SQL Server。

Basically, I have a complicated select that is the basis for several stored procedures. I would like to share the code across the stored procedures, however, I'm not sure how to do this. One way I could do this is by making the shared select a stored procedure and then calling that stored procedure from the other ones. I can't figure out how to work with the result set of the nested stored procedure. If I could put them in a temp table I could use the results effectively, but I can't figure out how to get them in a temp table. For example, this does not work:

基本上，我有一个复杂的选择，它是几个存储过程的基础。我希望跨存储过程共享代码，但是，我不确定如何做到这一点。我可以这样做的一种方法是，让共享选择一个存储过程，然后调用另一个存储过程。我不知道如何使用嵌套存储过程的结果集。如果我能把它们放在临时表中，我就能有效地使用结果，但是我不能想出如何将它们放在临时表中。例如，这行不通:

CREATE TEMPORARY TABLE tmp EXEC nested_sp();

4 个解决方案

#1

The problem is, Stored Procedures don't really return output directly. They can execute select statements inside the script, but have no return value.

问题是，存储过程并没有直接返回输出。它们可以在脚本中执行select语句，但没有返回值。

MySQL calls stored procedures via CALL StoredProcedureName(); And you cannot direct that output to anything, as they don't return anything (unlike a function).

MySQL通过调用storedprogramurename()调用存储过程;你不能将输出指向任何东西，因为它们不返回任何东西(不像函数)。

MySQL Call Command

MySQL调用命令

#2

My first reaction was "That sounds like a view to me". Doesn't that abstract it enough so you can just add the variability into an SP per case?

我的第一反应是“这听起来像是我的看法”。这难道不够抽象吗?你可以把可变性加入一个SP的例子中?

Anything that adds a temp table that wouldn't otherwise be there is a very likely antipattern.

任何添加临时表(否则不存在)的内容都很可能是反模式。

#3

i know this is coming really late but since it took me ages to find a real solution i might as well share. I worked on an example that is below.

我知道这真的很晚了，但因为我花了很长时间才找到一个真正的解决方案，我也可以分享。我在下面举了一个例子。

the tables created are:

创建的表有:

CREATE TABLE BOOK(
B_ID INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY(B_ID),
TITLE VARCHAR(100),
DESCRIPTION VARCHAR(30),
PRICE DOUBLE);

CREATE TABLE BOOK_COMMENT(

PRIMARY KEY(B_C_ID),
B_C_ID INT NOT NULL AUTO_INCREMENT,
REMARK VARCHAR(120),
B_ID INT,
FOREIGN KEY(B_ID) REFERENCES BOOK(B_ID));

CREATE TABLE AUTHOR(
A_ID INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY(A_ID),
A_NAME CHAR(15),
B_ID INT,

FOREIGN KEY(B_ID) REFERENCES BOOK(B_ID));

DELIMITER
分隔符


CREATE PROCEDURE BOOK_IMPORTANT( _PRICE DOUBLE, _B_ID INT, A_NAME CHAR(15), _BD_ID INT)

BEGIN

INSERT INTO BOOK(PRICE)

VALUES(_PRICE);

SET _B_ID=LAST_INSERT_ID();

INSERT INTO BOOK_COMMENT(B_ID)

VALUES(_B_ID);

SET _BD_ID=LAST_INSERT_ID();

INSERT INTO AUTHOR(A_NAME,B_ID)

VALUES(A_NAME,_BD_ID);

END

then use the following to insert the values.

然后使用以下代码插入值。

CALL BOOK_IMPORTANT('0.79',LAST_INSERT_ID(),'',LAST_INSERT_ID());

LAST_INSERT_ID() takes the last auto increment of the table and inserts it into the referencing column of the child table.

LAST_INSERT_ID()获取表的最后一个自动增量，并将其插入到子表的引用列中。

In the procedure parameters _B_ID and _BD_ID represent the B_ID since I need B_ID as a foreign key in both tables.

在过程参数_B_ID和_BD_ID中表示B_ID，因为我需要B_ID作为两个表中的外键。

Sorry for the excess wording. All the other guys expect you to automatically know how to do it. Hope it helps

抱歉，用词过多。其他所有人都希望你能自动知道怎么做。希望它能帮助

#4

You cannot "SELECT INTO" with stored procedures.

不能使用存储过程“选择进入”。

Create the temporary table first and have your stored procedure to store the query result into the created temporary table using normal "INSERT INTO". The temporary table is visible as long as you drop it or until the connection is closed.

首先创建临时表，并让存储过程使用普通的“插入”将查询结果存储到创建的临时表中。临时表是可见的，只要您删除它或直到连接关闭。

#1