【SQL】小心在循环中声明变量——浅析SQL变量作用域
小编:啊南 184阅读 2020.11.30
本文适用: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中选中执行的部分算一批(前提是选中部分不含上述划分点)。
相关推荐
- SQL Server 常用函数 1.获取当前时间--GetDate()2.DatePart() 函数3.字符串截取--substring三个参数,第一个参数需要截取的,第二个参数,截取字符串的起始位置(起始位置是1),第三个参数需要截取字符串的长度。4.日期增减函数-DateAdd三个参数,第一参数合法的日期表达式,第二个参…
- 3DMAX提示和技巧 本主题标识使用 Civil View 的一些重要提示和技巧。常规使用屏幕分辨率至少为 1280x1024 的 Civil View。低于此分辨率时,一些面板将占用过多屏幕空间。 将视口设置为线框显示以达到最佳性能。 要尽可能简化用户界面,请在单个视口中工作并关闭 3ds Max 命令面…