如何使用递归函数更新表? [英] How to use a recursive function to update a table?

问题描述

这是这个问题的后续.

函数是不允许写入数据库的，但是如果我想在每次调用函数时更新一条记录，特别是递归函数呢?

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?

目前，我有一个函数，它接受一个 ID 并返回一个浮点数.我想使用给定的 ID 和返回的浮点数更新表.通常，可以使用一个简单的存储过程来调用该函数，然后进行更新.我的函数是递归的，所以解决方案没那么简单...

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:

• 创建递归函数，使其接受一个表作为参数
• 如果表为空，则创建；否则，将表复制到一个新变量中(因为它将是只读的)
• 在进行递归调用之前更新函数中复制的表，并将副本传递给函数
• 最后，返回完整的表格(不确定我将如何知道"它是否完整)
• 从使用返回的表进行多次更新的存储过程调用此函数

在尝试类似的方法之前，我正在寻找替代方案.好像以前做过.

I'm looking for alternatives before I even try something like that. Seems that this has been done before.

推荐答案

任何递归实现都是 T-SQL 迟早会碰到 @@NESTLEVEL 上限:

Any recursive implementation is T-SQL will sooner or later run into the @@NESTLEVEL cap:

您可以嵌套存储过程和最多 32 个托管代码引用水平.嵌套级别增加一当被调用的存储过程或托管代码引用开始执行时减一被调用的存储过程或托管代码参考完成执行.试图超过 32 的最大值嵌套的层次导致整个调用链失败.

You can nest stored procedures and managed code references up to 32 levels. The nesting level increases by one when the called stored procedure or managed code reference begins execution and decreases by one when the called stored procedure or managed code reference completes execution. Attempting to exceed the maximum of 32 levels of nesting causes the whole calling chain to fail.

但是我们从 CS101 知道，任何递归算法都可以在堆栈的帮助下实现为迭代算法.所以你需要一个表作为一个堆栈(参见 Using Tables as Queues 进行一些相关讨论).使用存储过程，而不是函数，因为在 T-SQL 中函数"是特殊的(基本上是数据访问路径)并且不允许修改数据.每当您想到函数"(如在 C/C++/C# 函数或方法中)时，您确实需要一个存储过程.您不能从过程中返回表格"，因此输出必须是您将结果写入其中的表格.

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.

所有这些都只是理论，因为您没有提供实际问题，只是对问题类型的描述.如果我们知道真正的问题，我们可能会说存储过程是否有意义，使用游标是否可以，使用#temp 表是否可行等等.所有这些只是为了考虑普通的普通算法.如果您考虑数据大小和并发问题，事情可能会非常变得棘手.

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.

这篇关于如何使用递归函数更新表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！