SQL Server 存储过程语法教程

1. 什么是存储过程？

存储过程（Stored Procedure）是一种在 SQL Server 数据库中定义的可执行的、可重用的数据库对象。它包含了一组 SQL 语句和控制结构，用于执行特定的任务或操作。存储过程通常具有以下特点：

封装性：存储过程将一系列 SQL 语句封装在一个单一的命名单元中。这有助于组织和管理数据库逻辑，同时也提高了数据的安全性，因为用户无法直接访问存储过程中的 SQL 语句。
可重用性：存储过程可以在不同的地方和时间被多次调用。这使得相同的操作可以在多个地方使用，从而提高了代码的重用性。
性能优化：存储过程可以被 SQL Server 编译和优化，以提高执行效率。存储过程的执行计划可以被缓存，减少了重复编译的开销。
安全性：存储过程可以控制对数据库对象的访问权限。只有具有执行权限的用户或应用程序才能执行存储过程。
参数化：存储过程可以接受参数，从外部传递数据给存储过程，使其更加通用和灵活。
减少网络流量：通过将一组操作放在数据库内部执行，可以减少网络传输的数据量，提高应用程序性能。

总之，存储过程是 SQL Server 数据库管理和应用程序开发中的重要工具，能够提高性能、安全性和代码的可维护性。它们在执行常见的数据库操作和数据处理任务时非常有用。

2. 存储过程的创建、删除、更新

创建存储过程是使用 SQL Server 数据库的关键任务之一。存储过程是数据库对象，包含一组 SQL 语句和逻辑，它们可以在需要时被调用。以下是关于创建存储过程的步骤和示例。

创建存储过程的语法

在 SQL Server 中，创建存储过程的一般语法如下：

CREATE PROCEDURE ProcedureName
    @Parameter1 DataType,
    @Parameter2 DataType
AS
BEGIN
    -- 存储过程的主体逻辑
END;

CREATE PROCEDURE 语句用于声明你要创建一个存储过程。
ProcedureName 是存储过程的名称，你可以根据需要自定义名称。
@Parameter1 和 @Parameter2 是存储过程的参数，你可以定义零个或多个参数。
DataType 是参数的数据类型，例如 INT、VARCHAR 等。
AS 关键字用于标识存储过程的主体逻辑的开始。
BEGIN 和 END 用于包裹存储过程的实际操作。

参数的定义与传递

存储过程可以接受参数，这使得它们可以更通用和灵活。参数分为输入参数和输出参数：

输入参数：用于传递数据给存储过程，在存储过程内部使用。
输出参数：用于从存储过程中返回数据给调用方。

以下是一个示例，演示如何创建一个简单的存储过程：

CREATE PROCEDURE GetEmployee
    @EmployeeID INT
AS
BEGIN
    SELECT * FROM Employees WHERE EmployeeID = @EmployeeID;
END;

在这个示例中，我们创建了一个名为 GetEmployee 的存储过程，它接受一个输入参数 @EmployeeID，并根据这个参数返回相应的员工信息。

示例：创建一个简单的存储过程

以下是一个更详细的示例，展示如何创建一个简单的存储过程，它接受两个参数，执行一个查询，并返回结果：

CREATE PROCEDURE GetSalesData
    @StartDate DATE,
    @EndDate DATE
AS
BEGIN
    SELECT SalesDate, TotalSales
    FROM Sales
    WHERE SalesDate BETWEEN @StartDate AND @EndDate;
END;

在这个示例中，存储过程名为 GetSalesData，它接受两个日期参数 @StartDate 和 @EndDate，并返回在指定日期范围内的销售数据。

创建存储过程是 SQL Server 数据库管理和应用程序开发中的基本技能之一。它们可以用于执行各种数据库操作，从简单的查询到复杂的数据处理。存储过程的创建是数据库开发的重要一步，使你能够更好地组织和管理数据库逻辑。

当你创建存储过程后，你可能需要对其进行修改或删除。这两个操作同样在数据库开发中非常重要。让我们看看如何进行存储过程的修改和删除。

修改存储过程

要修改现有的存储过程，你可以使用 ALTER PROCEDURE 语句，并提供新的存储过程定义。以下是一个示例，演示如何修改存储过程的参数：

ALTER PROCEDURE GetSalesData
    @StartDate DATE,
    @EndDate DATE,
    @Category VARCHAR(50)
AS
BEGIN
    SELECT SalesDate, TotalSales
    FROM Sales
    WHERE SalesDate BETWEEN @StartDate AND @EndDate
    AND Category = @Category;
