實驗三 存儲過程與觸發器

實驗目的：

學習SQL語言進行編程的基本方法與技術，能夠編寫存儲過程、觸發器解決資料庫需要處理的複雜問題。

實驗內容：

1、 設計一個存儲過程或者自定義函數，練習存儲過程的設計方法。
2、 設計觸發器，理解觸發器的工作原理與設計方法。

實驗過程及要求：

1、 編寫存儲過程，傳入學號，查詢該同學所有選修記錄，結果顯示信息項包括學號、姓名、班級名稱、課程名、學分、成績.
2、撰寫存儲過程，完成以下操作代碼寫在空白處。
（1）插入數據學院2020級電腦科學技術1班、電腦科學技術2班記錄。
（2）針對2個班級，產生學生的記錄插入到學生信息表中，每班學生數30人。
3、編寫觸發器：當在學生表中更新（增、刪、改）學生記錄時，計算學生班級總人數，並更新班級表中對應的記錄中

實驗具體操作

1、 編寫存儲過程，傳入學號，查詢該同學所有選修記錄，結果顯示信息項包括學號、姓名、班級名稱、課程名、學分、成績.

DROP PROCEDURE if exists GetStudentCourseRecords;
DELIMITER //

CREATE PROCEDURE GetStudentCourseRecords(IN student_id VARCHAR(9))
BEGIN
    SELECT s.Sid, s.Sname, g.gname, c.Cname, c.credit, sc.score1,sc.score2
    FROM ustudent s
    INNER JOIN ugrade g ON s.gid = g.gid
    INNER JOIN usc sc ON s.Sid = sc.sid
    INNER JOIN ucourse c ON sc.cid = c.Cid
    WHERE s.Sid = student_id;
END //

DELIMITER ;

call GetStudentCourseRecords('012005004');

2、撰寫存儲過程，完成以下操作代碼寫在空白處。
1）插入數據學院2020級電腦科學技術1班、電腦科學技術2班記錄。
2）針對2個班級，產生學生的記錄插入到學生信息表中，每班學生數30人。

DELIMITER //
DROP PROCEDURE if exists InsertClassData;
CREATE PROCEDURE InsertClassData()
BEGIN
    DECLARE i INT DEFAULT 1;
    DECLARE j INT DEFAULT 1;
    DECLARE random_sex varchar(5);


    -- 1) 插入數據學院2020級電腦科學技術1班、電腦科學技術2班記錄
    WHILE i <= 2
        DO
            INSERT INTO ugrade (gid, gname, gyear)
            VALUES (CONCAT('20', LPAD(i, 2, '0')), CONCAT('電腦科學技術', i, '班'), 2020);

            SET i = i + 1;
        END WHILE;

    -- 2) 產生學生的記錄插入到學生信息表中 每班學生數30人
    SET i = 1;
    WHILE i <= 2
        DO
            SELECT IF(RAND() < 0.5, '男', '女') INTO random_sex;

            WHILE j <= 30
                DO
                    INSERT INTO ustudent (Sid, Sname, Ssexy, Sbdate, gid, stele)
                    VALUES (CONCAT('S', LPAD(j, 7, '0')), CONCAT('stu', LPAD(j, 3, '0')), random_sex, '2002-01-01',
                            CONCAT('20', LPAD(i, 2, '0')), '1234567890');

                    SET j = j + 1;
                END WHILE;

            SET i = i + 1;
        END WHILE;
END //

DELIMITER ;


call InsertClassData()

執行之前表內容

ugrade

ustudent

執行之後

3、編寫觸發器：當在學生表中更新（增、刪、改）學生記錄時，計算學生班級總人數，並更新班級表中對應的記錄中

DELIMITER //

CREATE TRIGGER UpdateClassStudentCount
AFTER INSERT ON ustudent
FOR EACH ROW
BEGIN
    DECLARE class_id VARCHAR(2);
    DECLARE student_count INT;

    -- class id
    SET class_id = NEW.gid;

    -- count of students in the class
    SELECT COUNT(*) INTO student_count FROM ustudent WHERE gid = class_id;

    -- ugrade table
    UPDATE ugrade SET gyear = student_count WHERE gid = class_id;
END //

DELIMITER ;