浅析SQL Server的嵌套存储过程中使用同名的临时表怪像(2)-木庄网络博客

当前第2页返回上一页

在存储过程或触发器中创建的本地临时表的名称可以与在调用存储过程或触发器之前创建的临时表名称相同。但是，如果查询引用临时表，而同时有两个同名的临时表，则不定义针对哪个表解析该查询。嵌套存储过程同样可以创建与调用它的存储过程所创建的临时表同名的临时表。但是，为了对其进行修改以解析为在嵌套过程中创建的表，此表必须与调用过程创建的表具有相同的结构和列名。下面的示例说明了这一点。

CREATE PROCEDURE dbo.Test2
AS
  CREATE TABLE #t(x INT PRIMARY KEY);
  INSERT INTO #t VALUES (2);
  SELECT Test2Col = x FROM #t;
GO
 
CREATE PROCEDURE dbo.Test1
AS
  CREATE TABLE #t(x INT PRIMARY KEY);
  INSERT INTO #t VALUES (1);
  SELECT Test1Col = x FROM #t;
EXEC Test2;
GO
 
CREATE TABLE #t(x INT PRIMARY KEY);
INSERT INTO #t VALUES (99);
GO
 
EXEC Test1;
GO

官方文档中“同时有两个同名的临时表，则不定义针对哪个表解析该查询”这种阐述感觉还是让人有点迷糊。这里简单解释一下，在存储过程的嵌套调用中，允许外层过程和内层存储过程中存在相同名字的本地临时表，但是在内存过程中，如果要对其进行修改或解析（修改很好理解，例如新增索引，增加字段等这类DDL操作；关于解析，查询临时表，SQL中指定字段名，就需要解析resolve），那么此时这个临时表必须表结构一致，否则就会报错。官方文档，就是这么一句话，告诉你不行，但是具体原因没有说。那么我们不妨做一些推测，在存储过程的嵌套调用中，是否创建了两个本地临时表呢？有没有可能实际只创建了一个本地临时表呢？出现本地临时表重用的情况呢？那么我们简单验证一下，如下所示，这里可以判断实际上创建了两个本地临时表。并没有出现临时表重用的情况。

SELECT * 
FROM sys.dm_os_performance_counters
WHERE counter_name LIKE 'Temp Tables Creation Rate%';
 
EXEC PRC_TEST;
 
SELECT * 
FROM sys.dm_os_performance_counters
WHERE counter_name LIKE 'Temp Tables Creation Rate%';

当然你可以用下面SQL来进行验证，跟上面验证的结果一致。

IF EXISTS(SELECT 1 FROM sys.objects WHERE object_id= OBJECT_ID(N'dbo.PRC_SUB_TEST' ) AND OBJECTPROPERTY(object_id, 'IsProcedure')=1)
BEGIN
  DROP PROCEDURE dbo.PRC_SUB_TEST;
END
GO
 
 
CREATE PROCEDURE dbo.PRC_SUB_TEST
AS
BEGIN
  
  SELECT * FROM #tmp_test;
 
  SELECT * FROM tempdb.dbo.sysobjects WHERE name LIKE '#tmp_test%'
  CREATE TABLE #tmp_test(name VARCHAR(128));
 
  INSERT INTO #tmp_test
  SELECT name FROM sys.objects
  SELECT * FROM tempdb.dbo.sysobjects WHERE name LIKE '#tmp_test%'
  SELECT * FROM #tmp_test;
END
GO

然后我们来看看临时表的“作用域”，抱歉我用这么一个概念，官方文档是没有这个概念，这个只是我们思考的一个方面，细节方面没有必要抬杠。如下所示，我们修改一下存储过程

IF EXISTS(SELECT 1 FROM sys.objects WHERE object_id= OBJECT_ID(N'dbo.PRC_SUB_TEST' ) AND OBJECTPROPERTY(object_id, 'IsProcedure')=1)
BEGIN
  DROP PROCEDURE dbo.PRC_SUB_TEST;
END
GO
CREATE PROCEDURE dbo.PRC_SUB_TEST
AS
BEGIN
  
  SELECT * FROM #tmp_test;
  CREATE TABLE #tmp_test(name VARCHAR(128));
 
  INSERT INTO #tmp_test
  SELECT name FROM sys.objects
 
  SELECT * FROM #tmp_test;
END
GO

通过实验验证，我们发现外层存储过程的临时表在内层存储过程中有效，它的“作用域”是在内层存储过程的同名临时表创建之前，这个跟高级语言中的全局变量和局部变量作用域有点类似。

既然创建了两个本地临时表，那么为什么修改或解析的时候就会报错呢？个人的一个猜测是，优化器解析过后，在执行过程中，解析或修改的时候，数据库引擎无法判断或者代码里面没有这种逻辑去控制检索哪一个临时表。有可能是代码里面的一个缺陷亦或是某种逻辑原因导致。上述仅仅是个人的一个猜测、推理。如有不足或不对的地方，敬请指正。

参考资料：

https://docs.microsoft.com/zh-cn/previous-versions/sql/sql-server-2012/ms174979(v=sql.110)?redirectedfrom=MSDN

到此这篇关于浅析SQL Server的嵌套存储过程中使用同名的临时表怪像的文章就介绍到这了,更多相关SQL Server嵌套存储过程内容请搜索

更多SQL内容来自木庄网络博客

标签：SQL

返回前面的内容