END;

在此示例中，我们添加了一个新的参数 @Category 到存储过程 GetSalesData，以便在查询中使用。这使你可以扩展存储过程的功能，以满足新的需求。

删除存储过程

要删除存储过程，可以使用 DROP PROCEDURE 语句，后跟存储过程的名称。以下是一个示例：

1	`DROP PROCEDURE GetSalesData;`

这将永久删除名为 GetSalesData 的存储过程。请小心使用此命令，因为它无法恢复已删除的存储过程。在删除存储过程之前，确保你不再需要它，或者在删除之前创建备份。

修改和删除存储过程是数据库维护和开发中的常见任务，它们使你能够适应数据库结构和业务需求的变化。通过使用 ALTER PROCEDURE 和 DROP PROCEDURE 语句，你可以轻松管理存储过程，以确保数据库的有效性和一致性。

3. 存储过程的执行

存储过程可以通过多种方式执行，包括直接执行和从应用程序代码中调用。下面将介绍不同的执行方式，包括不指定变量名直接传值的方式。

直接执行存储过程

在 SQL Server 中，你可以使用 EXEC 语句来直接执行存储过程。以下是一些执行方式的示例：

方式1：指定参数名和值

1	`EXEC MyStoredProcedure @Parameter1 = Value1, @Parameter2 = Value2;`

在这种方式下，参数名和值是显式指定的。

方式2：只传递值

如果存储过程的参数在定义时没有指定默认值，你可以只传递参数的值，无需指定参数名。

1	`EXEC MyStoredProcedure Value1, Value2;`

这种方式下，SQL Server 会按照参数在存储过程中的顺序依次匹配值。要小心使用这种方式，确保参数的顺序正确。

方式3：使用参数名传递值

你也可以直接使用参数名传递值，无需显式指定参数名。这种方式在参数顺序变化时尤其有用。

1	`EXEC MyStoredProcedure Value1, @Parameter2 = Value2;`

从应用程序代码中调用存储过程

除了直接执行存储过程，你还可以从应用程序代码中调用存储过程。这通常使用数据库连接库（如 ADO.NET、Entity Framework 等）完成。以下是一个示例：

使用 C# 和 ADO.NET

using System.Data;
using System.Data.SqlClient;

string connectionString = "your_connection_string";
int parameter1 = 42;
string parameter2 = "SomeValue";

using (SqlConnection connection = new SqlConnection(connectionString))
{
    connection.Open();

    using (SqlCommand cmd = new SqlCommand("MyStoredProcedure", connection))
    {
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@Parameter1", parameter1);
        cmd.Parameters.AddWithValue("@Parameter2", parameter2);

        cmd.ExecuteNonQuery();
    }
}

在这个示例中，我们使用 C# 和 ADO.NET 从应用程序代码中调用了名为 MyStoredProcedure 的存储过程，并传递了参数的值。这种方式在开发应用程序时非常常见，因为它允许你通过应用程序与数据库交互。

无论你是在 SQL 查询工具中直接执行存储过程，还是从应用程序代码中调用它，存储过程的执行方式取决于具体需求和编程环境。不同的方式提供了不同的灵活性和控制选项，以满足不同的应用场景。

4. 存储过程的参数

存储过程是用于执行数据库操作的重要工具，它可以接受参数以便进行不同的数据处理。存储过程的参数分为输入参数和输出参数，它们使存储过程更加通用和灵活。

输入参数

输入参数用于将数据传递给存储过程，供其内部使用。在存储过程中，输入参数的值可以被引用和操作，但无法被更改。以下是定义输入参数的示例：

CREATE PROCEDURE MyStoredProcedure
    @InputParameter1 DataType,
    @InputParameter2 DataType
AS
BEGIN
    -- 存储过程的主体逻辑
END;

@InputParameter1 和 @InputParameter2 是输入参数的名称。
DataType 是参数的数据类型，可以是整数、字符、日期等。

在存储过程内部，你可以使用这些输入参数执行各种操作，例如查询数据库、更新记录或计算结果。

输出参数

输出参数用于从存储过程中返回数据给调用方。输出参数可以在存储过程执行后包含特定的结果值。以下是定义输出参数的示例：

CREATE PROCEDURE MyStoredProcedure
    @InputParameter DataType,
    @OutputParameter DataType OUTPUT
AS
BEGIN
    -- 存储过程的主体逻辑
    SET @OutputParameter = SomeValue;
