SQL在存储过程中使用递归

发布: 2007-6-21 12:06 | 作者: | 来源: | 查看: 102次 | 进入软件测试论坛讨论

　　
　　递归的基本概念非常简单：一段给定的代码对自身进行调用，直到某些边界条件得到满足。在本文中，我们将演示如何在T-SQL中使用递归。
　　

　　在我的眼中，递归是最为精致的程序结构之一。我已经在许多场合用不同的编程语言实现过它。递归的基本概念非常简单：一段给定的代码对自身进行调用，直到某些边界条件得到满足。我将通过下面的内容展示如何在T-SQL中使用递归。我所用到的是递归的经典例子：阶乘计算。
　　
　　阶乘的意思就是将小于等于这一数字的所有数字相乘，直至乘到2。例如，factorial（10）即等于10 * 9 * 8 * 7 * 6 * 5 * 4 * 3 * 2（你也可以加上“*1”，但似乎是多此一举）。
　　
　　以下代码即实现了阶乘：
　　
　　CREATE PROCEDURE [dbo].[Factorial_ap]
　　
　　(
　　
　　@Number Integer,
　　
　　@RetVal Integer OUTPUT
　　
　　)
　　
　　AS
　　
　　DECLARE @In Integer
　　
　　DECLARE @Out Integer
　　
　　IF @Number != 1
　　
　　BEGIN
　　
　　SELECT @In = @Number – 1
　　
　　EXEC Factorial_ap @In, @Out OUTPUT
　　
　　SELECT @RetVal = @Number * @Out
　　
　　END
　　
　　ELSE
　　
　　BEGIN
　　
　　SELECT @RetVal = 1
　　
　　END
　　
　　RETURN
　　
　　GO
　　
　　假设你需要计算factorial（n），这一过程将对自身调用（n-2）次。SQL Server允许调用深度高达32次的递归，但到了13次的时候，你就会遭遇到算法溢出（arithmetic overflow）警告。如果你希望计算大型数据的阶乘，你应该将变量声明为BigInt 而非Integer。这样一来你就可以计算factorial（20），结果是2,432,902,008,176,640,000。这一结果的增长是如此迅速，因而如果是计算factorial（21），那么这一限制将被再次突破。
　　
　　尽管阶乘机制非常美妙，但在日常编程中似乎没有多少机会用到。然而，上面的代码还是精确的展示了递归的基本原理和具体的实践。
　　
　　在一些实际问题中，递归都将是一种有价值的编程技巧。其中一个经典的编程问题名为“Bill of Materials（用料单）”。该问题有至少两种应用：
　　
　　给出所需对象的一个实例，求出构造此实例的用料单；
　　
　　指定组成某个对象的若干对象的详细目录，求出能够构造出多少对象？
　　
　　现在让我们来假设我们已经有了对象O，它是由四个X对象和三个Y对象以及七个Z对象组成。因此，要构造一个单独的O对象显然我们将会需要四个X对象、三个Y对象和七个Z对象。然而，对象Y和Z都需要一定数量的Q对象（例如对螺丝指定周长、螺栓样式、螺帽样式）。因此我们需要分析对象Y和X，确定它们所需要的Q对象个数，然后再确定我们能提供相应总数。如果不能，那么我们将无法创建对象Q。
　　
　　SQL Server 2000无法较方便的解决这一问题，除非你能够提前知道递归层次。然而，SQL 2005测试版在这一问题上已经进行了很长时间的研究。SQL项目负责人Joe Celko提供了非常巧妙的解决办法，该办法涉及到在行插入（row-insert）时间对递归层次进行跟踪。这一解决方案非常有用，但需要使用触发器或类似机制通过每一次插入、更新或删除来更新层次深度队列。你可以查看这种方法在Access下的实现。随后就可以方便的将这种解决方案引入到SQL Server中，并根据自己的需要进行修改。