MySQL中存儲函數、觸發器的示例分析

這篇文章主要介紹了MySQL中存儲函數、觸發器的示例分析，具有一定借鑒價值，感興趣的朋友可以參考下，希望大家閱讀完這篇文章之后大有收獲，下面讓小編帶著大家一起了解一下。

 
創建函數：
 
1. 存儲函數也是過程式對象之一，與存儲過程很相似。
 
它們都是由SQL和過程式語句組成的代碼片斷，并且可以從應用程序和SQL中調用。然而，它們也有一些區別：
 
（1）存儲函數不能擁有輸出參數，因為存儲函數本身就是輸出參數；
  www.2cto.com  
（2）不能用CALL語句來調用存儲函數；
 
（3）存儲函數必須包含一條RETURN語句，而這條特殊的SQL語句不允許包含于存儲過程中。
 
2.創建存儲函數使用CREATEFUNCTION語句。
 
要查看中有哪些存儲函數，可以使用show function satus命令(與存儲過程類似)。CREATE function語法格式：
 
CREATE FUNCTION sp_name ([func_parameter[,...]])
 
   returns type
 
   [characteristic ...] routine_body
 
說明：存儲函數的定義格式和存儲過程相差不大。
 
●   sp_name是存儲函數的名稱。存儲函數不能擁有與存儲過程相同的名字。
 
●  func_parameter是存儲函數的參數，參數只有名稱和類型，不能指定IN、OUT和INOUT。RETURNS type子句聲明函數返回值的數據類型。
 
●  routine_body是存儲函數的主體，也叫存儲函數體，所有在存儲過程中使用的SQL語句在存儲函數中也適用，包括流程控制語句、游標等。但是存儲函數體中必須包含一個RETURN value語句，value為存儲函數的返回值。這是存儲過程體中沒有的。
 
例1： 創建一個存儲函數，它返回XS表中學生的數目作為結果。
  www.2cto.com  
DELIMITER $$
 
CREATE FUNCTION NUM_OF_XS()
 
RETURNS INTEGER
 
BEGIN
 
   RETURN (SELECT COUNT(*)FROM XS);
 
END$$
 
DELIMITER ;
 
例2: 創建一個存儲函數來刪除XS_KC表中有但XS表中不存在的學號。
 
DELIMITER $$
 
CREATE FUNCTION DELETE_STU(XH CHAR(6))
 
  RETURNS BOOLEAN
 
BEGIN
 
DECLARE STU CHAR(6);
 
SELECT 姓名 INTO STU FROM XS WHERE 學號=XH;
 
IF STU IS NULL THEN
 
    DELETE FROM XS_KCWHERE 學號=XH;
 
    RETURN TRUE;
 
ELSE
 
    RETURN FALSE;
  www.2cto.com  
END IF;
 
END$$
 
DELIMITER ;
 
3.  調用創建的函數
 
存儲函數創建完后，就如同系統提供的內置函數（如version()），所以調用存儲函數的方法也差不多，都是使用SELECT關鍵字。
 
語法格式為： SELECT sp_name ([func_parameter[,...]])
 
例： 調用例1中的存儲函數　：SELECT NUM_OF_XS();
 
存儲函數中還可以調用另外一個存儲函數或者存儲過程。
 
例：創建一個存儲函數，通過調用存儲函數NAME_OF_STU獲得學號的姓名，判斷姓名是否是“王林”，是則返回王林的出生日期，不是則返回“FALSE”。
 
DELIMITER $$
 
CREATE FUNCTION IS_STU(XH CHAR(6))
 
RETURNS CHAR(10)
 
BEGIN
 
DECLARE NAME CHAR(8);
 
SELECT NAME_OF_STU(XH)INTO NAME;
 
IF NAME= '王林'  THEN
 
    RETURN(SELECT 出生日期 FROM XS WHERE 學號=XH);
 
ELSE
 
    RETURN 'FALSE';
 
END IF;  www.2cto.com  
 
END$$
 
DELIMITER ;
 
4.  刪除與修改創建的函數
 
刪除存儲函數的方法與刪除存儲過程的方法基本一樣，都使用DROP FUNCTION語句。
 
語法格式為：  DROPFUNCTION [IF EXISTS] sp_name
 
例： 刪除例1中的存儲函數NUM_OF_XS。
 
DROP FUNCTION IF EXISTS NUM_OF_XS;
 
同樣也是使用ALTER FUNCTION語句可以修改存儲函數的特征。
 
語法格式為： ALTER FUNCTION sp_name [characteristic ...]
 
當然，要修改存儲函數的內容則要采用先刪除后定義的方法。
 
觸發器
 
