MySQL存储过程之：`存储过程`的参数类型：`IN`、`OUT`和`INOUT`参数的用法与区别。

MySQL存储过程：参数类型深度剖析（IN、OUT、INOUT）

大家好，今天我们来深入探讨MySQL存储过程中三种参数类型：IN、OUT和INOUT。 理解它们之间的区别和用法对于编写高效、灵活的存储过程至关重要。我们将通过大量的代码示例，结合实际应用场景，彻底掌握这些概念。

1. IN参数：输入参数

IN参数是存储过程最常见的参数类型。 它的作用是将调用者（例如应用程序或另一个存储过程）传递的值传递给存储过程。存储过程内部可以读取IN参数的值，但不能修改它，修改对调用者不可见。换句话说，IN参数是只读的。

语法:

CREATE PROCEDURE procedure_name (IN param_name data_type)
BEGIN
  -- 存储过程体
END;

示例：根据客户ID查询客户信息

假设我们有一个customers表，包含以下字段：customer_id (INT), customer_name (VARCHAR), city (VARCHAR)。 我们创建一个存储过程，根据传入的customer_id查询客户信息。

DROP PROCEDURE IF EXISTS get_customer_by_id;
CREATE PROCEDURE get_customer_by_id (IN cust_id INT)
BEGIN
  SELECT customer_name, city
  FROM customers
  WHERE customer_id = cust_id;
END;

-- 调用存储过程
CALL get_customer_by_id(123);

在这个例子中，cust_id 是一个IN参数。调用者传递123作为cust_id的值，存储过程读取这个值，并在WHERE子句中使用它来过滤customers表。存储过程内部不能修改cust_id的值，即使修改了，也不会影响调用者传递的原始值。

更复杂的例子：根据城市和最小订单金额查询客户

DROP PROCEDURE IF EXISTS get_customers_by_city_and_min_order;
CREATE PROCEDURE get_customers_by_city_and_min_order (
  IN city_name VARCHAR(50),
  IN min_order_amount DECIMAL(10,2)
)
BEGIN
  SELECT c.customer_name
  FROM customers c
  JOIN orders o ON c.customer_id = o.customer_id
  WHERE c.city = city_name
  GROUP BY c.customer_id
  HAVING SUM(o.order_amount) >= min_order_amount;
END;

-- 调用存储过程
CALL get_customers_by_city_and_min_order('New York', 1000.00);

这里，city_name和min_order_amount都是IN参数，分别用于过滤城市和最小订单金额。

IN参数的特点：

• 从调用者传递数据到存储过程。
• 存储过程内部只读，不能修改。
• 是最常用的参数类型。

2. OUT参数：输出参数

OUT参数用于将存储过程的处理结果返回给调用者。 在存储过程内部，我们需要先为OUT参数赋值，然后调用者才能获取到这个值。

语法:

CREATE PROCEDURE procedure_name (OUT param_name data_type)
BEGIN
  -- 存储过程体
END;

示例：获取客户总数

我们创建一个存储过程，计算customers表中的客户总数，并将结果通过OUT参数返回。

DROP PROCEDURE IF EXISTS get_total_customer_count;
CREATE PROCEDURE get_total_customer_count (OUT total_count INT)
BEGIN
  SELECT COUNT(*) INTO total_count FROM customers;
END;

-- 调用存储过程
SET @customer_count = 0; -- 声明一个用户变量
CALL get_total_customer_count(@customer_count);
SELECT @customer_count; -- 查看结果

在这个例子中，total_count是一个OUT参数。 在存储过程内部，我们使用SELECT COUNT(*) INTO total_count将客户总数赋值给total_count。 然后，调用者可以通过用户变量@customer_count来获取这个值。

更复杂的例子：获取指定城市客户的平均订单金额

DROP PROCEDURE IF EXISTS get_avg_order_amount_by_city;
CREATE PROCEDURE get_avg_order_amount_by_city (
  IN city_name VARCHAR(50),
  OUT avg_amount DECIMAL(10,2)
)
BEGIN
  SELECT AVG(o.order_amount) INTO avg_amount
  FROM customers c
  JOIN orders o ON c.customer_id = o.customer_id
  WHERE c.city = city_name;
