如何使用递归函数更新表？
这是此问题的后续内容。
不允许函数写入数据库，但是如果我想在每次调用函数（特别是递归函数）时更新记录怎么办？
目前，我有一个接受 ID 并返回浮点数的函数。我想使用给定的 ID 和返回的浮点数更新表。通常，可以使用一个简单的存储过程来调用该函数，然后进行更新。我的函数是递归的，所以解决方案并不是那么简单...
我正在考虑尝试这样做：
- 创建递归函数，以便它接受一个表作为参数
- 如果表为空，则创建它；否则，将表复制到一个新变量（因为它将是只读的）
- 在进行递归调用之前更新函数中复制的表，并
- 在最后将副本传递给函数，返回完整的表（不知道我将如何“知道”它是否完整）
- 从存储过程中调用此函数，该存储过程使用返回的表进行多次更新
我什至在尝试类似的操作之前正在寻找替代方案。看来这件事以前已经做过了。
This is a follow-up from this question.
Functions are not allowed to write to the database, but what if I wanted to update a record every time a function was called, specifically a recursive function?
Currently, I have a function that takes an ID and returns a float. I'd like to update a table using the given ID and the returned float. Ordinarily, a simple stored procedure could be used to call the function and then make the update. My function is recursive, and so the solution isn't that simple...
I'm thinking about attempting to do this:
- Create the recursive function so that it takes a table as a parameter
- If the table is null, create it; else, copy the table to a new variable (because it will be readonly)
- update the copied table in the function before making the recursive call, and pass the copy to the function
- at the end, return the complete table (not sure how I will "know" is it complete yet)
- call this function from a stored procedure that uses the returned table to make several updates
I'm looking for alternatives before I even try something like that. Seems that this has been done before.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
任何递归实现 T-SQL 迟早都会遇到
@@ NESTLEVEL 上限：但我们从 CS101 中知道，任何递归算法都可以在堆栈的帮助下实现为迭代算法。因此，您需要一个表来充当堆栈（请参阅将表用作队列 进行一些相关讨论）。使用存储过程，而不是函数，因为在 T-SQL 中“函数”是特殊的东西（基本上是数据访问路径）并且不允许修改数据。每当您想到“函数”（如 C/C++/C# 函数或方法）时，您确实需要一个存储过程。您不能从过程返回“表”，因此输出必须是您将结果写入其中的表。
所有这些都只是理论，因为您没有提供实际问题，只是对问题类型的描述。如果我们知道真正的问题，我们可能会说存储过程是否有意义，使用游标是否可以，使用#temp 表是否是正确的方法等等。所有这些只是为了考虑普通的算法。如果添加数据大小考虑因素和并发问题，事情可能会变得非常棘手。
Any recursive implementation is T-SQL will sooner or later run into the
@@NESTLEVELcap:But we know from CS101 than any recursive algorithm can be implemented as an iterative algorithm with the help of a stack. So you need a table to act as a stack (see Using Tables as Queues for some related discussion). Use a stored procedure, not a function, since in T-SQL 'functions' are something special (a data access path basically) and are not allowed to modify data. Whenever you think 'function' (as in C/C++/C# function or method), you really need a stored procedure. You cannot return 'tables' from procedures, so the output has to be a table into which you write the results.
All this is just theory, because you did not provide the actual problem, just a description of the type of problem. If we'd know the real problem, we might say whether a stored procedure makes sense, whether using cursors is OK, whether using a #temp table is the way to go etc etc. And all these just to consider the plain vanilla algorithm. Things can get really hairy if you add size-of-data considerations and concurrency issues.