【SQL】小心在循环中声明变量——浅析SQL变量作用域

时间：2020-12-04 16:58:58

本文适用：T-SQL(SQL Server)

先看这个语句：

DECLARE @i INT = 0

WHILE @i < 3 --跑3圈

BEGIN

    --每圈都定义一个表变量，并插入一行

    DECLARE @t TABLE(Col INT PRIMARY KEY) --主键唯一约束

    INSERT @t VALUES (1)

    SET @i += 1

END

如果你认为这个语句跑起来没问题，那你值得看下去，会避免以后踩到【SQL变量作用域】的坑。

事实上这个语句会报2次“违反了PRIMARY KEY约束…”，原因是@t这个表变量，并不是在每一圈都重新声明一个新的，而是声明1次后就一直沿用，由于该表具有主键约束，所以之后的两圈在插入的时候，由于已经存在相同主键，于是报上述错误。

换成普通变量也一样：

DECLARE @i INT = 0

WHILE @i < 3 --跑3圈

BEGIN

    --同样，该变量也只会声明1次，之后沿用

    DECLARE @s VARCHAR(20)

    IF @s IS NULL --所以第1圈会进入该分支

        SET @s = 's'

    ELSE          --之后的圈则进入该分支

        SET @s += 's'

    PRINT @s

    SET @i += 1

END

--执行结果：

s

ss

sss

所以到这里能得出一个结论：

循环中的变量只会声明一次，并在之后一直沿用。

理解这一点很重要，因为这与C#等编译语言非常不同，C#中每一圈声明的变量都相当于重新建一个，与上一圈的毫无关系，但在sql中不能这么思考。

尝试把上面的语句小改一下：

DECLARE @i INT = 0

WHILE @i < 3 --跑3圈

BEGIN

    DECLARE @s VARCHAR(20) = 's' --声明并赋值

    SET @s += 's'

    PRINT @s

    SET @i += 1

END

这次得到的结果会是3个ss，看起来是@s在每一圈得到了重建，那这似乎与上面的结论有悖，不是只会声明1次吗？其实并没有矛盾，而是【declare @s xxx = 's'】相当于【declare @s xxx】+【set @s = 's'】俩语句，声明的确只有1次，但稍后的赋值却是每圈都在进行，相当于每圈一开始都把@s重置为's'，所以是这个结果。这也提醒：见到declare @x xxx = xxx时，要看成两个动作。

其实这个问题本质上是一个变量作用域问题，只不过SQL中的变量作用域，与C#等语言按语句块划分不一样，SQL的变量作用域是【批】，这一点在MSDN中有说。比如下面的语句：

IF 1 = 2

    DECLARE @s VARCHAR(20)

SELECT @s

按说declare @s并不会得到执行，@s并没有声明，但事实上这个语句一切正常，不会报错。原因就在于声明语句比较特殊，它并不依赖位置，系统“见到”就算数，所以不管变量在多深的语句块中声明，它在本批接下来的语句中都是有效的。印象中某种SQL的写法是声明在一个区，逻辑在一个区，既然你t-sql的声明具有“提升”这种特点，我认为做成那种比较好，而不是混在逻辑语句中搞特殊。

回到开头的问题，现在我们清楚，虽然变量在循环中声明，但它并不会被多次执行，甚至不是在第1圈的时候执行，而是在某个时机由系统将所有声明统一执行，大概类似C#的静态字段，不管定义在哪里，CLR会确保在使用该类前完成初始化。

至于什么叫一【批】SQL，我没有找到很正式的定义，根据所学，我的理解是：没GO就是一批；有GO的话，GO之间算一批；exec、sp_executesql算一批；ssms中选中执行的部分算一批（前提是选中部分不含上述划分点）。如有错漏还请指正，感谢。

- EOF -

相关文章