【MySQL】提高篇—视图与存储过程：存储过程（Procedure）的创建与调用

在关系数据库中，存储过程（Stored Procedure）是一组预编译的 SQL 语句和可选的控制流语句（如条件语句和循环语句），它们被存储在数据库中并可以被客户端应用程序或数据库用户调用。存储过程可以接受输入参数，返回输出参数，并且可以执行复杂的操作。

存储过程在实际应用中具有重要性，主要体现在以下几个方面：

1. 提高性能：由于存储过程在数据库中预编译并存储，可以减少网络传输的开销，并提高执行效率。

2. 封装逻辑：将复杂的业务逻辑封装在存储过程中，可以简化应用程序代码，提高代码的可维护性。

3. 安全性：通过存储过程，可以限制用户对底层表的直接访问，只允许通过存储过程进行操作，从而增强数据安全性。

4. 事务控制：存储过程可以包含事务控制语句，使得在执行多个操作时可以保证数据的一致性和完整性。

存储过程的创建与调用

存储过程的创建使用 CREATE PROCEDURE 语句，基本语法如下：

CREATE PROCEDURE procedure_name (parameters)
BEGIN
    -- SQL statements
END;

示例

假设我们有一个简单的员工表 employees，其结构如下：

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    department VARCHAR(50),
    salary DECIMAL(10, 2)
);

我们可以插入一些示例数据：

INSERT INTO employees (employee_id, first_name, last_name, department, salary) VALUES
(1, 'John', 'Doe', 'Engineering', 75000),
(2, 'Jane', 'Smith', 'Marketing', 60000),
(3, 'Alice', 'Johnson', 'Engineering', 80000),
(4, 'Bob', 'Brown', 'Sales', 55000);

创建存储过程

示例 1：创建一个简单的存储过程

我们希望创建一个存储过程，用于插入新员工记录。

-- 创建存储过程
DELIMITER //
CREATE PROCEDURE AddEmployee(
    IN emp_id INT,
    IN first_name VARCHAR(50),
    IN last_name VARCHAR(50),
    IN department VARCHAR(50),
    IN salary DECIMAL(10, 2)
)
BEGIN
    INSERT INTO employees (employee_id, first_name, last_name, department, salary)
    VALUES (emp_id, first_name, last_name, department, salary);
END //
DELIMITER ;

解释：

• DELIMITER //：改变语句结束符，避免在存储过程中出现的分号被误认为是语句结束。这里我们使用 // 作为新的结束符。

• CREATE PROCEDURE AddEmployee(...)：定义一个名为 AddEmployee 的存储过程，接收多个输入参数。

• BEGIN ... END：存储过程的主体，包含要执行的 SQL 语句。

• INSERT INTO employees ...：将输入参数插入到 employees 表中。

调用存储过程

一旦存储过程被创建，我们可以使用 CALL 语句来调用它。

-- 调用存储过程
CALL AddEmployee(5, 'Charlie', 'Davis', 'HR', 65000);

解释：

• CALL AddEmployee(...)：调用名为 AddEmployee 的存储过程，并传递相应的参数。

• 这个调用将会在 employees 表中插入一条新的员工记录。

查询数据

在调用存储过程后，我们可以查询 employees 表来验证数据是否成功插入。

-- 查询员工表
SELECT * FROM employees;

解释：

• 这个查询将返回 employees 表中的所有记录，包括刚刚插入的员工 Charlie Davis。

更新存储过程

我们可以创建一个存储过程来更新员工的薪水。

示例 2：创建更新薪水的存储过程

-- 创建存储过程更新员工薪水
DELIMITER //
CREATE PROCEDURE UpdateEmployeeSalary(
    IN emp_id INT,
    IN new_salary DECIMAL(10, 2)
)
BEGIN
    UPDATE employees
    SET salary = new_salary
    WHERE employee_id = emp_id;
END //
DELIMITER ;

解释：

• CREATE PROCEDURE UpdateEmployeeSalary(...)：定义一个名为 UpdateEmployeeSalary 的存储过程，接收员工 ID 和新的薪水作为输入参数。

• UPDATE employees SET salary = new_salary WHERE employee_id = emp_id;：更新指定员工的薪水。

调用更新存储过程

我们可以调用这个存储过程来更新某个员工的薪水。

-- 调用存储过程更新薪水
CALL UpdateEmployeeSalary(3, 85000);

解释：

• 这个调用将更新员工 ID 为 3 的员工（即 Alice Johnson）的薪水为 85000。

查询更新后的数据

我们可以再次查询 employees 表来验证薪水是否成功更新。

-- 查询员工表
SELECT * FROM employees;

解释：

• 这个查询将返回 employees 表中的所有记录，包括更新后的员工薪水。

删除存储过程

如果不再需要某个存储过程，可以使用 DROP PROCEDURE 命令删除它。

-- 删除存储过程
DROP PROCEDURE AddEmployee;

解释：

• 这个命令将删除名为 AddEmployee 的存储过程，存储过程定义将不再存在。

总结

通过示例能够理解存储过程的创建与调用方法：

1. 存储过程的定义：存储过程是一组预编译的 SQL 语句，可以封装复杂的逻辑。

2. 创建存储过程：使用 CREATE PROCEDURE 语句定义存储过程，并使用 DELIMITER 更改语句结束符。

3. 调用存储过程：使用 CALL 语句来调用存储过程并传递参数。

4. 更新存储过程：可以创建新的存储过程来执行不同的操作，如更新数据。

5. 删除存储过程：使用 DROP PROCEDURE 命令删除不再需要的存储过程。