1.  創建觸發器
 
創建觸發器使用CREATEtrigger語句，要查看數據庫中有哪些觸發器可以使用show triggers命令。
 
CREATE TRIGGER語法格式：
 
CREATE TRIGGERtrigger_name trigger_time  trigger_event
 
   ON tbl_nameFOR EACH ROW  trigger_stmt
 
說明：
 
●   trigger_name：觸發器的名稱，觸發器在當前數據庫中必須具有唯一的名稱。如果要在某個特定數據庫中創建，名稱前面應該加上數據庫的名稱。
 
●  trigger_time：觸發器觸發的時刻，有兩個選項：AFTER和BEFORE，以表示觸發器是在激活它的語句之前或之后觸發。如果想要在激活觸發器的語句執行之后執行幾個或更多的改變，通常使用AFTER選項；如果想要驗證新數據是否滿足使用的限制，則使用BEFORE選項。在MySQL中區別不明顯，before跟after用法差不多。  www.2cto.com  
 
●  trigger_event：觸發事件，指明了激活觸發程序的語句的類型。trigger_event可以是下述值之一：
 
INSERT：將新行插入表時激活觸發器。例如，通過INSERT、LOAD DATA和REPLACE語句。
 
UPDATE：更改某一行時激活觸發器。例如，通過UPDATE語句。
 
DELETE：從表中刪除某一行時激活觸發器。例如，通過DELETE和REPLACE語句。
 
●  tbl_name：與觸發器相關的表名，在該表上發生觸發事件才會激活觸發器。同一個表不能擁有兩個具有相同觸發時刻和事件的觸發器。例如，對于某一表，不能有兩個BEFORE UPDATE觸發器，但可以有1個BEFORE UPDATE觸發器和1個BEFOREINSERT觸發器，或1個BEFORE UPDATE觸發器和1個AFTER UPDATE觸發器。
 
●  FOR EACH ROW：這個聲明用來指定，對于受觸發事件影響的每一行，都要激活觸發器的動作。例如，使用一條語句向一個表中添加一組行，觸發器會對每一行執行相應觸發器動作。
 
● trigger_stmt：觸發器動作，包含觸發器激活時將要執行的語句。如果要執行多個語句，可使用BEGIN... END復合語句結構。這樣，就能使用存儲過程中允許的相同語句。
 
注意：觸發器不能返回任何結果到客戶端，為了阻止從觸發器返回結果，不要在觸發器定義中包含SELECT語句。同樣，也不能調用將數據返回客戶端的存儲過程。
 
2.   new.列名、old.列名用法
 
在MySQL觸發器中的SQL語句可以關聯表中的任意列。但不能直接使用列的名稱去標志，那會使系統混淆，因為激活觸發器的語句可能已經修改、刪除或添加了新的列名，而列的舊名同時存在。因此必須用這樣的語法來標志：“NEW.column_name”或者“OLD.column_name”。NEW.column_name用來引用新行的一列，OLD.column_name用來引用更新或刪除它之前的已有行的一列。
 
對于INSERT語句，只有NEW是合法的；對于DELETE語句，只有OLD才合法；而UPDATE語句可以與NEW或OLD同時使用。
 
例： 創建一個觸發器，當刪除表XS中某個學生的信息時，同時將XS_KC表中與該學生有關的數據全部刪除。  www.2cto.com  
 
DELIMITER $$
 
CREATE TRIGGERXS_DELETE AFTER DELETE
 
   ON XS FOR EACH ROW
 
BEGIN
 
   DELETE FROM XS_KC WHERE學號=OLD.學號;
 
END$$
 
DELIMITER ;
 
現在驗證一下觸發器的功能：DELETE FROM XS WHERE學號='081101';
 
使用SELECT語句查看XS_KC表中的情況：SELECT * FROM XS_KC;
 
注意：當觸發器涉及對觸發表自身的更新操作時，只能使用BEFORE，AFTER觸發器將不被允許。
 
3.刪除觸發器  www.2cto.com  
 
和其他數據庫對象一樣，使用DROP語句即可將觸發器從數據庫中刪除。語法格式：DROP TRIGGER[schema_name.]trigger_name
 
說明：trigger_name：指要刪除的觸發器名稱。schema_name為所在數據庫的名稱，如果在當前數據庫，可以省略。
 
例： 刪除觸發器XS_DELETE：   DROP TRIGGERXS_DELETE;
 

感謝你能夠認真閱讀完這篇文章，希望小編分享的“MySQL中存儲函數、觸發器的示例分析”這篇文章對大家有幫助，同時也希望大家多多支持億速云，關注億速云行業資訊頻道，更多相關知識等著你來學習!