END;

@OutputParameter 带有 OUTPUT 关键字，表示它是一个输出参数。
存储过程内部可以使用 SET 语句来设置输出参数的值，以便在存储过程执行后返回给调用方。

使用参数传递数据

存储过程参数的主要目的是传递数据。以下是如何在执行存储过程时传递参数的示例：

在 EXEC 语句中传递参数值

1	`EXEC MyStoredProcedure @InputParameter1 = Value1, @InputParameter2 = Value2;`

在这里，Value1 和 Value2 是要传递给存储过程的具体值。

从应用程序代码中调用存储过程

在应用程序代码中，你可以使用数据库连接库（如 ADO.NET）来为存储过程的参数赋值，然后执行存储过程。以下是一个示例：

using System.Data;
using System.Data.SqlClient;

string connectionString = "your_connection_string";
int inputParameter = 42;

using (SqlConnection connection = new SqlConnection(connectionString))
{
    connection.Open();

    using (SqlCommand cmd = new SqlCommand("MyStoredProcedure", connection))
    {
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@InputParameter", inputParameter);

        // 执行存储过程
        cmd.ExecuteNonQuery();

        // 获取输出参数的值（如果有输出参数）
        var outputValue = cmd.Parameters["@OutputParameter"].Value;
    }
}

在应用程序代码中，你可以设置输入参数的值，并在存储过程执行后检索输出参数的值（如果有输出参数）。

存储过程参数使数据库操作更加灵活和通用。它们可以用于接受外部数据、执行特定的操作，并将结果返回给调用方，这是在数据库管理和应用程序开发中非常常见的需求。

5. 错误处理与异常

错误处理是存储过程开发中的一个重要方面，因为它确保了在执行期间出现问题时如何处理异常情况。SQL Server 提供了一些机制来处理错误和异常，包括 TRY...CATCH 块和 RAISEERROR 函数。

TRY…CATCH 块

TRY...CATCH 块是一种用于处理异常的结构，允许你捕获并处理执行存储过程期间出现的错误。以下是 TRY...CATCH 块的基本结构：

BEGIN TRY
    -- 可能引发错误的代码
END TRY
BEGIN CATCH
    -- 处理错误的代码
END CATCH

在 TRY 块中，你可以放置可能引发错误的代码。如果在 TRY 块中出现了错误，控制将转移到相应的 CATCH 块，其中可以处理错误，例如记录错误信息或执行特定的操作。

RAISEERROR 函数

RAISEERROR 函数用于引发自定义错误消息，通常与 TRY...CATCH 块一起使用。它的语法如下：

1	`RAISEERROR (MessageString, Severity, State);`

MessageString 是要显示的错误消息。
Severity 是错误的严重性级别，通常在 0 到 25 之间，越高表示错误越严重。
State 是错误状态码，通常用于标识错误的来源。

以下是一个使用 TRY...CATCH 块和 RAISEERROR 函数的示例：

BEGIN TRY
    -- 尝试执行可能引发错误的操作
    SELECT 1 / 0; -- 这会引发除零错误
END TRY
BEGIN CATCH
    -- 处理错误
    DECLARE @ErrorMessage NVARCHAR(4000) = ERROR_MESSAGE();
    DECLARE @ErrorSeverity INT = ERROR_SEVERITY();
    DECLARE @ErrorState INT = ERROR_STATE();

    -- 记录错误信息
    RAISEERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);
END CATCH

在这个示例中，TRY...CATCH 块尝试执行一个除零操作，它会引发一个错误。CATCH 块捕获错误，记录错误信息，然后使用 RAISEERROR 函数引发自定义错误消息。

错误处理和异常处理是确保存储过程能够应对执行期间出现的问题的关键部分。通过使用 TRY...CATCH 块和 RAISEERROR 函数，你可以优雅地处理错误，记录相关信息，同时确保存储过程能够在出现问题时继续执行或提供有关问题的有用信息。

6. 存储过程中的控制流程

存储过程不仅仅是一系列 SQL 语句的集合，还可以包含控制流程逻辑，这意味着你可以在存储过程中使用条件、循环和跳转等结构来实现更复杂的逻辑。以下是存储过程中常用的控制流程结构：

1. IF…ELSE 语句

IF...ELSE 语句用于根据条件执行不同的代码块。它的基本语法如下：

IF Condition
BEGIN
    -- 在条件为真时执行的代码
END
ELSE
BEGIN
    -- 在条件为假时执行的代码
