在 MySQL 中，存储过程（Stored Procedure）是一组预编译的 SQL 语句，可以通过名称和参数进行调用。存储过程可以简化复杂的数据库操作，提高代码的复用性和性能，并增强安全性。以下详细介绍如何创建和调用存储过程，并展示一些具体的代码示例。
创建存储过程
使用 CREATE PROCEDURE 语句创建存储过程。存储过程可以包含输入参数（IN）、输出参数（OUT）和输入输出参数（INOUT），可以包含多条 SQL 操作，包含条件逻辑、循环等。
语法
CREATE PROCEDURE procedure_name ([parameter_list])
BEGIN
-- SQL 语句
END;
示例：简单存储过程
下面创建一个简单的存储过程 AddEmployee，用于插入新员工记录。
-- 创建数据库
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 ;
调用存储过程
使用 CALL 语句调用存储过程。
示例：调用 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 ;
调用带输出参数的存储过程：
CALL CalculateAnnualSalary(1, @annual_salary);
SELECT @annual_salary;
带条件逻辑的存储过程
存储过程可以包含条件逻辑，如 IF、CASE 等语句。
-- 创建 AdjustSalary 存储过程，调整员工薪水
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 等。
-- 创建 BatchAdjustSalaries 存储过程，批量增加员工薪水
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;
-- 创建 AdjustSalary 存储过程
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 ;
-- 调用 AdjustSalary 存储过程
CALL AdjustSalary(1, 5000.00);
-- 创建 BatchAdjustSalaries 存储过程
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 ;
-- 调用 BatchAdjustSalaries 存储过程
CALL BatchAdjustSalaries(1000.00);
通过以上代码示例，展示了如何在 MySQL 中创建和调用存储过程，以及存储过程中的各种逻辑控制和参数类型。存储过程是强大的数据库工具，可以帮助简化复杂操作，增强系统性能和安全性。