在 Synapse SQL 池中使用 T-SQL 循环Using T-SQL loops in Synapse SQL pool

本文包含的技巧涉及如何使用 T-SQL 循环和替换游标来进行 SQL 池解决方案开发。Included in this article are tips for SQL pool solution development using T-SQL loops and replacing cursors.

WHILE 循环的用途Purpose of WHILE loops

Synapse SQL 池支持对重复执行的语句块使用 WHILE 循环。Synapse SQL pool supports the WHILE loop for repeatedly executing statement blocks. 只要指定的条件都成立,或者在代码专门使用 BREAK 关键字终止循环之前,此 WHILE 循环将一直继续。This WHILE loop continues for as long as the specified conditions are true or until the code specifically terminates the loop using the BREAK keyword.

循环适合用于替换 SQL 代码中定义的游标。Loops are useful for replacing cursors defined in SQL code. 幸运的是,几乎所有以 SQL 代码编写的游标都是快进的只读变体。Fortunately, almost all cursors that are written in SQL code are of the fast forward, read-only variety. 因此,WHILE 循环是用于替换游标的绝佳替代方案。So, WHILE loops are a great alternative for replacing cursors.

在 Synapse SQL 池中替换游标Replacing cursors in Synapse SQL pool

但是,在一头扎进去之前,你应该问自己以下问题:“是否可以重写此游标以使用基于集的操作?”However, before diving in head first you should ask yourself the following question: "Could this cursor be rewritten to use set-based operations?"

在许多情况下,答案是肯定的,通常这也是最佳方法。In many cases, the answer is yes and is frequently the best approach. 基于集的操作的执行速度通常比迭代性的逐行方法要快得多。A set-based operation often performs faster than an iterative, row by row approach.

可以轻松使用循环构造来替换快进只读游标。Fast forward read-only cursors can be easily replaced with a looping construct. 下面是一个简单的示例。The following example is a simple one. 此代码示例更新数据库中每个表的统计信息。This code example updates the statistics for every table in the database. 通过迭代循环中的表,每个命令将依次执行。By iterating over the tables in the loop, each command executes in sequence.

首先,创建一个临时表,其中包含用于标识各个语句的唯一行号:First, create a temporary table containing a unique row number used to identify the individual statements:

CREATE TABLE #tbl
WITH
( DISTRIBUTION = ROUND_ROBIN
)
AS
SELECT  ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS Sequence
,       [name]
,       'UPDATE STATISTICS '+QUOTENAME([name]) AS sql_code
FROM    sys.tables
;

其次,初始化执行循环所需的变量:Second, initialize the variables required to perform the loop:

DECLARE @nbr_statements INT = (SELECT COUNT(*) FROM #tbl)
,       @i INT = 1
;

现在,每次对一个语句执行一次循环:Now loop over statements executing them one at a time:

WHILE   @i <= @nbr_statements
BEGIN
    DECLARE @sql_code NVARCHAR(4000) = (SELECT sql_code FROM #tbl WHERE Sequence = @i);
    EXEC    sp_executesql @sql_code;
    SET     @i +=1;
END

最后,将第一个步骤创建的临时表删除Finally drop the temporary table created in the first step

DROP TABLE #tbl;

后续步骤Next steps

有关更多开发技巧,请参阅 开发概述For more development tips, see development overview.