頭歌實訓之存儲過程、函數與觸發器

🌟?各位看官好，我是maomi_9526！

🌍?種一棵樹最好是十年前，其次是現在！

🚀?今天來學習C語言的相關知識。

👍?如果覺得這篇文章有幫助，歡迎您一鍵三連，分享給更多人哦

第1關：創建存儲過程

第2關：創建函數-count_credit

第3關：存儲過程中使用游標

第4關：創建觸發器-計算總學分?

第5關：創建觸發器-級聯刪除

總結：頭歌第二關及第三關

問題分析：

第1關：創建存儲過程

任務描述
本關任務：創建存儲過程,實現對學生姓名進行模糊查詢。

相關知識
為了完成本關任務，你需要掌握：
1.存儲過程的定義；
2.存儲過程的創建；
3.存儲過程的執行。

存儲過程的定義
?存儲過程(Stored Procedure)是數據庫中一組預編譯的SQL語句集合，存儲在數據庫中，可以被應用程序調用。存儲過程可以包含復雜的邏輯、變量、條件判斷和循環等，能夠實現一些復雜的業務邏輯，提高代碼的復用性和執行效率。

存儲過程的創建
創建存儲過程格式：

DELIMITER //
CREATE PROCEDURE 過程名([參數列表])
BEGIN
? ? -- SQL語句
END //
DELIMITER ;

DELIMITER：MySQL 默認的語句分隔符是分號（;），但在存儲過程中，分號可能會被用作語句的分隔符，因此需要使用 DELIMITER 關鍵字來改變分隔符，通常將其改為 // 或其他符號，以免與存儲過程內部的分號沖突。
存儲過程名稱：存儲過程的名稱，用于后續調用存儲過程時標識該存儲過程。
參數列表：存儲過程的參數，可以有輸入參數（IN）、輸出參數（OUT）和輸入輸出參數（INOUT）。參數的格式為參數名參數類型，例如 IN id INT 表示一個輸入參數 id，類型為整數。
BEGIN...END：存儲過程的主體部分，包含需要執行的 SQL 語句。

執行存儲過程

調用存儲過程的語法如下：

CALL 存儲過程名稱(參數值);

存儲過程名稱：要調用的存儲過程的名稱。
參數值：調用存儲過程時傳遞的參數值（實參），參數值的順序和類型應與存儲過程定義時的參數列表一致。

編程要求

根據提示，在右側編輯器補充代碼，創建存儲過程pro_findname(IN word CHAR(1)) 對學生姓名進行模糊查找,輸入任一漢字，輸出姓名中含有該漢字的全部學生信息。

測試說明
平臺會對你編寫的代碼進行測試，將調用你編寫的存儲過程:call pro_findname('明')，具體輸出請參考右側測試集。

開始你的任務吧，祝你成功！

 use teachingdb;/****請在此編寫代碼，操作完畢之后點擊評測******/
DELIMITER $$/**********Begin**********/create procedure pro_findname(in word char(10))Beginselect * from student where sname like concat('%',word,'%');
End $$/**********End**********/
DELIMITER ;

第2關：創建函數-count_credit

任務描述
本關任務：創建和使用存儲函數。

相關知識
為了完成本關任務，你需要掌握：

創建存儲函數；
調用存儲函數。

創建存儲函數

存儲函數（Stored Function）是一種在數據庫中存儲的可調用的函數，類似于存儲過程，但存儲函數的主要目的是返回一個值，而存儲過程主要用于執行一系列操作。存儲函數可以被 SQL 語句直接調用，也可以在其他存儲過程或存儲函數中調用。
創建存儲函數格式

DELIMITER //
CREATE FUNCTION 函數名稱 (參數列表)
RETURNS 返回值類型 ?READS SQL DATA
BEGIN
? ? -- 函數的 SQL 語句
? ? RETURN 返回值;
END//
DELIMITER ;

調用函數

SELECT 函數名稱(參數值);
在表達式中使用函數名稱(參數值)。

