sql server 循环_学习SQL：SQL Server循环简介

最新推荐文章于 2024-08-19 03:54:15 发布

culuo4781

最新推荐文章于 2024-08-19 03:54:15 发布

阅读量6.2k

点赞数 4

文章标签：数据库 python java mysql sql

原文链接：https://www.sqlshack.com/learn-sql-intro-to-sql-server-loops/

版权

sql server 循环

Loops are one of the most basic, still very powerful concepts in programming – the same stands for SQL Server loops. Today, we’ll give a brief info on how they function and introduce more complex concepts in upcoming articles of this series.

循环是编程中最基本但仍非常强大的概念之一-SQL Server循环也是如此。今天，我们将在本系列的后续文章中简要介绍它们的功能，并介绍更复杂的概念。

介绍 (Introduction )

We won’t use any data model in this article. Although this might sound weird (well, we’re working with a database, and there is no data!?), you’ll get the point. Since this is the intro article on SQL Server loops, we’ll cover basic concepts that you could combine with data to get the desired result.

本文不会使用任何数据模型。尽管这听起来很奇怪（嗯，我们正在使用数据库，但是没有数据！？），但您会明白的。由于这是有关SQL Server循环的介绍性文章，因此我们将介绍一些基本概念，您可以将这些基本概念与数据结合以获得所需的结果。

But, first of all – let’s see what loops are. Loops are the programming concept that enables us to write a few lines of code and repeat them until the loop condition holds.

但是，首先-让我们看看什么是循环。循环是一种编程概念，使我们能够编写几行代码并重复执行直到循环条件成立为止。

Almost all programming languages implement them, and we’ll usually meet these 3 types of loops:

几乎所有的编程语言都实现它们，我们通常会遇到以下三种类型的循环：

WHILE – While the loop condition is true, we’ll execute the code inside that loop
WHILE –当循环条件为true时，我们将在该循环内执行代码
DO … WHILE – Works in the same manner as the WHILE loop, but the loop condition is tested at the end of the loop. WHILE loops and DO … WHILE loops are very similar and could easily simulate each other. REPEAT … UNTIL (Pascal) is similar to DO … WHILE loop and the loop shall iterate until we “reach” that condition
DO…WHILE –以与WHILE循环相同的方式工作，但是循环条件在循环结束时进行测试。 WHILE循环和DO…WHILE循环非常相似，可以轻松地相互模拟。 REPEAT…UNTIL（Pascal）类似于DO…WHILE循环，循环将迭代直到我们“达到”该条件
FOR – By definition, this loop shall be used to run code inside the loop for the number of times you’ll exactly know before this loop starts. That is true in most cases, and such a loop (if available) should be used in such a manner (to avoid complicated code), but still, you could change the number of times it executes inside the loop
FOR –根据定义，此循环将用于在循环内运行代码，直到达到此循环开始之前您确切知道的次数。在大多数情况下是这样，应该以这种方式（避免复杂的代码）使用这种循环（如果可用），但是仍然可以更改其在循环中执行的次数

For us, the most important facts are:

对我们而言，最重要的事实是：

SQL Server implements the WHILE loop allowing us to repeat a certain code while the loop condition holds
SQL Server实现了WHILE循环，允许我们在循环条件成立时重复某些代码
If, for any reason, we need other loops, we can simulate them using a WHILE loop. We’ll show this later in the article
如果出于任何原因，我们需要其他循环，则可以使用WHILE循环对其进行仿真。我们将在文章稍后展示
Loops are rarely used, and queries do most of the job. Still, sometimes, loops prove to be very useful and can ease our life a lot
循环很少使用，查询完成大部分工作。有时候，循环仍然非常有用，可以极大地减轻我们的生活
You shouldn’t use loops for anything you like. They could cause serious performance issues, so be sure you know what you’re doing
您不应该对任何喜欢的东西使用循环。它们可能会导致严重的性能问题，因此请确保您知道自己在做什么

IF…ELSE IF和PRINT (IF … ELSE IF and PRINT)

Before we move to loops, we’ll present two SQL statements/commands – IF (IF … ELSE) and PRINT.

在进行循环之前，我们将介绍两个SQL语句/命令-IF（IF…ELSE）和PRINT。

IF statement is pretty simple, right after the IF keyword, you’ll put the condition. If that condition evaluates, the block of statements shall execute. If there is nothing else, that’s it.

