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

本文适用: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 -

上一篇:【SQL】面面俱到 | 在SQL中使用CUBE和ROLLUP实现数据多维汇总


下一篇:【SQL】链接服务器