編程要求
根據提示，在右側編輯器補充代碼，設計函數 count_credit(sno CHAR(6))，根據學號（sno）計算該學生的總學分，只有當成績大于等于60分時才能獲得該門課程的學分。

測試說明
平臺會對你編寫的代碼進行測試：本題中該學生選“馬蓉”，學號為“97001”)

?開始你的任務吧，祝你成功！

 use teachingdb;/****請在此編寫代碼，操作完畢之后點擊評測******//**********Begin**********/delimiter ##create Function count_credit(v_sno char(6))returns int reads sql dataBegindeclare sums int ;select sum(credit) into sums from course natural join score where grade >=60 and v_sno=sno;return sums;end ##delimiter ;/**********End**********/

第3關：存儲過程中使用游標

任務描述
本關任務：創建存儲過程，在存儲過程中定義游標計算總學分。

相關知識
為了完成本關任務，你需要掌握：
1.游標的定義；
2.游標的使用。

游標的定義
游標（Cursor）主要用于存儲過程和函數中，用于逐行處理查詢結果集。

在存儲過程或函數中，使用 DECLARE CURSOR 語句來創建游標。

DECLARE cursor_name CURSOR FOR select_statement;

游標的使用

打開游標

OPEN cursor_name;

獲取數據

FETCH cursor_name INTO var_name1, var_name2, ...;

關閉游標

CLOSE cursor_name;

舉例：通過游標獲取users表中的id,name，并輸出結果。

DELIMITER //
CREATE PROCEDURE process_users()
BEGIN
? ?-- 聲明變量
? ?DECLARE finished INTEGER DEFAULT 0;
? ?DECLARE user_id INT DEFAULT 0;
? ?DECLARE user_name VARCHAR(50) DEFAULT '';
? ?-- 聲明游標
? ?DECLARE my_cursor CURSOR FOR SELECT id, name FROM users;
? ?-- 聲明結束處理
? ?DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1;
? ?-- 打開游標
? ?OPEN my_cursor;
? ?-- 獲取數據
? ?fetch_loop: LOOP
? ? ? ?FETCH my_cursor INTO user_id, user_name;
? ? ? ?IF finished = 1 THEN
? ? ? ? ? ?LEAVE fetch_loop;
? ? ? ?END IF;
? ? ? ?-- 處理每一行數據
? ? ? ?SELECT user_id, user_name;
? ?END LOOP;
? ?-- 關閉游標
? ?CLOSE my_cursor;
END //
DELIMITER ;

編程要求
根據提示，在右側編輯器補充代碼，創建存儲過程 p_count_credit，在存儲過程中創建游標stucur保存所有學生的學號，使用loop循環從游標中提取每個學生的學號，根據當前學號調用count_credit 函數計算學生的總學分，并更新學生表中的總學分值。

測試說明
平臺會對你編寫的代碼進行測試，將調用你編寫的存儲過程p_count_credit，函數的count_credit的功能和第2關中的相同，具體輸出請參考右側測試集。

開始你的任務吧，祝你成功！

 use teachingdb;/****請在此編寫代碼，操作完畢之后點擊評測******//**********Begin**********/drop procedure if exists p_count_credit;delimiter ##create procedure  p_count_credit()begindeclare v_sno varchar(20);declare v_credit int default 0;declare stucur cursor for select sno from student;declare exit handler for not found close stucur ;open stucur;while true dofetch stucur into v_sno;update student set totalcredit =count_credit(v_sno) where sno=v_sno;end while;close stucur;select *from student;end ##delimiter ;/**********End**********/

第4關：創建觸發器-計算總學分?

任務描述
本關任務：創建觸發器 sum_credit，實現對 student 表總學分的計算。

相關知識
為了完成本關任務，你需要掌握：
1.觸發器的定義；
2.觸發器的創建。

觸發器的定義
觸發器（Trigger）是一種特殊的存儲過程，它在表上的數據發生變化（如INSERT、UPDATE 和 DELETE操作）時自動執行。觸發器的主要作用是維護數據的完整性和一致性，或者在數據變化時執行一些自動化的操作。

