MySQL自定義函數及觸發器

2022-08-04 14:03:06

1. 儲存函數(自定義函數)

自定義函數是一種對MySQL擴充套件的途徑，其用法與內建的函數相同。
定義函數的兩個必要條件：引數、返回值。函數可以返回任意型別的值，同樣可以接收這些型別的引數。

關於函數體：

函數體是由合法的SQL語句構成。
函數體可以是簡單的SELECT或INSERT語句。
函數體如果為符合結構則使用BEGIN....AND語句包裹。
複合結構可以包含宣告、迴圈、控制結構等等。

重點：自定義的函數不能重名，類似於定義了一個全域性變數，變數名不能一致。

1.1 定義儲存函數

語法格式：

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

需求：定義一個儲存過程的函數，獲取滿足條件的總記錄條數

實現：

delimiter $
create function fun(countryId int)
returns int 
begin
    # 定義一個儲存總資料條數的變數
    declare cum int default 0;
    # 查詢等於傳遞引數的全部的資料數，然後將其賦值給定義的變數
    select count(*) into cum from city where country_id = countryId;
    # 返回結果值。儲存函數必須有返回值
    return cum;
end $

delimiter ;

1.2 呼叫儲存函數

語法格式：

select 函數名(參數列);

注意：呼叫儲存過程的時候使用的是call關鍵字，但是在呼叫儲存函數的時候直接使用select即可，就和呼叫MySQL一個普通的聚合函數的方式一樣即可。

select fun(1);
# 這裡和儲存過程一樣，呼叫的時候需要加小括號和引數，但是在刪除的時候指定函數名即可

1.3 刪除儲存函數

語法格式

drop [if exists] function fun;

2. 觸發器

2.1 觸發器介紹

觸發器是與表有關的資料庫物件，指在 insert/update/delete 之前或之後，觸發並執行觸發器中定義的SQL集合。觸發器的這種特性可以協助應用在資料庫端確保資料的完整性、紀錄檔記錄以及資料校驗等操作。
只有增加、刪除、修改的時候才可以使用觸發器，查詢的時候不可以使用觸發器。
使用別名OLD和NEW來參照觸發器中發生變化的記錄內容，這與其他的資料庫是相似對的。MySQL觸發器還只支援行級觸發，不支援語句級觸發。oracle支援行級和語句級觸發器都支援。
OLD、NEW這兩個變數又叫做行記錄變數。可以通過這個兩個變數來獲取即將要操作的資料表中的資料。

2.2 建立觸發器

語法格式：

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

需求:通過觸發器記錄 emp 表的資料變更紀錄檔 emp_logs ，其中包含增加、修改、刪除

實現:

分析：一個觸發器只能操作一種資料的操作型別，不可以同時完成增加、修改、刪除的操作。所以此時需要定義多個觸發器來完成這個紀錄檔記錄的任務。

因為 MySQL中是行級操作的觸發器，所以 new 以及 old 中儲存的都是一整行資料。

建立執行 insert 的觸發器:

使用 new 關鍵字可以獲取到操作的資料，在insert模式下，new變數中儲存的就是即將插入的資料
使用的是 after ，在執行完表 emp 的新增之後執行這個觸發器記錄紀錄檔。
這個觸發器什麼時候執行與兩點有關：
- 必須操作的是 emp 這個表，也就是on後面宣告的這個表。
- 必須執行的 insert 操作。

建立執行 update 的觸發器:

此時 old 變數中儲存的是被修改前的資料，new 變數中儲存的是修改之後的資料

建立執行 delete 的觸發器:

此時的 old 變數中儲存的即將刪除的資料

測試：測試都必須是操作的 emp 表，這樣才會觸發上邊定義的觸發器。

2.3 刪除觸發器

語法結構：

drop trigger [schema_name.](資料庫名)trigger_name(觸發器名);

如果沒有指定 schema_name(資料庫名)，預設為當前資料庫。

2.4 檢視觸發器

可以通過執行 show triggers 命令檢視觸發器的狀態、語法等資訊。

語法結構：

show triggers;

到此這篇關於MySQL自定義函數以及觸發器的文章就介紹到這了,更多相關MySQL 觸發器內容請搜尋it145.com以前的文章或繼續瀏覽下面的相關文章希望大家以後多多支援it145.com！

MySQL自定義函數及觸發器

目錄

1. 儲存函數(自定義函數)

1.1 定義儲存函數

1.2 呼叫儲存函數

1.3 刪除儲存函數

2. 觸發器

2.1 觸發器介紹

2.2 建立觸發器

2.3 刪除觸發器

2.4 檢視觸發器

熱門文章