預存程序、函數、觸發器

標籤：

觸發器原理：
          觸發器與預存程序非常相似，觸發器也是SQL語句集，兩者唯一的區別是觸發器不能用EXECUTE語句調用，而是在使用者執行Transact-SQL語句時自動觸發（啟用）執行。觸發器是在一個修改了指定表中的資料時執行的預存程序。通常通過建立觸發器來強制實現不同表中的邏輯相關資料的參考完整性和一致性。由於使用者不能繞過觸發器，所以可以用它來強制實施複雜的商務規則，以確保資料的完整性。觸發器不同於預存程序，觸發器主要是通過事件執行觸發而被執行的，而預存程序可以通過預存程序名稱名字而直接調用。當對某一表進行諸如UPDATE、INSERT、DELETE這些操作時，SQLSERVER就會自動執行觸發器所定義的SQL語句，從而確保對資料的處理必須符合這些SQL語句所定義的規則。
觸發器的作用：
觸發器的主要作用是其能夠實現由主鍵和外鍵所不能保證的複雜的參照完整性和資料的一致性。它能夠對資料庫中的相關表進行級聯修改，強制比CHECK約束更複雜的資料完整性，並自訂動作訊息，維護非正常化資料以及比較資料修改前後的狀態。與CHECK約束不同，觸發器可以引用其它表中的列。在下列情況下使用觸發器實現複雜的參考完整性；強制資料間的完整性。建立多行觸發器，當插入，更新、刪除多行資料時，必須編寫一個處理多行資料的觸發器。執行串聯更新或串聯刪除這樣的動作。級聯修改資料庫中所有相關表。撤銷或者復原違反參考完整性的操作，防止非法修改資料。
觸發器與預存程序的區別：
         觸發器與預存程序的主要區別在於觸發器的運行方式。預存程序必須有使用者、應用程式或者觸發器來顯示的調用並執行，而觸發器是當特定時間出現的時候，自動執行或者啟用的，與串連用資料庫中的使用者、或者應用程式無關。當一行被插入、更新或者刪除時觸發器才執行，同時還取決於觸發器是怎樣建立的，當UPDATE發生時使用一個更新觸發器，當INSERT發生時使用一個插入觸發器，當DELETE發生時使用一個刪除觸發器。

 

預存程序 
是 SQL 陳述式和可選控制流程語句的先行編譯集合，以一個名稱儲存並作為一個單元處理。預存程序儲存在資料庫內，可由應用程式通過一個調用執行，而且允許使用者聲明變數、有條件執行以及其它強大的編程功能。 
預存程序可包含程式流、邏輯以及對資料庫的查詢。它們可以接受參數、輸出參數、返回單個或多個結果集以及傳回值。 
可以出於任何使用 SQL 陳述式的目的來使用預存程序，它具有以下優點： 
可以在單個預存程序中執行一系列 SQL 陳述式。 
可以從自己的預存程序內引用其它預存程序，這可以簡化一系列複雜語句。 
預存程序在建立時即在伺服器上進行編譯，所以執行起來比單個 SQL 陳述式快。 

觸發器 
是一種特殊類型的預存程序，當使用下面的一種或多種資料修改操作在指定表中對資料進行修改時，觸發器會生效：UPDATE、INSERT 或 DELETE。觸發器可以查詢其它表，而且可以包含複雜的 SQL 陳述式。它們主要用於強制複雜的商務規則或要求。例如，可以控制是否允許基於顧客的當前帳戶狀態插入定單。 
觸發器還有助於強制參考完整性，以便在添加、更新或刪除表中的行時保留表之間已定義的關係。然而，強制參考完整性的最好方法是在相關表中定義主鍵和外鍵約束。如果使用資料庫圖表，則可以在表之間建立關係以自動建立外鍵約束。有關詳細資料，請參見表關係。 
使用觸發器的優點 
觸發器的優點如下： 
觸發器是自動的：它們在對錶的資料作了任何修改（比如手工輸入或者應用程式採取的操作）之後立即被啟用。 
觸發器可以通過資料庫中的相關表進行層疊更改。例如，可以在 titles 表的 title_id 列上寫入一個刪除觸發器，以使其它表中的各匹配行採取刪除操作。該觸發器用 title_id 列作為唯一鍵，在 titleauthor、sales 及 roysched 表中對各匹配行進行定位。 
觸發器可以強制限制，這些限制比用 CHECK 條件約束所定義的更複雜。與 CHECK 條件約束不同的是，觸發器可以引用其它表中的列。例如，觸發器可以復原試圖對價格低於 10 美元的書（儲存在 titles 表中）應用折扣（儲存在 discounts 表中）的更新。

 

