MySQL中存儲(chǔ)函數(shù)創(chuàng)建與觸發(fā)器設(shè)置的方法

這篇文章主要介紹“MySQL中存儲(chǔ)函數(shù)創(chuàng)建與觸發(fā)器設(shè)置的方法”，在日常操作中，相信很多人在MySQL中存儲(chǔ)函數(shù)創(chuàng)建與觸發(fā)器設(shè)置的方法問(wèn)題上存在疑惑，小編查閱了各式資料，整理出簡(jiǎn)單好用的操作方法，希望對(duì)大家解答”MySQL中存儲(chǔ)函數(shù)創(chuàng)建與觸發(fā)器設(shè)置的方法”的疑惑有所幫助！接下來(lái)，請(qǐng)跟著小編一起來(lái)學(xué)習(xí)吧！

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

1、存儲(chǔ)函數(shù)沒(méi)有輸出參數(shù)，因?yàn)榇鎯?chǔ)函數(shù)本身就是輸出參數(shù)。

2、不能用CALL語(yǔ)句來(lái)調(diào)用存儲(chǔ)函數(shù)。

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

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

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

語(yǔ)法格式： 

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

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

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

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

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

例：創(chuàng)建一個(gè)存儲(chǔ)函數(shù)來(lái)刪除Sell表中有但Book表中不存在的記錄 

DELIMITER $$
CREATE FUNCTION del_sell(book_bh CHAR(20))
RETURNS BOOLEAN
BEGIN
DECLARE bh CHAR(20);
SELECT 圖書(shū)編號(hào) INTO bh FROM Book WHERE 圖書(shū)編號(hào)=book_bh;
IF bh IS NULL THEN
DELETE FROM Sell WHERE 圖書(shū)編號(hào)=book_bh;
RETURN TRUE;
ELSE
RETURN FALSE;
END IF;
END$$
DELIMITER ;

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

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

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

語(yǔ)法格式：

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

例：創(chuàng)建一個(gè)存儲(chǔ)函數(shù)publish_book，通過(guò)調(diào)用存儲(chǔ)函數(shù)author_book獲得圖書(shū)的作者，并判斷該作者是否姓“張”，是則返回出版時(shí)間，不是則返回“不合要求”。 

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 出版時(shí)間 FROM Book WHERE 書(shū)名=b_name);
ELSE
RETURN'不合要求';
END IF;
END$$
DELIMITER ;

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

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

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

語(yǔ)法格式：

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

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

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

DROP FUNCTION IF EXISTS a;

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

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

語(yǔ)法格式：

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

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

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

例： 創(chuàng)建一個(gè)表table1，其中只有一列a，在表上創(chuàng)建一個(gè)觸發(fā)器，每次插入操作時(shí)，將用戶變量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ù)庫(kù)中有哪些觸發(fā)器可以使用SHOW TRIGGERS命令。

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

對(duì)于INSERT語(yǔ)句，只有NEW是合法的，對(duì)于DELETE語(yǔ)句，只有OLD才合法。而UPDATE語(yǔ)句可以與NEW和OLD同時(shí)使用。

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

DELIMITER $$
CREATE TRIGGER book_del AFTER DELETE
ON Book FOR EACH ROW
BEGIN
DELETE FROM Sell WHERE 圖書(shū)編號(hào)=OLD.圖書(shū)編號(hào);
END$$
DELIMITER ;

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

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

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

1、定義存儲(chǔ)過(guò)程：創(chuàng)建一個(gè)與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)用存儲(chǔ)過(guò)程data_copy()

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

5、刪除觸發(fā)器

語(yǔ)法格式：

DROP TRIGGER 觸發(fā)器名

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

DROP TRIGGER members_ins;

到此，關(guān)于“MySQL中存儲(chǔ)函數(shù)創(chuàng)建與觸發(fā)器設(shè)置的方法”的學(xué)習(xí)就結(jié)束了，希望能夠解決大家的疑惑。理論與實(shí)踐的搭配能更好的幫助大家學(xué)習(xí)，快去試試吧！若想繼續(xù)學(xué)習(xí)更多相關(guān)知識(shí)，請(qǐng)繼續(xù)關(guān)注億速云網(wǎng)站，小編會(huì)繼續(xù)努力為大家?guī)?lái)更多實(shí)用的文章！