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 &#39;45000&#39;
SET message_text=&#39;录入成绩不符合规定，拒绝插入&#39;;
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資料庫的觸發器和預存程序實例分析的詳細內容。更多資訊請關注PHP中文網其他相關文章！