使用游标查询

DDL

-- 创建student表
CREATE TABLE student (
    id INT PRIMARY KEY AUTO_INCREMENT,
    createDate DATE NOT NULL,
    userName VARCHAR(50) NOT NULL,
    phone VARCHAR(20) NOT NULL,
    age INT NOT NULL,
    sex ENUM('男', '女') NOT NULL,
    introduce TEXT
);


-- 创建score表
CREATE TABLE score (
    id INT PRIMARY KEY AUTO_INCREMENT,
    scoreName VARCHAR(50) NOT NULL,
    result INT NOT NULL,
    studentId INT,
    FOREIGN KEY (studentId) REFERENCES student(id) ON DELETE CASCADE
);

DML

-- 插入5条学生信息
INSERT INTO student (createDate, userName, phone, age, sex, introduce) VALUES
('2023-01-01', '张三', '13800138000', 20, '男', '来自北方，喜欢运动'),
('2023-02-02', '李四', '13900139000', 21, '女', '来自南方，爱好音乐'),
('2023-03-03', '王五', '13700137000', 22, '男', '来自东部，擅长编程'),
('2023-04-04', '赵六', '13600136000', 23, '女', '来自西部，喜欢旅行'),
('2023-05-05', '孙七', '13500135000', 24, '男', '来自中部，热爱阅读');

-- 插入10条成绩信息
INSERT INTO score (scoreName, result, studentId) VALUES
('数学', 90, (SELECT id FROM student WHERE userName = '张三')),
('语文', 85, (SELECT id FROM student WHERE userName = '张三')),
('数学', 88, (SELECT id FROM student WHERE userName = '李四')),
('英语', 92, (SELECT id FROM student WHERE userName = '李四')),
('物理', 89, (SELECT id FROM student WHERE userName = '王五')),
('化学', 93, (SELECT id FROM student WHERE userName = '王五')),
('数学', 86, (SELECT id FROM student WHERE userName = '赵六')),
('生物', 91, (SELECT id FROM student WHERE userName = '赵六')),
('历史', 87, (SELECT id FROM student WHERE userName = '孙七')),
('地理', 94, (SELECT id FROM student WHERE userName = '孙七'));

创建游标

DELIMITER //

CREATE PROCEDURE GetStudentInfoByName(IN studentName VARCHAR(50))
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE v_createDate DATE;
    DECLARE v_userName VARCHAR(50);
    DECLARE v_phone VARCHAR(20);
    DECLARE v_age INT;
    DECLARE v_sex ENUM('男', '女');
    DECLARE v_introduce TEXT;
    DECLARE v_scoreName VARCHAR(50);
    DECLARE v_result INT;
    
    -- 声明游标
    DECLARE cur CURSOR FOR 
        SELECT 
            s.createDate, 
            s.userName, 
            s.phone, 
            s.age, 
            s.sex, 
            s.introduce, 
            sc.scoreName, 
            sc.result 
        FROM 
            student s 
        JOIN 
            score sc ON s.id = sc.studentId 
        WHERE 
            s.userName = studentName;
    
    -- 声明结束处理
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    
    -- 打开游标
    OPEN cur;
    
    -- 读取游标数据
    read_loop: LOOP
        FETCH cur INTO v_createDate, v_userName, v_phone, v_age, v_sex, v_introduce, v_scoreName, v_result;
        IF done THEN
            LEAVE read_loop;
        END IF;
        
        -- 这里可以处理每一行的数据，比如输出或插入到临时表中
        SELECT v_createDate, v_userName, v_phone, v_age, v_sex, v_introduce, v_scoreName, v_result;
    END LOOP;
    
    -- 关闭游标
    CLOSE cur;
END //

DELIMITER ;

调用

CALL GetStudentInfoByName('某个学生姓名');