MySQL 儲存程式之儲存程序(Stored Routines) by Stanley | CodeData
top
photo credit: Jason Cartwright via photopin cc

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 資料複製基礎介紹

分享:
按讚!加入 CodeData Facebook 粉絲群

留言

留言請先。還沒帳號註冊也可以使用FacebookGoogle+登錄留言

關於作者

曾任昇陽電腦教育訓練中心講師,授課範圍有Solaris / MySQL / Perl ... 等,對IT技術的興趣很廣泛,尤其熱衷在研究開源的技術。

熱門論壇文章

熱門技術文章