如何使用存储的 MYSQL 程序的表输出
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17944871/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to use Table output from stored MYSQL Procedure
提问by Johannes
I've been looking for the last hour or so and haven't found a conclusive answer to this seemingly simple problem:
我一直在寻找最后一个小时左右，但还没有找到这个看似简单的问题的结论性答案：
How do you call a stored MYSQL function/procedure and use its output in further SELECT queries?
您如何调用存储的 MYSQL 函数/过程并在进一步的 SELECT 查询中使用其输出？
Although this obviously doesn't work, this is the kind of thing I'd like to have:
虽然这显然不起作用，但这是我想要的东西：
SELECT P.`id` FROM (CALL test_proc()) AS P
Where test_proc() is defined by:
其中 test_proc() 由以下定义：
DROP PROCEDURE IF EXISTS test_proc;
DELIMITER ;;
CREATE PROCEDURE test_proc()
BEGIN
SELECT * FROM `table`;
END;;
DELIMITER ;
Just as an example. I'd be fine with using a stored function as well.
举个例子。我也可以使用存储函数。
回答by Michael - sqlbot
This can't be done, directly, because the output of an unbounded select in a stored procedure is a result set sent to the client, but not technically a table.
这不能直接完成，因为存储过程中无界选择的输出是发送到客户端的结果集，但技术上不是表。
The workaround is to let the proc put the data in a temporary table after creating the table for you. This table will be available only to your connection when the procedure finishes. It will not cause a conflict if somebody else runs the proc at the same time and won't be visible to any other connection.
解决方法是在为您创建表后让 proc 将数据放入临时表中。过程完成后，此表将仅对您的连接可用。如果其他人同时运行 proc 并且对任何其他连接不可见，则不会导致冲突。
Add this to the procedure:
将此添加到程序中：
DROP TEMPORARY TABLE IF EXISTS foo;
CREATE TEMPORARY TABLE foo SELECT ... your existing select query here ...;
When your procedure finishes, SELECT * FROM foo;will give you what you what you would have gotten from the proc. You can join to it pretty much like any table.
当你的程序完成时，SELECT * FROM foo;会给你你从 proc 中得到的东西。您可以像加入任何表一样加入它。
When you're done, drop it, or it will go away on its own when you disconnect. If you run the proc again, it will be dropped and recreated.
完成后，将其放下，否则断开连接时它会自行消失。如果您再次运行该 proc，它将被删除并重新创建。
回答by sinecospi
Here's a concrete example in MySql 8, based on @Michael-sqlbot's suggestion:
这是 MySql 8 中的一个具体示例，基于@Michael-sqlbot 的建议：
-- Create some test data
drop table if exists someData ;
create table someData (itemId int, itemName varcharacter(20), itemQty int, itemCat int) ;
insert into someData values
(1,'First', 9, 1)
,(2,'Second',50, 3)
,(3,'Third', 12, 5)
,(4,'Fourth', 7, 3)
,(5,'Fifth', 1, 2)
,(6,'Sixth', 1, 1)
,(7,'Seventh', 9, 3)
,(8,'Eighth', 0, 2)
;
-- Just checking that it's all there!
select * from someData ;
-- Define the proc
delimiter //
drop procedure if exists prcSomeData //
create procedure prcSomeData()
comment 'Create a temporary table called "tmpSomeData"'
begin
drop table if exists tmpSomeData ;
create temporary table tmpSomeData as
select itemCat
, sum(itemQty) as 'itemsTot'
, min(itemQty) as 'lowestQty'
, max(itemQty) as 'highestQty'
from someData
group by itemCat
order by itemCat
;
end //
delimiter ;
-- Gotta run the proc to instantiate the temp table
call prcSomeData() ; -- produces a temporary table "tmpSomeData", exists for the session
-- Now it's usable
select * from tmpSomedata ;
Which produces:
其中产生：
itemCat|itemsTot|lowestQty|highestQty|
-------|--------|---------|----------|
1| 10| 1| 9|
2| 1| 0| 1|
3| 66| 7| 50|
5| 12| 12| 12|
