通过MySQL的存储过程从多个表返回数据

-- 创建 employees 表
CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    department_id INT
);

-- 创建 departments 表
CREATE TABLE departments (
    id INT PRIMARY KEY,
    name VARCHAR(100)
);

-- 插入示例数据
INSERT INTO employees (id, name, department_id) VALUES (1, 'Alice', 1);
INSERT INTO employees (id, name, department_id) VALUES (2, 'Bob', 2);
INSERT INTO departments (id, name) VALUES (1, 'HR');
INSERT INTO departments (id, name) VALUES (2, 'Engineering');

-- 创建存储过程
DELIMITER //
CREATE PROCEDURE GetEmployeeWithDepartment()
BEGIN
    SELECT e.id AS employee_id, e.name AS employee_name, d.name AS department_name
    FROM employees e
    JOIN departments d ON e.department_id = d.id;
END //
DELIMITER ;

-- 调用存储过程
CALL GetEmployeeWithDepartment();

GRANT EXECUTE ON PROCEDURE your_database.GetEmployeeWithDepartment TO 'your_user'@'localhost';