【MySQL学习】存储过程

原创已于 2025-04-18 08:10:43 修改 · 1.2k 阅读

11 ·

本内容遵循CC 4.0 BY-SA版权协议

标签

#mysql #学习 #数据库

于 2025-04-16 23:35:30 首次发布

第二步：创建存储过程：ProcessStudentGrades

第三步：测试

一、定义

存储过程是一组预编译的 SQL 语句集合，经过编译后存储在数据库服务器上。

可以把它想象成数据库中的一个自定义函数或脚本。

主要用于封装常用的、复杂的或重复性的数据库操作，简化应用程序开发，提高性能和安全性。

二、基本语法

1.创建存储过程

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

(1)DELIMITER // 和 DELIMITER ;

存储过程内部通常包含分号 (;) 作为 SQL 语句的结束符，为了让 MySQL 客户端能够区分存储过程定义的结束 (END) 和内部 SQL 语句的结束，需要临时改变语句结束符（常用 // 或 $$），定义完毕后再改回默认的分号 (;)。

(2) parameter_list：参数列表（可选），可以是输入、输出或输入输出参数。

IN: 输入参数（默认模式）。值由调用者传入存储过程，在过程中只读。
OUT: 输出参数。值在存储过程中被设置，并可以返回给调用者。调用时传入的变量会被修改。
INOUT: 输入输出参数。调用者传入初始值，存储过程可以读取和修改它，修改后的值可以返回给调用者。

示例1：计算公司给定部门的总人数

DELIMITER //
CREATE PROCEDURE get_employee_count_by_dept(IN dept_id INT, OUT emp_count INT)
BEGIN
    SELECT COUNT(*) INTO emp_count
    FROM employees
    WHERE department_id = dept_id;
END //
DELIMITER ;

CALL get_employee_count_by_dept(10, @count); 
SELECT @count;

CALL get_employee_count_by_dept(10, @count); -- 将部门 10 的员工数放入 @count 变量

示例2：给定num，计算num+1的值

CREATE PROCEDURE increment(INOUT num INT)
BEGIN
    SET num = num + 1;
END //
SET @value = 10;
CALL increment(@value);
SELECT @value;

2.删除存储过程

DROP PROCEDURE [IF EXISTS] procedure_name;

使用 IF EXISTS避免因存储过程不存在而报错

3.查看存储过程

（1）查看数据库中的存储过程列表

SHOW PROCEDURE STATUS [WHERE condition];

示例1：

SHOW PROCEDURE STATUS;

说明：没有WHERE子句，启动当前用户有权限访问的所有存储过程的信息。
结果：返回所有存储过程的元数据，可能包含多个数据库中的存储过程。

示例二：

SHOW PROCEDURE STATUS WHERE Db = 'AlinJ';

（2）查看存储过程定义

SHOW CREATE PROCEDURE procedure_name;

三、控制语句

1.变量声明与赋值

（1）变量声明

在存储过程中定义局部变量，用于存储临时数据

DECLARE variable_name data_type [DEFAULT value];

变量声明必须位于BEGIN ... END块的开头，且位于任何其他语句之前。
变量的作用域仅限于当前BEGIN ... END块，块结束后变量自动推理。

（2）变量赋值

方法一：SET语句

SET variable_name = ALinJ;

方法二：SELECT ... INTO 语句

SELECT column_name INTO variable_name FROM table_name WHERE condition;

示例：将employees表中id为1的员工的姓名赋给变量

SELECT name INTO @emp_name FROM employees WHERE id = 1;
SELECT @emp_name;

（3）示例：统计用户表中用户数量，并根据数量进行状态设置。

DELIMITER //
CREATE PROCEDURE count_users()
BEGIN
    DECLARE total_users INT DEFAULT 0;
    DECLARE user_status VARCHAR(20);

    SELECT COUNT(*) INTO total_users FROM users;

    SET user_status = IF(total_users > 100, 'High', 'Low');

    SELECT total_users AS 'Total Users', user_status AS 'User Status';
END //
DELIMITER ;

CALL count_users();

2. 条件语句（IF-ELSE）

