mysql菜鸟教程

首页 >> mysql菜鸟教程

专栏导航

14.4 带参数的存储过程

在上一节中，我们学习了存储过程的基本概念和创建方法。但那些存储过程都是“死”的——它们只能执行固定的操作。现实中的业务需求往往是变化的，比如查询不同员工的信息、按照不同百分比加薪等。这就需要存储过程能够接收外部传入的值，并根据这些值执行相应的逻辑。参数正是为了解决这个问题而设计的。

一、为什么需要参数？

参数让存储过程变得更加灵活和通用。通过参数，你可以：

将外部数据传递给存储过程，使其执行不同的操作。
从存储过程中获取计算结果或状态信息。
实现数据的双向传递（传入并返回修改后的值）。

二、参数的类型

MySQL 存储过程支持三种类型的参数，每种类型都有其特定的用途：

类型

说明

方向

使用场景

输入参数

调用者 → 存储过程

传递查询条件、要更新的值等，过程内部不能修改并返回。

OUT

输出参数

存储过程 → 调用者

返回计算结果、状态码、行数等，调用者需传入变量接收。

INOUT

输入输出参数

双向传递

传入一个值，过程内部修改后返回新值。

参数定义语法

在创建存储过程时，参数定义放在过程名后面的括号内：

CREATE PROCEDURE 过程名(
    [IN | OUT | INOUT] 参数名 数据类型,
    [IN | OUT | INOUT] 参数名 数据类型,
    ...
)
BEGIN
    -- 过程体
END;

如果不指定参数类型，默认为 IN。
数据类型可以是任何有效的 MySQL 数据类型（如 INT、VARCHAR、DECIMAL、DATE 等）。

三、实战示例

我们将继续使用 employees 表来演示不同类型的参数。

CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    department VARCHAR(50),
    salary DECIMAL(10,2),
    hire_date DATE
);

INSERT INTO employees (name, department, salary, hire_date) VALUES
('张三', '技术部', 8000, '2023-01-10'),
('李四', '市场部', 7000, '2023-02-15'),
('王五', '技术部', 9000, '2023-03-20'),
('赵六', '销售部', 6000, '2023-04-01');

示例1：使用 IN 参数查询员工

创建一个存储过程，根据传入的员工姓名（或ID）查询该员工信息。

DELIMITER //

CREATE PROCEDURE GetEmployeeByName(IN emp_name VARCHAR(50))
BEGIN
    SELECT * FROM employees WHERE name = emp_name;
END //

DELIMITER ;

调用：

CALL GetEmployeeByName('张三');

如果传入的名字不存在，结果集为空。

扩展：可以传入多个参数，例如同时查询部门和最低工资：

DELIMITER //
CREATE PROCEDURE GetEmployeesByDeptAndSalary(
    IN dept VARCHAR(50),
    IN min_salary DECIMAL(10,2)
)
BEGIN
    SELECT * FROM employees
    WHERE department = dept AND salary >= min_salary;
END //
DELIMITER ;

CALL GetEmployeesByDeptAndSalary('技术部', 8000);

示例2：使用 OUT 参数返回统计值

有时我们不需要返回结果集，而只需要获取一个计算值（如总人数、平均工资）。这时可以用 OUT 参数。

DELIMITER //

CREATE PROCEDURE CountEmployeesByDept(
    IN dept VARCHAR(50),
    OUT total INT
)
BEGIN
    SELECT COUNT(*) INTO total
    FROM employees
    WHERE department = dept;
END //

DELIMITER ;

调用：

CALL CountEmployeesByDept('技术部', @count);
SELECT @count;  -- 输出 2

这里使用了 SELECT ... INTO 将查询结果直接赋值给 OUT 参数。

示例3：使用 INOUT 参数修改并返回值

INOUT 参数既接收外部值，又可以将修改后的值传回。例如，我们想对员工的工资进行调整，并返回调整后的新工资。

DELIMITER //

CREATE PROCEDURE AdjustSalary(
    IN emp_id INT,
    INOUT amount DECIMAL(10,2)   -- 传入调整金额，返回调整后工资
)
BEGIN
    -- 获取当前工资
    DECLARE current_salary DECIMAL(10,2);
    
    SELECT salary INTO current_salary
    FROM employees WHERE id = emp_id;
    
    -- 更新工资
    UPDATE employees
    SET salary = current_salary + amount
    WHERE id = emp_id;
    
    -- 将 amount 设置为新工资
    SET amount = current_salary + amount;
END //

DELIMITER ;

调用：

SET @adjust = 500;   -- 要增加的金额
CALL AdjustSalary(1, @adjust);
SELECT @adjust;      -- 返回调整后的工资（如 8500）

注意：调用时必须使用变量（以 @ 开头），不能直接传常量。

三、结合流程控制与参数

参数可以与流程控制语句结合，实现更复杂的逻辑。例如，根据不同的部门实施不同的加薪策略。

DELIMITER //

CREATE PROCEDURE RaiseSalaryByDept(
    IN dept_name VARCHAR(50),
    IN percent DECIMAL(5,2),
    OUT affected_rows INT,
    OUT new_avg DECIMAL(10,2)
)
BEGIN
    -- 更新工资
    UPDATE employees
    SET salary = salary * (1 + percent/100)
    WHERE department = dept_name;
    
    -- 获取受影响行数
    SET affected_rows = ROW_COUNT();
    
    -- 计算新的平均工资
    SELECT AVG(salary) INTO new_avg
    FROM employees
    WHERE department = dept_name;
END //

DELIMITER ;

调用：

CALL RaiseSalaryByDept('技术部', 10, @rows, @avg);
SELECT @rows, @avg;

四、注意事项

参数命名：避免与列名相同，以免引起歧义。通常参数名加前缀（如 p_、in_）或使用有意义的名称。
数据类型匹配：传入的参数值必须与参数类型兼容，否则会出错或产生隐式转换。
OUT/INOUT 参数必须使用变量：调用时不能直接传递常量，必须使用用户变量（如 @var）。
作用域：参数在整个存储过程内部有效，其作用域类似于局部变量。
NULL 值处理：参数可以接受 NULL 值，但在使用时需要合理处理，避免导致意外结果。
OUT 参数不接收输入值：即使你在调用时给 OUT 参数变量赋了值，该值也不会传入存储过程。存储过程开始执行时，OUT 参数的值为 NULL。
INOUT 参数会接收传入的值，并在过程体内可以修改。

小结

存储过程通过参数实现与调用者的数据交换。
IN 参数用于传入值，过程内部只读。
OUT 参数用于返回计算值，过程内部赋值，调用者通过变量获取。
INOUT 参数双向传递，可以修改并返回。
参数极大地增强了存储过程的灵活性和实用性，是编写可复用业务逻辑的基础。