Skip to content
博客
什么是存储过程？

什么是存储过程？

SQL（Structured Query Language，结构化查询语言）是用于管理和操作关系型数据库的标准语言。其中一个强大且常用的功能就是存储过程。存储过程是一组预编译并存储在数据库中的 SQL 语句集合，可以接受输入参数、执行操作并返回结果。下面我们来探讨一下存储过程是什么、如何创建它们以及使用它们的最佳实践。

stored-procedures

存储过程简介

存储过程听起来可能是一个复杂的术语，但它们是高效数据库管理的基础。我们先从它的定义开始。

什么是存储过程？

存储过程是在数据库服务器上预先定义并存储的一系列 SQL 语句，当需要执行这些操作时，可以通过调用存储过程的名字来执行，而不是发送多个单独的查询命令。

下面是一个简化版的示例，展示如何在 SQL Server 中创建一个简单的存储过程：

CREATE PROCEDURE procedure_name
AS
BEGIN
   -- SQL statements
END

下面是存储过程的一些关键组成部分：

  • 输入参数：这些是从外部传递给存储过程的值，用于定制存储过程的行为。输入参数允许存储过程根据不同的条件执行不同的操作。
  • 输出参数：与输入参数类似，输出参数也是存储过程的一部分，但它们的作用是返回值给调用者，而不是接收值。
  • 局部变量：这些是在存储过程中声明的变量，用于在执行期间存储中间结果或计算值。局部变量仅在存储过程的上下文中可见，并且在其生命周期内可以多次赋值。
  • SQL 语句：这些构成了存储过程的核心逻辑，包括但不限于数据的查询、插入、更新和删除操作。

这些组件共同协作，使存储过程成为一种既可重用又高效的执行数据库操作的方式。通过将常见的数据库任务封装在存储过程中，可以简化应用程序的开发，同时提高性能和安全性。

stored-procedures

存储过程如何工作

存储过程是在数据库服务器内部执行的，这意味着它们能够以更高的效率完成操作，相比从客户端逐一发送多个查询而言，其执行速度更快。此外，使用存储过程还可以显著减少网络流量，因为只需要将最终结果集从服务器传输到客户端，而不需要来回传输每个单独查询的结果。这样一来，不仅提高了数据处理的速度，还降低了网络带宽的使用。

在数据库管理中的角色

存储过程在数据库管理中扮演着核心角色，因为它们将业务逻辑集中存储在数据库服务器上。这样做可以确保关键操作始终以一致、安全且高效的方式执行。具体来说，存储过程有助于：

  • 维护数据完整性：通过确保所有的数据操作都遵循预定的规则和约束，存储过程有助于维持数据的完整性和一致性。
  • 实施业务逻辑：将复杂的业务规则封装在存储过程中，可以确保这些规则得到严格执行，而不会因为客户端代码的变化而受到影响。
  • 简化数据库交互：通过提供封装了复杂操作的接口，存储过程降低了应用程序与数据库交互的复杂性，使得开发和维护变得更加简单。

使用存储过程的好处

使用存储过程有以下几个关键优势：

  1. 增强性能：
    1. 存储过程预编译后执行更快。
    2. 提高响应速度，更高效地使用服务器资源。
  2. 可重用性和可维护性：
    1. 存储过程可以多次调用，减少代码重复。
    2. 更新存储过程即可在所有使用的地方生效，确保一致性，减少错误。
  3. 数据安全：
    1. 控制数据库访问，限制直接操作表的能力。
    2. 通过存储过程提供安全层，防止未经授权的访问和恶意攻击。

与存储过程一起使用的常用命令

现在让我们看看与存储过程配对的有用命令。

CREATE PROCEDURE

正如前面提到的，该命令用于在数据库中定义一个新的存储过程。以下是使用此函数的存储过程的示例：

假设我们有一个名为“Employees”的表，其中包含以下列：

  • EmployeeID
  • FirstName
  • LastName
  • DepartmentID
  • Salary

我们想要创建一个存储过程来检索属于特定部门的所有员工。

CREATE PROCEDURE GetEmployeesByDepartment
   @DepartmentID INT
AS
BEGIN
   SELECT EmployeeID, FirstName, LastName, DepartmentID, Salary
   FROM Employees
   WHERE DepartmentID = @DepartmentID;
END;

EXEC

该命令用于执行存储过程。它还可用于传递输入和输出参数。对于我们之前的示例，“EXEC”命令如下所示：