END

以下是一个示例，演示如何在存储过程中使用 IF...ELSE：

IF @IsAdmin = 1
BEGIN
    -- 执行管理员操作
END
ELSE
BEGIN
    -- 执行普通用户操作
END

2. WHILE 循环

WHILE 循环允许你多次执行一组语句，只要给定条件为真。它的语法如下：

WHILE Condition
BEGIN
    -- 循环体，可以包含多个语句
END

以下是一个示例，演示如何在存储过程中使用 WHILE 循环：

DECLARE @Counter INT = 1;
WHILE @Counter <= 10
BEGIN
    -- 执行循环内的操作
    SET @Counter = @Counter + 1;
END

3. GOTO 语句和标签的使用

GOTO 语句和标签的组合允许你在存储过程中跳转到带有标签的代码块，通常用于处理错误、异常或特定条件下的控制流程。冒号 : 用于定义标签，标识代码块的起始点。虽然强大，但也潜在危险，应小心使用。以下是示例和用法：

-- 标签的使用
ErrorHandling:
-- 错误处理逻辑

-- 在某个条件下跳转到标签
IF @SomeCondition = 1
BEGIN
    -- 执行某些操作
END
ELSE
BEGIN
    GOTO ErrorHandling; -- 跳转到错误处理代码块
END

在上述示例中，ErrorHandling 后的冒号 : 标识了一个标签，用于标识错误处理代码块的起始点。当某个条件不满足时，通过 GOTO 语句，控制流程会跳转到标签 ErrorHandling 处执行相应的错误处理逻辑。标签和冒号的使用允许你在存储过程中灵活地管理控制流程，特别是在处理异常情况时非常有用。

4. RETURN 语句

RETURN 语句用于从存储过程中返回结果。它可以用于提前退出存储过程执行，也可以用于返回值。以下是一个示例：

IF @SomeCondition = 1
BEGIN
    -- 执行某些操作
    RETURN 0; -- 返回成功代码
END

5. 使用逻辑运算符

你可以在存储过程中使用逻辑运算符（如 AND 和 OR）以及括号来构建复杂的条件表达式，以便更灵活地控制流程。这使你能够编写更复杂的条件和逻辑，以满足具体需求。以下是一个示例：

IF (@IsAdmin = 1 AND @HasPermission = 1) OR @UserRole = 'SuperAdmin'
BEGIN
    -- 执行某些操作
END
ELSE
BEGIN
    -- 执行其他操作
END

这些控制流程结构使你能够编写更复杂的存储过程，可以根据条件执行不同的操作，循环执行操作，或者跳转到其他部分来处理不同的情况。但请小心使用，确保代码清晰可维护，以避免混乱和错误。

7. 返回数据

在存储过程中，有多种方式可以返回数据给调用方或客户端应用程序，具体取决于存储过程的设计和需求。以下是一些常见的方法：

1. 使用 SELECT 语句

最常见的方式是通过在存储过程中使用 SELECT 语句来返回数据集。这些数据可以被存储过程的调用方或客户端应用程序检索和处理。

CREATE PROCEDURE GetEmployees
AS
BEGIN
    SELECT EmployeeID, FirstName, LastName FROM Employees;
END;

在这个示例中，存储过程 GetEmployees 使用 SELECT 语句返回了员工表中的数据。

2. 使用 OUTPUT 参数

你可以在存储过程中定义输出参数，然后通过这些参数将数据返回给调用方。

CREATE PROCEDURE GetEmployeeName
    @EmployeeID INT,
    @EmployeeName NVARCHAR(50) OUTPUT
AS
BEGIN
    SELECT @EmployeeName = FirstName + ' ' + LastName
    FROM Employees
    WHERE EmployeeID = @EmployeeID;
END;

在这个示例中，存储过程 GetEmployeeName 接受一个输入参数 @EmployeeID，并将查询结果存储在输出参数 @EmployeeName 中。

3. 使用表变量或临时表

你可以在存储过程中创建表变量或临时表，将数据插入到这些表中，然后返回表的内容。这对于返回多个数据行或多个结果集非常有用。

CREATE PROCEDURE GetProductsByCategory
    @CategoryID INT
AS
BEGIN
    DECLARE @Products TABLE (
        ProductID INT,
        ProductName NVARCHAR(100)
    );

    INSERT INTO @Products
    SELECT ProductID, ProductName
    FROM Products
    WHERE CategoryID = @CategoryID;

    SELECT * FROM @Products;