IF condition THEN
    -- 语句
ELSEIF condition THEN
    -- 语句
ELSE
    -- 语句
END IF;

示例1：根据用户分数判断是否及格

DELIMITER //
CREATE PROCEDURE check_score(IN score INT, OUT result VARCHAR(20))
BEGIN
    IF score >= 60 THEN
        SET result = 'Pass';
    ELSE
        SET result = 'Fail';
    END IF;
    
    SELECT result AS 'Result';
END //
DELIMITER ;

CALL check_score(75, @result);

示例2：根据分数评定等级（A、B、C）

DELIMITER //
CREATE PROCEDURE grade_score(IN score INT, OUT grade VARCHAR(10))
BEGIN
    IF score >= 90 THEN
        SET grade = 'A';
    ELSEIF score >= 75 THEN
        SET grade = 'B';
    ELSEIF score >= 60 THEN
        SET grade = 'C';
    ELSE
        SET grade = 'Fail';
    END IF;
    
    SELECT grade AS 'Grade';
END //
DELIMITER ;

CALL grade_score(85, @grade);

示例3：检查分数是否有效，并评估等级

DELIMITER //
CREATE PROCEDURE validate_score(IN score INT, OUT message VARCHAR(50))
BEGIN
    IF score < 0 OR score > 100 THEN
        SET message = 'Invalid score';
    ELSE
        IF score >= 60 THEN
            SET message = 'Pass';
        ELSE
            SET message = 'Fail';
        END IF;
    END IF;
    
    SELECT message AS 'Message';
END //
DELIMITER ;

CALL validate_score(-10, @message);

3. 循环语句

（1）WHILE 循环

WHILE condition DO
    -- 语句
END WHILE;

条件为真时执行循环体。
条件在循环开始前检查，可能一次都不执行。

示例：插入 1 到 5 的数字到表中。

CREATE TABLE numbers (num INT);

DELIMITER //
CREATE PROCEDURE insert_numbers()
BEGIN
    DECLARE i INT DEFAULT 1;
    
    WHILE i <= 5 DO
        INSERT INTO numbers VALUES (i);
        SET i = i + 1;
    END WHILE;
    
    SELECT * FROM numbers;
END //
DELIMITER ;

CALL insert_numbers();

（2）REPEAT 循环

REPEAT
    -- 语句
UNTIL condition
END REPEAT;

先执行循环体，再检查条件。
至少执行一次，条件为真时退出循环。

示例：计算 1 到 5 的累加和。

DELIMITER //
CREATE PROCEDURE calculate_sum(OUT total INT)
BEGIN
    DECLARE i INT DEFAULT 1;
    SET total = 0;
    
    REPEAT
        SET total = total + i;
        SET i = i + 1;
    UNTIL i > 5
    END REPEAT;
    
    SELECT total AS 'Total Sum';
END //
DELIMITER ;

CALL calculate_sum(@total);

（3）LOOP 循环

[label:] LOOP
    -- 语句
    IF condition THEN
        LEAVE label; -- 相当于break
    END IF;
    -- ITERATE label; -- 相当于continue
END LOOP [label];

无条件循环，必须通过LEAVE语句退出。
ITERATE语句可跳到下一次循环。
label是可选的循环标签，用于LEAVE和ITERATE。

示例1：插入 1 到 5 的数字，并跳过

DELIMITER //
CREATE PROCEDURE insert_numbers_with_skip()
BEGIN
    DECLARE i INT DEFAULT 1;
    
    my_loop: LOOP
        IF i > 5 THEN
            LEAVE my_loop;
        END IF;
        
        IF i = 3 THEN
            SET i = i + 1;
            ITERATE my_loop;
        END IF;
        
        INSERT INTO numbers VALUES (i);
        SET i = i + 1;
    END LOOP my_loop;
    
    SELECT * FROM numbers;
END //
DELIMITER ;

CALL insert_numbers_with_skip();

四、游标（Cursor）

游标是 MySQL 存储过程中用于处理查询结果集的工具，允许逐行读取和操作查询结果。

基本使用步骤：