EXEC GetEmployeesByDepartment @DepartmentID = 1;

ALTER PROCEDURE

此命令允许你在不删除和重新创建的情况下修改现有的存储过程。继续之前的例子，如果我们想修改名为 “GetEmployeesByDepartment” 的存储过程，以便增加一个额外的薪资过滤条件，也就是说，我们希望检索特定部门中薪资高于某一特定数额的员工信息。

这是一个例子：

ALTER PROCEDURE GetEmployeesByDepartment
   @DepartmentID INT,
   @MinSalary DECIMAL(10, 2)
AS
BEGIN
   SELECT EmployeeID, FirstName, LastName, DepartmentID, Salary
   FROM Employees
   WHERE DepartmentID = @DepartmentID AND Salary > @MinSalary;
END;

DROP PROCEDURE

如果不再需要某个存储过程，可以使用 DROP PROCEDURE 命令将其从数据库中移除。

DROP PROCEDURE GetEmployeesByDepartment

这条命令会从数据库中彻底删除指定的存储过程，释放其所占用的资源。在执行此操作前，请确保你不再需要该存储过程，并且已经备份了任何必要的逻辑或信息。

如何创建和使用存储过程

我们将在三个领域研究创建和使用存储过程：

  • MySQL
  • SQL Server
  • Oracle

MySQL

在 MySQL 中创建存储过程 (opens in a new tab)相当简单。您可以使用“CREATE PROCEDURE”语句定义过程、指定参数并编写 SQL 代码。

您可以这样做：

第一步：创建员工表

首先，让我们创建一个示例员工表来填充我们要使用的数据。

CREATE TABLE Employees (
   EmployeeID INT PRIMARY KEY AUTO_INCREMENT,
   FirstName VARCHAR(50),
   LastName VARCHAR(50),
   DepartmentID INT,
   Salary DECIMAL(10, 2)
);

第二步：插入示例数据

将一些示例数据插入到Employees 表中。

INSERT INTO Employees (FirstName, LastName, DepartmentID, Salary)
VALUES
('John', 'Doe', 1, 60000),
('Jane', 'Smith', 2, 65000),
('Sam', 'Brown', 1, 62000),
('Sue', 'Green', 3, 67000);

第三步：创建存储过程

让我们创建一个存储过程来根据部门检索员工。

CREATE PROCEDURE GetEmployeesByDepartment(IN depID INT)
BEGIN
   SELECT EmployeeID, FirstName, LastName, DepartmentID, Salary
   FROM Employees
   WHERE DepartmentID = depID;
END;

stored-procedures

第四步：调用存储过程

要调用存储过程并检索特定部门的员工，请使用 CALL 语句。

CALL GetEmployeesByDepartment(1);

stored-procedures

SQL Server

在SQL Server (opens in a new tab)中，存储过程的创建和执行略有不同，但没有太大变化。这是一个例子：

第一步：创建员工表

首先，让我们创建一个示例员工表。

CREATE TABLE Employees (
   EmployeeID INT PRIMARY KEY IDENTITY(1,1),
   FirstName NVARCHAR(50),
   LastName NVARCHAR(50),
   DepartmentID INT,
   Salary DECIMAL(10, 2)
);

第二步：插入示例数据

接下来，我们将向Employees 表插入一些示例数据。

INSERT INTO Employees (FirstName, LastName, DepartmentID, Salary)
VALUES
('John', 'Doe', 1, 60000),
('Jane', 'Smith', 2, 65000),
('Sam', 'Brown', 1, 62000),
('Sue', 'Green', 3, 67000);

第三步：创建存储过程

让我们创建一个存储过程来根据部门检索员工。

CREATE PROCEDURE GetEmployeesByDepartment
   @DepartmentID INT
AS
BEGIN
   SELECT EmployeeID, FirstName, LastName, DepartmentID, Salary
   FROM Employees
   WHERE DepartmentID = @DepartmentID;
END;

stored-procedures

第四步：执行存储过程

要执行存储过程并检索特定部门的员工，请使用 EXEC 语句。

EXEC GetEmployeesByDepartment @DepartmentID = 1;

stored-procedures

Oracle

Oracle 也支持存储过程。以下是有关如何使用 SQL 在 Oracle 中实现它们的分步指南。

第一步：创建员工表

首先，让我们创建一个示例员工表。

CREATE TABLE Employees (
   EmployeeID NUMBER PRIMARY KEY,
   FirstName VARCHAR2(50),
   LastName VARCHAR2(50),
   DepartmentID NUMBER,
   Salary NUMBER(10, 2)
);

