MySQL如何自訂函數及觸發器

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 的觸發器:

• #在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如何自訂函數及觸發器的詳細內容。更多資訊請關注PHP中文網其他相關文章！