如何将SELECT查询转换为存储函数？实现按班次查询司机详情

针对你的两个需求，我来一步步帮你实现：

一、把SELECT查询转为存储函数的基本思路 #

要把一个SELECT查询封装成存储函数，核心步骤是：

• 定义函数的输入参数（比如这里的shift）
• 指定函数的返回类型（不同数据库对多行结果的处理方式略有不同，下面会针对主流数据库给出方案）
• 在函数体内编写查询逻辑，将硬编码的条件替换为参数
• 处理返回结果（比如MySQL中可返回JSON数组，PostgreSQL可直接返回表类型）

二、针对你的需求编写存储函数 #

我把原查询中的隐式连接改成了显式JOIN语法，这是更规范的SQL写法，逻辑和原查询完全一致。

方案1：MySQL中返回JSON格式的司机记录集（适合8.0+版本） #

如果你的数据库是MySQL 8.0及以上，可以利用JSON函数把查询结果打包成JSON数组返回：

DELIMITER //

CREATE FUNCTION GetDriversByShift(p_shift VARCHAR(10)) 
RETURNS JSON
DETERMINISTIC
BEGIN
    DECLARE result JSON;
    SELECT JSON_ARRAYAGG(
        JSON_OBJECT(
            'driver_no', driver.driver_no,
            'driver_name', driver.driver_name,
            'licence_no', driver.licence_no,
            'address', driver.address,
            'd_age', driver.d_age,
            'salary', driver.salary
        )
    ) INTO result
    FROM driver
    JOIN bus_driver AS bd ON bd.driver_no = driver.driver_no
    WHERE bd.shift = p_shift;
    
    RETURN result;
END //

DELIMITER ;

方案2：MySQL中用存储过程返回表格型记录 #

如果希望直接返回传统的表格形式结果，MySQL的存储函数无法实现，但可以用存储过程替代：

DELIMITER //

CREATE PROCEDURE GetDriversByShiftProc(p_shift VARCHAR(10))
BEGIN
    SELECT driver.driver_no, driver.driver_name, driver.licence_no, driver.address, driver.d_age, driver.salary
    FROM driver
    JOIN bus_driver AS bd ON bd.driver_no = driver.driver_no
    WHERE bd.shift = p_shift;
END //

DELIMITER ;

方案3：PostgreSQL中直接返回表类型记录 #

如果使用PostgreSQL，存储函数可以直接返回符合结构的表：

CREATE OR REPLACE FUNCTION GetDriversByShift(p_shift VARCHAR)
RETURNS TABLE(
    driver_no INT,
    driver_name VARCHAR,
    licence_no VARCHAR,
    address VARCHAR,
    d_age INT,
    salary NUMERIC
) AS $$
BEGIN
    RETURN QUERY
    SELECT driver.driver_no, driver.driver_name, driver.licence_no, driver.address, driver.d_age, driver.salary
    FROM driver
    JOIN bus_driver AS bd ON bd.driver_no = driver.driver_no
    WHERE bd.shift = p_shift;
END;
$$ LANGUAGE plpgsql;

三、使用示例 #

调用MySQL JSON函数： #

SELECT GetDriversByShift('2');

会返回包含所有符合班次2的司机信息的JSON数组。

调用MySQL存储过程： #

CALL GetDriversByShiftProc('2');

会直接返回表格形式的司机记录。

调用PostgreSQL函数： #

SELECT * FROM GetDriversByShift('2');

会返回标准的结果表。

注意事项 #

• 确保你拥有创建函数/存储过程的数据库权限
• 参数类型要和bus_driver.shift字段的类型匹配（比如如果shift是INT类型，参数就要改成INT）
• MySQL中DETERMINISTIC关键字表示函数返回结果仅由输入参数决定，适合这类纯查询场景

内容的提问来源于stack exchange，提问作者Deepak Pawade