声明指标：用于存储游标读取的数据和控制循环的标志。
声明游标：绑定一个SELECT语句。
声明HANDLER：处理游标读取结束。
打开景点。
循环读取数据：用FETCH读取每一行，处理逻辑。
关闭游标。

（1）声明游标

//声明游标

DECLARE cursor_name CURSOR FOR select_statement;

游标必须绑定一个SELECT语句。
声明必须在存储过程的BEGIN ... END块开头，在变量声明之后。

（2）处理游标结束

问题：当游标读取到最后一行后，继续FETCH会导致错误。
解决方法：使用HANDLER处理NOT FOUND条件。

DECLARE CONTINUE HANDLER FOR NOT FOUND SET variable = value;

（3）打开游标

OPEN cursor_name;

游标后，MySQL 会执行SELECT语句，并将结果集加载到内存中。
此时游标指向结果集的第一行之前。

（4）读取数据

从游标中读取一行数据，将数据存储到指定的变量中。

FETCH cursor_name INTO variable_list;

（5）关闭景点

CLOSE cursor_name;

示例1：遍历用户表，记录每个用户的ID和名称到日志表

第一步：准备数据

CREATE TABLE users (id INT, name VARCHAR(50));
CREATE TABLE logs (log_id INT AUTO_INCREMENT PRIMARY KEY, message VARCHAR(100));

INSERT INTO users VALUES (1, 'Alice'), (2, 'Bob'), (3, 'Charlie');

第二步：创建存储过程

DELIMITER //
CREATE PROCEDURE log_users()
BEGIN
    -- 声明变量
    DECLARE done INT DEFAULT 0;
    DECLARE u_id INT;
    DECLARE u_name VARCHAR(50);
    
    -- 声明游标
    DECLARE cur CURSOR FOR SELECT id, name FROM users;
    
    -- 声明 HANDLER
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
    
    -- 打开游标
    OPEN cur;
    
    -- 循环读取
    read_loop: LOOP
        FETCH cur INTO u_id, u_name;
        IF done THEN
            LEAVE read_loop;
        END IF;
        
        -- 插入日志
        INSERT INTO logs (message) VALUES (CONCAT('User: ', u_id, ', Name: ', u_name));
    END LOOP read_loop;
    
    -- 关闭游标
    CLOSE cur;
    
    -- 查看结果
    SELECT * FROM logs;
END //
DELIMITER ;

第三步：调用

CALL log_users();

示例二：遍历用户表，给分数低于60的用户加10分，并记录操作日志。

第一步：准备数据

CREATE TABLE users (id INT, name VARCHAR(50), score INT);
CREATE TABLE logs (log_id INT AUTO_INCREMENT PRIMARY KEY, message VARCHAR(100), log_time DATETIME);

INSERT INTO users VALUES (1, 'Alice', 50), (2, 'Bob', 70), (3, 'Charlie', 45);

第二步：创建存储过程

DELIMITER //
CREATE PROCEDURE update_scores()
BEGIN
    DECLARE done INT DEFAULT 0;
    DECLARE u_id INT;
    DECLARE u_score INT;
    DECLARE cur CURSOR FOR SELECT id, score FROM users;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
    
    OPEN cur;
    update_loop: LOOP
        FETCH cur INTO u_id, u_score;
        IF done THEN
            LEAVE update_loop;
        END IF;
        
        -- 检查分数是否低于 60
        IF u_score < 60 THEN
            SET u_score = u_score + 10;
            UPDATE users SET score = u_score WHERE id = u_id;
            INSERT INTO logs (message, log_time) 
            VALUES (CONCAT('User ', u_id, ' score updated to ', u_score), NOW());
        END IF;
    END LOOP update_loop;
    CLOSE cur;
    
    SELECT * FROM users;
    SELECT * FROM logs;
END //
DELIMITER ;

第三步：调用

CALL update_scores();

示例3：将users表中的数据迁移到users_backup表，跳过分数低于50的用户。

第一步：准备数据

CREATE TABLE users (id INT, name VARCHAR(50), score INT);
CREATE TABLE users_backup (id INT, name VARCHAR(50), score INT);

