存储过程课堂练习

1.创建存储过程，查询指定学院的学生学号和姓名。调用该存储过程，并显示结果。

DELIMITER //
CREATE PROCEDURE GetStudentsByDepartment(IN dept_code CHAR(4))
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE stdid INT;
    DECLARE stdname VARCHAR(20);
    DECLARE cur CURSOR FOR 
        SELECT s.stdid, s.stdname
        FROM students s
        JOIN classes c ON s.classcode = c.classcode
        JOIN departments d ON c.dptcode = d.dptcode
        WHERE d.dptcode = dept_code;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    OPEN cur;
    read_loop: LOOP
        FETCH cur INTO stdid, stdname;
        IF done THEN
            LEAVE read_loop;
        END IF;
        SELECT stdid, stdname;
    END LOOP;
    
    CLOSE cur;
END //
DELIMITER ;

CALL GetStudentsByDepartment('INDE');

2.创建存储过程，实现按学院统计学生人数，并将统计结果通过输出参数返回给调用的语句。

DELIMITER //

CREATE PROCEDURE CountStudentsByDepartment(
    OUT total_inde INT,
    OUT total_made INT,
    OUT total_elde INT)
BEGIN
    SELECT COUNT(*) INTO total_inde
    FROM students s
    JOIN classes c ON s.classcode = c.classcode
    JOIN departments d ON c.dptcode = d.dptcode
    WHERE d.dptcode = 'INDE';
    SELECT COUNT(*) INTO total_made
    FROM students s
    JOIN classes c ON s.classcode = c.classcode
    JOIN departments d ON c.dptcode = d.dptcode
    WHERE d.dptcode = 'MADE';
    SELECT COUNT(*) INTO total_elde
    FROM students s
    JOIN classes c ON s.classcode = c.classcode
    JOIN departments d ON c.dptcode = d.dptcode
    WHERE d.dptcode = 'ELDE';
END //
DELIMITER ;

CALL CountStudentsByDepartment(@total_inde, @total_made, @total_elde);
SELECT @total_inde AS 'inde', @total_made AS 'made', @total_elde AS 'elde';