MySQL中儲存函數建立與觸發器設定的方法

儲存函數也是過程式物件之一，與預存程序相似。這些程式碼片段包含SQL和過程式語句，可以從應用程式和SQL中呼叫。然而，他們也有一些差異：

1、儲存函數沒有輸出參數，因為儲存函數本身就是輸出參數。

2、不能用CALL語句來呼叫儲存函數。

3、儲存函數必須包含一條RETURN語句，而這條特殊的SQL語句不允許包含於預存程序中

1、建立儲存函數

使用CREATE FUNCTION語句建立儲存函數

語法格式： 

CREATE FUNCTION 儲存函數名稱（[參數[,...]]）
RETURNS 類型
函數本體

註：儲存函數不能擁有與預存程序相同的名字。儲存函數體中必須包含一個RETURN值語句，值為儲存函數的傳回值。

範例：建立一個儲存函數，當其傳回Book表中圖書數目作為結果 

DELIMITER $$
CREATE FUNCTION num_book()
RETURNS INTEGER
BEGIN
RETURN(SELECT COUNT(*)FROM Book);
END$$
DELIMITER ;

RETURN子句中包含SELECT語句時，SELECT語句的傳回結果只能是一行且只能有一列值。即使儲存函數不需要參數，呼叫時也需要使用()，例如：num_book()。

範例：建立一個儲存函數來刪除Sell表中有但Book表中不存在的記錄 

DELIMITER $$
CREATE FUNCTION del_sell(book_bh CHAR(20))
RETURNS BOOLEAN
BEGIN
DECLARE bh CHAR(20);
SELECT 图书编号 INTO bh FROM Book WHERE 图书编号=book_bh;
IF bh IS NULL THEN
DELETE FROM Sell WHERE 图书编号=book_bh;
RETURN TRUE;
ELSE
RETURN FALSE;
END IF;
END$$
DELIMITER ;

此儲存函數給定圖書編號作為輸入參數，先按給定的圖書編號到Book表查找看有沒有該圖書編號的書，如果沒有，回傳false，如果有，回傳true。同時也要到Sell表中刪除該圖書編號的書。若要列出資料庫中的預存程序，請使用SHOW FUNCTION STATUS指令即可。

2、呼叫儲存函數

儲存函數建立完後，呼叫儲存函數的方法和使用系統提供的內建函數相同，都是使用SELECT關鍵字。

語法格式：

SELECT 儲存函數名稱（[參數[,...]]）

範例：建立一個儲存函數publish_book，透過呼叫儲存函數author_book來獲得圖書的作者，並判斷該作者是否姓“張”，是則返回出版時間，不是則返回“不合要求”。

DELIMITER $$
CREATE FUNCTION publish_book(b_name CHAR(20))
RETURNS CHAR(20)
BEGIN
DECLARE name CHAR(20);
SELECT author_book(b_name)INTO name;
IF name like'张%' THEN
RETURN(SELECT 出版时间 FROM Book WHERE 书名=b_name);
ELSE
RETURN'不合要求';
END IF;
END$$
DELIMITER ;

呼叫儲存函數publish_book查看結果：

SELECT publish_book('電腦網路技術');

刪除儲存函數的方法並刪除存儲過程的方法基本上一樣，使用DROP FUNCTION語句

語法格式：

DROP FUNCTION [IF EXISTS]儲存函數名稱

##：IF EXISTS子句是MySQL的擴展，如果函數不存在，它防止錯誤

#例：刪除儲存函數a 

DROP FUNCTION IF EXISTS a;

3、建立觸發器

使用CREATE TRIGGER語句建立觸發器

語法格式：

CREATE TRIGGER 觸發器名稱觸發時間觸發事件
ON 表名FOR EACH ROW 觸發器動作

#觸發器有兩種觸發選項：BEFORE和AFTER，分別表示觸發器是在啟動它的語句之前或之後被觸發。通常使用AFTER選項來在啟動觸發器後執行語句。 BEFORE選項用於驗證新資料是否符合使用限制。

包含SELECT語句的觸發器會傳回結果到客戶端，為了避免這種情況，應該避免在觸發器定義中使用SELECT語句。同樣，也不能呼叫將資料傳回客戶端的預存程序。

範例： 建立一個表table1，其中只有一列a，在表上建立一個觸發器，每次插入操作時，將使用者變數str的值設為TRIGGER IS WORKING。

CREATE TABLE table1(a INTEGER);
CREATE TRIGGER table1_insert AFTER INSERT
ON table1 FOR EACH ROW
SET@str='TRIGGER IS WORKING';

要查看資料庫中有哪些觸發器可以使用SHOW TRIGGERS指令。

在MySQL觸發器中的SQL語句可以關聯表中的任意欄位。但不能直接使用列的名稱去標誌，那會使系統混淆，因為啟動觸發器的語句可能已經修改、刪除或新增了新的列名，而列的舊名同時存在。必須使用這種語法來識別：NEW.column_name或OLD.column_name。 NEW.column_name用來引用新行的一列，OLD.column_name用來引用更新或刪除它之前的已有行的一列。

對於INSERT語句，只有NEW是合法的，對於DELETE語句，只有OLD才合法。而UPDATE語句可以與NEW和OLD同時使用。

建立一個觸發器，使得當刪除表格「Book」中某本圖書的資訊時，同時刪除所有與該圖書有關的「Sell」表格中的資料。 

DELIMITER $$
CREATE TRIGGER book_del AFTER DELETE
ON Book FOR EACH ROW
BEGIN
DELETE FROM Sell WHERE 图书编号=OLD.图书编号;
END$$
DELIMITER ;

當觸發器要觸發的是表本身的更新操作時，只能使用BEFORE觸發器，而AFTER觸發器將不被允許。

4、在觸發器中呼叫預存程序 

例：假設Bookstore資料庫中有一個與Members表結構完全一樣的表member_b，建立一個觸發器，在Members表中加入數據的時候，呼叫預存程序，將member_b表中的資料與Members表同步。

1、定義預存程序：建立一個與Members表結構完全一樣的表格member_b 

DELIMITER $$
CREATE PROCEDURE data_copy()
BEGIN
REPLACE member_b SELECT * FROM Members;
END$$

2、建立觸發器：呼叫儲存程序data_copy()

DELIMITER $$
CREATE TRIGGER members_ins AFTER INSERT
ON Members FOR EACH ROW
CALL data_copy();
DELIMITER ;

5 、刪除觸發器

語法格式：

##DROP TRIGGER 觸發器名稱

範例：刪除觸發器members_ins

DROP TRIGGER members_ins;

以上是MySQL中儲存函數建立與觸發器設定的方法的詳細內容。更多資訊請關注PHP中文網其他相關文章！