END;

在这个示例中，存储过程 GetProductsByCategory 创建了一个表变量 @Products，将符合条件的产品数据插入到表中，然后通过 SELECT 语句返回表的内容。

4. 使用 XML 或 JSON

如果需要返回复杂的数据结构，你可以将结果转化为 XML 或 JSON 格式，然后将其返回给调用方。SQL Server 提供了内置函数和方法来处理 XML 和 JSON 数据。

CREATE PROCEDURE GetEmployeesAsJSON
AS
BEGIN
    SELECT EmployeeID, FirstName, LastName
    FROM Employees
    FOR JSON AUTO;
END;

在这个示例中，存储过程 GetEmployeesAsJSON 使用 FOR JSON 子句将查询结果以 JSON 格式返回。

使用 RETURN 语句

RETURN 语句通常用于从存储过程中返回一个整数值，表示存储过程的执行状态或结果。这个返回值可以用于指示存储过程的成功或失败，或者返回其他自定义信息。

以下是一个示例，演示如何在存储过程中使用 RETURN 语句：

CREATE PROCEDURE CalculateSum
    @Value1 INT,
    @Value2 INT
AS
BEGIN
    DECLARE @Sum INT;

    SET @Sum = @Value1 + @Value2;

    IF @Sum > 100
    BEGIN
        -- 存储过程执行成功
        RETURN 0;
    END
    ELSE
    BEGIN
        -- 存储过程执行失败
        RETURN -1;
    END
END;

在这个示例中，存储过程 CalculateSum 接受两个整数参数，计算它们的和，并根据计算结果使用 RETURN 语句返回不同的值。如果和大于 100，存储过程返回 0，表示成功；否则，返回 -1，表示失败。

应用程序或客户端应用程序可以检查存储过程的返回值，以确定执行是否成功，然后根据需要采取进一步的操作。 RETURN 语句通常用于返回存储过程的状态或执行结果，但不用于返回数据集或查询结果。如果需要返回数据集，通常使用 SELECT 语句或输出参数。

无论使用哪种方法，存储过程可以根据具体需求返回不同类型的数据，包括单个值、数据集、XML、JSON 或其他自定义数据结构。数据的返回方式应根据应用程序的要求和数据交互的复杂性来选择。

8. 表值函数和标量函数

在 SQL Server 中，表值函数和标量函数是两种特殊类型的用户定义函数，它们允许你封装和重用 SQL 逻辑，以便在查询中使用。这些函数可以在存储过程中或 SQL 查询中发挥关键作用。

表值函数（Table-Valued Functions）

表值函数是一种用户定义函数，它返回一个表作为结果。这允许你将表值函数的结果集嵌套到查询中，类似于从表中检索数据。有两种类型的表值函数：

内联表值函数（Inline Table-Valued Function）：这类函数返回一个结果集，并可以像表一样直接参与查询的 FROM 子句中。内联表值函数的结果集在查询中扮演了一个虚拟表的角色。

CREATE FUNCTION GetEmployeesByDepartment(@DepartmentID INT)
RETURNS TABLE
AS
RETURN (
    SELECT EmployeeID, FirstName, LastName
    FROM Employees
    WHERE DepartmentID = @DepartmentID
);

这个内联表值函数接受一个部门 ID，返回该部门的所有员工。你可以在查询中像使用表一样调用它：

1	`SELECT * FROM GetEmployeesByDepartment(1);`

多语句表值函数（Multi-Statement Table-Valued Function）：这类函数使用 BEGIN...END 块内的多条语句来生成结果集。它们允许更复杂的逻辑，但不能像内联表值函数那样直接嵌套在查询中。

CREATE FUNCTION GetHighSalaryEmployees()
RETURNS @HighSalaryEmployees TABLE
(
    EmployeeID INT,
    FirstName NVARCHAR(50),
    LastName NVARCHAR(50)
)
AS
BEGIN
    INSERT INTO @HighSalaryEmployees
    SELECT EmployeeID, FirstName, LastName
    FROM Employees
    WHERE Salary > 50000;
    RETURN;
END;

标量函数（Scalar Functions）

标量函数是一种用户定义函数，它返回单个标量值作为结果，例如整数、字符串或日期。标量函数通常用于计算、转换或处理数据。以下是一个标量函数的示例：

CREATE FUNCTION CalculateAge(@BirthDate DATE)
RETURNS INT
AS
BEGIN
    DECLARE @Age INT;
    SET @Age = DATEDIFF(YEAR, @BirthDate, GETDATE());
    RETURN @Age;
