MySQL(14) 如何创建和调用存储过程？

在 MySQL 中，存储过程（Stored Procedure）是一组预编译的 SQL 语句，可以通过名称和参数进行调用。存储过程可以简化复杂的数据库操作，提高代码的复用性和性能，并增强安全性。以下详细介绍如何创建和调用存储过程，并展示一些具体的代码示例。

创建存储过程

使用 CREATE PROCEDURE 语句创建存储过程。存储过程可以包含输入参数（IN）、输出参数（OUT）和输入输出参数（INOUT），可以包含多条 SQL 操作，包含条件逻辑、循环等。

语法

CREATE PROCEDURE procedure_name ([parameter_list])
BEGIN
    -- SQL 语句
END;

示例：简单存储过程

下面创建一个简单的存储过程 AddEmployee，用于插入新员工记录。

-- 创建数据库
CREATE DATABASE company;

-- 选择数据库
USE company;

-- 创建 employees 表
CREATE TABLE employees (
    emp_id INT AUTO_INCREMENT PRIMARY KEY,
    emp_name VARCHAR(100) NOT NULL,
    emp_position VARCHAR(100),
    emp_salary DECIMAL(10, 2),
    hire_date DATE
);

-- 创建 AddEmployee 存储过程
DELIMITER //

CREATE PROCEDURE AddEmployee (
    IN name VARCHAR(100),
    IN position VARCHAR(100),
    IN salary DECIMAL(10, 2),
    IN hire_date DATE
)
BEGIN
    INSERT INTO employees (emp_name, emp_position, emp_salary, hire_date)
    VALUES (name, position, salary, hire_date);
END;
//

DELIMITER ;

调用存储过程

使用 CALL 语句调用存储过程。

示例：调用 AddEmployee 存储过程

CALL AddEmployee('John Doe', 'Manager', 75000.00, '2023-10-01');

带输出参数的存储过程

存储过程可以包含输出参数，用于返回结果。下面是一个带输出参数的存储过程示例：

-- 创建 CalculateAnnualSalary 存储过程
DELIMITER //

CREATE PROCEDURE CalculateAnnualSalary (
    IN emp_id INT,
    OUT annual_salary DECIMAL(10, 2)
)
BEGIN
    SELECT emp_salary * 12 INTO annual_salary
    FROM employees
    WHERE employees.emp_id = emp_id;
END;
//

DELIMITER ;

调用带输出参数的存储过程：

CALL CalculateAnnualSalary(1, @annual_salary);
SELECT @annual_salary;

带条件逻辑的存储过程

存储过程可以包含条件逻辑，如 IF、CASE 等语句。

-- 创建 AdjustSalary 存储过程，调整员工薪水
DELIMITER //

CREATE PROCEDURE AdjustSalary (
    IN emp_id INT,
    IN adjustment DECIMAL(10, 2)
)
BEGIN
    DECLARE current_salary DECIMAL(10, 2);

    -- 获取当前薪水
    SELECT emp_salary INTO current_salary
    FROM employees
    WHERE employees.emp_id = emp_id;

    -- 调整薪水
    IF current_salary IS NOT NULL THEN
        UPDATE employees
        SET emp_salary = current_salary + adjustment
        WHERE employees.emp_id = emp_id;
    END IF;
END;
//

DELIMITER ;

调用带条件逻辑的存储过程：

CALL AdjustSalary(1, 5000.00);

带循环的存储过程

存储过程还可以包含循环语句，如 LOOP、WHILE 等。

-- 创建 BatchAdjustSalaries 存储过程，批量增加员工薪水
DELIMITER //