IF语句非常简单，在IF关键字之后，您将放置条件。如果评估该条件，则应执行语句块。如果没有别的，就是这样。

You could also add ELSE to the IF statement, and this will result in the following – if the original condition wasn’t true, the code in the ELSE part should execute.

您还可以在IF语句中添加ELSE，这将导致以下结果–如果原始条件不成立，则应执行ELSE部分中的代码。

If we want to test multiple conditions, we’ll use, IF (1^st condition) … ELSE IF (2^nd condition) … ELSE IF (n-th condition) … ELSE. We’ll do exactly that in our example – just to show how it works in SQL Server.

如果要测试多个条件，我们将使用IF（ ^第一个条件）... ELSE IF（ ^第二个条件）... ELSE IF（第n个条件）... ELSE。我们将在示例中完全做到这一点-只是为了展示它在SQL Server中的工作方式。

But before that – the PRINT command. PRINT simply prints the text placed after that command. That is inside quotes, but you could also concatenate strings and use variables.

但在此之前– PRINT命令。 PRINT仅打印该命令之后的文本。那是在引号内，但是您也可以连接字符串并使用变量。

DECLARE @num1 INTEGER;
DECLARE @num2 INTEGER;
 
SET @num1 = 20;
SET @num2 = 30;
 
IF (@num1 > @num2)
  PRINT '1st number is greater than 2nd number.'
ELSE IF (@num2 > @num1)
  PRINT '2nd number is greater than 1st number.'
ELSE 
  PRINT 'The numbers are equal.';

SQL Server loops - IF, ELSE IF and PRINT

With the set of commands above, we’ve:

使用上面的命令集，我们已经：

Declared two variables and assigned values to them
声明两个变量并为其分配值
Used the IF … ELSE IF statement to test which variable is greater
使用IF…ELSE IF语句测试哪个变量更大

While this example is pretty simple (and it is obvious which number is greater), it’s a nice and simple way to demonstrate how IF … ELSE IF and PRINT work in SQL Server.

尽管此示例非常简单（显而易见，哪个数字更大），但这是演示IF…ELSE IF和PRINT如何在SQL Server中工作的一种好方法。

Now we’ll use statements from the previous example to show one more thing.

现在，我们将使用上一个示例中的语句来显示更多内容。

DECLARE @num1 INTEGER;
DECLARE @num2 INTEGER;
 
SET @num1 = 100;
SET @num2 = 30;
 
IF (@num1 > @num2) BEGIN
  PRINT '1st number is greater than 2nd number.'
  IF (@num1 > 75) 
    PRINT '1st number is greater than 75.' 
  ELSE IF (@num1 > 50) 
    PRINT '1st number is greater than 50.' 
  ELSE 
    PRINT '1st number is less than or equal to 50.';
END
ELSE IF (@num2 > @num1)
  PRINT '2nd number is greater than 1st number.'
ELSE 
  PRINT 'The numbers are equal.';

You can notice that we’ve placed the IF statement inside another IF statement. This is called nested IF. You could avoid it by using logical operators in the 1^st IF statement, but this way, the code is more readable).

您会注意到我们已经将IF语句放置在另一个IF语句中。这称为嵌套IF。您可以通过在第^1个 IF语句中使用逻辑运算符避免它，但这样一来，代码更易读）。

The goal of our code is to compare two numbers and also print if the first one is greater than 75, greater than 50, or less or equal to 50 (and only in case if the first number is greater than the second number).

我们的代码的目标是比较两个数字，并在第一个数字大于75，大于50或小于或等于50时打印（并且仅在第一个数字大于第二个数字的情况下）。

Similarly to the previous example, this code is not very “smart” but used to show the concept of nested IF.

与前面的示例类似，此代码不是很“智能”，但用于显示嵌套IF的概念。

SQL Server循环 (SQL Server Loops)

Now we’re ready to move to SQL Server loops. We have 1 loop at our disposal, and that is the WHILE loop. You might think why we don’t have others too, and the answer is that the WHILE loop will do the job. First, we’ll take a look at its syntax.

现在，我们准备移至SQL Server循环。我们有1个循环可供使用，这就是WHILE循环。您可能会想为什么我们也没有其他人，答案是WHILE循环会完成这项工作。首先，我们来看一下它的语法。

WHILE {condition holds}
BEGIN
{…do something…}
END;

{条件成立}
开始
{…做一点事…}
结束;

