SQL Server数据库存储过程

SQL Server 中的存储过程（Stored Procedure）是一组为了完成特定功能的 SQL 语句集合，这些语句被编译并存储在数据库中，用户可以通过指定存储过程的名字和参数（假如有的话）来实行它。存储过程可以提高性能、简化复杂的操纵、减少网络流量以及提供安全性。

---

一、存储过程的类型

1、系统存储过程 (System Stored Procedures)

• 系统存储过程以 sp_ 开头，而且存储在 master 数据库中。
  
• 它们用于实行与 SQL Server 系统管理和操纵相关的各种使命。
  
• 比方，sp_helpdb 用于返回有关数据库的信息，sp_configure 用于检察或更改服务器级别的配置选项。
  
• 大多数系统存储过程都是只读的，而且不担当用户提供的输入（只管它们大概有一些可选的参数）。
  
• 开辟者通常不需要（也不发起）修改或覆盖系统存储过程。
  

2、用户定义的存储过程 (User-Defined Stored Procedures)

• 用户定义的存储过程是由数据库用户或开辟者创建的，用于实行用户指定的使命。
  
• 它们可以包含复杂的 SQL 逻辑、控制流语句（如 IF、WHILE 等）、变量声明和赋值、事务处置处罚等。
  
• 用户定义的存储过程可以担当输入参数（IN）、输出参数（OUT）或两者都担当（INOUT）。
  
• 它们可以返回单个值（使用 RETURN 语句）或结果集（使用 SELECT 语句）。
  
• 用户定义的存储过程存储在特定的数据库中，而且可以通过完全限定的名称（包括数据库名、架构名和存储过程名）或仅通过存储过程名（假如它在当前上下文中可见）来调用。
  
• 开辟者经常使用用户定义的存储过程来封装复杂的业务逻辑、提高代码重用性、简化应用步调与数据库之间的交互等。
  

3、扩展存储过程 (Extended Stored Procedures)

• 扩展存储过程是一种旧的技术，答应开辟者使用外部编程语言（如 C 或 C++）来创建可以在 SQL Server 环境中调用的函数。
  
• 虽然扩展存储过程提供了与操纵系统和其他外部资源的交互能力，但它们的使用已经逐渐被数据库CLR集成（Common Language Runtime Integration）所取代，后者答应开辟者在 SQL Server 中使用 .NET Framework 语言（如 C# 或 VB.NET）编写代码。
  
• 由于安全性和可维护性问题，新的 SQL Server 部署通常不推荐使用扩展存储过程。
  

二、存储过程的优点

1.性能提拔：

• 存储过程是预编译的，这意味着在初次实行时，SQL Server 会对其进行解析、优化并存储在高速缓存中。后续调用时，直接从缓存中取出实行，减少了编译的开销。
  
• 存储过程答应使用实行计划重用，这进一步提高了性能。
  

2.代码重用：

• 存储过程可以被多次调用，从而避免了在多个地方编写相同的 SQL 代码。
  
• 通过参数化，存储过程可以更加灵活和通用。
  

3.安全性：

• 通过限制对基础表的直接访问，可以提高数据的安全性。用户只能通过存储过程来访问数据，如许你可以控制哪些数据可以被访问或修改。
  
• 可以使用存储过程来验证用户输入，确保数据的完备性和正确性。
  

4.简化网络流量：

• 假如应用步调在远程服务器上，调用存储过程通常只需要发送存储过程的名称和参数（而不是完备的 SQL 语句），这可以减少网络流量。
  

5.维护方便：

• 假如需要修改业务逻辑，只需要修改存储过程，而不需要修改和重新部署多个应用步调。
  
• 可以使用 SQL Server Management Studio (SSMS) 或其他工具来方便地管理和调试存储过程
  

6.事务处置处罚：

• 存储过程可以很轻易地包含事务逻辑，确保数据的完备性和同等性。
  

7.自动化：

• 存储过程可以与 SQL Server Agent 作业联合使用，实现自动化的数据库使命，如定期备份、数据清理等。
  

三、存储过程的语法

1. 简单的存储过程（无参数）

