从存储过程中选择数据 [英] SELECT-ing data from stored procedures

问题描述

我有一个复杂的SELECT查询，该查询按时间范围进行过滤，并且我希望可以使用用户提供的参数来指定此时间范围(开始和结束日期).因此，我可以使用存储过程来执行此操作，并且返回的结果是多行结果集.我遇到的问题是以后如何处理该结果集.我不能做这样的事情:

I have a complicated SELECT query that filters on a time range, and I want this time range (start and end dates) to be specifiable using user-supplied parameters. So I can use a stored procedure to do this, and the return is a multiple-row result set. The problem I'm having is how to deal with this result set afterwards. I can't do something like:

SELECT * FROM (CALL stored_procedure(start_time, end_time))

即使存储过程只是一个带参数的SELECT.服务器端的预处理语句也不起作用(它们也不是持久性的). 有些人建议使用临时表.不是理想解决方案的原因是:1)我不想指定表架构，似乎必须这样做； 2)临时表的生存期仅限于查询的调用，它不需要坚持下去.

even though the stored procedure is just a SELECT that takes parameters. Server-side prepared statement also don't work (and they're not persistent either). Some suggest using temporary tables; the reason that's not an ideal solution is that 1) I don't want to specify the table schema and it seems that you have to, and 2) the lifetime of the temporary table would only be limited to a invocation of the query, it doesn't need to persist beyond that.

因此，回顾一下，我想要类似服务器端的持久准备语句，其返回值是MySQL可以像子查询一样操作的结果集.有任何想法吗?谢谢.

So to recap, I want something like a persistent prepared statement server-side, whose return is a result set that MySQL can manipulate as if it was a subquery. Any ideas? Thanks.

顺便说一句，我正在使用MySQL 5.0.我知道这是一个很旧的版本，但是此功能似乎在任何较新的版本中都不存在.我不确定在其他SQL引擎中是否可以从存储过程中进行SELECT-ing.目前暂时不可以切换，但是我想知道是否有可能，以防将来我们决定切换.

By the way, I'm using MySQL 5.0. I know it's a pretty old version, but this feature doesn't seem to exist in any more recent version. I'm not sure whether SELECT-ing from a stored procedure is possible in other SQL engines; switching is not an option at the moment, but I'd like to know whether it's possible anyway, in case we decide to switch in the future.

推荐答案

在其他引擎中也可以从函数中进行选择.例如，Oracle允许您编写一个返回用户定义类型表的函数.您可以在函数中定义结果集，使用查询甚至使用选择和代码的组合来填充结果集.最终，可以从函数返回结果集，然后可以使用以下命令继续查询该结果集:

Selecting from functions is possible in other engines. For instance, Oracle allows you to write a function that returns a table of user defined type. You can define result sets in the function, fill them by using queries or even using a combination of selects and code. Eventually, the result set can be returned from the function, and you can continue to query on that by using:

select * from table(FunctionToBeCalls(parameters));

唯一的缺点是此结果集未建立索引，因此如果在复杂查询中使用该函数，可能会很慢.

The only disadvantage, is that this result set is not indexed, so it might be slow if the function is used within a complex query.

在MySQL中，不可能做到这一点.无法直接在选择查询中使用过程的结果集.您可以从函数返回单个值，并且可以在过程中使用OUT或INOUT参数从中返回值. 但是整个结果集是不可能的.在您的过程中填充临时表是您将获得的最接近的结果.

In MySQL nothing like this is possible. There is no way to use a result set from a procedure directly in a select query. You can return single values from a function and you can use OUT or INOUT parameters to you procedure to return values from. But entire result sets is not possible. Filling a temporary table within you procedure is the closest you will get.

这篇关于从存储过程中选择数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！