詳解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語句。同樣，也不能呼叫將資料返回使用者端的儲存過程。

例： 建立一個表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中儲存函數建立與觸發器設定的詳細內容，更多關於MySQL儲存函數 觸發器的資料請關注it145.com其它相關文章！