sql中变量用法_SQL变量：基础和用法

admin管理员组

文章数量:1530085

sql中变量用法

In this article, we will learn the notions and usage details of the SQL variable. In SQL Server, local variables are used to store data during the batch execution period. The local variables can be created for different data types and can also be assigned values. Additionally, variable assigned values can be changed during the execution period. The life cycle of the variable starts from the point where it is declared and has to end at the end of the batch. On the other hand, If a variable is being used in a stored procedure, the scope of the variable is limited to the current stored procedure. In the next sections, we will reinforce this theoretical information with various examples

在本文中，我们将学习SQL变量的概念和用法细节。 在SQL Server中，局部变量用于在批处理执行期间存储数据。 可以为不同的数据类型创建局部变量，也可以为其分配值。 此外，可以在执行期间更改变量分配的值。 变量的生命周期从声明它的点开始，并且必须在批处理结束时结束。 另一方面，如果在存储过程中使用了变量，则该变量的范围仅限于当前存储过程。 在下一部分中，我们将通过各种示例来加强这一理论信息

Note: In this article examples, the sample AdventureWorks database is used.

注意：在本文示例中，将使用示例AdventureWorks数据库。

SQL变量声明 (SQL Variable declaration)

The following syntax defines how to declare a variable:

以下语法定义了如何声明变量：

DECLARE { @LOCAL_VARIABLE data_type [ = value ] }

Now, let’s interpret the above syntax.

现在，让我们解释以上语法。

Firstly, if we want to use a variable in SQL Server, we have to declare it. The DECLARE statement is used to declare a variable in SQL Server. In the second step, we have to specify the name of the variable. Local variable names have to start with an at (@) sign because this rule is a syntax necessity. Finally, we defined the data type of the variable. The value argument which is indicated in the syntax is an optional parameter that helps to assign an initial value to a variable during the declaration. On the other hand, we can assign or replace the value of the variable on the next steps of the batch. If we don’t make any initial value assigned to a variable, it is initialized as NULL.

首先，如果要在SQL Server中使用变量，则必须对其进行声明。 DECLARE语句用于在SQL Server中声明变量。 在第二步中，我们必须指定变量的名称。 局部变量名称必须以at（@）符号开头，因为此规则是语法上的必要性。 最后，我们定义了变量的数据类型。 语法中指示的value参数是一个可选参数，有助于在声明期间为变量分配初始值。 另一方面，我们可以在批处理的后续步骤中分配或替换变量的值。 如果我们不给变量赋任何初始值，则将其初始化为NULL。

The following example will declare a variable whose name will be @VarValue and the data type will be varchar. At the same time, we will assign an initial value which is ‘Save Our Planet’:

以下示例将声明一个变量，其名称为@VarValue，数据类型为varchar。 同时，我们将分配一个初始值“ Save Our Planet”：

DECLARE @TestVariable AS VARCHAR(100)='Save Our Planet'
PRINT @TestVariable

给SQL变量赋值 (Assigning a value to SQL Variable )

SQL Server offers two different methods to assign values into variables except for initial value assignment. The first option is to use the SET statement and the second one is to use the SELECT statement. In the following example, we will declare a variable and then assign a value with the help of the SET statement:

SQL Server提供了两种不同的方法将值分配给变量（初始值分配除外）。 第一种选择是使用SET语句，第二种选择是使用SELECT语句。 在下面的示例中，我们将声明一个变量，然后借助SET语句分配一个值：

DECLARE @TestVariable AS VARCHAR(100)
SET @TestVariable = 'One Planet One Life'
PRINT @TestVariable

In the following example, we will use the SELECT statement in order to assign a value to a variable:

在以下示例中，我们将使用SELECT语句为变量赋值：

DECLARE @TestVariable AS VARCHAR(100)
SELECT @TestVariable = 'Save the Nature'
PRINT @TestVariable

Additionally, the SELECT statement can be used to assign a value to a variable from table, view or scalar-valued functions. Now, we will take a glance at this usage concept through the following example:

此外，SELECT语句可用于为表，视图或标量值函数中的变量分配值。 现在，通过以下示例，我们将大致了解一下这种用法概念：

DECLARE @PurchaseName AS NVARCHAR(50)
SELECT @PurchaseName = [Name]
FROM [Purchasing].[Vendor]
WHERE BusinessEntityID = 1492
PRINT @PurchaseName

As can be seen, the @PurchaseName value has been assigned from the Vendor table.

可以看出，@ PurchaseName值是从Vendor表分配的。

Now, we will assign a value to variable from a scalar-valued function:

现在，我们将为标量值函数的变量赋值：

DECLARE @StockVal AS INT
SELECT @StockVal=dbo.ufnGetStock(1)
SELECT @StockVal AS [VariableVal]

多个SQL变量 (Multiple SQL Variables )

For different cases, we may need to declare more than one variable. In fact, we can do this by declaring each variable individually and assigned a value for every parameter:

对于不同的情况，我们可能需要声明多个变量。 实际上，我们可以通过分别声明每个变量并为每个参数分配一个值来做到这一点：

