【MySQL】提高篇—视图与存储过程：存储过程（Procedure）的创建与调用
在关系数据库中，存储过程（Stored Procedure）是一组预编译的 SQL 语句和可选的控制流语句（如条件语句和循环语句），它们被存储在数据库中并可以被客户端应用程序或数据库用户调用。存储过程可以接受输入参数，返回输出参数，并且可以执行复杂的操作。
存储过程在实际应用中具有重要性，主要体现在以下几个方面：
-
提高性能：由于存储过程在数据库中预编译并存储，可以减少网络传输的开销，并提高执行效率。
-
封装逻辑：将复杂的业务逻辑封装在存储过程中，可以简化应用程序代码，提高代码的可维护性。
-
安全性：通过存储过程，可以限制用户对底层表的直接访问，只允许通过存储过程进行操作，从而增强数据安全性。
-
事务控制：存储过程可以包含事务控制语句，使得在执行多个操作时可以保证数据的一致性和完整性。
存储过程的创建与调用
存储过程的创建使用 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)
)
BEGININSERT 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)
)
BEGINUPDATE employeesSET salary = new_salaryWHERE 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的存储过程，存储过程定义将不再存在。
总结
通过示例能够理解存储过程的创建与调用方法：
-
存储过程的定义：存储过程是一组预编译的 SQL 语句，可以封装复杂的逻辑。
-
创建存储过程：使用
CREATE PROCEDURE语句定义存储过程，并使用DELIMITER更改语句结束符。 -
调用存储过程：使用
CALL语句来调用存储过程并传递参数。 -
更新存储过程：可以创建新的存储过程来执行不同的操作，如更新数据。
-
删除存储过程：使用
DROP PROCEDURE命令删除不再需要的存储过程。