As you could easily conclude – while the loop conditions are true, we’ll execute all statements in the BEGIN … END block. Since we’re talking about SQL Server loops, we have all SQL statements at our disposal, and we can use them in the WHILE loop as we like.

您可以轻松得出结论–当循环条件为true时，我们将在BEGIN … END块中执行所有语句。由于我们在谈论SQL Server循环，因此我们可以使用所有SQL语句，并且可以根据需要在WHILE循环中使用它们。

Let’s now take a look at the first example of the WHILE loop.

现在让我们看一下WHILE循环的第一个示例。

DECLARE @i INTEGER;
SET @i = 1;
 
WHILE @i <= 10
BEGIN
   PRINT CONCAT('Pass...', @i);
   SET @i = @i + 1;
END;

Simulating FOR loop using WHILE loop in SQL Server

We’ve declared the variable @i and set it to 1. If the current value of the variable is <= 10, we’ll enter the loop body and execute statements. Each time we enter the body, we’ll increase @i by 1. That way, the value of variable @i will become 10 at some point, and that will prevent the loop from running over and over again.

我们已经声明了变量@i并将其设置为1。如果变量的当前值<= 10，我们将输入循环体并执行语句。每次进入主体时，我们都会将@i加1。这样，变量@i的值将在某个时刻变为10，这将防止循环反复运行。

Few things are important to mention here:

这里没有几件事很重要：

The @i variable counts how many times we were in the loop, and sometimes the word “counter”, shall be used for such variable. Naming the counter @i is also a good practice
@i变量计算循环中的次数，有时将“ counter”一词用于此类变量。命名@i也是一个好习惯
Since we know that this loop shall always execute exactly 10 times – @i starts from 1, increase by 1, and we’ll repeat that until @i becomes 11 – this is also the simulation of the FOR loop using the WHILE loop
因为我们知道该循环将始终准确执行10次-@i从1开始，增加1，我们将重复执行直到@i变为11-这也是使用WHILE循环对FOR循环的模拟
It’s always important to be sure that the loop condition won’t always be true. If the loop condition always holds, the loop will be infinite, and, in most cases, we don’t want that (especially in the database)
确保循环条件不会始终为真始终很重要。如果循环条件始终成立，则循环将是无限的，并且在大多数情况下，我们不希望这样做（尤其是在数据库中）

Hint: Infinite loops are rarely used in programming. One such case is when we want to wait for a signal (something) to happen. We’ll wait using the loop that never ends. While this is very useful in programming, using such a loop in databases would not be a smart move (we’ll impact performance, and the whole point of databases is to get data out of it as fast as possible).
提示：无限循环在编程中很少使用。 一种这样的情况是当我们要等待信号（某物）发生时。 我们将等待使用永不结束的循环。 尽管这在编程中非常有用，但是在数据库中使用这样的循环并不是明智之举（我们会影响性能，而数据库的全部目的是尽可能快地从中获取数据）。

Two keywords – BREAK and CONTINUE, are present in most programming languages. Same stands for SQL Server loops. The idea is the following:

大多数编程语言中都有两个关键字BREAK和CONTINUE 。相同代表SQL Server循环。这个想法如下：

When you encounter the BREAK keyword in the loop, you simply disregard all statements until the end of the loop (don’t execute any) and exit the loop (not going to the next step, even if the loop condition holds)
当您在循环中遇到BREAK关键字时，您只需忽略所有语句，直到循环结束（不执行任何语句）并退出循环（即使循环条件成立，也不进行下一步）
The CONTINUE acts similar to BREAK – it disregards all statements until the end of the loop, but then continues with the loop
CONTINUE的行为类似于BREAK –忽略所有语句直到循环结束，但随后继续循环

DECLARE @i INTEGER;
SET @i = 1;
 
WHILE @i <= 10
BEGIN
   PRINT CONCAT('Pass...', @i);
   IF @i = 9 BREAK;
   SET @i = @i + 1;
END;

BREAK command inside the SQL Server WHILE loop

You can notice that placing a BREAK in the loop resulted that we exited the loop in the 9^th pass.

您会注意到，在循环中放置BREAK会导致我们在第9 ^次通过中退出循环。

DECLARE @i INTEGER;
SET @i = 1;
 
WHILE @i <= 10
BEGIN
   PRINT CONCAT('Pass...', @i);
   IF @i = 9 CONTINUE;
   SET @i = @i + 1;
END;

