MySQL 儲存程式之儲存程序（Stored Routines）

MySQL 效能調校 << 前情

講到資料庫的後端運用，效能調校等議題時，儲存程式一定會常常被拿來討論。當然，在MySQL中，也是支援儲存程式的。

儲存程式包括下面三類：

• 儲存程序（Stored Routines）
• 觸發器（Triggers）
• 事件（Events）

其中的儲存程序又可以再區分成兩種：

• 儲存步驟（Stored Procedure）
• 儲存函式（Stored Function）

接下來的幾周，我會依序作一個完整的說明。

首先，我要先介紹儲存步驟。如果，你對MySQL的儲存步驟很陌生，你可以先用mysql用戶端程式連線到MySQL伺服器中，並用”help create procedure”取得儲存步驟的線上說明文件。

$ mysql -u root -p localhost;

執行指令之後你會發現，文件的可分為兩個部份。第一個部份是創建儲存步驟的語法（之後會在作深入的介紹），第二個部份是詳細的說明。如果你對儲存步驟有一定程度的了解（或是有寫過其他資料庫的儲存步驟），你應該可以直接參考語法便可創建自己的儲存步驟。但如果你是一個初學者，讓我會建議你先瀏覽第二部份的細節介紹，這樣你就會更容易了解語法上的意義與用法。

接下來我要示範，如何在MySQL用戶端的環境中，創建一個儲存步驟。在這裡，我選擇使用MySQL的內建用戶端程式”mysql”來作示範。因為它算是最容易取得，也最常被使用的用戶端程式。流程如下：

1. 登入MySQL伺服器：

$ mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
...
mysql>

2. 刪除已存在的儲存步驟。

mysql> drop procedure if exists test_in_and_out; -- 如果儲存步驟存在則刪除它。

3. 變更執行符號：

mysql> delimiter // -- 將MySQL的執行符號從分號(;)轉換為兩個斜線(//)。

