stored procedure

Stored procedure
http://dev.mysql.com/doc/refman/5.0/en/stored-procedures.html

什麼是stored procedure
stored procedure是由一群sql敘述及流程控制的語法組成，本質上就是mysql中的程式語言，只是stored procedure的程式語法，跟php的不一樣，能使用的環境只限於mysql資料庫中，專為處理資料庫的操作，為了處理資料庫中的資料，有一些語法與php這種泛用型的程式語言，語法方面就稍微有些不同。就像戰機有雖然都有戰鬥功能，但為了不同的目的，有不同設計，以發揮團隊作戰的整體力量，有的戰機是專用對付入侵的戰機，是屬於空優型戰機，有的戰機，武裝不強，但體型龐大，有匿蹤功能，可以攜帶大量彈藥轟炸敵軍，屬於轟炸機。
mysql的stored procedure的語法遵循SQL:2003的語法標準，IBM的DB2也一樣遵循這套標準，遵循相同的標準有相當明顯的好處，如果要轉換資料庫，stored procedure不同重新撰寫，除錯，可以輕易的進行轉換，不會因為換了資料庫，必須重頭學習另一種語法，所有的基礎重頭來過。
使用stored procedure可以減少php程式碼的數量並增加程式的效能，php程式中只要呼叫stored procedure，stored procedure便會執行stored procedure中所有的sql敘述，並將結果傳回php程式中，php不用再一一呼叫sql statement，執行各自獨立的sql statement，再一一判斷結果，進行處理，這些sql statement及判斷統統都包裝在stored procedure裡頭，php及mysql之間傳遞的資料數量大幅減少，由於php與mysql之間等待資料傳送的時間及次數減少，可以加快程式的速度，也由於只要呼叫stored procedure，而不用呼叫各自獨立的sql statement，也減少了程式碼的數量。
目前，stored procedure不支援遞迴呼叫，如果要使用遞迴呼叫，必須將mysql變數：max_sp_recursion_depth 設定為非零的數字，預設是零，不允許遞迴，設為1，允許遞迴一次，設為2，允許遞迴二次，以此類推。

stored procedure的管理
stored procedure都存在mysql資料庫中的proc資料表中(mysql.proc)，這個資料表儲存所有的stroed procedure，這些資料透過管理工具，可以運作正常，但如果手動修改，就有可能發生不可預期的後果，絕對不要手動修改這些資料，而是要透過create procedure，alter proceudre或是drop procedure來進行管理。

要建立stored procedure就必須有create routine的權限

要修改stored procedure就必須有alter routine的權限

要執行stored procedure就必須有execute的權限

有create routine的權限就自動會有alter routine及execute的權限

建立stored procedure的語法
create [definer={usercurrent_user}] procedure sp_name([sp_parameter],[sp_parameter]...)
[characteristic]
stored procedure body
[characteristic]
language sql
目前沒有作用
[not] deterministic
如果沒設，預設為 not deterministic
{ CONTAINS SQL NO SQL READS SQL DATA MODIFIES SQL DATA }
目前沒有強制性，mysql只會當做註解
sql security {definerinvoker}
預設為definer
comment '註解內容'

修改
alter procedure sp_name ...

刪除
drop procedure sp_name;

執行stored procedure
call 預存程序名稱(參數);
以實際範例操作會比較清楚。
/**
*建立store procedure，名稱為sp_test
*/
delimiter //
create definer=current_user procedure sp_test(out version varchar(60),out day varchar(60))
language sql
not deterministic
contains sql
sql security definer
begin
select version() into version;
select curdate() into day;
end;
//
delimiter ;
/**
*執行stored procedure，stored procedure的名稱為sp_test
*/
call sp_test(@version,@day);
select @version,@day;

變數的處理
變數宣告
declare i int default 1;
變數的設定
set i=3;
select 4*5 into i;

例外處理
例外訊息列表
http://dev.mysql.com/doc/refman/5.0/en/error-messages-server.html
看下面的實例比較快
delimiter //
create procedure sp_h()
begin
declare no_table condition for 1146;
declare continue handler for no_table
begin
select 'hi!! world!';
end;
select * from a;
end
//
delimiter ;

cursor
只能讀
只能單向移動，不能跳過任一列
create procedure test()
begin
declare var_name varchar(30);
declare cur1 cursor for select name from a;
declare exit handler for sqlstate '02000' begin end;
open cur1;
repeat
fetch cur1 into var_name;
select var_name;
until 0 end repeat;
close cur1;
end;

宣告的順序
變數及condition
cursor
handler

流程控制
mysql的stored procedure流程控制語法跟一般的程式語言相同，有迴圈及判斷式的二類。迴圈及判斷式的用法與php的用法相同，只是語法不一樣，寫幾個具體範例熟悉一下迴圈及判斷式的語法會比較較快進入狀況，manual上的說明，範例太少了，自己出幾個題目，動手寫一些小程式會比較快熟悉stored procedure的迴圈判斷式的語法。
Mysql的stored procedure的iterate及leave是用在迴圈的語法，iterate的功能與php中的continue相同，leave就跟php中的break相同，都是控制迴圈進行的語法，讓程式能依照判斷式跳出迴圈或繼續重頭執行迴圈中的程式碼。
底下是使用stored procedure的語法寫的簡易程式，最好是使用記本先打好，再匯入mysql或是使用mysql的圖形化介面的管理工具，如navicat、Mysql Administrator，進行stored procedure的撰寫，才會比較快速有效率，不然是很難進行除錯的。工欲善其事，必先善其器，好的工具及方法，可以加快你進入stored procedure世界的速度。
程式很簡單，只要有程式基礎，看一下manual就知道程式是怎麼運作，但學習方法比較容易被忽略，如果只是看看過去，沒有實際撰寫的話，不容易深入了解stored procedure的運作。最好自己出題，實際寫幾個stored procedure，搭配manual上的說明，才能對stored procedure有完整的認識。
以loop迴圈寫的九九乘法表
delimiter //
create procedure sp_a()
begin
declare i int default 1;
declare j int default 1;
label_a: loop
if i<=9 then
set j=1;
label_b: loop
if j<=9 then
select i,j,i*j;
set j=j+1;
iterate label_b;
end if;
leave label_b;
end loop label_b;
set i=i+1;
iterate label_a;
end if;
leave label_a;
end loop label_a;
end;
//
delimiter ;

以repeat迴圈寫的九九乘法表
delimiter //
create procedure sp_b()
begin
declare i int default 1;
declare j int default 1;
repeat
set j=1;
repeat
select i,j,i*j;
set j=j+1;
until j>9 end repeat;
set i=i+1;
until i>9 end repeat;
end;
//
delimiter ;

以while迴圈寫的九九乘法表
delimiter //
create procedure sp_c()
begin
declare i int default 1;
declare j int default 1;
while i<=9 do
set j=1;
while j<=9 do
select i,j,i*j;
set j=j+1;
end while;
set i=i+1;
end while;
end;
//
delimiter ;

if判斷式的使用
delimiter //
create procedure sp_d(in age int)
begin
if age<18 then
select 'you are young man';
elseif age>=18 and age<40 then
select 'you have many life experiences';
else
select 'you ard old man';
end if;
end;
//
delimiter ;

case判斷式的使用
delimiter //
create procedure sp_e(in selector int)
begin
case selector
when 1 then
select 'yellow';
when 2 then
select 'black';
when 3 then
select 'red';
else
select 'no color selected';
end case;
end;
//
delimiter ;