【无标题】

DDL

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
);
CREATE TABLE score (
    id INT PRIMARY KEY AUTO_INCREMENT,
    scoreName VARCHAR(50) NOT NULL,
    result DECIMAL(5, 2) NOT NULL,
    studentId INT,
    FOREIGN KEY (studentId) REFERENCES student(id) ON DELETE CASCADE
);

DQL

DELIMITER //

CREATE PROCEDURE GetStudentScoreByName(IN p_userName VARCHAR(50))
BEGIN
    -- 声明变量来存储从游标中检索的数据
    DECLARE done INT DEFAULT FALSE;
    DECLARE v_student_id INT;
    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_score_id INT;
    DECLARE v_scoreName VARCHAR(50);
    DECLARE v_result DECIMAL(5, 2);
    
    -- 声明游标
    DECLARE cur CURSOR FOR 
        SELECT 
            s.id, s.createDate, s.userName, s.phone, s.age, s.sex, s.introduce,
            sc.id AS score_id, sc.scoreName, sc.result
        FROM 
            student s
        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_student_id, v_createDate, v_userName, v_phone, v_age, v_sex, v_introduce, v_score_id, v_scoreName, v_result;
        IF done THEN
            LEAVE read_loop;
        END IF;
        
        -- 这里你可以对每一行数据进行处理，例如打印或返回
        SELECT 
            v_student_id AS student_id, 
            v_createDate AS createDate, 
            v_userName AS userName, 
            v_phone AS phone, 
            v_age AS age, 
            v_sex AS sex, 
            v_introduce AS introduce,
            v_score_id AS score_id, 
            v_scoreName AS scoreName, 
            v_result AS result;
    END LOOP;
    
    -- 关闭游标
    CLOSE cur;
END //

DELIMITER ;