mysql存储过程返回 list结果集方式

K-seo • 2024-03-02 14:40 • 行业资讯 • 195 views

MySQL存储过程是一种在数据库中存储复杂程序代码的方式，它可以在数据库服务器上运行，而不需要将数据发送到客户端进行处理，存储过程可以接收参数、执行逻辑操作并返回结果，在某些情况下，我们可能需要存储过程返回一个列表（list）结果集，本文将介绍如何在MySQL中使用存储过程返回列表结果集。

1. 创建存储过程

mysql存储过程返回 list结果集方式

我们需要创建一个存储过程，在这个例子中，我们将创建一个名为get_employees的存储过程，它接收一个部门ID作为参数，并返回该部门的所有员工姓名和年龄。

DELIMITER //
CREATE PROCEDURE get_employees(IN department_id INT, OUT employees_list TEXT)
BEGIN
    SELECT JSON_ARRAYAGG(JSON_OBJECT('name', name, 'age', age)) INTO employees_list
    FROM employees
    WHERE department_id = department_id;
END //
DELIMITER ;

在这个存储过程中，我们使用了JSON_ARRAYAGG函数将查询结果转换为一个JSON数组，然后使用JSON_OBJECT函数将每个员工的姓名和年龄组合成一个JSON对象，我们将这个JSON数组赋值给employees_list输出参数。

2. 调用存储过程

接下来，我们需要调用这个存储过程并获取返回的结果集，为了实现这一点，我们可以使用CALL语句：

SET @department_id := 1;
SET @employees_list := '';
CALL get_employees(@department_id, @employees_list);
SELECT @employees_list;

在这个例子中，我们将部门ID设置为1，并将employees_list变量设置为空字符串，我们使用CALL语句调用get_employees存储过程，并将结果赋值给@employees_list变量，我们选择@employees_list变量以查看返回的结果集。

mysql存储过程返回 list结果集方式

3. 处理结果集

由于存储过程返回的是JSON格式的结果集，我们需要使用适当的函数来解析和处理这些数据，在MySQL中，我们可以使用JSON_EXTRACT或->>操作符来提取JSON数组中的值：

SET @department_id := 1;
SET @employees_list := (SELECT JSON_ARRAYAGG(JSON_OBJECT('name', name, 'age', age)) FROM employees WHERE department_id = @department_id);
SELECT name, age FROM JSON_TABLE(@employees_list, '$[*]' COLUMNS(name VARCHAR(255), age INT));

在这个例子中，我们首先使用子查询将查询结果转换为JSON数组，并将其赋值给@employees_list变量，我们使用JSON_TABLE函数将JSON数组转换为一个关系表，并从中选择员工姓名和年龄。