END;

这个标量函数接受出生日期，返回计算出的年龄。

1	`SELECT dbo.CalculateAge('1990-01-01'); -- 使用标量函数计算年龄`

表值函数和标量函数可以在 SQL 查询中使用，以实现更灵活的数据操作和逻辑封装。它们可以用于简化查询、减少重复性代码，提高可维护性，并提供更强大的数据操作功能。

当谈到表值函数、标量函数以及存储过程时，有一些关键区别需要理解。下面我将为你提供示例，并解释这三者之间的不同之处。

与存储过程的区别

下面是一个表格，描述了函数和存储过程之间的关键区别：

特征	函数	存储过程
返回值	返回一个值或表	通常不返回值，可以执行一系列操作
用途	通常用于封装计算、数据处理或转换逻辑	用于执行数据操作、事务管理、错误处理等
执行方式	可以在查询中直接使用，嵌套在查询中	通常需要使用 `EXEC` 或 `CALL` 显式执行
事务	可以在事务中调用，但通常不引起事务的隐式启动或提交	可以包含事务管理逻辑，例如启动、提交或回滚事务
修改数据库	通常不用于直接修改数据库中的数据	通常用于执行更新、插入、删除等操作，可以修改数据库中的数据
数据操作类型	可能包括 `SELECT` 查询、计算、数据处理等	包括更新、插入、删除、错误处理等数据操作

这个表格强调了函数和存储过程之间的重要区别，尤其是在返回值、用途和执行方式方面。选择使用哪种方式应根据具体需求和任务的性质来决定。

9. 事务管理

在 SQL Server 中，事务管理是数据库管理的关键部分。事务是一组 SQL 操作，要么全部成功执行，要么全部失败并回滚到之前的状态。这确保了数据库的一致性和可靠性。SQL Server 提供了多种方法来管理事务，包括显式事务和隐式事务。

显式事务

显式事务是由开发者显式指定和控制的事务。它们通常用于复杂的数据库操作，确保多个 SQL 语句要么全部执行成功，要么全部失败。显式事务通常使用以下关键字来定义和管理：

BEGIN TRANSACTION：开始一个新事务。
COMMIT：提交当前事务，如果一切正常，事务中的更改将保存到数据库。
ROLLBACK：回滚当前事务，如果出现错误，事务中的更改将被撤销。

以下是一个示例，演示如何使用显式事务管理：

BEGIN TRANSACTION;

UPDATE Accounts SET Balance = Balance - 100 WHERE AccountID = 1;
INSERT INTO Transactions (AccountID, Amount) VALUES (1, -100);

-- 检查其他条件，如果不符合条件则回滚事务
IF @SomeCondition = 0
BEGIN
    ROLLBACK;
    PRINT 'Transaction rolled back.';
END
ELSE
BEGIN
    COMMIT;
    PRINT 'Transaction committed.';
END;

隐式事务

隐式事务是在不明确声明事务的情况下自动启动的事务。通常，每个 SQL 语句都在自己的事务内运行。如果 SQL Server 检测到错误，它将回滚当前事务。如果一切正常，它将自动提交当前事务。

以下是一个示例，演示隐式事务的工作方式：

1
2
3

-- 如果任何语句失败，SQL Server 将自动回滚事务
UPDATE Employees SET Salary = Salary + 500 WHERE Department = 'HR';
INSERT INTO Transactions (EmployeeID, Amount) VALUES (123, 500);

保存点（Savepoints）

SQL Server 允许你在事务中设置保存点，以便在发生错误时回滚到特定的保存点，而不是回滚整个事务。这对于在事务中执行多个步骤并希望能够部分回滚时非常有用。

BEGIN TRANSACTION;

-- 步骤 1
UPDATE Products SET Quantity = Quantity - 10 WHERE ProductID = 1;

-- 设置保存点
SAVE TRANSACTION Step1Complete;

-- 步骤 2
INSERT INTO Orders (ProductID, Quantity) VALUES (1, 10);

-- 检查条件，如果不符合条件则回滚到保存点
IF @SomeCondition = 0
BEGIN
    ROLLBACK TRANSACTION Step1Complete;
    PRINT 'Rolled back to Step 1.';
END
ELSE
BEGIN
    COMMIT;
    PRINT 'Transaction committed.';
END;

在这个示例中，如果某些条件不满足，事务将回滚到保存点 Step1Complete，仅回滚第一步骤的更改。

