探索 SQL：动态重新计算列

• 下载数据库备份文件 - 78 KB

引言

我们的问题是为特定表的每一行执行不同的计算，每次计算涉及多个列。计算列不适用于此场景，因为您无法在一个计算列中存储每一行的不同计算。

在查询中使用表达式是一种常见做法。但您是否知道，您可以基于计算（而不是简单地使用查询来从表中返回基本列）来构建查询结果集中的一列？ 如果您对同一表使用相同的计算在许多查询中，将计算存储在基本表中作为计算列可以缩短您的查询，并减少所需的代码维护。

SQL Server 7.0 引入了计算列，SQL Server 2000 增加了在计算列上创建索引的功能，因此结果可以存储在磁盘上。 对计算列的索引节省了执行计算所需的 CPU 时间，并允许高效的过滤和排序。

开发

为了了解如何解决我们的问题，让我们看一下 "Computation" 表结构。

表计算列，封装在 Unicode 字符串中，将存储引用 arg1、arg2 或 arg3 任何列的计算。 您可以在计算列中存储的一些可能值的示例如下

N'@arg1+@arg2+@arg3'
N'@arg1*@arg2-@arg3'
N'CEILING(@arg1+@arg2/@arg3)'

我们希望在 "rez" 列中自动计算 arg1、arg2 或 arg3 列的更新/插入结果；结果由存储在 "computation" 列中的公式给出。 这一事实将在每一行上用不同的公式计算。

当执行 INSERT 或 UPDATE 时发生的事情的引擎存储在 Computation 表的触发器 trgComputation 中。

CREATE TRIGGER trgComputation ON [dbo].[Computation] FOR INSERT, UPDATE
AS
declare  @rows as int
set      rows = @@rowcount

if not @rows > 0 return
if not update(arg1) and not update(arg2) and not update(arg3) return

declare
    @key            as int,
    @arg1           as int,
    @arg2           as int,
    @arg3           as int,
    @rez            as int,
    @comp           as nvarchar(500),
    @param          as nvarchar(500)

select @key = min(id) from inserted

while @key is not null
begin
    select 
        @arg1 = arg1,
        @arg2 = arg2,
        @arg3 = arg3,
        @comp = computation
        from inserted where @key = id


    set @comp = N'set @rez= ' + @comp
    set @param = N'@rez int output, @arg1 int, @arg2 int, @arg3 int'


    exec sp_executesql @comp, @param, @rez output, @arg1, @arg2, @arg3


    update computation set rez=@rez where id= @key 


    select @key = min(id) from inserted where id>@key
end

首先，触发器会检查激活它的 INSERT 或 UPDATE 操作是否影响了任何行；如果没有，触发器不需要做任何事情。

接下来，触发器执行一个循环，该循环遍历 "INSERTED" 表中的所有行，该表包含在基本表 Computation 中插入或修改的所有行。

在循环体中，代码从 INSERTED 中的当前行获取输入参数和计算的值，并将这些值存储在局部变量中。

现在，代码需要动态地执行存储在 @comp 变量中的计算，并将计算中的 @resultvariable 的值传递给触发器的 @rez 变量。为了实现此结果，您可以使用 sp_executesql 系统存储过程的一个未记录的功能，该功能允许您使用输出参数

DECLARE 	@r_out		as int
    EXEC	sp_executesql
			N'set	@r = @p1 * @p2',
			N'@p1 int, @p2 int, @r int output',
			@r	= @r_out OUTPUT,
			@p1	= 10,
			@p2	= 5

    SELECT	@r_out

这个简单的脚本示例使用 sp_executesql 来计算两个输入参数的乘积，并将结果存储在名为 @r 的输出参数中。

sp_executesql 接受的第一个参数是您要动态执行的语句，第二个参数是包含该语句使用的输入和输出参数列表的字符串。 sp_executesql 接受的所有其他参数都是将值分配给该语句使用的参数。

现在让我们尝试这个过程。看看我们当时的表格

现在，使用以下更新语句对 arg1 列进行更新
update computation set arg1 = arg1 + 10;

现在看看结果

sp_executesql 系统存储过程允许您动态执行计算，甚至使用输出参数。如果没有动态执行计算的能力，那么为表中的每一行维护不同的计算将会不必要地复杂。

本文的灵感来自于 SQL Server 杂志。