MySQL数据库触发器和游标

DDL

CREATE TABLE student (
    id INT PRIMARY KEY AUTO_INCREMENT,
    createDate DATETIME NOT NULL,
    userName VARCHAR(255) NOT NULL,
    phone VARCHAR(20) NOT NULL,
    age INT NOT NULL,
    sex VARCHAR(10) NOT NULL,
    introduce TEXT
);

CREATE TABLE score (
    id INT PRIMARY KEY AUTO_INCREMENT,
    scoreName VARCHAR(255) NOT NULL,
    result DECIMAL(10, 2) NOT NULL,
    studentId INT,
    FOREIGN KEY (studentId) REFERENCES student(id)
);

DML

-- 插入5条学生信息
INSERT INTO student (createDate, userName, phone, age, sex, introduce) VALUES
('2023-01-01 00:00:00', '张三', '1234567890', 20, '男', '来自北方的学生'),
('2023-01-02 00:00:00', '李四', '0987654321', 21, '女', '来自南方的学生'),
('2023-01-03 00:00:00', '王五', '1122334455', 22, '男', '喜欢运动的学生'),
('2023-01-04 00:00:00', '赵六', '6543210987', 21, '女', '热爱艺术的学生'),
('2023-01-05 00:00:00', '孙七', '7890123456', 20, '男', '擅长编程的学生');


-- 插入10条成绩信息，每个学生2条成绩
INSERT INTO score (scoreName, result, studentId) VALUES
('语文', 90.50, 1),
('数学', 85.25, 1),
('语文', 88.75, 2),
('数学', 92.00, 2),
('语文', 78.50, 3),
('数学', 82.00, 3),
('语文', 95.00, 4),
('数学', 90.00, 4),
('语文', 80.00, 5),
('数学', 85.00, 5),

-- 假设学生ID从1到5，这里只列出前5条，实际应继续添加剩余5条

-- ...
;

触发器

-- 假设你已经创建了student和score表


-- 插入示例数据
INSERT INTO score (scoreName, result, studentId) VALUES ('数学', 90.50, 1);
INSERT INTO score (scoreName, result, studentId) VALUES ('语文', 85.25, 1);
INSERT INTO score (scoreName, result, studentId) VALUES ('英语', 92.00, 2);


-- 创建触发器
DELIMITER //
CREATE TRIGGER tr_score_name_before_update
BEFORE UPDATE ON score
FOR EACH ROW
BEGIN
    IF NEW.scoreName = '数学' THEN
        SET NEW.scoreName = '天书';
    END IF;
END;
//
DELIMITER ;


-- 尝试更新scoreName为'数学'的记录
UPDATE score SET scoreName = '数学' WHERE id = 1;


-- 查询score表查看结果
SELECT * FROM score;

游标

DELIMITER //
CREATE PROCEDURE GetStudentScoresByName(IN p_userName VARCHAR(255))
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE v_userName VARCHAR(255);
    DECLARE v_phone VARCHAR(20);
    DECLARE v_age INT;
    DECLARE v_sex VARCHAR(10);
    DECLARE v_introduce TEXT;
    DECLARE v_scoreName VARCHAR(255);
    DECLARE v_result DECIMAL(10, 2);
    
    -- 声明游标
    DECLARE cur CURSOR FOR 
        SELECT 
            s.userName, 
            s.phone, 
            s.age, 
            s.sex, 
            s.introduce, 
            sc.scoreName, 
            sc.result 
        FROM 
            student s 
        LEFT JOIN 
            score sc ON s.id = sc.studentId 
        WHERE 
            s.userName = p_userName;
    
    -- 声明一个 CONTINUE HANDLER，当游标完成或没有更多行时设置 done = TRUE
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    
    -- 打开游标
    OPEN cur;
    
    -- 从游标中获取数据
    read_loop: LOOP
        FETCH cur INTO v_userName, v_phone, v_age, v_sex, v_introduce, v_scoreName, v_result;
        
        IF done THEN
            LEAVE read_loop;
        END IF;
        
        -- 在这里你可以对获取到的数据进行处理，例如打印出来
        SELECT 
            v_userName AS userName, 
            v_phone AS phone, 
            v_age AS age, 
            v_sex AS sex, 
            v_introduce AS introduce, 
            v_scoreName AS scoreName, 
            v_result AS result;
    END LOOP;
    
    -- 关闭游标
    CLOSE cur;
END //
DELIMITER ;