存储过程（Stored Procedure）是指一组预编译的SQL语句，这些语句存储在数据库中，可以反复执行。存储过程使得数据库的操作可以模块化，便于管理和复用，并且在执行过程中可以提高性能和安全性。
存储过程的优点
- 性能提升：由于存储过程是预编译的，数据库引擎不需要每次执行时重新编译。
- 代码复用：存储过程可以在多个应用程序中复用，减少代码重复。
- 安全性：通过存储过程可以限制对底层数据表的直接访问，提高数据安全性。
- 简化复杂操作：将复杂的业务逻辑封装在存储过程中，减少应用程序代码的复杂度。
创建存储过程
在 MySQL 中，可以使用 CREATE PROCEDURE 语句来创建存储过程。以下是创建存储过程的基本语法：
CREATE PROCEDURE procedure_name (parameter_list)
BEGIN
-- SQL 语句
END;
示例代码
假设我们有一个 employees 表，我们将创建一个存储过程来插入新员工记录。
-- 创建数据库
CREATE DATABASE company;
-- 选择数据库
USE company;
-- 创建 employees 表
CREATE TABLE employees (
emp_id INT AUTO_INCREMENT PRIMARY KEY,
emp_name VARCHAR(100) NOT NULL,
emp_position VARCHAR(100),
emp_salary DECIMAL(10, 2),
hire_date DATE
);
-- 创建存储过程
DELIMITER //
CREATE PROCEDURE AddEmployee (
IN name VARCHAR(100),
IN position VARCHAR(100),
IN salary DECIMAL(10, 2),
IN hire_date DATE
)
BEGIN
INSERT INTO employees (emp_name, emp_position, emp_salary, hire_date)
VALUES (name, position, salary, hire_date);
END;
//
DELIMITER ;
调用存储过程
使用 CALL 语句调用存储过程：
CALL AddEmployee('John Doe', 'Manager', 75000.00, '2023-10-01');
带输出参数的存储过程
存储过程还可以包含输出参数，用于返回结果。以下是一个带输出参数的存储过程示例：
-- 创建存储过程，计算员工的年薪
DELIMITER //
CREATE PROCEDURE CalculateAnnualSalary (
IN emp_id INT,
OUT annual_salary DECIMAL(10, 2)
)
BEGIN
SELECT emp_salary * 12 INTO annual_salary
FROM employees
WHERE employees.emp_id = emp_id;
END;
//
DELIMITER ;
调用带输出参数的存储过程：
CALL CalculateAnnualSalary(1, @annual_salary);
SELECT @annual_salary;
带条件逻辑的存储过程
存储过程可以包含条件逻辑，如 IF、CASE 等语句。以下是一个包含条件逻辑的存储过程示例：
-- 创建存储过程，根据职位调整员工薪水
DELIMITER //
CREATE PROCEDURE AdjustSalary (
IN emp_id INT,
IN adjustment DECIMAL(10, 2)
)
BEGIN
DECLARE current_salary DECIMAL(10, 2);
-- 获取当前薪水
SELECT emp_salary INTO current_salary
FROM employees
WHERE employees.emp_id = emp_id;
-- 调整薪水
IF current_salary IS NOT NULL THEN
UPDATE employees
SET emp_salary = current_salary + adjustment
WHERE employees.emp_id = emp_id;
END IF;
END;
//
DELIMITER ;
调用带条件逻辑的存储过程：
CALL AdjustSalary(1, 5000.00);
带循环的存储过程
存储过程还可以包含循环语句，如 LOOP、WHILE 等。以下是一个包含循环的存储过程示例：
-- 创建存储过程，批量增加员工薪水
DELIMITER //
CREATE PROCEDURE BatchAdjustSalaries (
IN adjustment DECIMAL(10, 2)
)
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE emp_id INT;
DECLARE cur CURSOR FOR SELECT emp_id FROM employees;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
OPEN cur;
read_loop: LOOP
FETCH cur INTO emp_id;
IF done THEN
LEAVE read_loop;
END IF;
-- 调用 AdjustSalary 存储过程
CALL AdjustSalary(emp_id, adjustment);
END LOOP;
CLOSE cur;
END;
//
DELIMITER ;
调用带循环的存储过程：
CALL BatchAdjustSalaries(1000.00);
完整的存储过程示例
以下是一个完整的存储过程示例，从创建表到定义和调用存储过程的完整过程。
-- 创建数据库
CREATE DATABASE company;
-- 选择数据库
USE company;
-- 创建 employees 表
CREATE TABLE employees (
emp_id INT AUTO_INCREMENT PRIMARY KEY,
emp_name VARCHAR(100) NOT NULL,
emp_position VARCHAR(100),
emp_salary DECIMAL(10, 2),
hire_date DATE
);
-- 创建 AddEmployee 存储过程
DELIMITER //
CREATE PROCEDURE AddEmployee (
IN name VARCHAR(100),
IN position VARCHAR(100),
IN salary DECIMAL(10, 2),
IN hire_date DATE
)
BEGIN
INSERT INTO employees (emp_name, emp_position, emp_salary, hire_date)
VALUES (name, position, salary, hire_date);
END;
//
DELIMITER ;
-- 调用 AddEmployee 存储过程
CALL AddEmployee('John Doe', 'Manager', 75000.00, '2023-10-01');
-- 创建 CalculateAnnualSalary 存储过程
DELIMITER //
CREATE PROCEDURE CalculateAnnualSalary (
IN emp_id INT,
OUT annual_salary DECIMAL(10, 2)
)
BEGIN
SELECT emp_salary * 12 INTO annual_salary
FROM employees
WHERE employees.emp_id = emp_id;
END;
//
DELIMITER ;
-- 调用 CalculateAnnualSalary 存储过程
CALL CalculateAnnualSalary(1, @annual_salary);
SELECT @annual_salary;
通过以上代码示例，展示了如何在 MySQL 中创建和调用存储过程，以及存储过程中的各种逻辑控制和参数类型。存储过程是强大的数据库工具，可以帮助简化复杂操作，增强系统性能和安全性。