INSERT INTO users VALUES (1, 'Alice', 50), (2, 'Bob', 70), (3, 'Charlie', 40);

第二步：创建存储过程

DELIMITER //
CREATE PROCEDURE backup_users()
BEGIN
    DECLARE done INT DEFAULT 0;
    DECLARE u_id INT;
    DECLARE u_name VARCHAR(50);
    DECLARE u_score INT;
    DECLARE cur CURSOR FOR SELECT id, name, score FROM users;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
    
    OPEN cur;
    backup_loop: LOOP
        FETCH cur INTO u_id, u_name, u_score;
        IF done THEN
            LEAVE backup_loop;
        END IF;
        
        -- 跳过分数低于 50 的用户
        IF u_score < 50 THEN
            ITERATE backup_loop;
        END IF;
        
        -- 插入到备份表
        INSERT INTO users_backup VALUES (u_id, u_name, u_score);
    END LOOP backup_loop;
    CLOSE cur;
    
    SELECT * FROM users_backup;
END //
DELIMITER ;

第三步：调用

CALL backup_users();

五、案例语句

1.值匹配

CASE expression
    WHEN value1 THEN
        -- 语句
    WHEN value2 THEN
        -- 语句
    [ELSE
        -- 语句]
END CASE;

示例：根据用户等级设置描述。

DELIMITER //
CREATE PROCEDURE describe_level(IN level CHAR(1), OUT description VARCHAR(20))
BEGIN
    CASE level
        WHEN 'A' THEN
            SET description = 'Excellent';
        WHEN 'B' THEN
            SET description = 'Good';
        WHEN 'C' THEN
            SET description = 'Average';
        ELSE
            SET description = 'Unknown';
    END CASE;
    
    SELECT description AS 'Description';
END //
DELIMITER ;

CALL describe_level('B', @description);

2.条件匹配

CASE
    WHEN condition1 THEN
        -- 语句
    WHEN condition2 THEN
        -- 语句
    [ELSE
        -- 语句]
END CASE;

示例：根据分数评定等级。

DELIMITER //
CREATE PROCEDURE grade_score_case(IN score INT, OUT grade VARCHAR(10))
BEGIN
    CASE
        WHEN score >= 90 THEN
            SET grade = 'A';
        WHEN score >= 75 THEN
            SET grade = 'B';
        WHEN score >= 60 THEN
            SET grade = 'C';
        ELSE
            SET grade = 'Fail';
    END CASE;
    
    SELECT grade AS 'Grade';
END //
DELIMITER ;

CALL grade_score_case(85, @grade);

五、综合案例

我将创建一个存储过程ProcessStudentGrades，用于管理学生的成绩数据，支持以下功能：

批量更新成绩（加分或减分）。
根据成绩评定等级（A、B、C、Fail）。
记录操作日志（例如，谁的成绩被更新、等级如何）。
如果操作失败，记录错误日志。

第一步：创建表

CREATE TABLE students (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    grade INT
);