事务管理是确保数据库操作的一致性和可靠性的重要组成部分。通过使用显式或隐式事务以及保存点，你可以有效地管理事务，以适应不同的业务需求。

10. 游标（Cursor）

游标（Cursor）是一种数据库对象，用于在结果集中逐行浏览数据，通常用于对数据进行逐行处理。以下是游标的生命周期：

阶段	操作	示例
游标声明和定义	声明游标并定义要检索的数据集	DECLARE cursor_name CURSOR FOR SELECT column1, column2 FROM table
游标打开	打开游标以准备开始数据检索	OPEN cursor_name
数据检索	逐行检索数据并存储在变量中	FETCH NEXT FROM cursor_name INTO @var1, @var2
处理操作	处理从游标中检索到的数据	在此执行数据操作和逻辑
游标关闭	关闭游标以释放数据库资源	CLOSE cursor_name
游标释放	释放游标以清除游标定义和声明	DEALLOCATE cursor_name

下面是一个示例存储过程，使用游标遍历表中的数据：

CREATE PROCEDURE ProcessDataWithCursor
AS
BEGIN
    DECLARE @EmployeeID INT;
    DECLARE @FirstName NVARCHAR(50);
    DECLARE @LastName NVARCHAR(50);

    -- 声明和打开游标
    DECLARE EmployeeCursor CURSOR FOR
    SELECT EmployeeID, FirstName, LastName
    FROM Employees;

    OPEN EmployeeCursor;

    -- 获取第一行数据
    FETCH NEXT FROM EmployeeCursor INTO @EmployeeID, @FirstName, @LastName;

    -- 开始循环处理数据
    WHILE @@FETCH_STATUS = 0
    BEGIN
        -- 在这里进行数据处理，可以执行任何操作
        PRINT 'Employee: ' + @FirstName + ' ' + @LastName;

        -- 获取下一行数据
        FETCH NEXT FROM EmployeeCursor INTO @EmployeeID, @FirstName, @LastName;
    END;

    -- 关闭和释放游标
    CLOSE EmployeeCursor;
    DEALLOCATE EmployeeCursor;
END;

这个存储过程使用游标来遍历名为 Employees 的表中的员工数据。在游标内部，它获取每一行的员工数据，并可以对每一行数据执行自定义操作。游标在循环结束后被关闭和释放。

需要注意的是，使用游标可能会导致性能问题，特别是在处理大量数据时。因此，应该仔细考虑是否有更有效的方法来执行数据操作，例如使用集合操作（如 UPDATE 或 INSERT INTO ... SELECT）来替代游标。

11. 系统变量

以下是这些常见系统变量的用法：

@@ERROR：用于检查最近一次 SQL 操作是否引发了错误。通常与 TRY...CATCH 块结合使用，以捕获和处理错误。

BEGIN TRY
    -- 一些 SQL 操作
END TRY
BEGIN CATCH
    -- 处理错误
    IF @@ERROR <> 0
    BEGIN
        -- 执行回滚等操作
        ROLLBACK;
        -- 记录错误信息
        PRINT 'An error occurred: ' + CAST(@@ERROR AS NVARCHAR(10));
    END
END CATCH

@@IDENTITY：用于获取最近插入的标识列的值，通常在 INSERT 操作之后使用。
1
2
INSERT INTO Customers (Name) VALUES ('John Doe'); SELECT @@IDENTITY;

@@ROWCOUNT：用于确定最近的 SQL 语句影响了多少行，通常用于检查操作是否成功或获取受影响的行数。

1
2
3

DELETE FROM Products WHERE Price < 10.00;
IF @@ROWCOUNT > 0
    PRINT 'Deleted ' + CAST(@@ROWCOUNT AS NVARCHAR(10)) + ' rows.';

@@FETCH_STATUS：通常与游标一起使用，以检查 FETCH 操作的状态。

-- 声明和打开游标
DECLARE EmployeeCursor CURSOR FOR
SELECT EmployeeID, FirstName, LastName
FROM Employees;

OPEN EmployeeCursor;

-- 获取第一行数据
FETCH NEXT FROM EmployeeCursor INTO @EmployeeID, @FirstName, @LastName;

-- 检查 FETCH 操作的状态
-- 0：FETCH 操作成功，已经获得了一行或更多行。
-- 1：FETCH 操作失败或没有更多的行可供获取。
-- 2：游标未初始化。
IF @@FETCH_STATUS = 0
BEGIN
    -- 数据获取成功
    PRINT 'Employee: ' + @FirstName + ' ' + @LastName;
