I need to make a stored procedure or function that returns a set of rows. I've noted that in a stored procedure i can SELECT * FROM table with success. If i fetch rows in a loop and SELECT something, something_other FROM table once per loop execution, I only get one single result.
我需要创建一个返回一组行的存储过程或函数。我注意到在存储过程中我可以SELECT * FROM表成功。如果我在一个循环中获取行并选择一些东西,每个循环执行一次,其他一些FROM表,我只得到一个结果。
What I need to do is looping, doing some calculations and returning a rowset. What's the best way to do this? A temporary table? Stored functions?
我需要做的是循环,做一些计算并返回一个rowset。最好的方法是什么?临时表?存储功能?
Any help appreciated.
任何帮助赞赏。
2 个解决方案
#1
It sounds like you're using a cursor inside the body of the stored procedure to accomplish your looping?
听起来你正在使用存储过程体内的光标来完成循环?
My first advise is: try to do your calculations in a single query without resorting to cursors. What's the calculation exactly?
我的第一个建议是:尝试在单个查询中进行计算而不使用游标。准确的计算是什么?
If you really do need to use a cursor, then INSERT the results of each loop into a temporary table and then SELECT * from that table when you're done looping.
如果确实需要使用游标,则将每个循环的结果插入到临时表中,然后在完成循环时从该表中选择SELECT *。
#2
You can return multiple result sets but only if the client library supports it and the client is expecting it. Some don't, so using them will result in out-of-sequence errors.
您可以返回多个结果集,但仅当客户端库支持它并且客户端期望它时。有些则没有,因此使用它们会导致无序错误。
You can certainly build a temporary table, select from it and drop it inside the procedure, that would be safe. Another option is to build a UNION select using a prepared SQL statement which returns all the rows you need and execute that. That's a bit messy.
您当然可以构建一个临时表,从中进行选择并将其放入过程中,这是安全的。另一种选择是使用准备好的SQL语句构建UNION选择,该语句返回所需的所有行并执行该操作。那有点乱。
#1
It sounds like you're using a cursor inside the body of the stored procedure to accomplish your looping?
听起来你正在使用存储过程体内的光标来完成循环?
My first advise is: try to do your calculations in a single query without resorting to cursors. What's the calculation exactly?
我的第一个建议是:尝试在单个查询中进行计算而不使用游标。准确的计算是什么?
If you really do need to use a cursor, then INSERT the results of each loop into a temporary table and then SELECT * from that table when you're done looping.
如果确实需要使用游标,则将每个循环的结果插入到临时表中,然后在完成循环时从该表中选择SELECT *。
#2
You can return multiple result sets but only if the client library supports it and the client is expecting it. Some don't, so using them will result in out-of-sequence errors.
您可以返回多个结果集,但仅当客户端库支持它并且客户端期望它时。有些则没有,因此使用它们会导致无序错误。
You can certainly build a temporary table, select from it and drop it inside the procedure, that would be safe. Another option is to build a UNION select using a prepared SQL statement which returns all the rows you need and execute that. That's a bit messy.
您当然可以构建一个临时表,从中进行选择并将其放入过程中,这是安全的。另一种选择是使用准备好的SQL语句构建UNION选择,该语句返回所需的所有行并执行该操作。那有点乱。