MySQL數(shù)據(jù)庫(kù)的觸發(fā)器和存儲(chǔ)過(guò)程實(shí)例分析

這篇文章主要介紹“MySQL數(shù)據(jù)庫(kù)的觸發(fā)器和存儲(chǔ)過(guò)程實(shí)例分析”的相關(guān)知識(shí)，小編通過(guò)實(shí)際案例向大家展示操作過(guò)程，操作方法簡(jiǎn)單快捷，實(shí)用性強(qiáng)，希望這篇“MySQL數(shù)據(jù)庫(kù)的觸發(fā)器和存儲(chǔ)過(guò)程實(shí)例分析”文章能幫助大家解決問(wèn)題。

一、實(shí)驗(yàn)?zāi)康?/h3>

• 1、掌握某主流DBMS支持的SQL編程語(yǔ)言和編程規(guī)范，規(guī)范設(shè)計(jì)存儲(chǔ)過(guò)程；

• 2、能夠理解不同類型觸發(fā)器的作用和執(zhí)行原理，驗(yàn)證觸發(fā)器的有效性;

• 3、培養(yǎng)學(xué)生的系統(tǒng)思維，提升解決復(fù)雜工程問(wèn)題所需的編程能力。

二、實(shí)驗(yàn)要求

掌握某主流DBMS的SQL編程語(yǔ)言，在前面創(chuàng)建的數(shù)據(jù)庫(kù)基礎(chǔ)上，定義BEFORE（for）觸發(fā)器和AFTER觸發(fā)器；掌握數(shù)據(jù)庫(kù)存儲(chǔ)過(guò)程定義、存儲(chǔ)過(guò)程運(yùn)行，存儲(chǔ)過(guò)程更名，存儲(chǔ)過(guò)程刪除，存儲(chǔ)過(guò)程的參數(shù)傳遞。

溫馨提示：以下內(nèi)容均已經(jīng)過(guò)測(cè)試，不過(guò)難免會(huì)有疏漏，但是代碼實(shí)現(xiàn)大多數(shù)思路及實(shí)現(xiàn)經(jīng)測(cè)驗(yàn)都是正確的。

三、實(shí)現(xiàn)內(nèi)容及步驟

1、創(chuàng)建一個(gè)不帶參數(shù)的簡(jiǎn)單存儲(chǔ)過(guò)程

• (1)創(chuàng)建存儲(chǔ)過(guò)程sp_avggrade，實(shí)現(xiàn)查詢每門課程學(xué)生的平均成績(jī)的功能；

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

(2)調(diào)用該存儲(chǔ)過(guò)程，實(shí)現(xiàn)查詢。

call sp_avggrade();

2、創(chuàng)建一個(gè)帶輸入?yún)?shù)的存儲(chǔ)過(guò)程

(1)創(chuàng)建存儲(chǔ)過(guò)程sp_course_avggrade, 實(shí)現(xiàn)通過(guò)輸入課程編號(hào)參數(shù)查詢指定課程編號(hào)的課程平均成績(jī)的功能；

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

(2)調(diào)用該存儲(chǔ)過(guò)程，獲取指定課程的平均成績(jī)。

3、創(chuàng)建一個(gè)帶輸入輸出參數(shù)的存儲(chǔ)過(guò)程

(1)創(chuàng)建存儲(chǔ)過(guò)程sp_sdept _student，實(shí)現(xiàn)根據(jù)用戶輸入的院系編號(hào)參數(shù)，查找該學(xué)院的學(xué)生人數(shù)，并以變量形式輸出的功能；

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)調(diào)用該存儲(chǔ)過(guò)程，以返回變量的形式獲取相應(yīng)院系的學(xué)生人數(shù)。

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

4、觸發(fā)器的創(chuàng)建與使用

(1)在學(xué)生表上創(chuàng)建觸發(fā)器，實(shí)現(xiàn)學(xué)生表中刪除學(xué)生記錄時(shí)，成績(jī)表中該學(xué)生成績(jī)記錄的級(jí)聯(lián)刪除；

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

(2)在選課表上創(chuàng)建觸發(fā)器，若錄入的成績(jī)大于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='錄入成績(jī)不符合規(guī)定，拒絕插入';
END IF;
END//
delimiter;

(3)驗(yàn)證(1)、(2)中觸發(fā)器

驗(yàn)證(1)

DELETE
FROM student
WHERE sno='201513';

驗(yàn)證(2)

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

關(guān)于“MySQL數(shù)據(jù)庫(kù)的觸發(fā)器和存儲(chǔ)過(guò)程實(shí)例分析”的內(nèi)容就介紹到這里了，感謝大家的閱讀。如果想了解更多行業(yè)相關(guān)的知識(shí)，可以關(guān)注億速云行業(yè)資訊頻道，小編每天都會(huì)為大家更新不同的知識(shí)點(diǎn)。