概述
儲存過程和自定義函式是事先經過編譯並儲存在資料庫中的一段SQL語句的集合。相對普通查詢優點：
- 可以簡化應用開發人員的工作,可重用。
- 減少資料庫與應用伺服器之間的資料傳輸。
- 提高了資料處理的效率。
- 安全性提高。由於儲存過程也可以使用許可權控制，而且引數化的儲存過程可以防止SQL隱碼攻擊，也在一定程度上保證了安全性。
儲存過程與函式的區別在於函式必須有返回值，而儲存過程沒有，儲存過程的引數可以使用in(輸入)，out(輸出)，inout(輸入輸出)，而函式的引數只能是in型別。
建立、修改、呼叫 儲存過程或函式
#建立儲存過程
create procedure sp_name([proc_paramenter[,...]])
[characteristic ...] routine_body
#proc_parameter: [in|out|inout] param_name type
#type:任何可用的MySQL資料型別
#routine_body:是SQL程式碼的內容，可以用BEGIN...END來表示SQL程式碼的開始和結束。
___________________________________________________
#建立函式
create function sp_name([func_parameter[,...]])
return type
[characteristic ...]routine_body
___________________________________________________
#修改儲存過程或函式
alter {procedure|function} sp_name [characteristic ...]
___________________________________________________
#呼叫過程的語句
call sp_name([parameter[,...]]);
___________________________________________________
#刪除儲存過程或函式
drop {procedure|function} [if exists] sp_name;
___________________________________________________
#檢視儲存過程或函式狀態
show {procedure|function} status like 'sp_name';
___________________________________________________
#檢視儲存過程或函式的定義
show create {procedure|function} sp_name;複製程式碼其中characteristic的取值為：
| 值 | 說明 |
|---|---|
| language sql | 說明routine_body部分是由SQL語句組成的，當前系統支援的語言為SQL |
| [not] deterministic | 指明儲存過程執行的結果是否確定。DETERMINISTIC 表示結果是確定的。每次執行儲存過程時，相同的輸入會得到相同的輸出。 |
| {contains sql \no sql \ reads sql data \modifies sql data} | 指明子程式使用SQL語句的限制。CONTAINS SQL表明子程式包含SQL語句，但是不包含讀寫資料的語句；NO SQL表明子程式不包含SQL語句；READS SQL DATA：說明子程式包含讀資料的語句；MODIFIES SQL DATA表明子程式包含寫資料的語句。預設情況下，系統會指定為CONTAINS SQL。 |
| sql_security{definer\invoker} | 指明誰有許可權來執行。DEFINER 表示只有定義者才能執行；INVOKER 表示擁有許可權的呼叫者可以執行。預設情況下，系統指定為DEFINER。 |
| comment 'string' | 註釋資訊，可以用來描述儲存過程或函式 |
MySQL的儲存過程或函式中允許包含DDL語句，也允許儲存過程中執行事務處理。儲存過程和函式中可以呼叫其他的過程或函式。
儲存過程及函式基本使用舉例
舉例說明：
delimiter //
create procedure procedure_test(
in v_min int,in v_max int,out num int)
reads sql data
begin
#查詢出test1表中sid在v_min 和v_max之間的記錄
select * from test1
where sid > v_min and sid < v_max;
#將返回的記錄行數寫入num變數中輸出
select found_rows() into num;
end //
delimiter ;複製程式碼注意：“DELIMITER //”語句的作用是將MYSQL的結束符設定為//，因為MYSQL預設的語句結束符為分號;，為了避免與儲存過程中SQL語句結束符相沖突，需要使用DELIMITER 改變儲存過程的結束符，並以“END //”結束儲存過程。
儲存過程定義完畢之後再使用DELIMITER ;恢復預設結束符。DELIMITER 也可以指定其他符號為結束符
建立的儲存過程及test1資料
呼叫儲存過程極其結果
輸出變數
刪除儲存過程
檢視儲存過程
檢視儲存過程定義
使用變數舉例
變數的作用範圍只能在begin...end塊中，可以用在巢狀中。變數的定義必須寫在複合語句的開頭，並且在任何其他語句的前面。可以一次宣告多個相同型別的變數。
(10)mysql中的變數-參考連結
#建立一個函式返回v_min、v_max之間的sid之和。
delimiter //
create function function_test
(v_min int,v_max int)
returns int
reads sql data
begin
declare temp int;
select sum(sid) into @temp
from test1
where sid > v_min and sid < v_max;
return @temp
end //
delimiter ;複製程式碼建立結果：
呼叫函式執行結果
使用條件定義、處理
條件定義和處理可以用來定義在處理過程中遇到問題（錯誤，警告，異常）時的處理步驟。條件處理程式定義參見連結如下：
(11)mysql中的條件定義、處理-參考連結
1. 未使用條件定義處理程式
delimiter $
create procedure pro_condition()
begin
set @x=1;
select * from test111;
set @x=2;
end$
delimiter ;複製程式碼結果
2. 使用了條件定義處理程式
drop procedure if exists pro_condition;
delimiter $
create procedure pro_condition()
begin
declare continue handler for 1146 set @x2=1;
set @x=1;
select * from test111;
set @x=2;
end$
delimiter ;複製程式碼結果：
使用游標
在儲存過程中可以使用游標對結果集進行迴圈處理。游標使用參看連結如下：
(12)mysql中的游標-參考連結
DELIMITER $
CREATE PROCEDURE pro_cursor()
BEGIN
DECLARE sid_i INT;
DECLARE cur_test CURSOR FOR SELECT sid FROM test1;
DECLARE EXIT HANDLER FOR NOT FOUND CLOSE cur_test;
SET @x1=0;
SET @x2=0;
OPEN cur_test;
REPEAT
FETCH cur_test INTO sid_i;
SET @x1=@x1+sid_i;
SET @x2=@x2+1;
UNTIL 0 END REPEAT;
CLOSE cur_test;
END $
DELIMITER ;
#檢視結果
call pro_cursor();
select @x1,@x2;複製程式碼test1表結構及資料
結果
使用流程控制
儲存過程和函式中可以使用流程控制來控制語句的執行。MySQL 中可以使用IF 語句、CASE 語句、LOOP語句、LEAVE 語句、ITERATE 語句、REPEAT 語句和WHILE 語句來進行流程控制。具體流程使用參見如下連結：
(13)mysql中的流程控制-參考連結
delimiter $
create procedure pro_flow_control(in sid_v int)
begin
if sid_v > 5 then
select * from test1 where sid>5;
else
select * from test1 where sid<=5;
end if;
end $
delimiter ;複製程式碼結果：