CREATE PROCEDURE BatchAdjustSalaries (
    IN adjustment DECIMAL(10, 2)
)
BEGIN
    DECLARE done INT DEFAULT 0;
    DECLARE emp_id INT;
    DECLARE cur CURSOR FOR SELECT emp_id FROM employees;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

    OPEN cur;

    read_loop: LOOP
        FETCH cur INTO emp_id;
        IF done THEN
            LEAVE read_loop;
        END IF;

        -- 调用 AdjustSalary 存储过程
        CALL AdjustSalary(emp_id, adjustment);
    END LOOP;

    CLOSE cur;
END;
//

DELIMITER ;

调用带循环的存储过程：

CALL BatchAdjustSalaries(1000.00);

完整的存储过程示例

以下是一个完整的存储过程示例，从创建表到定义和调用存储过程的完整过程。

-- 创建数据库
CREATE DATABASE company;

-- 选择数据库
USE company;

-- 创建 employees 表
CREATE TABLE employees (
    emp_id INT AUTO_INCREMENT PRIMARY KEY,
    emp_name VARCHAR(100) NOT NULL,
    emp_position VARCHAR(100),
    emp_salary DECIMAL(10, 2),
    hire_date DATE
);

-- 创建 AddEmployee 存储过程
DELIMITER //

CREATE PROCEDURE AddEmployee (
    IN name VARCHAR(100),
    IN position VARCHAR(100),
    IN salary DECIMAL(10, 2),
    IN hire_date DATE
)
BEGIN
    INSERT INTO employees (emp_name, emp_position, emp_salary, hire_date)
    VALUES (name, position, salary, hire_date);
END;
//

DELIMITER ;

-- 调用 AddEmployee 存储过程
CALL AddEmployee('John Doe', 'Manager', 75000.00, '2023-10-01');

-- 创建 CalculateAnnualSalary 存储过程
DELIMITER //

CREATE PROCEDURE CalculateAnnualSalary (
    IN emp_id INT,
    OUT annual_salary DECIMAL(10, 2)
)
BEGIN
    SELECT emp_salary * 12 INTO annual_salary
    FROM employees
    WHERE employees.emp_id = emp_id;
END;
//

DELIMITER ;

-- 调用 CalculateAnnualSalary 存储过程
CALL CalculateAnnualSalary(1, @annual_salary);
SELECT @annual_salary;

-- 创建 AdjustSalary 存储过程
DELIMITER //

CREATE PROCEDURE AdjustSalary (
    IN emp_id INT,
    IN adjustment DECIMAL(10, 2)
)
BEGIN
    DECLARE current_salary DECIMAL(10, 2);

    -- 获取当前薪水
    SELECT emp_salary INTO current_salary
    FROM employees
    WHERE employees.emp_id = emp_id;

    -- 调整薪水
    IF current_salary IS NOT NULL THEN
        UPDATE employees
        SET emp_salary = current_salary + adjustment
        WHERE employees.emp_id = emp_id;
    END IF;
END;
//

DELIMITER ;

-- 调用 AdjustSalary 存储过程
CALL AdjustSalary(1, 5000.00);

-- 创建 BatchAdjustSalaries 存储过程
DELIMITER //

CREATE PROCEDURE BatchAdjustSalaries (
    IN adjustment DECIMAL(10, 2)
)
BEGIN
    DECLARE done INT DEFAULT 0;
    DECLARE emp_id INT;
    DECLARE cur CURSOR FOR SELECT emp_id FROM employees;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

    OPEN cur;

    read_loop: LOOP
        FETCH cur INTO emp_id;
        IF done THEN
            LEAVE read_loop;
        END IF;

        -- 调用 AdjustSalary 存储过程
        CALL AdjustSalary(emp_id, adjustment);
    END LOOP;

    CLOSE cur;
END;
//

DELIMITER ;

-- 调用 BatchAdjustSalaries 存储过程
CALL BatchAdjustSalaries(1000.00);

通过以上代码示例，展示了如何在 MySQL 中创建和调用存储过程，以及存储过程中的各种逻辑控制和参数类型。存储过程是强大的数据库工具，可以帮助简化复杂操作，增强系统性能和安全性。