一、 簡介
存儲過程（Stored Procedure）是一組為了完成特定功能的SQL語句集，經編譯後存儲在數據庫中。用戶通過指定存儲過程的名字並給出參數（如果該存儲過程帶有參數）來執行它。存儲過程是數據庫中的一個重要對象，任何一個設計良好的數據庫應用程序都應該用到存儲過程。
雖然常用術語是存儲過程（stored procedure），但MySQL實際上實現了兩中類型，除了存儲過程外，還有存儲函數（stored routine）,它們統稱為存儲例程。
二、基本格式
1、存儲過程
CREATE PROCEDURE 過程名 ([過程參數[,...]])
[特性 ...] 過程體
如創建: CEATE PROCEDURE p1 (a INT)
SELECT a;
調用一下：CALL p1(8);
將顯示：
+------+
| a |
+------+
| 8 |
+------+
1 row in set (0.00 sec)
2、存儲函數
CREATE FUNCTION 函數名 ([函數參數[,...]])
RETURNS 返回類型
[特性 ...] 函數體
如創建：CREATE FUNCTION f1 (x INT)
RETURNS INT
RETURN x; /* 過程函數一次只能返回一個值
調用一下：SELECT f1 (3);
將顯示：
+-------+
| f1(3) |
+-------+
| 3 |
+-------+
1 row in set (0.00 sec)
3、過程參數
[ IN | OUT | INOUT ] 參數名 參數類型
4、函數參數
參數名 參數類型
5、返回類型
有效的MySQL數據類型即可
6、過程體/函數體格式如下
BEGIN
有效的SQL語句
END
7、特性（一般不要求）
LANGUAGE SQL
| [NOT] DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT 'string'
8、存儲過程中參數的IN,OUT,INOUT類型
存儲過程可以接受輸入參數，並把參數返回給調用方。不過，對於每個參數，需要聲明其參數名、數據類型，還要指定此參數是用於向過程傳遞信息，還是從過程傳回信息，或是二者兼有。
為聲明參數的作用，使用如下3個關鍵字之一：
˜ IN : IN參數只用來向過程傳遞信息，為默認值。
˜ OUT : OUT參數只用來從過程傳回信息。
˜ INOUT ： INOUT參數可以向過程傳遞信息，如果值改變，則可再從過程外調用。
如果僅僅想把數據傳給 MySQL 存儲過程，那就使用“in” 類型參數；如果僅僅從 MySQL 存儲過程返回值，那就使用“out” 類型參數；如果需要把數據傳給 MySQL 存儲過程，還要經過一些計算後再傳回給我們，此時，要使用“inout” 類型參數。
對於任何聲明為OUT或INOUT的參數，，當調用存儲過程時需要在參數名前加上@符號，這樣該參數就可以在過程外調用了。
下面舉三個實例：MySQL 存儲過程 “in” 參數：跟 C 語言的函數參數的值傳遞類似， MySQL 存儲過程內部可能會修改此參數，但對 in 類型參數的修改，對調用者（caller）來說是不可見的（not visible）。
create procedure pr_param_in( in id int -- in 類型的 MySQL 存儲過程參數)begin if (id is not null) then set id = id + 1; end if; select id as id_inner;end;set @id = 10; call pr_param_in(@id); select @id as id_out;mysql> call pr_param_in(@id);+----------+| id_inner |+----------+| 11 |+----------+ MySQL> select @id as id_out;+--------+| id_out |+--------+| 10 |+--------+可以看到：用戶變量 @id 傳入值為 10，執行存儲過程後，在過程內部值為：11（id_inner），但外部變量值依舊為：10（id_out）。
MySQL 存儲過程 “out” 參數：從存儲過程內部傳值給調用者。在存儲過程內部，該參數初始值為 null，無論調用者是否給存儲過程參數設置值。
create procedure pr_param_out( out id int)begin select id as id_inner_1; /* id 初始值為 null*/ if (id is not null) then set id = id + 1; select id as id_inner_2; else select 1 into id; end if; select id as id_inner_3;end;set @id = 10; call pr_param_out(@id); select @id as id_out;mysql> set @id = 10;mysql>mysql> call pr_param_out(@id); /*外部變量不能傳給存儲過程*/+------------+| id_inner_1 |+------------+| NULL |+------------+ +------------+| id_inner_3 |+------------+| 1 |+------------+ MySQL> select @id as id_out; /*過程將改變的值傳給外部變量*/+--------+| id_out |+--------+| 1 |+--------+可以看出，雖然我們設置了用戶定義變量 @id 為 10，傳遞 @id 給存儲過程後，在存儲過程內部，id 的初始值總是 null（id_inner_1）。最後 id 值（id_out = 1）傳回給調用者。
MySQL 存儲過程 inout 參數跟 out 類似，都可以從存儲過程內部傳值給調用者。不同的是：調用者還可以通過 inout 參數傳遞值給存儲過程。
drop procedure if exists pr_param_inout; create procedure pr_param_inout( inout id int)begin select id as id_inner_1; -- id 值為調用者傳進來的值 if (id is not null) then set id = id + 1; select id as id_inner_2; else select 1 into id; end if; select id as id_inner_3;end;set @id = 10; call pr_param_inout(@id); select @id as id_out;mysql> set @id = 10; mysql>mysql> call pr_param_inout(@id);+------------+| id_inner_1 |+------------+| 10 |+------------+ +------------+| id_inner_2 |+------------+| 11 |+------------+ +------------+| id_inner_3 |+------------+| 11 |+------------+mysql>mysql> select @id as id_out;+--------+| id_out |+--------+| 11 |+--------+從結果可以看出：我們把 @id（10），傳給存儲過程後，存儲過程最後又把計算結果值 11（id_inner_3）傳回給調用者。 MySQL 存儲過程 inout 參數的行為跟 C 語言函數中的引用傳值類似。
通過以上例子：如果僅僅想把數據傳給 MySQL 存儲過程，那就使用“in” 類型參數；如果僅僅從 MySQL 存儲過程返回值，那就使用“out” 類型參數；如果需要把數據傳給 MySQL 存儲過程，還要經過一些計算後再傳回給我們，此時，要使用“inout” 類型參數。
9、聲明和設置變量
（1） 聲明變量
在存儲例程使用局部變量之前，必須聲明局部變量，變量聲明通過DECLARE語句實現，其原型如下：
DECLARE variable_name TYPE ;
如：DECLARE x VARCHAR(254);
在聲明變量時，聲明必須放在BEGIN/END塊中。此外聲明必須在執行該塊任何其它語句之前進行。
（2） 設置變量
SET語句用來設置聲明的存儲例程變量值。其原型如下：
SET variable_name =value;
如下聲明和設置變量過程：
DECLARE x INT;
SET x = 155;
也可使用SELECT…..INTO語句設置變量。
如：
SELECT 155 INTO x;
當然，此變量是聲明該變量的BEGIN/END塊作用范圍內的一個局部變量。如果希望在存儲例程外使用此變量，需要將其作為OUT變量傳遞。
10、執行存儲例程
執行存儲例程一般是通過CALL和SELECT語句來完成的。
三、多語句存儲例程
單語句存儲例程非常有用，但存儲例程的真正功能在於它能夠封裝和執行多個SQL語句。
下面介紹創建多語句存儲例程時常用的語法。
1、BEGIN和END
當創建多語句存儲例程時，需要將語句包圍在BEGIN/END塊中。
此塊的原型如下：
BEGIN
statement 1;
statement 2;
……
statement N;
END
注意，塊中每條語句必須以分號結尾。
2、條件語句
˜ IF-ELSEIF-ELSE語句
和C語言中if語句相似。
其原型如下：
IF condition1 THEN
statement1;
ELSEIF condition2 THEN
Statement2;
…….
END IF
˜ CASE語句
需要比較一組可能的值時CASE語句很有用。也是一種條件判斷語句。
其原型如下：
CASE
WHEN condition1 THEN statement1;
WHEN condition2 THEN statement2;
………
END CASE;
3、迭代
有些任務需要能夠重復地執行一組語句。下面介紹能夠迭代執行和退出循環的各種方法。
˜ ITERATE語句
執行ITERATE語句將使嵌入該語句的LOOP、REPEAT或WHILE循環返回頂部，並在此執行。
其原型如下：
ITERATE label
˜ LEAVE語句
在得到變量的值或特定任務的結果後，可能希望通過LEAVE命令立即退出循環或BEGIN/END塊。
其原型如下：
LEAVE label
˜ LOOP語句
LOOP語句將不斷的迭代處理定義在其代碼塊中的一組語句，直到遇到LEAVE為止。
其原型如下：
[begin_label:] LOOP
Statement_list
END LOOP [end_label]
˜ REPEAT語句
REPEAT語句在操作上幾乎與WHILE相同，很想C語言中的DO….WHERE語句。
其原型如下：
REPEAT
Statement_list
UNTIL condition
END REPEAT
˜ WHILE語句
其原型如下：
WHILE condition DO
Statement_list
END WHILE
下面寫一個循環語句的存儲過程：
DELIMITER $$
DROP PROCEDURE IF EXISTS `yyw`.`p2` $$
CREATE PROCEDURE `yyw`.`p2` ()
BEGIN
declare v int;
set v=0;
LOOP_LABLE:loop
if v=3 then
set v=v+1;
ITERATE LOOP_LABLE;
end if;
insert into vs values(v); /*將循環值插入數據表vs中*/
set v=v+1;
if v>=5 then
leave LOOP_LABLE;
end if;
end loop;
END $$
DELIMITER ;
四、從另一個例程中調用例程
DELIMITER //
CREATE PROCEDURE p1()
BEGIN
Statement_list
END//
CREATE PROCEDURE p2()
BEGIN
Statement_list
END//
CREATE PROCEDURE p3()
BEGIN
CALL p1();
CALL p2();
END//
注意：直接采用MySQL的Administrator管理器編輯時，可以直接采用函數文本錄入；
但若在腳本中自動導入存儲過程或函數時，由於MySQL默認以";"為分隔符，則過程體的每一句都被MySQL以存儲過程編譯，則編譯過程會報錯；所以要事先用DELIMITER關鍵字申明當前段分隔符
用完了就把分隔符還原。 如下所示：
DELIMITER ＄＄
Stored Procedures and Functions
DELIMITER ;
五、刪除和查看存儲例程
1、刪除存儲例程
要刪除存儲例程，可以執行DROP語句。
其原型如下：
DROP (PROCEDURE|FUNCTION) P_name;
2、查看例程狀態
其原型如下：
SHOW (PROCEDURE|FUNCTION) STATUS LIKE ‘P_name’
如：
SHOW PROCEDURE STATUS LIKE ‘P3’\G；
注意: 使用\G選項以垂直格式顯示輸出信息。
3、查看例程的創建語法
通過SHOW CREATE語句可以查看創建特定例程所用的語法。
其原型如下;
SHOW CREATE (PROCEDURE|FUNCTION) Db_name.P_name
六、實例
一般在MySQL Query Browser中更方便的創建存儲過程及修改內容。
（1）簡單的加法運算
DELIMITER $$
DROP PROCEDURE IF EXISTS `yyw`.`p4` $$
CREATE DEFINER=`yang`@`10.10.19.161` PROCEDURE `p4`(a int,b int)
BEGIN
declare c int; /*聲明的變量
if a is null then /*IF語句
set a = 0;
end if;
if b is null then
set b = 0;
end if;
set c = a + b;
select c as sum; /*結果顯示c的值
END $$
DELIMITER ;
調用以下：CALL p4(3,4);
將顯示：
+------+
| sum |
+------+
| 7 |
+------+
1 row in set (0.00 sec)
（2）存儲過程中的循環語句、數據表數據的導入導出及SQL函數的使用
DELIMITER $$
DROP PROCEDURE IF EXISTS `yyw`.`p4` $$
CREATE DEFINER=`yang`@`10.10.19.161` PROCEDURE ‘pro_prime2’(in num int)
BEGIN
declare i,j,x,y int default 0; /*聲明變量並默認為0*/
select yywID into j from text1; /*從數據表txte1中字段yywID的值賦給變量j*/
select 'count ',j; /*顯示count字符和j的值*/
while i<num do /*while……do循環語句*/
set x=2;
pp1:while x<=sqrt(j) do /*調用內部函數SQRT用於求平方*/
if j%x=0 then /*if循環語句*/
set y=1;
leave pp1;
else
set x=x+1;
end if;
end while;
if y=1 then
set y=0;
else
set i=i+1;
insert into text2 values(j); /*將j的值插入數據表text2*/
end if;
set j=j+1; /*實現j的自增*/
end while;
END $$
DELIMITER ;
假如原先在數據庫中分別建立表text1和text2，text1中有一個字段初始值為3，text2為空；
下面執行一下此存儲過程：
MySQL> CALL pro_prime2(5);
+--------+------+
| count | j |
+--------+------+
| count | 3 |
+--------+------+
1 row in set (0.00 sec)
MySQL> select *from text2;
+-------+
| yywID |
+-------+
| 3 |
| 5 |
| 7 |
| 11 |
| 13 |
+-------+
5 rows in set (0.00 sec)
（3）用存儲過程實現計算數據庫中某個成績表總分及平均分，並且調用過程後
能夠自動顯示基本信息（如學號、姓名、總分、平均分等）。
首先在數據庫中建一個成績表（命名為chengjibiao）如下：
+-----+------+--------+-------+--------+
| NUM | Name | Enlish | Maths | Physis |
+-----+------+--------+-------+--------+
| 1 | 楊業 | 92 | 87 | 96 |
| 2 | 劍鋒 | 82 | 98 | 93 |
| 3 | 張美 | 96 | 86 | 94 |
| 4 | 張文 | 76 | 99 | 95 |
| 5 | 葉倩 | 97 | 86 | 88 |
| 6 | 方文 | 87 | 96 | 94 |
| 7 | 李麗 | 97 | 86 | 83 |
| 8 | 賈宇 | 67 | 89 | 77 |
| 9 | 王勃 | 89 | 67 | 75 |
| 10 | 劉三 | 85 | 78 | 95 |
+-----+------+--------+-------+--------+
‚ 用SQL語句寫存儲過程p1
DELIMITER $$
DROP PROCEDURE IF EXISTS `yyw`.`p1` $$
CREATE DEFINER=`yang`@`10.10.19.161` PROCEDURE `p1`(N int)
BEGIN
declare a int; /* 變量的聲明 */
declare b int;
declare c int;
declare d int;
declare e int;
declare f char(100);
declare g decimal(4,2);
set e=1;
create table zongping (NUM int,Name char(255),Enlish int,Maths int,Physis int,Total int,aver decimal( 4,2)); /* 建一個數據表以存放要顯示的內容*/
repeat /* 引進一個REPEAT循環，來計算每位學生總分及平均分*/
select Enlish,Maths,Physis,Name into a,b,c,f from chengjibiao where NUM=e;
/* 導出數據庫chengjibiao中的三門成績及姓名，並把它們分別賦給變量a,b,c,f;*/
set d=a+b+c; /*求和*/
set g=(a+b+c)/3; /*求平均分*/
insert into zongping (NUM,Name,Enlish,Maths,Physis,Total,aver) values (e,f,a,b,c,d,g);
/*將學號，姓名，三門成績，總分，平均分的數據插入新建的數據表zongping中
set e=e+1; /*該條件可結束循環*/
until e=N /*N是調用存儲過程時根據學生數目來設定的*/
end repeat;
select *from zongping; /* 以數據表的形式顯示運行結果*/
drop table zongping; /*顯示結果後刪除表，也可不刪*/
END $$
DELIMITER ;
l 調用存儲過程
CALL P1 (11); /* 因為原成績表中有10列數據 ，故設N=11，也可根據不同的成績表另設
顯示結果如下：
+------+------+--------+-------+--------+-------+
| Name | Enlish | Maths | Physis | Total | Aver |
+------+------+--------+-------+--------+-------+-------+
| 1 | 楊業 | 92 | 87 | 96 | 275 | 91.67 |
| 2 | 劍鋒 | 82 | 98 | 93 | 273 | 91.00 |
| 3 | 張美 | 96 | 86 | 94 | 276 | 92.00 |
| 4 | 姜文 | 76 | 99 | 95 | 270 | 90.00 |
| 5 | 葉倩 | 97 | 86 | 88 | 271 | 90.33 |
| 6 | 方文 | 87 | 96 | 94 | 277 | 92.33 |
| 7 | 李麗 | 97 | 86 | 83 | 266 | 88.67 |
| 8 | 賈宇 | 67 | 89 | 77 | 233 | 77.67 |
| 9 | 王勃 | 89 | 67 | 75 | 231 | 77.00 |
| 10 | 劉三 | 85 | 78 | 95 | 258 | 86.00 |
+------+------+--------+-------+--------+-------+-------+
