1. 存儲(chǔ)函數(shù)(自定義函數(shù))

• 自定義函數(shù)是一種對(duì)MySQL擴(kuò)展的途徑，其用法與內(nèi)置的函數(shù)相同。
• 定義函數(shù)的兩個(gè)必要條件：參數(shù)、返回值。函數(shù)可以返回任意類型的值，同樣可以接收這些類型的參數(shù)。

關(guān)于函數(shù)體：

• 函數(shù)體是由合法的SQL語句構(gòu)成。
• 函數(shù)體可以是簡單的SELECT或INSERT語句。
• 函數(shù)體如果為符合結(jié)構(gòu)則使用BEGIN....AND語句包裹。
• 復(fù)合結(jié)構(gòu)可以包含聲明、循環(huán)、控制結(jié)構(gòu)等等。

重點(diǎn)：自定義的函數(shù)不能重名，類似于定義了一個(gè)全局變量，變量名不能一致。

1.1 定義存儲(chǔ)函數(shù)

語法格式：

create function 函數(shù)名(參數(shù)列表)
returns type(返回值類型)
begin
    --SQL語句
end;

需求：定義一個(gè)存儲(chǔ)過程的函數(shù)，獲取滿足條件的總記錄條數(shù)

實(shí)現(xiàn)：

delimiter $
create function fun(countryId int)
returns int 
begin
    # 定義一個(gè)存儲(chǔ)總數(shù)據(jù)條數(shù)的變量
    declare cum int default 0;
    # 查詢等于傳遞參數(shù)的全部的數(shù)據(jù)數(shù)，然后將其賦值給定義的變量
 ? ?select count(*) into cum from city where country_id = countryId;
 ? ?# 返回結(jié)果值。存儲(chǔ)函數(shù)必須有返回值
 ? ?return cum;
end $

delimiter ;

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

語法格式：

select 函數(shù)名(參數(shù)列表);

注意：調(diào)用存儲(chǔ)過程的時(shí)候使用的是call關(guān)鍵字，但是在調(diào)用存儲(chǔ)函數(shù)的時(shí)候直接使用select即可，就和調(diào)用MySQL一個(gè)普通的聚合函數(shù)的方式一樣即可。

select fun(1);
# 這里和存儲(chǔ)過程一樣，調(diào)用的時(shí)候需要加小括號(hào)和參數(shù)，但是在刪除的時(shí)候指定函數(shù)名即可

1.3 刪除存儲(chǔ)函數(shù)

語法格式

drop [if exists] function fun;

2. 觸發(fā)器

2.1 觸發(fā)器介紹

• 觸發(fā)器是與表有關(guān)的數(shù)據(jù)庫對(duì)象，指在 insert/update/delete 之前或之后，觸發(fā)并執(zhí)行觸發(fā)器中定義的SQL集合。觸發(fā)器的這種特性可以協(xié)助應(yīng)用在數(shù)據(jù)庫端確保數(shù)據(jù)的完整性、日志記錄以及數(shù)據(jù)校驗(yàn)等操作。
• 只有增加、刪除、修改的時(shí)候才可以使用觸發(fā)器，查詢的時(shí)候不可以使用觸發(fā)器。
• 使用別名OLD和NEW來引用觸發(fā)器中發(fā)生變化的記錄內(nèi)容，這與其他的數(shù)據(jù)庫是相似對(duì)的。MySQL觸發(fā)器還只支持行級(jí)觸發(fā)，不支持語句級(jí)觸發(fā)。oracle支持行級(jí)和語句級(jí)觸發(fā)器都支持。
• OLD、NEW這兩個(gè)變量又叫做行記錄變量。可以通過這個(gè)兩個(gè)變量來獲取即將要操作的數(shù)據(jù)表中的數(shù)據(jù)。

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

語法格式：

create trigger(觸發(fā)器) trigger_name(觸發(fā)器名稱)
before/after insert/update/delete
on tab_name(表名)
[for each row](行級(jí)觸發(fā)器)
begin
    trigger_stmt;(觸發(fā)器的邏輯)
end;

需求:通過觸發(fā)器記錄 emp 表的數(shù)據(jù)變更日志 emp_logs ，其中包含增加、修改、刪除

實(shí)現(xiàn):

分析：一個(gè)觸發(fā)器只能操作一種數(shù)據(jù)的操作類型，不可以同時(shí)完成增加、修改、刪除的操作。所以此時(shí)需要定義多個(gè)觸發(fā)器來完成這個(gè)日志記錄的任務(wù)。

因?yàn)?MySQL中是行級(jí)操作的觸發(fā)器，所以 new 以及 old 中存儲(chǔ)的都是一整行數(shù)據(jù)。

創(chuàng)建執(zhí)行 insert 的觸發(fā)器:

• 使用 new 關(guān)鍵字可以獲取到操作的數(shù)據(jù)，在insert模式下，new變量中存儲(chǔ)的就是即將插入的數(shù)據(jù)
• 使用的是 after ，在執(zhí)行完表 emp 的新增之后執(zhí)行這個(gè)觸發(fā)器記錄日志。
• 這個(gè)觸發(fā)器什么時(shí)候執(zhí)行與兩點(diǎn)有關(guān)：
  • 必須操作的是 emp 這個(gè)表，也就是on后面聲明的這個(gè)表。
  • 必須執(zhí)行的 insert 操作。

創(chuàng)建執(zhí)行 update 的觸發(fā)器:

此時(shí) old 變量中存儲(chǔ)的是被修改前的數(shù)據(jù)，new 變量中存儲(chǔ)的是修改之后的數(shù)據(jù)

創(chuàng)建執(zhí)行 delete 的觸發(fā)器:

此時(shí)的 old 變量中存儲(chǔ)的即將刪除的數(shù)據(jù)

測(cè)試：測(cè)試都必須是操作的 emp 表，這樣才會(huì)觸發(fā)上邊定義的觸發(fā)器。

2.3 刪除觸發(fā)器

語法結(jié)構(gòu)：

drop trigger [schema_name.](數(shù)據(jù)庫名)trigger_name(觸發(fā)器名);

如果沒有指定 schema_name(數(shù)據(jù)庫名)，默認(rèn)為當(dāng)前數(shù)據(jù)庫。

2.4 查看觸發(fā)器

可以通過執(zhí)行 show triggers 命令查看觸發(fā)器的狀態(tài)、語法等信息。

語法結(jié)構(gòu)：

show triggers;

到此這篇關(guān)于MySQL自定義函數(shù)以及觸發(fā)器的文章就介紹到這了,更多相關(guān)MySQL 觸發(fā)器內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論