CREATE TABLE logs (
    log_id INT AUTO_INCREMENT PRIMARY KEY,
    message VARCHAR(255),
    log_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO students (id, name, grade)
VALUES 
    (1, 'Alice', 85),
    (2, 'Bob', 55),
    (3, 'Charlie', 92);

第二步：创建存储过程：ProcessStudentGrades

DELIMITER //

CREATE PROCEDURE ProcessStudentGrades(
    IN action VARCHAR(20),  -- 操作类型：ADD（加分）、SUBTRACT（减分）、GRADE（评定等级）
    IN score_change INT,    -- 分数变化值（加分或减分）
    OUT result_message VARCHAR(100) 
)
BEGIN
    -- 声明变量
    DECLARE done INT DEFAULT 0;
    DECLARE s_id INT;
    DECLARE s_name VARCHAR(50);
    DECLARE s_grade INT;
    DECLARE new_grade INT;
    DECLARE grade_level VARCHAR(10);
    DECLARE loop_counter INT DEFAULT 0;
    
    -- 声明游标
    DECLARE cur CURSOR FOR SELECT id, name, grade FROM students;
    
    -- 声明游标结束处理器
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
    
    -- 声明错误处理器
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        SET result_message = 'Error occurred during operation.';
        INSERT INTO logs (message) 
        VALUES (CONCAT('Error: ', result_message, ' during action: ', action));
    END;
    
  
    SET result_message = 'Operation completed successfully.';
    
    -- 使用 CASE 语句处理操作类型
    CASE UPPER(action)
        WHEN 'ADD' THEN
            -- 使用游标遍历学生，加分
            OPEN cur;
            add_loop: LOOP
                FETCH cur INTO s_id, s_name, s_grade;
                IF done THEN
                    LEAVE add_loop;
                END IF;
                
                -- 计算新分数
                SET new_grade = s_grade + score_change;
                
                -- 确保分数在 0-100 范围内
                IF new_grade > 100 THEN
                    SET new_grade = 100;
                END IF;
                
                -- 更新分数
                UPDATE students SET grade = new_grade WHERE id = s_id;
                INSERT INTO logs (message) 
                VALUES (CONCAT('Student ', s_name, ' (ID: ', s_id, ') grade updated from ', s_grade, ' to ', new_grade));
            END LOOP add_loop;
            CLOSE cur;
            
        WHEN 'SUBTRACT' THEN
            -- 使用 WHILE 循环遍历学生，减分
            SET done = 0; 
            OPEN cur;
            WHILE NOT done DO
                FETCH cur INTO s_id, s_name, s_grade;
                IF done THEN
                    LEAVE;
                END IF;
                
                -- 计算新分数
                SET new_grade = s_grade - score_change;
                
                -- 确保分数在 0-100 范围内
                IF new_grade < 0 THEN
                    SET new_grade = 0;
                END IF;
                
                -- 更新分数
                UPDATE students SET grade = new_grade WHERE id = s_id;
                INSERT INTO logs (message) 
                VALUES (CONCAT('Student ', s_name, ' (ID: ', s_id, ') grade updated from ', s_grade, ' to ', new_grade));
            END WHILE;
            CLOSE cur;
            
        WHEN 'GRADE' THEN
            -- 使用 REPEAT 循环遍历学生，评定等级
            SET done = 0; 
            OPEN cur;
            REPEAT
                FETCH cur INTO s_id, s_name, s_grade;
                IF NOT done THEN
                    -- 使用 IF-ELSE 评定等级
                    IF s_grade >= 90 THEN
                        SET grade_level = 'A';
                    ELSEIF s_grade >= 75 THEN
                        SET grade_level = 'B';
                    ELSEIF s_grade >= 60 THEN
                        SET grade_level = 'C';
                    ELSE
                        SET grade_level = 'Fail';
                    END IF;
                    
                    -- 记录等级日志
                    INSERT INTO logs (message) 
                    VALUES (CONCAT('Student ', s_name, ' (ID: ', s_id, ') grade: ', s_grade, ', level: ', grade_level));
                    
                    -- 简单计数
                    SET loop_counter = loop_counter + 1;
                END IF;
            UNTIL done
            END REPEAT;
            CLOSE cur;
            
           
            SET result_message = CONCAT('Graded ', loop_counter, ' students successfully.');
            
        ELSE
            SIGNAL SQLSTATE '45000' 
            SET MESSAGE_TEXT = 'Invalid action specified.';
    END CASE;
END //

DELIMITER ;

第三步：测试

-- 加分操作
CALL ProcessStudentGrades('ADD', 10, @msg);
SELECT @msg AS Result;
SELECT * FROM students;
SELECT * FROM logs;

-- 减分操作
CALL ProcessStudentGrades('SUBTRACT', 5, @msg);
SELECT @msg AS Result;
SELECT * FROM students;
SELECT * FROM logs;

-- 评定等级
CALL ProcessStudentGrades('GRADE', NULL, @msg);
SELECT @msg AS Result;
SELECT * FROM logs;

-- 无效操作
CALL ProcessStudentGrades('INVALID', NULL, @msg);
SELECT @msg AS Result;
SELECT * FROM logs;