MySQL存储过程

掌握存储过程的创建、使用和管理

全站首页 频道首页

什么是存储过程？

存储过程是一组为了完成特定功能的SQL语句集合，经编译后存储在数据库中。用户通过指定存储过程的名称并给出参数来执行它。存储过程可以包含业务逻辑、控制流语句和对数据库的操作。

存储过程的优点

  • 提高性能 - 存储过程在创建时编译，之后每次执行都不需要重新编译
  • 减少网络流量 - 只需要传递存储过程名称和参数，而不是多条SQL语句
  • 增强安全性 - 可以通过存储过程限制对基础数据的访问
  • 代码复用 - 存储过程可以在多个应用程序中重复使用
  • 模块化编程 - 将复杂的业务逻辑封装在存储过程中
  • 事务管理 - 可以在存储过程中实现复杂的事务控制
  • 减少客户端代码 - 将数据处理逻辑移到数据库服务器端

存储过程的缺点

  • 调试困难 - 存储过程调试比应用程序代码困难
  • 移植性差 - 不同数据库系统的存储过程语法不同
  • 增加数据库负载 - 复杂逻辑在数据库端执行可能影响性能
  • 版本控制困难 - 存储过程的版本管理不如应用程序代码方便

创建存储过程

基本语法

SQL示例 
DELIMITER //
CREATE PROCEDURE procedure_name([parameter_list])
[characteristic ...]
BEGIN
    -- 存储过程体
    -- SQL语句和控制语句
END //
DELIMITER ;

存储过程特性

特性 描述 示例
COMMENT 存储过程注释 COMMENT '这是一个示例存储过程'
LANGUAGE SQL 实现语言 LANGUAGE SQL
DETERMINISTIC 是否确定性 DETERMINISTIC 或 NOT DETERMINISTIC
CONTAINS SQL 包含的SQL类型 CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA
SQL SECURITY 安全上下文 SQL SECURITY DEFINER | INVOKER

简单存储过程示例

SQL示例 
DELIMITER //
CREATE PROCEDURE GetAllEmployees()
BEGIN
    SELECT * FROM employees;
END //
DELIMITER ;

带特性的存储过程

SQL示例 
DELIMITER //
CREATE PROCEDURE GetEmployeeCount()
COMMENT '获取员工总数'
LANGUAGE SQL
NOT DETERMINISTIC
READS SQL DATA
SQL SECURITY DEFINER
BEGIN
    SELECT COUNT(*) AS total_employees FROM employees;
END //
DELIMITER ;

存储过程参数

存储过程可以接受三种类型的参数：IN（输入参数）、OUT（输出参数）和INOUT（输入输出参数）。

IN参数（输入参数）

SQL示例 
DELIMITER //
CREATE PROCEDURE GetEmployeeByDepartment(IN dept_name VARCHAR(50))
BEGIN
    SELECT * FROM employees 
    WHERE department = dept_name;
END //
DELIMITER ;

OUT参数（输出参数）

SQL示例 
DELIMITER //
CREATE PROCEDURE GetEmployeeCount(OUT total_count INT)
BEGIN
    SELECT COUNT(*) INTO total_count FROM employees;
END //
DELIMITER ;

INOUT参数（输入输出参数）

SQL示例 
DELIMITER //
CREATE PROCEDURE IncreaseSalary(INOUT salary DECIMAL(10,2), IN percentage DECIMAL(5,2))
BEGIN
    SET salary = salary * (1 + percentage / 100);
END //
DELIMITER ;

多参数示例

SQL示例 
DELIMITER //
CREATE PROCEDURE UpdateEmployeeSalary(
    IN emp_id INT,
    IN new_salary DECIMAL(10,2),
    OUT old_salary DECIMAL(10,2),
    OUT update_status VARCHAR(50)
)
BEGIN
    -- 获取旧工资
    SELECT salary INTO old_salary 
    FROM employees 
    WHERE employee_id = emp_id;
    
    -- 更新工资
    UPDATE employees 
    SET salary = new_salary 
    WHERE employee_id = emp_id;
    
    -- 设置更新状态
    IF ROW_COUNT() > 0 THEN
        SET update_status = '更新成功';
    ELSE
        SET update_status = '更新失败，员工不存在';
    END IF;
END //
DELIMITER ;

调用存储过程

调用无参数存储过程

SQL示例 
CALL GetAllEmployees();

调用带IN参数的存储过程

SQL示例 
CALL GetEmployeeByDepartment('IT');