一般在MySQL伺服器中，delimiter為”分號”，所以，在儲存步驟中會用分號作為指令的結尾。但，為了在創建的過程中，不被mysql用戶端誤判，而將未完成儲存步驟送到伺服器端。所以，我們便將用戶端的執行符號從分號轉換為兩個斜線(//)。

4. 建立一個儲存步驟。

mysql> create procedure test_in_and_out(in a int, out b int, inout c int)
-> begin
-> set a = 1;
-> set c = c * 2;
-> end// -- 創建儲存步驟。
Query OK, 0 rows affected (0.01 sec)

5. 回復預設執行符號：

mysql> delimiter ; -- 再次將MySQL的執行符號從兩個斜線(//)轉換為分號(;)。

6. 測試儲存步驟：

6-1. 設定變數：

mysql> set @x = 0, @y = 2, @z = 4; -- 設定x, y, z三個變數的值為0, 2, 4。
Query OK, 0 rows affected (0.00 sec)

6-2. 呼叫儲存步驟：

mysql> call test_in_and_out(@x, @y, @z); -- 使用”call”語法，呼叫存在的儲存步驟。
Query OK, 0 rows affected (0.00 sec)

6-3. 檢視結果：

mysql> select @x, @y, @z; -- 檢視x, y, z 三個環境變數值。
+------+------+------+
| @x | @y | @z |
+------+------+------+
| 0 | NULL | 8 |
+------+------+------+
1 row in set (0.00 sec)

為何會出現這樣的結果呢？之後我會做詳細的介紹。

接下來我們就來詳細介紹儲存步驟的語法。

語法：

CREATE
[DEFINER = { user | CURRENT_USER }]
PROCEDURE sp_name ([proc_parameter[,...]])
[characteristic ...] routine_body

其中的”DEFINEER”是定義誰創建了此儲存步驟，預設是CURRENT_USER，也就是下CREATE指令的使用者。且在預設的情況下，MySQL會將ALTER ROUTINE和EXECUTE的權限給創建者。

參數：

[ IN | OUT | INOUT ] param_name type

IN代表傳入參數，也就是參數值只進不出。所以在上述的儲存步驟（test_in_and_out）範例中，雖然在步驟中，我們將變數a設為1，因為參數a只進不出，所以，最後我們查詢出a參數的值仍為在執行儲存步驟前，指定給a的0（而不是1）。

OUT代表傳出參數，也就是參數值只出不進。所以在上述的儲存步驟範例中，我們雖然先將參數b設為2後再執行儲存步驟，但是參數只出不進，所以，參數b在步驟初始化的階段，就被設為NULL，加上我們在步驟裡，並未再指定值給參數b。所以，最後儲存步驟將NULL傳出來給外部的參數b，這也就是為何最後我們查詢參數b時，其值為NULL。

INOUT代表傳入傳出參數，也就是參數傳進也傳出。所以c的傳入值是4，在儲存步驟我們又將c參數設定為 c * 2（也就是4 * 2 = 8），最後我們查詢參數c的值，就等於8。

屬性：

COMMENT 'string'
| LANGUAGE SQL
| [NOT] DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }

DETERMINISTIC的定義為，在傳入相同的參數時，會有相同的結果。預設為”NOT DETERMINISTIC”。如果錯將”NOT DETERMINISTIC”的執行步驟設為”DETERMINISTIC”時，MySQL優化器會產生不正確的執行計畫而導致非預期的結果。

“CONTAINS SQL/NO SQL/READS SQL DATA/MODIFIES SQL DATA”只是用來諮詢，伺服器不會用這些資訊來限制使用者權限。

SQL SECURITY將決定，在執行這個儲存步驟時，會採用什麼身份來執行。預設值是DEFINER。而執行的使用者，必須要有”EXECUTE”的權限。

儲存程序主體：

必須為有效的SQL程序語法。

接下來，我們要介紹變數宣告及游標(cursor)的使用方法。預先準備的表格及建立儲存步驟的指令如下。

mysql> create database if not exits demo;
mysql> use demo;
mysql> create table if not exists target(id int, name varchar(20));
mysql> insert into target values (1, 'Stanley'), (2, 'Christy'), (3, 'Joseph'), (4, 'Chantelle');
mysql> create table if not exists my_target(id int, name varchar(20));
mysql> drop procedure if exists copyTarget2Mytarget;
mysql> delimiter //
mysql> create procedure copyTarget2Mytarget()
-> begin
-> declare i int;
-> declare count int;
-> declare max_count int default 10;
-> declare n varchar(20) default '';
-> declare done int default 0;
-> declare not_found condition for sqlstate '02000';
-> declare c cursor for select * from demo.target;
-> declare continue handler for not_found set done=1;
-> open c;
-> repeat
-> fetch c into i, n;
-> if done != 1
-> then
-> set count=1;
-> while(count<=max_count)
-> do
-> insert into demo.my_target values (i, n);
-> set count=count+1;
-> end while;
-> end if;
-> until done = 1 end repeat;
-> close c;
-> end//
mysql> delimiter ;
mysql> call copyTarget2Mytarget();

結果：

mysql> select * from my_target;
+------+-----------+
| id | name |
+------+-----------+
| 1 | Stanley |
| 1 | Stanley |
…
| 4 | Chantelle |
| 4 | Chantelle |
+------+-----------+
40 rows in set (0.00 sec)
mysql>

宣告儲存步驟時，會使用”begin”和”end”來定義步驟主體。

例如：

begin
...
end

在使用儲存步驟的宣告時，順序是不可以任意變動的，而在宣告游標時，常常會伴隨宣告條件（condition）(非必要)和處理器(handler)，其順序如下：

1. 宣告變數。

變數宣告的語法為：delcare [變數名] [型別] default [預設值];

例如：

declare max_count int default 10;
declare n varchar(20) default '';

2. 宣告條件(condition)。

條件宣告的語法為：declare [條件名] condition for sqlstate [錯誤代碼];

例如：

declare not_found condition for sqlstate '02000';

3. 宣告游標(cursor)。

游標宣告的語法為：declare [游標名] cursor for [sql 語句];

例如：

declare c cursor for select * from demo.target;

4. 宣告處理器(handler)。

處理器宣告的語法為：declare [處理器名] handler for [條件名] [sql 語句];

例如：

declare continue handler for not_found set done=1;

當步驟中想開啟游標時，使用”open”加上游標名來開始游標。當游標被開始時，才會對伺服器發動交易。

例如：

open c;

而我們要如何將表格內的資料擷取出來，我們可以用”fetch”指令將資料列的資料指定給變數來達到此目的。

例如：

fetch c into i, n;

如何連續瀏覽表格，通常我們會用”repeat”迴圈來達到此目的。

例如：

repeat
...
until [條件] end repest;

此如，你就可以完全的控制資料表的資料內容來做資料處理。

至於創建儲存函式，語法如下：

CREATE
[DEFINER = { user | CURRENT_USER }]
FUNCTION sp_name ([func_parameter[,...]])
RETURNS type
[characteristic ...] routine_body

與儲存步驟的差異是，函式必須指定回傳型別，且在函式的主體中，必須要有回傳值。

例如：

retun 1;

函式參數：

param_name type

在儲存函式中，參數只需要型別即可（可是為儲存步驟的”IN”，只進不出）。

結論：

資料庫管理師可以藉由儲存程序常商業邏輯包裝起來，除了可以有安全控管的功能外，也可以簡化開發，方便程式攥寫。此外，有些複雜的邏輯與資料處理，可以先在資料庫端預先處理，就可以提昇整體效能。所以，儲存程序的攥寫，是資料庫管理師必須熟識的技能。

後續 >> MySQL 資料複製基礎介紹