MySQL 存储过程/函数可以返回表吗？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1637288/
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
Can a stored procedure/function return a table?
提问by Cambiata
Can a MySql stored procedure / function return a table without the use of temp table?
MySql 存储过程/函数可以在不使用临时表的情况下返回表吗？
Creating the following procedure
创建以下过程
CREATE PROCEDURE database.getExamples()
SELECT * FROM examples;
and later calling it with
然后用
CALL database.getExamples()
displays the example table - just as expected - but the following doesn't seem to be possible:
显示示例表 - 正如预期的那样 - 但以下似乎是不可能的：
SELECT * FROM CALL database.getExamples()
Is it possible at all to return a query result table from a stored procedure / function, and if so - how?
是否有可能从存储过程/函数返回查询结果表，如果是这样 - 如何？
采纳答案by Quassnoi
As for now, this is not possible.
就目前而言，这是不可能的。
Here is the documentationon what may be used in the FROMclause:
以下是有关该条款中可能使用的内容的文档FROM：
table_references:
table_reference [, table_reference] ...
table_reference:
table_factor
| join_table
table_factor:
tbl_name [[AS] alias] [index_hint)]
| table_subquery [AS] alias
| ( table_references )
| { OJ table_reference LEFT OUTER JOIN table_reference
ON conditional_expr }
join_table:
table_reference [INNER | CROSS] JOIN table_factor [join_condition]
| table_reference STRAIGHT_JOIN table_factor
| table_reference STRAIGHT_JOIN table_factor ON conditional_expr
| table_reference {LEFT|RIGHT} [OUTER] JOIN table_reference join_condition
| table_reference NATURAL [{LEFT|RIGHT} [OUTER]] JOIN table_factor
join_condition:
ON conditional_expr
| USING (column_list)
index_hint:
USE {INDEX|KEY} [FOR JOIN] (index_list)
| IGNORE {INDEX|KEY} [FOR JOIN] (index_list)
| FORCE {INDEX|KEY} [FOR JOIN] (index_list)
index_list:
index_name [, index_name] ...
As you can see, stored procedures are not in this list.
如您所见，存储过程不在此列表中。
回答by Jakub Czaplicki
According to A.4 MySQL 5.6 FAQ: Stored Procedures and Functions:
根据A.4 MySQL 5.6 FAQ: Stored Procedures and Functions：
A.4.14: Can MySQL 5.6 stored routines return result sets?
Stored procedures can, but stored functions cannot.
A.4.14：MySQL 5.6 存储例程可以返回结果集吗？
存储过程可以，但存储函数不能。
回答by atjoedonahue
It looks like it can be done, but with use of output variables from the stored procedure. http://www.sqlinfo.net/mysql/mysql_stored_procedure_SELECT.php
看起来可以做到，但是要使用存储过程的输出变量。 http://www.sqlinfo.net/mysql/mysql_stored_procedure_SELECT.php
-- 1. Create Procedure
DROP PROCEDURE IF EXISTS `sp_students_SELECT_byPK`
GO
CREATE PROCEDURE sp_students_SELECT_byPK
(
IN p_student_id INT(11) ,
OUT p_password VARCHAR(15) ,
OUT p_active_flg TINYINT(4) ,
OUT p_lastname VARCHAR(30) ,
OUT p_firstname VARCHAR(20) ,
OUT p_gender_code VARCHAR(1) ,
OUT p_birth_dttm DATETIME
)
BEGIN
SELECT password ,
active_flg ,
lastname ,
firstname ,
gender_code ,
birth_dttm
INTO p_password ,
p_active_flg ,
p_lastname ,
p_firstname ,
p_gender_code ,
p_birth_dttm
FROM students
WHERE student_id = p_student_id ;
END
GO
-- 2. Select Results from Stored Procedure
/***
IN p_student_id INT(11)
OUT p_password VARCHAR(15)
OUT p_active_flg TINYINT(4)
OUT p_lastname VARCHAR(30)
OUT p_firstname VARCHAR(20)
OUT p_gender_code VARCHAR(1)
OUT p_birth_dttm DATETIME
***/
CALL sp_students_SELECT_byPK
(
8,
@p_password ,
@p_active_flg ,
@p_lastname ,
@p_firstname ,
@p_gender_code ,
@p_birth_dttm
)
GO
SELECT @p_password AS p_password ,
@p_active_flg AS p_active_flg ,
@p_lastname AS p_lastname ,
@p_firstname AS p_firstname ,
@p_gender_code AS p_gender_code ,
@p_birth_dttm AS p_birth_dttm
GO
回答by Harshal
Each SELECT statement that does not insert into a table or a variable will produce a result set.
每个不插入表或变量的 SELECT 语句都会产生一个结果集。
If you want your stored procedure to return only one result set, make sure that you only have one SELECT statement. If you have other SELECT statements, make sure that they insert results into a table or variable.
如果您希望您的存储过程只返回一个结果集，请确保您只有一个 SELECT 语句。如果您有其他 SELECT 语句，请确保它们将结果插入到表或变量中。
UPDATE Here are examples of stored procedures.
UPDATE 以下是存储过程的示例。
This stored procedure would return one result set:
此存储过程将返回一个结果集：
DELIMITER ;;
CREATE DEFINER=CURRENT_USER PROCEDURE stored_procedure_name()
BEGIN
DECLARE local_variable_name INT;
SELECT column_name FROM table_1 LIMIT 1 INTO local_variable_name;
SELECT * FROM table_1;
END;;
DELIMITER ;
This stored procedure would return two result sets:
DELIMITER ;;
CREATE DEFINER=CURRENT_USER PROCEDURE stored_procedure_name()
BEGIN
DECLARE local_variable_name INT;
SELECT column_name FROM table_1 LIMIT 1 INTO local_variable_name;
SELECT * FROM table_1;
SELECT * FROM table_2;
END;;
DELIMITER ;
参考：https: //dba.stackexchange.com/questions/8291/how-does-mysql-return-a-result-set-from-a-stored-procedure
回答by Ian Gregory
You might be able to do what you are attempting by using a view instead of a stored procedure, but that entirely depends on what the stored procedure does.
您可以通过使用视图而不是存储过程来执行您正在尝试的操作，但这完全取决于存储过程的作用。
If using a temp table is your only option, consider using the MEMORY storage engine.
如果使用临时表是您唯一的选择，请考虑使用 MEMORY 存储引擎。