以下的文章主要是介紹MySQL 觸發器，預存程序以及函數與視圖的執行個體示範過程，以下就是觸發器，預存程序以及函數與視圖的具體操作方案的描述，希望在你今後的學習中會對你有所協助。

MySQL 觸發器，預存程序以及函數與視圖的執行個體示範：

0.test資料庫有userinfo使用者資訊表 和userinfolog使用者資訊日誌表

 

1.建立一個userinfo表新增記錄時的MySQL 觸發器 將新增日誌加入到userinfolog

 

2.建立一個向userinfo表新增記錄的預存程序

 

3.根據userinfo表的出生日期欄位 我們將建立一個簡單算得年齡的自訂函數

 

4.建立一個userinfo的視圖 調用年齡函數

 

 

0.準備相關表

 

1. MySQL> use test;  
2. MySQL> create table userinfo(userid int,username varchar(10),userbirthday date);  
3. MySQL> create table userinfolog(logtime datetime,loginfo varchar(100));  
4. MySQL> describe userinfo; 

 

1.MySQL 觸發器

 

1. MySQL> delimiter |  
2. MySQL> create trigger beforeinsertuserinfo  
3. -> before insert on userinfo  
4. -> for each row begin  
5. -> insert into userinfolog values(now(),CONCAT(new.userid,new.username));  
6. -> end;  
7. -> |  
8. MySQL> delimiter ;  
9. MySQL> show triggers; 

 

2.預存程序

 

1. MySQL> delimiter //  
2. MySQL> create procedure spinsertuserinfo(  
3. -> puserid int,pusername varchar(10)  
4. -> ,puserbirthday date  
5. -> )  
6. -> begin  
7. -> insert into userinfo values(puserid,pusername,puserbirthday);  
8. -> end;  
9. -> //  
10. MySQL> show procedure status like ‘spinsertuserinfo‘;  
11. MySQL> call spinsertuserinfo(1,‘zhangsan‘,current_date);  
12. MySQL> select * from userinfo; 

3.自訂函數

1. MySQL> update userinfo  
2. -> set userbirthday=‘2000.01.01‘ 
3. -> where userid=‘1‘;  
4. MySQL> drop function if exists fngetage;  
5. MySQL> delimiter //  
6. MySQL> create function fngetage(pbirthday date)  
7. -> returns integer  
8. -> begin  
9. -> return year(now()) - year(pbirthday);  
10. -> end  
11. -> // 

 

4.視圖

 

1. MySQL> create view viewuserinfo  
2. -> as select * ,fngetage(userbirthday) as userage from userinfo;  
3. MySQL> select * from viewuserinfo; 

 

清除日誌記錄

 

1. MySQL> truncate table userinfolog;  
2. MySQL> delete from userinfolog; 

以上的相關內容就是對MySQL 觸發器 預存程序 函數 視圖的介紹，望你能有所收穫。

 

比較項目	預存程序	函數
是否有傳回值	可以有，也可以沒有	必須有且只有一個
是否可以單獨執行	可以	必須通過execute執行
SQL語句（DML或SELECT）可否調用	不可以	可以，且可以位於FROM關鍵字的後面（由於可以返回表對象）
參數類型	可以使用IN、OUT、IN OUT三種模式的參數	只有IN，Oracle可以使用IN、OUT、IN OUT三種參數
傳回值類型	可以通過OUT、IN OUT參數返回零個或多個參數值	單一值或一個表對象

1）一般來說，預存程序實現的功能要複雜一點，而函數實現的功能針對性比較強。 
2）預存程序一般是作為一個獨立的部分來執行（參照下面“預存程序-調用方法”），而函數可以作為查詢語句的一個部分來調用（用在select後面，或者from後面）。由於函數可以返回一個表對象，因此它可以在查詢語句中位於FROM關鍵字的後面。

預存程序、函數、觸發器