存儲函數(shù)也是過程式對象之一，與存儲過程相似。他們都是由SQL和過程式語句組成的代碼片段，并且可以從應(yīng)用程序和SQL中調(diào)用。然而，他們也有一些區(qū)別：

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

2、不能用CALL語句來調(diào)用存儲函數(shù)。

3、存儲函數(shù)必須包含一條RETURN語句，而這條特殊的SQL語句不允許包含于存儲過程中

1、創(chuàng)建存儲函數(shù)

使用CREATE FUNCTION語句創(chuàng)建存儲函數(shù)

語法格式： 

CREATE FUNCTION 存儲函數(shù)名 （[參數(shù)[,...]]）
RETURNS 類型
函數(shù)體

注：存儲函數(shù)不能擁有與存儲過程相同的名字。存儲函數(shù)體中必須包含一個RETURN值語句，值為存儲函數(shù)的返回值。

例：創(chuàng)建一個存儲函數(shù)，其返回Book表中圖書數(shù)目作為結(jié)果 

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

RETURN子句中包含SELECT語句時，SELECT語句的返回結(jié)果只能是一行且只能有一列值。雖然該存儲函數(shù)沒有參數(shù)，使用時也要用()，如num_book()。

例：創(chuàng)建一個存儲函數(shù)來刪除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 ;

該存儲函數(shù)給定圖書編號作為輸入?yún)?shù)，先按給定的圖書編號到Book表查找看有沒有該圖書編號的書，如果沒有，返回false，如果有，返回true。同時還要到Sell表中刪除該圖書編號的書。要查看數(shù)據(jù)庫中有哪些存儲函數(shù)，可以使用SHOW FUNCTION STATUS命令。

2、調(diào)用存儲函數(shù)

存儲函數(shù)創(chuàng)建完后，調(diào)用存儲函數(shù)的方法和使用系統(tǒng)提供的內(nèi)置函數(shù)相同，都是使用SELECT關(guān)鍵字。

語法格式：

SELECT 存儲函數(shù)名（[參數(shù)[,...]]）

例：創(chuàng)建一個存儲函數(shù)publish_book，通過調(diào)用存儲函數(shù)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 ;

調(diào)用存儲函數(shù)publish_book查看結(jié)果：

SELECT publish_book('計算機(jī)網(wǎng)絡(luò)技術(shù)');

刪除存儲函數(shù)的方法和刪除存儲過程的方法基本一樣，使用DROP FUNCTION語句

語法格式：

DROP FUNCTION [IF EXISTS]存儲函數(shù)名

注：IF EXISTS子句是MySQL的擴(kuò)展，如果函數(shù)不存在，它防止發(fā)生錯誤

例：刪除存儲函數(shù)a 

DROP FUNCTION IF EXISTS a;

3、創(chuàng)建觸發(fā)器

使用CREATE TRIGGER語句創(chuàng)建觸發(fā)器

語法格式：

CREATE TRIGGER 觸發(fā)器名 觸發(fā)時間 觸發(fā)事件
ON 表名 FOR EACH ROW 觸發(fā)器動作

觸發(fā)時間有兩個選項：BEFORE和AFTER，以表示觸發(fā)器是在激活它的語句之前或之后觸發(fā)。如果想要在激活觸發(fā)器的語句執(zhí)行之后執(zhí)行通常使用AFTER選項。如果想要驗證新數(shù)據(jù)是否滿足使用的限制，則使用BEFORE選項。

觸發(fā)器不能返回任何結(jié)果到客戶端，為了阻止從觸發(fā)器返回結(jié)果，不要在觸發(fā)器定義中包含SELECT語句。同樣，也不能調(diào)用將數(shù)據(jù)返回客戶端的存儲過程。

例： 創(chuàng)建一個表table1，其中只有一列a，在表上創(chuàng)建一個觸發(fā)器，每次插入操作時，將用戶變量str的值設(shè)為TRIGGER IS WORKING。

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

要查看數(shù)據(jù)庫中有哪些觸發(fā)器可以使用SHOW TRIGGERS命令。

在MySQL觸發(fā)器中的SQL語句可以關(guān)聯(lián)表中的任意列。但不能直接使用列的名稱去標(biāo)志，那會使系統(tǒng)混淆，因為激活觸發(fā)器的語句可能已經(jīng)修改、刪除或添加了新的列名，而列的舊名同時存在。因此必須用這樣的語法來標(biāo)志：NEW.column_name或者OLD.column_name。NEW.column_name用來引用新行的一列，OLD.column_name用來引用更新或刪除它之前的已有行的一列。

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

例：創(chuàng)建一個觸發(fā)器，當(dāng)刪除表Book中某圖書的信息時，同時將Sell表中與該圖書有關(guān)的數(shù)據(jù)全部刪除。 

DELIMITER $$
CREATE TRIGGER book_del AFTER DELETE
ON Book FOR EACH ROW
BEGIN
DELETE FROM Sell WHERE 圖書編號=OLD.圖書編號;
END$$
DELIMITER ;

當(dāng)觸發(fā)器要觸發(fā)的是表自身的更新操作時，只能使用BEFORE觸發(fā)器，而AFTER觸發(fā)器將不被允許。

4、在觸發(fā)器中調(diào)用存儲過程 

例：假設(shè)Bookstore數(shù)據(jù)庫中有一個與Members表結(jié)構(gòu)完全一樣的表member_b，創(chuàng)建一個觸發(fā)器，在Members表中添加數(shù)據(jù)的時候，調(diào)用存儲過程，將member_b表中的數(shù)據(jù)與Members表同步。

1、定義存儲過程：創(chuàng)建一個與Members表結(jié)構(gòu)完全一樣的表member_b 

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

2、創(chuàng)建觸發(fā)器：調(diào)用存儲過程data_copy()

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

5、刪除觸發(fā)器

語法格式：

DROP TRIGGER 觸發(fā)器名

例：刪除觸發(fā)器members_ins

DROP TRIGGER members_ins;

以上就是詳解MySQL中存儲函數(shù)創(chuàng)建與觸發(fā)器設(shè)置的詳細(xì)內(nèi)容，更多關(guān)于MySQL存儲函數(shù) 觸發(fā)器的資料請關(guān)注腳本之家其它相關(guān)文章！

最新評論