1. CREATE PROCEDURE ProcedureName -- ProcedureName为存储过程名称
2. AS  
3. BEGIN  
4.     -- SQL语句  
5.     SELECT * FROM YourTableName  --YourTableName为你的表名
6. END

复制代码

2. 带输入参数的存储过程

1. CREATE PROCEDURE ProcedureName -- ProcedureName为存储过程名
2.     @ParameterName DataType, -- 例如 @ID INT ，这里填变量名和变量数据类型
3.     @AnotherParameterName DataType -- 例如 @Name NVARCHAR(50)  
4. AS  
5. BEGIN  
6.     -- SQL语句，使用参数  
7.     SELECT * FROM YourTableName WHERE ID = @ID AND Name = @Name  
8. END

复制代码

3. 带输出参数的存储过程

1. CREATE PROCEDURE ProcedureName  
2.     @InputParameter INT,  
3.     @OutputParameter INT OUTPUT  
4. AS  
5. BEGIN  
6.     -- SQL语句，设置输出参数的值  
7.     SELECT @OutputParameter = SomeColumn  
8.     FROM YourTableName  
9.     WHERE AnotherColumn = @InputParameter  
10. END

复制代码

在调用带输出参数的存储过程时，你需要声明一个变量来接收输出参数的值：

1. DECLARE @MyOutput INT  
2. EXEC ProcedureName @InputParameter = 1, @OutputParameter = @MyOutput OUTPUT  
3. SELECT @MyOutput AS OutputValue

复制代码

4. 使用RETURN值的存储过程

存储过程还可以使用RETURN语句返回一个整数值，这通常用于指示存储过程的乐成或失败。但是，请留意，这与输出参数差别，因为RETURN值只能是一个整数，而且你不能从存储过程外部直接访问它（除非你在应用步调代码中捕获它）。

1. CREATE PROCEDURE ProcedureName  
2.     @ParameterName INT  
3. AS  
4. BEGIN  
5.     IF EXISTS (SELECT 1 FROM YourTableName WHERE ID = @ParameterName)  
6.         RETURN 0 -- 成功  
7.     ELSE  
8.         RETURN -1 -- 失败  
9. END

复制代码

5. 使用TRY...CATCH的存储过程（错误处置处罚）

你可以在存储过程中使用TRY...CATCH块来处置处罚错误：

1. CREATE PROCEDURE ProcedureName  
2.     @ParameterName INT  
3. AS  
4. BEGIN  
5.     BEGIN TRY  
6.         -- 可能会引发错误的SQL语句  
7.         -- ...  
8.     END TRY  
9.     BEGIN CATCH  
10.         -- 处理错误的代码  
11.         -- ...  
12.     END CATCH  
13. END

复制代码

四、调用存储过程

要调用存储过程，你可以使用EXEC或EXECUTE关键字：

1. EXEC ProcedureName -- 无参数  
2. EXEC ProcedureName @ParameterName = 1, @AnotherParameterName = 'Value' -- 带参数

复制代码

大概，假如存储过程没有参数或你想按参数顺序传递它们，你可以省略参数名：

1. EXEC ProcedureName 1, 'Value' -- 假设这是按参数定义的顺序

复制代码

五、存储过程实例

1、不带参数的存储过程

以下是一个简单的存储过程，它从一个名为Employees的表中检索全部员工的姓名和年事：

1. USE YourDatabaseName;  -- 替换为您的数据库名称  
2. GO  
3.   
4. CREATE PROCEDURE GetAllEmployees  
5. AS  
6. BEGIN  
7.     -- 设置NOCOUNT为ON，以减少不必要的返回消息  
8.     SET NOCOUNT ON;  
9.   
10.     -- 查询Employees表并返回结果  
11.     SELECT EmployeeName, Age  
12.     FROM Employees;  
13. END  
14. GO

复制代码

要使用此存储过程，您只需在SQL Server Management Studio (SSMS) 或其他支持SQL的客户端中使用语言调用：

1. EXEC GetAllEmployees;

复制代码

这将返回Employees表中全部员工的姓名和年事。
请留意，您需要确保YourDatabaseName和Employees表以及EmployeeName和Age列的名称与您的实际数据库和表结构相匹配。此外，假如您的Employees表包含敏感信息或大量数据，请确保在生产环境中谨慎使用此存储过程。
2、带输入参数的存储过程