DECLARE @Variable1 AS VARCHAR(100)
DECLARE @Variable2 AS UNIQUEIDENTIFIER
SET @Variable1 = 'Save Water Save Life'
SET @Variable2= '6D8446DE-68DA-4169-A2C5-4C0995C00CC1'
PRINT @Variable1
PRINT @Variable2

This way is tedious and inconvenient. However, we have a more efficient way to declare multiple variables in one statement. We can use the DECLARE statement in the following form so that we can assign values to these variables in one SELECT statement:

这种方式既乏味又不便。 但是，我们有一种更有效的方法来在一个语句中声明多个变量。 我们可以使用以下形式的DECLARE语句，以便我们可以在一个SELECT语句中为这些变量赋值：

DECLARE @Variable1 AS VARCHAR(100), @Variable2 AS UNIQUEIDENTIFIER
SELECT @Variable1 = 'Save Water Save Life' , @Variable2= '6D8446DE-68DA-4169-A2C5-4C0995C00CC1'
PRINT @Variable1
PRINT @Variable2

Also, we can use a SELECT statement in order to assign values from tables to multiple variables:

另外，我们可以使用SELECT语句将表中的值分配给多个变量：

DECLARE @VarAccountNumber AS NVARCHAR(15)
,@VariableName AS NVARCHAR(50)
SELECT @VarAccountNumber=AccountNumber , @VariableName=Name
FROM [Purchasing].[Vendor]
WHERE BusinessEntityID = 1492
PRINT @VarAccountNumber
PRINT @VariableName

有关SQL变量的有用提示 (Useful tips about the SQL Variables )

Tip 1: As we mentioned before, the local variable scope expires at the end of the batch. Now, we will analyze the following example of this issue:

提示1：如前所述，局部变量作用域在批处理结束时到期。 现在，我们将分析此问题的以下示例：

DECLARE @TestVariable AS VARCHAR(100)
SET @TestVariable = 'Think Green'
GO
PRINT @TestVariable

The above script generated an error because of the GO statement. GO statement determines the end of the batch in SQL Server thus @TestVariable lifecycle ends with GO statement line. The variable which is declared above the GO statement line can not be accessed under the GO statement. However, we can overcome this issue by carrying the variable value with the help of the temporary tables:

上面的脚本由于GO语句而产生错误。 GO语句确定SQL Server中批处理的结束，因此@TestVariable生命周期以GO语句行结束。 在GO语句行上方声明的变量不能在GO语句下访问。 但是，我们可以通过在临时表的帮助下携带变量值来克服此问题：

IF OBJECT_ID('tempdb..#TempTbl') IS NOT NULL DROP TABLE #TempTbl
DECLARE @TestVariable AS VARCHAR(100)
SET @TestVariable = 'Hello World'
SELECT @TestVariable AS VarVal INTO #TempTbl
GO
DECLARE @TestVariable AS VARCHAR(100)
SELECT @TestVariable = VarVal FROM #TempTbl
PRINT @TestVariable

Tip 2: Assume that, we assigned a value from table to a variable and the result set of the SELECT statement returns more than one row. The main issue at this point will be which row value is assigned to the variable. In this circumstance, the assigned value to the variable will be the last row of the resultset. In the following example, the last row of the resultset will be assigned to the variable:

技巧2：假设我们将表中的值分配给变量，并且SELECT语句的结果集返回多行。 此时的主要问题是将哪个行值分配给变量。 在这种情况下，为变量分配的值将是结果集的最后一行。 在以下示例中，结果集的最后一行将分配给变量：

SELECT AccountNumber 
FROM [Purchasing].[Vendor]
ORDER BY BusinessEntityID 
        
DECLARE @VarAccountNumber AS NVARCHAR(15)
SELECT @VarAccountNumber=AccountNumber 
FROM [Purchasing].[Vendor]
order by BusinessEntityID 
SELECT @VarAccountNumber AS VarValue

Tip 3: If the variable declared data types and assigned value data types are not matched, SQL Server makes an implicit conversion in the value assignment process, if it is possible. The lower precedence data type is converted to the higher precedence data type by the SQL Server but this operation may lead to data loss. For the following example, we will assign a float value to the variable but this variable data type has declared as an integer:

技巧3：如果变量声明的数据类型和赋值的数据类型不匹配，则SQL Server会在值分配过程中进行隐式转换（如果可能）。 SQL Server将较低优先级数据类型转换为较高优先级数据类型，但是此操作可能会导致数据丢失。 对于以下示例，我们将为该变量分配一个浮点值，但是此变量数据类型已声明为整数：

DECLARE @FloatVar AS FLOAT = 12312.1232
DECLARE @IntVar AS INT
SET @IntVar=@FloatVar
PRINT  @IntVar

结论 (Conclusion)

In this article, we have explored the concept of SQL variables from different perspectives, and we also learned how to define a variable and how to assign a value(s) to it.

在本文中，我们从不同的角度探讨了SQL变量的概念，并且还学习了如何定义变量以及如何为其分配值。

翻译自: https://www.sqlshack/sql-variables-basics-and-usage/

sql中变量用法

本文标签： 变量基础sql