调用带OUT参数的存储过程

SQL示例 
SET @count = 0;
CALL GetEmployeeCount(@count);
SELECT @count AS total_employees;

调用带INOUT参数的存储过程

SQL示例 
SET @current_salary = 5000;
CALL IncreaseSalary(@current_salary, 10);
SELECT @current_salary AS new_salary;

调用多参数存储过程

SQL示例 
SET @old_salary = 0;
SET @status = '';
CALL UpdateEmployeeSalary(101, 6000, @old_salary, @status);
SELECT @old_salary, @status;

存储过程控制语句

变量声明和赋值

SQL示例 
DELIMITER //
CREATE PROCEDURE CalculateBonus()
BEGIN
    DECLARE base_salary DECIMAL(10,2);
    DECLARE bonus DECIMAL(10,2);
    DECLARE total_income DECIMAL(10,2);
    
    SET base_salary = 5000;
    SET bonus = base_salary * 0.1;
    SET total_income = base_salary + bonus;
    
    SELECT base_salary, bonus, total_income;
END //
DELIMITER ;

条件语句（IF）

SQL示例 
DELIMITER //
CREATE PROCEDURE CheckSalary(IN emp_id INT)
BEGIN
    DECLARE emp_salary DECIMAL(10,2);
    DECLARE salary_level VARCHAR(20);
    
    SELECT salary INTO emp_salary 
    FROM employees 
    WHERE employee_id = emp_id;
    
    IF emp_salary > 10000 THEN
        SET salary_level = '高薪';
    ELSEIF emp_salary > 5000 THEN
        SET salary_level = '中等';
    ELSE
        SET salary_level = '一般';
    END IF;
    
    SELECT emp_salary, salary_level;
END //
DELIMITER ;

CASE语句

SQL示例 
DELIMITER //
CREATE PROCEDURE GetEmployeeLevel(IN emp_id INT)
BEGIN
    DECLARE emp_salary DECIMAL(10,2);
    DECLARE emp_level VARCHAR(20);
    
    SELECT salary INTO emp_salary 
    FROM employees 
    WHERE employee_id = emp_id;
    
    CASE
        WHEN emp_salary >= 15000 THEN SET emp_level = '高级';
        WHEN emp_salary >= 8000 THEN SET emp_level = '中级';
        WHEN emp_salary >= 3000 THEN SET emp_level = '初级';
        ELSE SET emp_level = '实习';
    END CASE;
    
    SELECT emp_salary, emp_level;
END //
DELIMITER ;

循环语句（WHILE）

SQL示例 
DELIMITER //
CREATE PROCEDURE GenerateTestData()
BEGIN
    DECLARE i INT DEFAULT 1;
    
    -- 创建临时表
    CREATE TEMPORARY TABLE IF NOT EXISTS test_data (
        id INT,
        name VARCHAR(50),
        value INT
    );
    
    -- 清空临时表
    DELETE FROM test_data;
    
    WHILE i <= 10 DO
        INSERT INTO test_data (id, name, value) 
        VALUES (i, CONCAT('Test ', i), i * 10);
        SET i = i + 1;
    END WHILE;
    
    SELECT * FROM test_data;
END //
DELIMITER ;

循环语句（REPEAT）

SQL示例 
DELIMITER //
CREATE PROCEDURE GenerateNumbers(IN max_num INT)
BEGIN
    DECLARE counter INT DEFAULT 1;
    
    CREATE TEMPORARY TABLE numbers (num INT);
    
    REPEAT
        INSERT INTO numbers VALUES (counter);
        SET counter = counter + 1;
    UNTIL counter > max_num
    END REPEAT;
    
    SELECT * FROM numbers;
END //
DELIMITER ;

循环语句（LOOP）

SQL示例 
DELIMITER //
CREATE PROCEDURE FibonacciSequence(IN n INT)
BEGIN
    DECLARE a INT DEFAULT 0;
    DECLARE b INT DEFAULT 1;
    DECLARE temp INT;
    DECLARE counter INT DEFAULT 1;
    
    CREATE TEMPORARY TABLE fibonacci (position INT, value INT);
    
    fib_loop: LOOP
        IF counter > n THEN
            LEAVE fib_loop;
        END IF;
        
        INSERT INTO fibonacci VALUES (counter, a);
        
        SET temp = a + b;
        SET a = b;
        SET b = temp;
        SET counter = counter + 1;
    END LOOP fib_loop;
    
    SELECT * FROM fibonacci;
