MySQL數據庫實驗之 觸發器和存儲過程

Posted on by

觀前提示：本篇內容為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);

四、實驗總結

  • 1、通過本次實驗，對存儲過程和觸發器有瞭更深的認識；
  • 2、能夠理解不同類型觸發器的作用和執行原理， 驗證瞭觸發器的有效性；
  • 3、熟練掌握瞭數據庫存儲過程定義、存儲過程運行，存儲過程更名，存儲過程刪除，存儲過程的參數傳遞。

到此這篇關於MySQL數據庫實驗之 觸發器和存儲過程的文章就介紹到這瞭,更多相關MySQ存儲過程內容請搜索WalkonNet以前的文章或繼續瀏覽下面的相關文章希望大傢以後多多支持WalkonNet！

推薦閱讀：