詳解MySQL中存儲函數(shù)創(chuàng)建與觸發(fā)器設(shè)置
存儲函數(shù)也是過程式對象之一,與存儲過程相似。他們都是由SQL和過程式語句組成的代碼片段,并且可以從應(yīng)用程序和SQL中調(diào)用。然而,他們也有一些區(qū)別:
1、存儲函數(shù)沒有輸出參數(shù),因?yàn)榇鎯瘮?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ā)時間有兩個選項(xiàng):BEFORE和AFTER,以表示觸發(fā)器是在激活它的語句之前或之后觸發(fā)。如果想要在激活觸發(fā)器的語句執(zhí)行之后執(zhí)行通常使用AFTER選項(xiàng)。如果想要驗(yàn)證新數(shù)據(jù)是否滿足使用的限制,則使用BEFORE選項(xiàng)。
觸發(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)混淆,因?yàn)榧せ钣|發(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)文章!
相關(guān)文章
MySQL5.7的sql腳本導(dǎo)入到MySQL5.5出錯3種解決方案
筆者需要將使用MySQL5.7數(shù)據(jù)庫的網(wǎng)站挪入winows服務(wù)器,目標(biāo)服務(wù)器使用的是MySQL5.5,因?yàn)榧骖櫟揭郧暗木W(wǎng)站,MySQL不能升級。遇到MySQL5.7的sql腳本導(dǎo)入到MySQL5.5出錯,總結(jié)了3種解決方案,總有一個方案適合你。2023-06-06使用mysqldump實(shí)現(xiàn)mysql備份
mysqldump客戶端可用來轉(zhuǎn)儲數(shù)據(jù)庫或搜集數(shù)據(jù)庫進(jìn)行備份或?qū)?shù)據(jù)轉(zhuǎn)移到另一個SQL服務(wù)器(不一定是一個MySQL服務(wù)器)。今天我們就來詳細(xì)探討下mysqldump的使用方法2016-11-11