数据库存储过程和 SQL 持久存储模块 (PSM)
存储过程对于数据库管理系统 (DBMS) 至关重要，因为它们可以增强安全性、提升性能并促进代码重用。一组 SQL 语句包含在预编译的数据库对象中，称为存储过程。它们可以被应用程序或其他数据库对象调用，并保存在数据库中。在本文中，我们将详细介绍 SQL 持久存储模块 (PSM)，这是一种 SQL 的过程式编程语言扩展，并探讨存储过程的概念。
理解存储过程
存储过程是已经预编译的数据库对象，包含一组 SQL 语句。它们可以被应用程序或其他数据库对象调用，并保存在数据库中。让我们看一个简单的 SQL Server 存储过程示例 -
CREATE PROCEDURE GetEmployeeById
@EmployeeId INT
AS
BEGIN
SELECT * FROM Employees WHERE EmployeeId = @EmployeeId
END
输入表 -Employees
+------------+--------------+------------+ | EmployeeId | EmployeeName | Department | +------------+--------------+------------+ | 1 | John Doe | HR | | 2 | Jane Smith | IT | | 3 | Mike Johnson | Sales | | 4 | Sarah Adams | Marketing | | 5 | Robert Brown | Finance | | 6 | Lisa Davis | HR | | 7 | David Wilson | IT | | 8 | Emily Lee | Sales | | 9 | Michael Chen | Marketing | | 10 | Olivia Clark | Finance | +------------+--------------+------------+
上面的代码定义了"GetEmployeeById"存储过程，它接受输入参数 @Employee Id。该过程根据提供的员工 ID，从"Employees"表中提取员工信息。
以下代码可用于运行该存储过程 -
EXEC GetEmployeeById @EmployeeId = 1
输出表
+------------+--------------+------------+ | EmployeeId | EmployeeName | Department | +------------+--------------+------------+ | 1 | John Doe | HR | +------------+--------------+------------+
当员工 ID 等于 1 时，这将启动存储过程并获取员工信息。
使用存储过程的优势
更佳性能 − 存储过程经过预编译，并以编译后的形式保存在数据库中，从而降低了解析和编译相关的开销。与动态生成的 SQL 语句相比，这可以提高执行速度。
代码可重用性 − 存储过程鼓励模块化编程和代码重用。它们可以被许多程序或其他存储过程调用，从而减少重复并提高一致性。
安全性 − 通过在过程级别启用访问控制，存储过程提供了额外的安全性。应用程序可以运行特定操作，同时限制对表的直接访问。
数据完整性 − 通过将复杂的数据修改算法封装到存储过程中，可以更有效地确保数据完整性。由于逻辑包含在数据库中，因此可以提供可靠且一致的结果。
为 SQL 添加过程功能 (SQL PSM)
除了 SQL 语言之外，用于过程编程的还有 SQL 持久存储模块 (PSM)。它允许开发人员在数据库内部构建函数和过程，从而实现高级数据处理和操作。让我们通过实际示例来了解 SQL PSM 的一些显著特性。
过程构造 −
过程特定标记语言 (PSM) 提供过程组件，包括条件语句(IF、CASE)、循环(WHILE、FOR)和异常处理(TRY-CATCH)。查看下面的示例，了解条件语句在 PSM 中的使用方式 −
示例
CREATE PROCEDURE GetEmployeeSalaryRange
@MinSalary DECIMAL(10,2),
@MaxSalary DECIMAL(10,2)
AS
BEGIN
IF @MinSalary <= @MaxSalary
BEGIN
SELECT * FROM Employees WHERE Salary BETWEEN @MinSalary AND @MaxSalary
END
ELSE
BEGIN
RAISERROR('Invalid salary range.', 16, 1)
END
END
输入表 -Employees
| EmployeeId | EmployeeName | Salary | |------------|--------------|-----------| | 1 | John Doe | 50000.00 | | 2 | Jane Smith | 65000.00 | | 3 | Mike Johnson | 75000.00 | | 4 | Lisa Davis | 45000.00 | | 5 | Mark Wilson | 80000.00 | | 6 | Sarah Brown | 55000.00 | | 7 | Alex Lee | 60000.00 | | 8 | Emily Clark | 70000.00 | | 9 | David Jones | 40000.00 | | 10 | Olivia Smith | 90000.00 |
输出表
结果假设调用过程 GetEmployeeSalaryRange，输入为 @MinSalary = 50000.00 和 @MaxSalary = 70000.00。
| EmployeeId | EmployeeName | Salary | |------------|--------------|-----------| | 1 | John Doe | 50000.00 | | 2 | Jane Smith | 65000.00 | | 3 | Mike Johnson | 75000.00 | | 6 | Sarah Brown | 55000.00 | | 7 | Alex Lee | 60000.00 | | 8 | Emily Clark | 70000.00 |
此代码中的存储过程"GetEmployeeSalaryRange"接受输入参数 @MinSalary 和 @MaxSalary。它使用 IF 语句有条件地检索工资在给定范围内的员工。如果 @MinSalary 高于 @MaxSalary，RAISERROR 语句将生成错误。
变量支持 −
PSM 允许定义和使用变量，这些变量可以保存输入/输出值或用于存储中间结果。让我们看一个存储过程使用变量完成计算的示例 −
CREATE PROCEDURE CalculateTotalSalary
@EmployeeId INT,
@BonusPercentage DECIMAL(5,2) OUTPUT,
@TotalSalary DECIMAL(10,2) OUTPUT
AS
BEGIN
DECLARE @BaseSalary DECIMAL(10,2)
SELECT @BaseSalary = Salary FROM Employees WHERE EmployeeId = @EmployeeId
SET @BonusPercentage = 0.1
SET @TotalSalary = @BaseSalary + (@BaseSalary * @BonusPercentage)
END
此代码中的"CalculateTotalSalary"存储过程通过将员工的总薪酬乘以奖金百分比来确定员工的总薪酬。使用输入参数@EmployeeId从"Employees"表中检索员工的基本工资。计算出的奖金百分比和总工资分别存储在输出参数@BonusPercentage和@TotalSalary中。
我们可以使用以下代码运行存储过程并获取计算值 -
DECLARE @Bonus DECIMAL(5,2) DECLARE @Total DECIMAL(10,2) EXEC CalculateTotalSalary @EmployeeId = 1, @BonusPercentage = @Bonus OUTPUT, @TotalSalary = @Total OUTPUT SELECT @Bonus AS BonusPercentage, @Total AS TotalSalary
输入表 -Employees
| EmployeeId | Salary | |------------|---------| | 1 | 5000.00 | | 2 | 6000.00 | | 3 | 4500.00 | | 4 | 7000.00 | | 5 | 5500.00 | | 6 | 8000.00 | | 7 | 4000.00 | | 8 | 6500.00 | | 9 | 7500.00 | | 10 | 5200.00 |
输出表
| EmployeeId | BonusPercentage | TotalSalary | |------------|-----------------|-------------| | 1 | 0.10 | 5500.00 | | 2 | 0.10 | 6600.00 | | 3 | 0.10 | 4950.00 | | 4 | 0.10 | 7700.00 | | 5 | 0.10 | 6050.00 | | 6 | 0.10 | 8800.00 | | 7 | 0.10 | 4400.00 | | 8 | 0.10 | 7150.00 | | 9 | 0.10 | 8250.00 | | 10 | 0.10 | 5720.00 |
错误处理 −
PSM 中的 TRY-CATCH 结构包含可靠的错误处理技术。让我们看一下如何在存储过程中使用 TRY-CATCH 来处理错误 −
CREATE PROCEDURE DivideNumbers
@Dividend INT,
@Divisor INT
AS
BEGIN
BEGIN TRY
SELECT @Dividend / @Divisor AS Result
END TRY
BEGIN CATCH
SELECT ERROR_NUMBER() AS ErrorNumber, ERROR_MESSAGE() AS ErrorMessage
END CATCH
END
此代码中使用存储过程"Divide Numbers"执行两个数字 @Dividend 和 @Divisor 的除法运算。除法运算在 TRY 块中尝试执行，如果出现问题，则激活 CATCH 块，并使用 ERROR_NUMBER() 和 ERROR_MESSAGE() 方法检索并显示错误信息。
输入表 -Employees
+----------+---------+ | Dividend | Divisor | +----------+---------+ | 10 | 2 | | 20 | 4 | | 15 | 3 | | 30 | 5 | | 12 | 4 | | 18 | 6 | | 25 | 5 | | 16 | 2 | | 35 | 7 | | 40 | 8 | +----------+---------+
以下代码可用于运行存储过程并处理任何错误 -
EXEC DivideNumbers @Dividend = 10, @Divisor = 0
输出表
+--------------+---------------------------------+ | ErrorNumber | ErrorMessage | +--------------+---------------------------------+ | 8134 | Divide by zero error encountered. | +--------------+---------------------------------+
这将导致除以零的错误，并且 CATCH 块将被激活以显示错误编号和消息。
函数定义 − 在数据库中，可以使用 SQL 持久存储模块 (PSM) 定义函数。可重用的代码块(称为函数)接受输入参数，执行某些操作并返回单个值。与任何其他 SQL 表达式一样，它们可以在 SQL 查询中使用。以下是如何在 SQL PSM 中定义函数的示例 −
CREATE FUNCTION GetEmployeeCountByDepartment(departmentId INT)
RETURNS INT
BEGIN
DECLARE @Count INT
SELECT @Count = COUNT(*) FROM Employees WHERE DepartmentId = departmentId
RETURN @Count
END
上面的代码定义了"GetEmployeeCountByDepartment"函数，该函数接收输入参数departmentId。该函数确定所选部门中有多少名员工，并以整数形式返回该数字。
输入表 -Employees
+------------+--------------+--------------+ | EmployeeId | EmployeeName | DepartmentId | +------------+--------------+--------------+ | 1 | John Doe | 1 | | 2 | Jane Smith | 1 | | 3 | Mark Johnson | 2 | | 4 | Emily Brown | 3 | | 5 | Alex Wilson | 2 | | 6 | Sarah Davis | 1 | | 7 | Mike Thompson| 3 | | 8 | Emma Lee | 2 | | 9 | James Miller | 1 | | 10 | Lily Anderson| 3 | +------------+--------------+--------------+
Departments 部门表
+--------------+----------------+ | DepartmentId | DepartmentName | +--------------+----------------+ | 1 | Sales | | 2 | Marketing | | 3 | Finance | | 4 | HR | | 5 | IT | +--------------+----------------+
可以使用以下代码在 SQL 查询中使用此方法 -
SELECT DepartmentId, GetEmployeeCountByDepartment(DepartmentId) AS EmployeeCount FROM Departments
输出表
+--------------+---------------+ | DepartmentId | EmployeeCount | +--------------+---------------+ | 1 | 4 | | 2 | 3 | | 3 | 2 | | 4 | 0 | | 5 | 0 | +--------------+---------------+
此查询为每个部门运行"按部门获取员工数量"函数，从"部门"数据库中检索部门 ID，然后获取关联的员工数量。
SQL PSM 的优势
在开发数据库时，使用 SQL PSM 具有许多优势。
增强功能 − 通过包含过程构造和变量支持，PSM 扩展了 SQL 的功能。这允许开发人员直接在数据库内执行复杂的业务逻辑和数据转换，从而消除了在 DBMS 外部传输和处理数据的需求。
增强性能 − 通过立即在数据库内执行逻辑，PSM 减少了在数据库和外部应用程序之间传输数据的开销。最终，网络延迟降低，性能提升。
代码可重用性和可维护性 − 通过将逻辑封装在过程和函数中，PSM 提高了代码可重用性。开发人员可以创建可供其他应用程序使用的模块化代码，从而减少重复并提高可维护性。
数据完整性和安全性 − 由于数据处理和操作逻辑位于数据库中，因此 PSM 可以保证数据的一致性和完整性。PSM 还支持细粒度的访问控制，通过限制对表的直接访问并仅向应用程序公开必要的进程来提高安全性。
结论
总而言之，SQL PSM 和存储过程是强大的工具，可以提高数据库系统的可用性、可靠性、安全性和数据完整性。开发人员可以利用这些功能来提高应用程序的速度、优化代码并确保数据库内数据操作的可靠性和安全性。存储过程和 SQL PSM 为有效、可靠的数据库开发提供了坚实的基础，无论是管理复杂的数据转换还是执行业务规则。
