从存储过程中选择数据
我有一个复杂的 SELECT 查询，用于筛选时间范围，并且我希望可以使用用户提供的参数来指定该时间范围（开始日期和结束日期）。所以我可以使用存储过程来做到这一点，并且返回的是多行结果集。我遇到的问题是之后如何处理这个结果集。我不能做这样的事情：
SELECT * FROM (CALL stored_procedure(start_time, end_time))
即使存储过程只是一个带有参数的 SELECT 。服务器端准备好的语句也不起作用（并且它们也不是持久的）。 有些人建议使用临时表；有些人建议使用临时表。这不是一个理想的解决方案的原因是 1) 我不想指定表模式，但似乎你必须这样做，2) 临时表的生命周期仅限于查询的调用，它不需要坚持下去。
回顾一下，我想要一个类似于服务器端持久准备语句的东西，它的返回是一个结果集，MySQL 可以像子查询一样操作它。有什么想法吗？谢谢。
顺便说一下，我使用的是MySQL 5.0。我知道这是一个相当旧的版本，但此功能似乎在任何较新的版本中都不存在。我不确定在其他 SQL 引擎中是否可以从存储过程进行 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))
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.
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.
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.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
在其他引擎中可以从功能中进行选择。例如，Oracle 允许您编写一个返回用户定义类型的表的函数。您可以在函数中定义结果集，通过使用查询甚至使用选择和代码的组合来填充它们。最终，可以从函数返回结果集，并且您可以使用以下方法继续查询：
唯一的缺点是该结果集没有索引，因此如果在复杂查询中使用该函数可能会很慢。
在 MySQL 中，这样的事情是不可能的。无法直接在选择查询中使用过程的结果集。您可以从函数返回单个值，并且可以在过程中使用
OUT或INOUT参数来返回值。但完整的结果集是不可能的。在您的过程中填充临时表是您将获得的最接近的结果。
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:
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.
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
OUTorINOUTparameters 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.