END;

-- 调用存储过程
SET @average_amount = 0.00;
CALL get_avg_order_amount_by_city('New York', @average_amount);
SELECT @average_amount;

这里，city_name是一个IN参数，用于指定城市；avg_amount是一个OUT参数，用于返回平均订单金额。

OUT参数的特点：

• 从存储过程传递数据到调用者。
• 在存储过程内部需要赋值。
• 调用者需要使用用户变量来接收返回值。

3. INOUT参数：输入/输出参数

INOUT参数是IN和OUT参数的结合体。它既可以从调用者传递数据到存储过程，又可以从存储过程将处理结果返回给调用者。 在调用存储过程之前，需要先为INOUT参数赋值；在存储过程内部，可以读取和修改INOUT参数的值；存储过程执行完毕后，调用者可以通过同一个变量获取修改后的值。

语法:

CREATE PROCEDURE procedure_name (INOUT param_name data_type)
BEGIN
  -- 存储过程体
END;

示例：将一个数字加倍

我们创建一个存储过程，将传入的数字加倍，并将结果通过INOUT参数返回。

DROP PROCEDURE IF EXISTS double_number;
CREATE PROCEDURE double_number (INOUT num INT)
BEGIN
  SET num = num * 2;
END;

-- 调用存储过程
SET @number = 5;
CALL double_number(@number);
SELECT @number; -- 输出 10

在这个例子中，num是一个INOUT参数。 调用者先将@number设置为5，然后传递给存储过程。 存储过程将@number的值乘以2，然后将结果10赋值给@number。 存储过程执行完毕后，调用者通过@number获取到修改后的值10。

更复杂的例子：累加订单金额，并返回累加后的结果

假设我们想要创建一个存储过程，接收一个初始金额和一个客户ID，然后将该客户的所有订单金额累加到初始金额上，并将最终的累加金额返回。

DROP PROCEDURE IF EXISTS accumulate_order_amount;
CREATE PROCEDURE accumulate_order_amount (
  IN cust_id INT,
  INOUT total_amount DECIMAL(10,2)
)
BEGIN
  SELECT SUM(order_amount) INTO @order_sum
  FROM orders
  WHERE customer_id = cust_id;

  IF @order_sum IS NULL THEN
    SET @order_sum = 0;
  END IF;

  SET total_amount = total_amount + @order_sum;
END;

-- 调用存储过程
SET @initial_amount = 100.00;
SET @customer_id = 123;
CALL accumulate_order_amount(@customer_id, @initial_amount);
SELECT @initial_amount; -- 输出累加后的金额

在这个例子中，cust_id是一个IN参数，用于指定客户ID；total_amount是一个INOUT参数，用于传递初始金额，并返回累加后的金额。 在调用存储过程之前，我们需要先为@initial_amount赋值。 存储过程会计算指定客户的所有订单金额，并将结果累加到@initial_amount上。 存储过程执行完毕后，调用者可以通过@initial_amount获取到累加后的金额。

INOUT参数的特点：

• 既可以从调用者传递数据到存储过程，又可以从存储过程传递数据到调用者。
• 在调用存储过程之前需要赋值。
• 在存储过程内部可以读取和修改。
• 调用者通过同一个变量获取修改后的值。

4. 参数类型的选择

选择哪种参数类型取决于存储过程的功能和数据流向。

参数类型	数据流向	是否需要调用者先赋值	存储过程内部是否可修改	用途
IN	调用者 -> 存储过程	是	否	将数据从调用者传递到存储过程，存储过程内部使用这些数据进行计算或查询。
OUT	存储过程 -> 调用者	否	是	将存储过程的处理结果返回给调用者。
INOUT	双向	是	是	将数据从调用者传递到存储过程，存储过程对数据进行修改，并将修改后的数据返回给调用者。 通常用于需要在存储过程中更新的变量，例如计数器、累加器等。

一些建议：

