一、什么是存储过程?
存储过程(Stored Procedure)是数据库中的一种可编程对象,它是一组为了完成特定功能的SQL语句集合,存储在数据库中并以名称标识。存储过程可以接收输入参数,返回输出参数,并在服务器端运行。这种方式提高了代码的复用性和性能,因为多次执行只需要发送调用命令,而不是多次传输SQL语句。
存储过程的优点包括:
- 性能优化:由于存储过程在数据库端运行,减少了网络通信。
- 代码复用:可以定义复杂的逻辑并重复使用。
- 安全性:通过限制访问权限,可以防止直接访问底层表。
- 简化复杂性:封装复杂的SQL逻辑,提高代码的可读性。
二、MySQL 中的存储过程代码演示
下面是一个完整的存储过程创建、调用和删除的示例:
(一)不带参数的存储过程
1. 创建示例数据库和表
CREATE DATABASE IF NOT EXISTS example_db;
USE example_db;
CREATE TABLE employees (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100),
salary DECIMAL(10, 2),
department VARCHAR(50)
);
-- 插入一些测试数据
INSERT INTO employees (name, salary, department)
VALUES
('Alice', 5000.00, 'HR'),
('Bob', 7000.00, 'IT'),
('Charlie', 6500.00, 'Finance');
2. 创建存储过程
需求:创建一个存储过程,目的是,根据部门名称查询员工信息。
DELIMITER $$
CREATE PROCEDURE GetEmployeesByDepartment(
IN dept_name VARCHAR(50) -- 输入参数,部门名称
)
BEGIN
-- 查询指定部门的员工
SELECT id, name, salary, department
FROM employees
WHERE department = dept_name;
END $$
DELIMITER ;
说明:
DELIMITER $$
:更改语句结束符,防止存储过程定义中的分号冲突。IN dept_name VARCHAR(50)
:定义输入参数
,表示调用时需要提供部门名称。CALL GetEmployeesByDepartment('IT');
SELECT ...
:存储过程的主体逻辑。
3. 调用存储过程
使用 CALL
关键字调用存储过程。
CALL GetEmployeesByDepartment('IT');
输出示例:
+----+------+--------+------------+
| id | name | salary | department |
+----+------+--------+------------+
| 2 | Bob | 7000.00| IT |
+----+------+--------+------------+
(二)带参数的存储过程
需求:计算某个部门的员工平均工资,并通过输出参数返回。
DELIMITER $$
CREATE PROCEDURE GetAverageSalaryByDepartment(
IN dept_name VARCHAR(50), -- 输入参数
OUT avg_salary DECIMAL(10, 2) -- 输出参数
)
BEGIN
-- 计算平均工资
SELECT AVG(salary) INTO avg_salary
FROM employees
WHERE department = dept_name;
END $$
DELIMITER ;
调用存储过程并获取结果:
SET @avg_salary = 0; -- 定义变量
CALL GetAverageSalaryByDepartment('HR', @avg_salary);
SELECT @avg_salary AS AverageSalary; -- 查看结果
输出示例:
+---------------+
| AverageSalary |
+---------------+
| 5000.00 |
+---------------+
5. 删除存储过程
如果需要删除存储过程,可以使用 DROP PROCEDURE
语句。
DROP PROCEDURE IF EXISTS GetEmployeesByDepartment;
DROP PROCEDURE IF EXISTS GetAverageSalaryByDepartment;
总结
存储过程是数据库中一个强大的工具,它封装了复杂的业务逻辑,提高了性能和代码复用性。MySQL 的存储过程支持输入参数、输出参数和逻辑控制语句,可以满足多种业务需求。在设计存储过程时需要注意性能优化和安全性,避免过度复杂化导致难以维护。