第二步：插入示例数据

接下来，我们将一些示例数据插入到员工表中以创建数据集。

INSERT INTO Employees (EmployeeID, FirstName, LastName, DepartmentID, Salary)
VALUES(1, 'John', 'Doe', 1, 60000);
INSERT INTO Employees (EmployeeID, FirstName, LastName, DepartmentID, Salary)
VALUES(2, 'Jane', 'Smith', 2, 65000);
INSERT INTO Employees (EmployeeID, FirstName, LastName, DepartmentID, Salary)
VALUES(3, 'Sam', 'Brown', 1, 62000);
INSERT INTO Employees (EmployeeID, FirstName, LastName, DepartmentID, Salary)
VALUES(4, 'Sue', 'Green', 3, 67000);

第三步：创建存储过程

让我们创建一个存储过程来根据部门检索员工。

CREATE OR REPLACE PROCEDURE GetEmployeesByDepartment (
   p_DepartmentID IN NUMBER
)
AS
BEGIN
   FOR r IN (SELECT EmployeeID, FirstName, LastName, DepartmentID, Salary
             FROM Employees
             WHERE DepartmentID = p_DepartmentID)
   LOOP
       DBMS_OUTPUT.PUT_LINE('EmployeeID: ' || r.EmployeeID ||
                            ', FirstName: ' || r.FirstName ||
                            ', LastName: ' || r.LastName ||
                            ', DepartmentID: ' || r.DepartmentID ||
                            ', Salary: ' || r.Salary);
   END LOOP;
END;

stored-procedures

设计存储过程：最佳实践

在结束本次实践介绍之后，让我们看一下设计存储过程的一些最佳实践。

使用参数化查询

存储过程中的参数化查询有助于防止 SQL 注入攻击 (opens in a new tab)。始终使用参数，而不是将用户输入直接连接到 SQL 语句中。

例如，不要使用这个：

SELECT * FROM Products WHERE ProductID = '101';

使用这个：

DECLARE p_ProductID INT;
SET p_ProductID = 101;
SELECT * FROM Products WHERE ProductID = p_ProductID;

限制对基础表的访问

如前所述，存储过程可以通过限制对基础表的直接访问来充当安全层。这降低了敏感数据被暴露的风险。

优化SQL代码

为了保证存储过程的高效运行，应当进行性能优化。这意味着要减少不必要的计算，并且要合理利用索引。可以通过分析查询的执行计划来发现并解决性能瓶颈，从而提升查询效率。

例如，应该避免使用“SELECT *”来获取表中的所有字段，因为这样做会增加数据传输量并且降低效率。相反，应当只选择需要的字段来缩小数据检索范围，以此来提高性能。

记录您的存储过程

记录代码同样适用于存储过程的编写。这对于其他开发者理解各个过程的作用和功能至关重要。同时，这也促进了命名规范和编码风格的一致性。

这一过程可以通过在存储过程中添加注释或者维护单独的文档来实现。例如：

-- This stored procedure retrieves all orders for a specific customer
CREATE PROCEDURE GetCustomerOrders
   @CustomerID INT
AS
BEGIN
   SELECT * FROM Orders WHERE CustomerID = @CustomerID;
END
/*
Stored Procedure Name: GetCustomerOrders
Purpose: Retrieve all orders associated with a given customer ID.
Input Parameters:
CustomerID: The ID of the customer whose orders are being retrieved.
Output: A list of order records from the Orders table
*/

维护版本控制

版本控制对于管理和追踪存储过程的变更来说是非常关键的。保持一个包含完整存储过程脚本及其文档变更历史的仓库是很有帮助的。这样做不仅便于跟踪所有的修改记录，还能确保在不同部署环境之间的一致性。

最后的想法

存储过程是一种高效且安全的数据库管理手段。它们提供了诸多优势，当配合恰当的最佳实践使用时，能够显著提升组织内部数据分析的效率和效果。

开始体验 Chat2DB Pro

如果你正在寻找一款强大、基于 AI 的数据库管理工具，快来试试 Chat2DB 吧！无论你是数据库管理员、开发者还是数据分析师，Chat2DB 都能通过 AI 的强大功能简化你的工作。

👉现在享受 Chat2DB Pro 30 天免费试用 (opens in a new tab)，即刻体验所有高级功能。

什么是数据库触发器？Chat2DB vs Datagrip：数据库管理工具的全面对比