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

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

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

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

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

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

語法格式： 

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

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

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

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

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

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

存儲函數創(chuàng)建完后，調用存儲函數的方法和使用系統提供的內置函數相同，都是使用SELECT關鍵字。

語法格式：

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

例：創(chuàng)建一個存儲函數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的擴展，如果函數不存在，它防止發(fā)生錯誤

例：刪除存儲函數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選項。如果想要驗證新數據是否滿足使用的限制，則使用BEFORE選項。

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

例： 創(chuàng)建一個表table1，其中只有一列a，在表上創(chuàng)建一個觸發(fā)器，每次插入操作時，將用戶變量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';

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

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

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

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

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

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

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

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

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

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

2、創(chuàng)建觸發(fā)器：調用存儲過程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中存儲函數創(chuàng)建與觸發(fā)器設置的詳細內容，更多關于MySQL存儲函數 觸發(fā)器的資料請關注腳本之家其它相關文章！

最新評論