觸發時機：觸發器可以設置在數據操作之前（BEFORE）或之后（AFTER）執行。
觸發事件：可以是INSERT、UPDATE或DELETE操作。

創建觸發器的語法：

CREATE TRIGGER trigger_name
{BEFORE | AFTER} {INSERT | UPDATE | DELETE} ON table_name
FOR EACH ROW
BEGIN
? ? -- 觸發器要執行的SQL語句
END;

BEFORE 或 AFTER 指定觸發器應在事件之前還是之后觸發。
INSERT, UPDATE, 或 DELETE 指定觸發器應該響應的事件類型。

觸發器表

在 MySQL 觸發器中，NEW 和 OLD 是兩個特殊的虛擬表（實際上它們是行數據的引用），用于訪問觸發事件中涉及的數據。這兩個虛擬表的具體含義和用途取決于觸發器所關聯的操作類型（INSERT、UPDATE 或 DELETE）。
NEW 表
- NEW 表表示即將插入到表中的新行數據，或者在更新操作中表示更新后的新值。
- 可用場景：在 BEFORE INSERT 和 AFTER INSERT 中，NEW 表示將要插入的新行。在 BEFORE UPDATE 和 AFTER UPDATE 中，NEW 表示更新后的字段值。
- 用法：可以通過 NEW.column_name 來訪問或修改某一列的值。

示例
（1）創建一觸發器 t_u_s，實現在更新學生表的學號時，同時更新 grade 表中的相關記錄的 student 的 id 值。

CREATE TRIGGER t_u_s
AFTER UPDATE ON student
for EACH ROW
BEGIN
? ? UPDATE grade SET studentid = new.studentid WHERE studentid = old.studentid;
END

OLD 表
OLD 表表示被刪除或更新的舊行數據。
可用場景：在 BEFORE DELETE 和 AFTER DELETE 中，OLD 表示將要被刪除的行。在 BEFORE UPDATE 和 AFTER UPDATE 中，OLD 表示更新前的字段值。
用法：可以通過 OLD.column_name 來訪問某一列的值，但不能對其進行修改（因為它是只讀的）。

示例
（2）建一個觸發器 t_d_s，當刪除表 student 中某個學生的信息時，同時將 grade 表中與該學生有關的數據全部刪除。

CREATE TRIGGER trigger_t1
AFTER DELETE ON student
FOR EACH ROW
BEGIN?
? ? DELETE FROM grade WHERE studentid = old.studentid;
END

編程要求
根據提示，在右側編輯器補充代碼，創建觸發器 sum_credit，實現對 student 表總學分的計算，當 score 中添加一條記錄時，student 表總學分的值做相應改變。當課程成績大于等于60分時，將該課程的學分加到該學生的總學分中。

測試說明
平臺會對你編寫的代碼進行測試：平臺會用“馬小燕”為測試用例進行測試。

開始你的任務吧，祝你成功！

 use teachingdb;/****請在此編寫代碼，操作完畢之后點擊評測******//**********Begin**********/delimiter ##create trigger sum_creditafter insert on score for each rowBeginif(new.grade>=60) thenupdate student set totalcredit =totalcredit+(select credit from course where cno=new.cno)where student.sno=new.sno;end if;end ##delimiter ;/**********End**********/

第5關：創建觸發器-級聯刪除

任務描述
本關任務：創建級聯刪除觸發器。

相關知識
為了完成本關任務，你需要掌握：
1.級聯刪除的定義；
2.級聯刪除的工作原理。

級聯刪除的定義

級聯刪除（Cascade Delete）是數據庫管理系統中的一種功能，用于定義外鍵約束時指定的刪除規則。當一個表中的記錄被刪除時，所有關聯的記錄也會根據級聯刪除規則自動刪除。這確保了數據的一致性，避免出現孤立記錄（即那些指向已刪除記錄的外鍵值）。

級聯刪除的工作原理