假设我们有一个名为Employees的表，它有一个EmployeeID列和一个EmployeeName列。我们可以创建一个存储过程，该过程担当一个EmployeeID作为输入参数，并返回相应的EmployeeName。

1. CREATE PROCEDURE GetEmployeeNameByID  
2.     @EmployeeID INT  
3. AS  
4. BEGIN  
5.     -- 设置NOCOUNT为ON，以阻止SQL Server发送额外的DONE_IN_PROC消息  
6.     -- 这通常用于提高性能  
7.     SET NOCOUNT ON;  
8.   
9.     -- 从Employees表中检索与给定EmployeeID关联的员工名称  
10.     SELECT EmployeeName  
11.     FROM Employees  
12.     WHERE EmployeeID = @EmployeeID;  
13. END;  
14. GO

复制代码

可以通过以下方式调用它：

1. EXEC GetEmployeeNameByID @EmployeeID = 1;  -- 假设你要查找ID为1的员工

复制代码

这将返回ID为1的员工的名称（假如存在于Employees表中）。假如表中没有与给定ID匹配的行，则查询将不返回任何结果。
3. 带输出参数的存储过程

1. CREATE PROCEDURE GetEmployeeCount
2.     @DepartmentID INT,
3.     @EmployeeCount INT OUTPUT
4. AS
5. BEGIN
6.     SET NOCOUNT ON;
8.     SELECT @EmployeeCount = COUNT(*)
9.     FROM Employees
10.     WHERE DepartmentID = @DepartmentID;
12.     -- 输出参数的值将在存储过程执行完成后返回给调用者
13. END;

复制代码

在这个存储过程中：

• 存储过程名为 GetEmployeeCount。
  
• 它担当两个参数：@DepartmentID（输入参数）和 @EmployeeCount（输出参数）。
  
• @EmployeeCount 被声明为输出参数，并在存储过程实行后返回其值给调用者。
  
• 存储过程内部通过查询计算指定部分的员工数量，并将结果赋值给 @EmployeeCount 输出参数。
  

你可以通过以下方式调用这个存储过程，并获取输出参数的值：

1. DECLARE @DepartmentID INT = 1; -- 设置部门ID
2. DECLARE @EmployeeCount INT; -- 声明输出参数
4. EXEC GetEmployeeCount @DepartmentID, @EmployeeCount OUTPUT;
6. SELECT @EmployeeCount AS EmployeeCount; -- 输出参数的值

复制代码

这将实行存储过程 GetEmployeeCount，并将部分ID为1的员工数量存储在 @EmployeeCount 变量中。然后，你可以通过 SELECT 语句检索输出参数的值。
4. 使用RETURN值的存储过程

1. CREATE PROCEDURE ValidateLogin
2.     @Username NVARCHAR(50),
3.     @Password NVARCHAR(50)
4. AS
5. BEGIN
6.     SET NOCOUNT ON;
8.     DECLARE @Result INT;
10.     -- 检查用户名和密码是否匹配
11.     IF EXISTS (
12.         SELECT 1
13.         FROM Users
14.         WHERE Username = @Username AND Password = @Password
15.     )
16.     BEGIN
17.         -- 如果匹配，则设置返回值为1
18.         SET @Result = 1;
19.     END
20.     ELSE
21.     BEGIN
22.         -- 如果不匹配，则设置返回值为0
23.         SET @Result = 0;
24.     END
26.     -- 返回结果
27.     RETURN @Result;
28. END;

复制代码

在这个存储过程中：

• 存储过程名为 ValidateLogin。
  
• 它担当两个输入参数：@Username 和 @Password。
  
• 在存储过程内部，它检查提供的用户名和暗码是否与数据库中的记载匹配。
  
• 假如用户名和暗码匹配，则设置返回值为1，否则设置为0。
  
• 末了，使用RETURN语句返回结果。
  

你可以像如许调用这个存储过程，并获取返回的值：

1. DECLARE @Username NVARCHAR(50) = 'example_user';
2. DECLARE @Password NVARCHAR(50) = 'example_password';
3. DECLARE @LoginResult INT;
5. EXEC @LoginResult = ValidateLogin @Username, @Password;
7. SELECT @LoginResult AS LoginResult;