SQL Server loops - CONTINUE command inside the SQL Server WHILE loop

The code above results with an infinite loop. The reason for that is that when @i becomes 9, we’ll CONTINUE the loop, and @i shall never have the value 10. Since this is an infinite loop, it will just spend resources on the server without doing anything. We can terminate the query by clicking on the “stop” button.

上面的代码产生无限循环。这样做的原因是，当@i变为9时，我们将继续循环，而@i永远不会具有值10。由于这是一个无限循环，因此它只会在服务器上花费资源而无需执行任何操作。我们可以通过单击“停止”按钮来终止查询。

CONTINUE command inside the SQL Server WHILE loop

After clicking on the stop button, you can notice that the loop did something, printed numbers 1 to 8, and number 9 as many times as it happened before we canceled the query.

单击停止按钮后，您会注意到循环执行了一些操作，打印出的数字1到8，以及数字9达到了取消查询之前的次数。

SQL Server循环和日期 (SQL Server Loops and Dates)

So far, we’ve covered the basics and how SQL Server loops function and how we combine statements like IF and PRINT with loops. Now we’ll use loops to do something useful. We want to print all dates between the two given dates.

到目前为止，我们已经介绍了基础知识以及SQL Server循环的功能以及如何将IF和PRINT等语句与循环结合在一起。现在，我们将使用循环来做一些有用的事情。我们要打印两个给定日期之间的所有日期。

DECLARE @date_start DATE;
DECLARE @date_end DATE;
DECLARE @loop_date DATE;
 
SET @date_start = '2020/11/11';
SET @date_end = '2020/12/12';
 
SET @loop_date = @date_start;
 
WHILE @loop_date <= @date_end
BEGIN
   PRINT @loop_date;
   SET @loop_date = DATEADD(DAY, 1, @loop_date);
END;

SQL Server loops - PRINTing dates in range using SQL Server WHILE loop

We’ve declared two variables, assigned date values to them. The only difference is that we’re using variable @loop_date. While we could do it without this variable, it’s a good practice to keep the original values (in our case, these are @date_start and @date_end) unchanged. In each step of the loop, we’ve printed the date and increased the “counter” by 1 day. This is useful, but still, we can’t use these dates in the query.

我们已经声明了两个变量，并为其分配了日期值。唯一的区别是我们正在使用变量@loop_date。尽管我们可以在没有此变量的情况下执行此操作，但最好还是保持原始值（在我们的示例中为@date_start和@date_end）保持不变。在循环的每个步骤中，我们都打印了日期，并将“计数器”增加了1天。这很有用，但仍然不能在查询中使用这些日期。

To do that, we’ll store values in the temporary table.

为此，我们将值存储在临时表中。

DROP TABLE IF EXISTS #dates;
CREATE TABLE #dates (
  report_date DATE
);
 
DECLARE @date_start DATE;
DECLARE @date_end DATE;
DECLARE @loop_date DATE;
 
SET @date_start = '2020/11/11';
SET @date_end = '2020/12/12';
 
SET @loop_date = @date_start;
 
WHILE @loop_date <= @date_end
BEGIN
   INSERT INTO #dates (report_date) VALUES (@loop_date);
   SET @loop_date = DATEADD(DAY, 1, @loop_date);
END;
 
SELECT * FROM #dates;
DROP TABLE IF EXISTS #dates;

SQL Server loops - INSERTing into temp table

We’ve dropped the temporary tables #dates (if it exists). After that, we’ve created a temporary table. The code used is almost the same as in the previous example. The difference is that, instead of using the PRINT command, at each step of the loop, we’ve inserted 1 row in the temporary table.

我们删除了临时表#dates（如果存在）。之后，我们创建了一个临时表。使用的代码与前面的示例几乎相同。区别在于，在循环的每一步中，我们没有使用PRINT命令，而是在临时表中插入了1行。

After the loop, we’ve selected from the temporary table as well dropped it.

循环之后，我们还从临时表中选择了它并删除了它。

结论 (Conclusion)

SQL Server loops are extremely powerful if you use them as they were intended to be used. Today, we’ve just scratched the surface, but all-important concepts were explained. In the upcoming article, we’ll show more complex examples and combine loops with other database objects.

如果您按预期使用它们，SQL Server循环将非常强大。今天，我们只是从头开始，但解释了所有重要的概念。在下一篇文章中，我们将显示更复杂的示例，并将循环与其他数据库对象结合在一起。