END //
DELIMITER ;

错误处理

声明处理程序

SQL示例 
DELIMITER //
CREATE PROCEDURE SafeDivision(
    IN dividend DECIMAL(10,2),
    IN divisor DECIMAL(10,2),
    OUT result DECIMAL(10,2),
    OUT error_msg VARCHAR(100)
)
BEGIN
    DECLARE division_by_zero CONDITION FOR SQLSTATE '22012';
    DECLARE EXIT HANDLER FOR division_by_zero
    BEGIN
        SET result = NULL;
        SET error_msg = '错误：除数不能为零';
    END;
    
    SET result = dividend / divisor;
    SET error_msg = '计算成功';
END //
DELIMITER ;

使用SIGNAL抛出错误

SQL示例 
DELIMITER //
CREATE PROCEDURE ValidateEmployee(IN emp_id INT)
BEGIN
    DECLARE emp_count INT;
    
    SELECT COUNT(*) INTO emp_count 
    FROM employees 
    WHERE employee_id = emp_id;
    
    IF emp_count = 0 THEN
        SIGNAL SQLSTATE '45000' 
        SET MESSAGE_TEXT = '员工ID不存在';
    END IF;
    
    SELECT * FROM employees WHERE employee_id = emp_id;
END //
DELIMITER ;

游标使用

基本游标操作

SQL示例 
DELIMITER //
CREATE PROCEDURE ProcessEmployeeSalaries()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE emp_id INT;
    DECLARE emp_name VARCHAR(100);
    DECLARE emp_salary DECIMAL(10,2);
    DECLARE total_salary DECIMAL(10,2) DEFAULT 0;
    
    -- 声明游标
    DECLARE emp_cursor CURSOR FOR 
        SELECT employee_id, first_name, salary 
        FROM employees;
    
    -- 声明处理程序
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    
    -- 创建临时表存储结果
    CREATE TEMPORARY TABLE salary_report (
        employee_id INT,
        employee_name VARCHAR(100),
        salary DECIMAL(10,2),
        salary_level VARCHAR(20)
    );
    
    OPEN emp_cursor;
    
    read_loop: LOOP
        FETCH emp_cursor INTO emp_id, emp_name, emp_salary;
        IF done THEN
            LEAVE read_loop;
        END IF;
        
        -- 处理数据
        SET total_salary = total_salary + emp_salary;
        
        -- 插入处理结果
        INSERT INTO salary_report (employee_id, employee_name, salary, salary_level)
        VALUES (
            emp_id, 
            emp_name, 
            emp_salary,
            CASE
                WHEN emp_salary > 10000 THEN '高薪'
                WHEN emp_salary > 5000 THEN '中等'
                ELSE '一般'
            END
        );
    END LOOP;
    
    CLOSE emp_cursor;
    
    -- 返回结果
    SELECT * FROM salary_report;
    SELECT total_salary AS total_salary_paid;
END //
DELIMITER ;

管理存储过程

查看存储过程

SQL示例 
-- 查看所有存储过程
SHOW PROCEDURE STATUS;

-- 查看特定数据库的存储过程
SHOW PROCEDURE STATUS WHERE db = 'database_name';

-- 查看特定存储过程的定义
SHOW CREATE PROCEDURE GetEmployeeByDepartment;

-- 从information_schema查看存储过程信息
SELECT * FROM information_schema.ROUTINES 
WHERE ROUTINE_TYPE = 'PROCEDURE';

修改存储过程

SQL示例 
DROP PROCEDURE IF EXISTS GetEmployeeByDepartment;
CREATE PROCEDURE GetEmployeeByDepartment(IN dept_name VARCHAR(50))
BEGIN
    -- 新的实现逻辑
    SELECT employee_id, first_name, last_name, department, salary
    FROM employees 
    WHERE department = dept_name
    ORDER BY salary DESC;
END //

删除存储过程

SQL示例 
DROP PROCEDURE GetEmployeeByDepartment;

-- 安全删除（如果存在）
DROP PROCEDURE IF EXISTS GetEmployeeByDepartment;

存储过程最佳实践

命名规范

  • 使用有意义的名称，反映存储过程的功能
  • 遵循一致的命名约定（如sp_前缀或公司特定前缀）
  • 避免使用MySQL保留关键字作为存储过程名称
  • 考虑使用动词开头，如Get、Insert、Update、Delete等

代码组织

  • 添加详细的注释说明存储过程的功能和参数
  • 使用一致的代码格式和缩进