复制代码

这将实行存储过程 ValidateLogin，并将提供的用户名和暗码作为参数传递给它。存储过程将验证登录信息并返回结果。然后，你可以通过 SELECT 语句检索存储过程的返回值。
5. 使用TRY...CATCH的存储过程（错误处置处罚）

1. CREATE PROCEDURE InsertEmployee
2.     @FirstName NVARCHAR(50),
3.     @LastName NVARCHAR(50),
4.     @DepartmentID INT
5. AS
6. BEGIN
7.     SET NOCOUNT ON;
9.     BEGIN TRY
10.         -- 开始事务
11.         BEGIN TRANSACTION;
13.         -- 在Employees表中插入新员工记录
14.         INSERT INTO Employees (FirstName, LastName, DepartmentID)
15.         VALUES (@FirstName, @LastName, @DepartmentID);
17.         -- 提交事务
18.         COMMIT TRANSACTION;
19.     END TRY
20.     BEGIN CATCH
21.         -- 发生错误时，回滚事务
22.         IF @@TRANCOUNT > 0
23.             ROLLBACK TRANSACTION;
25.         -- 获取错误信息并将其输出
26.         DECLARE @ErrorMessage NVARCHAR(4000) = ERROR_MESSAGE();
27.         DECLARE @ErrorSeverity INT = ERROR_SEVERITY();
28.         DECLARE @ErrorState INT = ERROR_STATE();
30.         -- 在实际应用中，这里可以进行日志记录、通知等操作
31.         PRINT 'Error Message: ' + @ErrorMessage;
32.         PRINT 'Error Severity: ' + CAST(@ErrorSeverity AS NVARCHAR);
33.         PRINT 'Error State: ' + CAST(@ErrorState AS NVARCHAR);
34.     END CATCH;
35. END;

复制代码

在这个存储过程中：

• 存储过程名为 InsertEmployee。
  
• 它担当三个输入参数：@FirstName、@LastName 和 @DepartmentID。
  
• 在TRY块内部，它开始一个事务，然后尝试将新员工的信息插入到Employees表中。
  
• 假如在TRY块中发生错误，CATCH块将捕获该错误，并在错误处置处罚代码中实行相应的操纵，比方回滚事务、输出错误信息等。
  
• 在这个示例中，错误信息被打印到控制台上，但在实际应用中，你可以根据需要采取其他处置处罚措施，如记载到日记或发送通知。
  

你可以像如许调用这个存储过程：

1. EXEC InsertEmployee 'John', 'Doe', 1;

复制代码

假如实行过程中发生了错误，TRY...CATCH块将捕获并处置处罚它，确保事务被正确回滚，而且错误信息被记载或输出。
六、总结

• 存储过程的定义：存储过程是一组预编译的SQL语句，可以被封装在数据库中，并通过名称和参数来调用。它们可以简化复杂的数据库操纵，提高性能和安全性。
  
• 存储过程的结构：存储过程通常由BEGIN和END块包裹，可以包含输入参数、输出参数、局部变量、SQL语句、控制流语句等。
  
• 参数的使用：存储过程可以担当输入参数和输出参数。输入参数用于传递值给存储过程，输出参数用于返回值给调用者。
  
• 错误处置处罚：使用TRY...CATCH块来处置处罚存储过程中大概发生的错误，确保数据库操纵的完备性和同等性。
  
• 事务管理：通过BEGIN TRANSACTION、COMMIT TRANSACTION和ROLLBACK TRANSACTION语句来管理事务，确保在复杂操纵中的数据同等性。
  
• 性能优化：合理设计存储过程，避免过多的逻辑复杂性和冗余的代码，以提高实行效率。
  
• 安全性考虑：存储过程可以资助减少SQL注入等安全威胁，通过限制对数据库的直接访问来提高数据安全性。
  
• 实践和练习：末了，强调通过实践和练习来加深对存储过程的理解和掌握。只有通过实际操纵，才能真正掌握存储过程的使用技巧和最佳实践。
  

总的来说，SQL Server数据库存储过程是一个强大而灵活的工具，可以资助数据库开辟人员提高效率、优化性能，并增强数据安全性。

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。