当前位置：极客笔记 > 数据库管理系统 > 数据库管理系统 精选笔记 > 数据库管理系统 数据库存储过程和SQL PSM

数据库管理系统 数据库存储过程和SQL PSM

数据库管理系统 数据库存储过程和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为有效可靠的数据库开发提供了坚实的基础。

Camera课程

Camera API2 教程

API2 教程
Camera 性能教程

Perf 教程
Camera Native Framework 教程

Native FW 教程
Camera QCOM ISP 教程

ISP 教程
Camera QCOM Sensor 教程

Sensor 教程

Python教程

Python 教程

Python 教程
NumPy 教程

NumPy 教程
Django 教程

Django 教程

Java教程

Java 教程

Java 教程

Web教程

JavaScript 教程

JavaScript 教程
jQuery 教程

jQuery 教程
CSS 教程

CSS 教程
PHP 教程

PHP 教程
Laravel 教程

Laravel 教程
TypeScript 教程

TypeScript 教程
WordPress 教程

WordPress 教程

数据库教程

SQL 教程

SQL 教程
SQLite 教程

SQLite 教程
MySQL 教程

MySQL 教程
PL/SQL 教程

PL/SQL 教程
MongoDB 教程

MongoDB 教程

图形图像教程

OpenCV 教程

OpenCV 教程

办公软件教程

Excel 教程

Excel 教程

Linux教程

Linux命令全集

Linux命令全集
Linux内核API

Linux内核API
LVGL 教程

LVGL 教程

计算机教程

操作系统 教程

操作系统 教程
计算机网络 教程

计算机网络 教程
C语言 教程

C语言 教程
C++ 教程

C++ 教程
Swift 教程

Swift 教程

大数据教程

Hadoop 教程

Hadoop 教程
Spark 教程

Spark 教程
Scala 教程

Scala 教程

开发工具教程

Git 教程

Git 教程
Jenkins 教程

Jenkins 教程
ChatGPT 教程

ChatGPT 教程
IntelliJ 教程

IntelliJ 教程

数据库管理系统 精选笔记