級聯刪除的工作原理
- 假設你有兩個表：Parent 和 Child。Child 表通過外鍵關聯到 Parent 表。當你在 Parent 表上設置級聯刪除時，如果從 Parent 表中刪除一條記錄，那么所有在 Child 表中與這條記錄相關聯的記錄也將被自動刪除。

編程要求
根據提示，在右側編輯器補充代碼，創建級聯刪除觸發器 del_student_score，當刪除 student 表中的某學生時，也刪除 score 表中的對應學號的學生選課記錄。

測試說明
平臺會對你編寫的代碼進行測試。

開始你的任務吧，祝你成功！

 use teachingdb;/****請在此編寫代碼，操作完畢之后點擊評測******//**********Begin**********/delimiter ##create trigger del_studnet_scorebefore delete on student for each rowbegindelete from score where sno=old.sno;end ##delimiter ;/**********End**********/

總結：頭歌第二關及第三關

常見答題系統工作原理：?

問題分析：

在頭歌平臺的測試過程中，由于他們只進行公開測試用例的驗證，部分潛在的邏輯錯誤可能沒有被及時發現。這種情況尤其會影響到 MySQL 相關的習題，因為這些習題通常會涉及到數據庫操作的細節，如果沒有合適的隱藏測試用例，某些問題可能不會暴露。

例如實驗五第二關：

自己代碼：

官方代碼：

?use teachingdb;
?/****請在此編寫代碼，操作完畢之后點擊評測******/
?
?/**********Begin**********/
? delimiter ##
? create Function count_credit(v_sno char(6))
? returns float reads sql data
? Begin
? declare sums float;
? select sum(credit) into sums from course natural join score where grade >=60 and v_sno=sno;
? ? return sums;
? end ##
? delimiter ;
??
?/**********End**********/

CREATE DEFINER=`root`@`localhost` FUNCTION `count_credit`(stuno CHAR(6))?
RETURNS int(11)
? ? READS SQL DATA
BEGIN
? ? DECLARE stucno CHAR(3); ?
? ? DECLARE cred INT DEFAULT 0;
? ? DECLARE t_cred INT DEFAULT 0;
? ? DECLARE done INT DEFAULT FALSE;
? ??
? ? -- 定義游標，從score表中獲取學生所選課程編號
? ? DECLARE stucur CURSOR FOR?
? ? ? ? SELECT cno?
? ? ? ? FROM score?
? ? ? ? WHERE sno = stuno AND grade >= 60;
? ??
? ? -- 定義處理游標未找到數據時的行為
? ? DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
? ??
? ? -- 打開游標
? ? OPEN stucur;
? ??
? ? -- 循環獲取課程編號并累加學分
? ? loop_cursor: LOOP
? ? ? ? FETCH stucur INTO stucno;
? ? ? ? IF done THEN?
? ? ? ? ? ? LEAVE loop_cursor;
? ? ? ? END IF;
? ? ? ??
? ? ? ? -- 獲取課程學分
? ? ? ? SELECT credit INTO cred?
? ? ? ? FROM course?
? ? ? ? WHERE cno = stucno;
? ? ? ??
? ? ? ? -- 累加學分
? ? ? ? SET t_cred = t_cred + cred;
? ? END LOOP;
? ??
? ? -- 返回總學分
? ? RETURN t_cred;
END
?

?勘誤：因為官方只進行公開實例進行測試，這里都可以進行通過

Mysql習題一般都是繼續使用官方的代碼進行測試，如果你在第三關依舊寫出float的代碼?*/

 use teachingdb;/****請在此編寫代碼，操作完畢之后點擊評測******//**********Begin**********/
select sum(credit) from score natural join course  where sno='96002';drop procedure if exists p_count_credit;delimiter ##create procedure  p_count_credit()begindeclare v_sno varchar(20);declare v_credit float default 0;declare stucur cursor for select sno from student;declare exit handler for not found close stucur ;open stucur;while true dofetch stucur into v_sno;select sum(credit) into v_credit from score natural join course where grade>=60 and sno=v_sno;update student set totalcredit =ifnull(v_credit,0) where sno=v_sno;end while;close stucur;select *from student;end ##delimiter ;/**********End**********/