极客笔记数据库管理系统 数据库存储过程和SQL PSM
存储过程对于数据库管理系统(DBMS)是至关重要的，因为它们增加了安全性，提升了性能，并鼓励代码复用。一组SQL语句被包含在称为存储过程的预编译数据库对象中。它们可以被应用程序或其他数据库对象调用，并且保存在数据库中。在本文中，我们将详细介绍SQL持续性存储模块(PSM)，这是一种针对SQL的过程编程语言扩展，并考察存储过程的概念。
理解存储过程
存储过程是已经预编译的数据库对象，包含一系列的SQL语句。它们可以被应用程序或其他数据库对象调用，并且保存在数据库中。让我们来看一个简单的SQL Server存储过程示例-
CREATE PROCEDURE GetEmployeeById
@EmployeeId INT
AS
BEGIN
SELECT * FROM Employees WHERE EmployeeId = @EmployeeId
END
输入表-员工
+------------+--------------+------------+
| 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。根据提供的Employee Id，该过程从“Employees”表中提取员工信息。
可使用以下代码来运行该存储过程 –
EXEC GetEmployeeById @EmployeeId = 1
输出表格
+------------+--------------+------------+
| EmployeeId | EmployeeName | Department |
+------------+--------------+------------+
| 1 | John Doe | HR |
+------------+--------------+------------+
通过Employee Id等于1，将会启动存储过程并获取员工的信息。
利用存储过程的好处
- 更好的性能 − 存储过程是预编译并以编译形式保存在数据库中，这减少了与解析和编译相关的开销。与动态生成的SQL语句相比，这导致执行速度更快。
-
代码复用性 − 存储过程鼓励模块化编程和代码重用。它们可以从许多程序或其他存储过程中调用，这减少了重复，并促进一致性。
-
安全性 − 通过在存储过程级别启用访问控制，存储过程提供了额外的安全度。应用程序可以被允许运行特定操作，同时对表的直接访问受到限制。
-
数据完整性 − 通过将复杂的数据修改算法封装到存储过程中，可以更有效地确保数据完整性。由于逻辑包含在数据库中，因此可以提供可靠和一致的结果。
向SQL添加过程性功能（SQL PSM）
除了用于过程性编程的SQL语言被称为SQL Persistent Stored Modules (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
输入表 – 员工
| 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 |
输出表格
假设使用输入 @MinSalary = 50000.00 和 @MaxSalary = 70000.00 调用过程 GetEmployeeSalaryRange 的结果。
| 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”存储过程通过将基本薪水乘以奖金百分比来确定员工的总报酬。员工的基本薪水从”Employees”表中使用输入参数@Employee Id检索。计算的奖金百分比和总薪水分别存储在输出参数@Bonus Percentage和@Total Salary中。
我们可以使用以下代码来运行存储过程并获取计算的值 −
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
输入表 – 员工
| 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()方法检索和显示错误信息。
输入表 – 员工
+----------+---------+
| 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。该函数确定选择的部门中有多少名员工，并将结果作为整数返回。
输入表格 – 员工
+------------+--------------+--------------+
| 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 |
+------------+--------------+--------------+
部门表
+--------------+----------------+
| 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 |
+--------------+---------------+
该查询在从”Departments”数据库中获取部门ID后，对每个部门运行“按部门获取员工人数”的函数，以获取关联的员工人数。
SQL PSM的优点
在开发数据库时，使用SQL PSM具有许多优点。
- 增强功能 − 通过包含过程性结构和变量支持，PSM扩展了SQL的功能。这消除了需要在DBMS外部进行数据传输和处理的要求，使开发人员能够在数据库中执行复杂的业务逻辑和数据转换。
-
增强性能 − PSM通过在数据库内部直接执行逻辑，减少了在数据库和外部应用程序之间传输数据的开销。因此，网络延迟减小，性能得到提升。
-
代码可重用性和可维护性 − 通过将逻辑封装在过程和函数中，PSM鼓励代码的可重用性。开发人员可以创建可以被其他应用程序使用的模块化代码，这减少了重复代码并增强了可维护性。
-
数据完整性和安全性 − 由于数据处理和操作逻辑位于数据库内部，PSM可以保证数据的一致性和完整性。PSM还可以实现细粒度访问控制，通过限制直接访问表并只向应用程序公开必要的过程，提高安全性。
结论
总之，SQL PSM和存储过程是改善数据库系统的可用性、可靠性、安全性和数据完整性的强大工具。开发人员可以通过利用这些功能，提高应用程序的速度，优化他们的代码，并在数据库内部保证可靠和安全的数据操作。无论是管理复杂的数据转换还是执行业务规则，存储过程和SQL PSM为有效可靠的数据库开发提供了坚实的基础。