• 尽量使用IN参数，因为它最简单，也最容易理解。
• 只有当存储过程需要将结果返回给调用者时，才使用OUT参数。
• 只有当需要在存储过程中更新变量，并将更新后的值返回给调用者时，才使用INOUT参数。 过度使用 INOUT 参数会使代码难以理解和维护。

5. 使用注意事项

• 数据类型匹配： 调用存储过程时，传递的参数的数据类型必须与存储过程定义的参数的数据类型兼容。
• 变量作用域： IN和INOUT参数的值在存储过程内部可见。 OUT参数的值只有在存储过程执行完毕后才对调用者可见。
• NULL值处理： 需要注意参数可能为NULL的情况，尤其是在使用INOUT参数时。
• 用户变量： 调用存储过程时，需要使用用户变量来接收OUT和INOUT参数的返回值。 用户变量以@开头。
• 参数名称： 存储过程内部可以使用与参数名称相同的局部变量，但建议避免这种情况，以提高代码的可读性。

6. 实际案例分析

案例1：分页查询

我们创建一个存储过程，实现分页查询功能。 该存储过程接收表名、页码和每页记录数作为输入，并返回指定页码的数据。

DROP PROCEDURE IF EXISTS get_paged_data;
CREATE PROCEDURE get_paged_data (
  IN table_name VARCHAR(255),
  IN page_number INT,
  IN page_size INT,
  OUT total_records INT
)
BEGIN
  -- 计算总记录数
  SET @sql = CONCAT('SELECT COUNT(*) INTO @total FROM ', table_name);
  PREPARE stmt FROM @sql;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;

  SET total_records = @total;

  -- 计算起始位置
  SET @offset = (page_number - 1) * page_size;

  -- 构建分页查询SQL
  SET @sql = CONCAT('SELECT * FROM ', table_name, ' LIMIT ', @offset, ', ', page_size);
  PREPARE stmt FROM @sql;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;
END;

-- 调用存储过程
SET @total_count = 0;
CALL get_paged_data('customers', 2, 10, @total_count);
SELECT @total_count; -- 输出总记录数

在这个例子中，table_name、page_number和page_size是IN参数，用于指定表名、页码和每页记录数；total_records是OUT参数，用于返回总记录数。 存储过程使用动态SQL来构建查询语句，并使用LIMIT子句实现分页。

案例2：更新库存

我们创建一个存储过程，用于更新商品的库存。 该存储过程接收商品ID和需要更新的数量作为输入，如果库存不足，则返回错误信息。

DROP PROCEDURE IF EXISTS update_stock;
CREATE PROCEDURE update_stock (
  IN product_id INT,
  IN quantity INT,
  OUT error_message VARCHAR(255)
)
BEGIN
  DECLARE current_stock INT;

  -- 获取当前库存
  SELECT stock_quantity INTO current_stock FROM products WHERE product_id = product_id;

  IF current_stock IS NULL THEN
    SET error_message = 'Product not found';
  ELSEIF current_stock + quantity < 0 THEN
    SET error_message = 'Insufficient stock';
  ELSE
    -- 更新库存
    UPDATE products SET stock_quantity = stock_quantity + quantity WHERE product_id = product_id;
    SET error_message = NULL;
  END IF;
END;

-- 调用存储过程
SET @error = NULL;
CALL update_stock(1, -5, @error);
SELECT @error; -- 输出错误信息或NULL

在这个例子中，product_id和quantity是IN参数，用于指定商品ID和需要更新的数量；error_message是OUT参数，用于返回错误信息。 存储过程首先检查商品是否存在，然后检查库存是否足够。 如果没有错误，则更新库存。

7. 总结与技巧

理解IN、OUT和INOUT参数的区别和用法是掌握MySQL存储过程的关键。 IN参数用于将数据传递给存储过程，OUT参数用于将结果返回给调用者，INOUT参数用于双向传递数据。选择合适的参数类型可以提高存储过程的效率和可读性。 记住，合理使用参数类型，可以让存储过程的功能更加强大，代码也更加清晰。