MySQL數據庫的觸發器和存儲過程實例分析

這篇文章主要介紹“MySQL數據庫的觸發器和存儲過程實例分析”的相關知識，小編通過實際案例向大家展示操作過程，操作方法簡單快捷，實用性強，希望這篇“MySQL數據庫的觸發器和存儲過程實例分析”文章能幫助大家解決問題。

一、實驗目的

• 1、掌握某主流DBMS支持的SQL編程語言和編程規范，規范設計存儲過程；

• 2、能夠理解不同類型觸發器的作用和執行原理，驗證觸發器的有效性;

• 3、培養學生的系統思維，提升解決復雜工程問題所需的編程能力。

二、實驗要求

掌握某主流DBMS的SQL編程語言，在前面創建的數據庫基礎上，定義BEFORE（for）觸發器和AFTER觸發器；掌握數據庫存儲過程定義、存儲過程運行，存儲過程更名，存儲過程刪除，存儲過程的參數傳遞。

溫馨提示：以下內容均已經過測試，不過難免會有疏漏，但是代碼實現大多數思路及實現經測驗都是正確的。

三、實現內容及步驟

1、創建一個不帶參數的簡單存儲過程

• (1)創建存儲過程sp_avggrade，實現查詢每門課程學生的平均成績的功能；

delimiter//
CREATE PROCEDURE sp_avggrade()
COMMENT '查詢每門課程學生的平均成績的功能'
BEGIN
SELECT cno as 課程號,avg(grade)as 平均成績
FROM sc
GROUP BY cno;
end//
delimiter;

(2)調用該存儲過程，實現查詢。

call sp_avggrade();

2、創建一個帶輸入參數的存儲過程

(1)創建存儲過程sp_course_avggrade, 實現通過輸入課程編號參數查詢指定課程編號的課程平均成績的功能；

delimiter//
CREATE PROCEDURE sp_course_avggrade(IN c_no CHAR(2))
COMMENT '通過輸入課程編號參數查詢指定課程編號的課程平均成績的功能'
BEGIN
SELECT cno,AVG(grade)
FROM sc
WHERE cno=c_no;
END//
delimiter;

(2)調用該存儲過程，獲取指定課程的平均成績。

3、創建一個帶輸入輸出參數的存儲過程

(1)創建存儲過程sp_sdept _student，實現根據用戶輸入的院系編號參數，查找該學院的學生人數，并以變量形式輸出的功能；

delimiter//
CREATE PROCEDURE sp_sdept_student2(IN _sdept CHAR(10),OUT num int)
BEGIN
SELECT COUNT(sno) INTO num
FROM student
WHERE _sdept = sdept;
END//
delimiter;

(2)調用該存儲過程，以返回變量的形式獲取相應院系的學生人數。

SET @num=10;
CALL sp_sdept_student2('計科',@num);
SELECT @num AS '人數';

4、觸發器的創建與使用

(1)在學生表上創建觸發器，實現學生表中刪除學生記錄時，成績表中該學生成績記錄的級聯刪除；

delimiter//
CREATE TRIGGER delete_stu
BEFORE DELETE
ON student
FOR EACH ROW
BEGIN
DELETE
FROM sc
WHERE sc.sno=old.sno;
END//
delimiter;

(2)在選課表上創建觸發器，若錄入的成績大于100分，小于0分，則拒絕插入記錄并顯示；

delimiter//
CREATE TRIGGER scgrade
BEFORE INSERT ON sc
FOR EACH ROW
BEGIN
IF new.grade>100 or new.grade < 0 THEN
SIGNAL SQLSTATE '45000'
SET message_text='錄入成績不符合規定，拒絕插入';
END IF;
END//
delimiter;

(3)驗證(1)、(2)中觸發器

驗證(1)

DELETE
FROM student
WHERE sno='201513';

驗證(2)

INSERT INTO sc(sno,cno,grade)
VALUES('2002720','1',200);

關于“MySQL數據庫的觸發器和存儲過程實例分析”的內容就介紹到這里了，感謝大家的閱讀。如果想了解更多行業相關的知識，可以關注億速云行業資訊頻道，小編每天都會為大家更新不同的知識點。