END

@@TRANCOUNT：用于跟踪事务的嵌套级别。通常与 BEGIN TRANSACTION 和 COMMIT/ROLLBACK TRANSACTION 一起使用。

BEGIN TRANSACTION; -- 嵌套事务级别 +1
-- 一些操作
IF @@TRANCOUNT > 0
    COMMIT; -- 提交内部事务，嵌套级别 -1

@@LANGUAGE：用于更改当前会话的语言设置，可以影响错误消息和日期格式。
1
2
SET LANGUAGE 'French'; PRINT 'Current Language: ' + @@LANGUAGE;
@@CONNECTIONS：用于获取当前数据库服务器的连接数，可用于监视服务器的负载。
1
SELECT @@CONNECTIONS AS 'Number of Connections';
@@CPU_BUSY：用于获取 SQL Server 实例的 CPU 使用率，有助于监视服务器的性能。
1
SELECT @@CPU_BUSY AS 'CPU Usage';
@@TOTAL_ERRORS：用于获取 SQL Server 实例的总错误数，有助于监视服务器的错误情况。
1
SELECT @@TOTAL_ERRORS AS 'Total Errors';
@@PACK_RECEIVED 和 @@PACK_SENT：用于监视网络数据包的接收和发送情况，有助于分析网络性能。
1
SELECT @@PACK_RECEIVED AS 'Packets Received', @@PACK_SENT AS 'Packets Sent';
@@SERVERNAME：用于获取 SQL Server 实例的名称，有助于标识服务器。
1
SELECT @@SERVERNAME AS 'Server Name';
@@SERVICENAME：用于获取 SQL Server 实例的服务名称，这也可以用于标识服务器。
1
SELECT @@SERVICENAME AS 'Service Name';
@@VERSION：用于获取 SQL Server 的版本信息，可以用于确定正在运行的 SQL Server 版本。
1
SELECT @@VERSION AS 'SQL Server Version';
@@OPTIONS：包含有关当前会话的选项设置，这可以用于查看当前会话的配置。
1
SELECT @@OPTIONS AS 'Session Options';

这些系统变量为性能监视、错误分析、服务器标识以及在数据库对象中控制流程、错误处理和获取与数据库操作相关信息提供了关键工具。

12. 系统存储过程

以下是一些常见系统存储过程：

sp_helpdb：用于查看数据库的信息，包括数据库文件的路径、大小等。
1
EXEC sp_helpdb;
sp_who：用于获取当前连接到 SQL Server 实例的会话信息，包括登录名、数据库、状态等。
1
EXEC sp_who;
sp_who2：用于提供比 sp_who 更详细的会话信息，包括更多列和信息。
1
EXEC sp_who2;
sp_configure：用于查看和更改 SQL Server 的配置选项。
1
EXEC sp_configure;

sp_executesql：用于执行动态 SQL 语句，通常与参数化查询一起使用，以避免 SQL 注入攻击。

1
2
3

DECLARE @sql NVARCHAR(MAX);
SET @sql = 'SELECT * FROM MyTable WHERE ColumnName = @Value';
EXEC sp_executesql @sql, N'@Value INT', @Value = 123;

sp_renamedb：用于更改数据库的名称。

1	`EXEC sp_renamedb 'OldDatabaseName', 'NewDatabaseName';`

sp_spaceused：用于获取数据库中表或索引的空间使用情况。
1
EXEC sp_spaceused 'TableName';
sp_help：用于查看数据库对象（表、视图等）的结构信息。
1
EXEC sp_help 'TableName';
sp_helptext：用于查看存储过程、触发器或视图的定义文本。
1
EXEC sp_helptext 'StoredProcedureName';
sp_depends：用于查看数据库对象之间的依赖关系，包括对象引用和被引用的关系。
1
EXEC sp_depends 'ObjectName';

这些系统存储过程提供了各种功能，用于管理和监视 SQL Server 实例，获取有关数据库和对象的信息，以及执行各种任务，如动态 SQL 执行和配置查看。在实际使用中，根据需求选择适当的系统存储过程。

编程 > SQL Server

#SQL #SQL Server

SQL Server 存储过程语法教程

http://www.loquy.cn/posts/ce3c51f7.html

作者

loquy

发布于

2023年10月11日

许可协议

ADO.NET 使用教程上一篇

C# 定时同步数据思路下一篇