关于旅游网站策划书/国内最新新闻
MYSQL存儲過程
原文：http://hi.baidu.com/atfox/blog/item/2832a6d305e5710a3bf3cf80.html
一、 簡介
存儲過程(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)。
1) in 類型的 MySQL 存儲過程參數:
create procedure pr_param_in( in id int )
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> set @id = 10;
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)。
2) out 類型的 MySQL 存儲過程參數:
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)傳回給調用者。
3) inout 類型的 MySQL 存儲過程參數:
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> call pr_param_inout(@id);
+------------+
| id_inner_1 |
+------------+
| 10 |
+------------+
+------------+
| id_inner_2 |
+------------+
| 11 |
+------------+
+------------+
| id_inner_3 |
+------------+
| 11 |
+------------+
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、條件語句
1)IF-ELSEIF-ELSE語句
和C語言中if語句相似。
其原型如下：
IF condition1 THEN
statement1;
ELSEIF condition2 THEN
Statement2;
…….
END IF
2)CASE語句
需要比較一組可能的值時CASE語句很有用。也是一種條件判斷語句。
其原型如下：
CASE
WHEN condition1 THEN statement1;
WHEN condition2 THEN statement2;
………
END CASE;
3、迭代
有些任務需要能夠重複地執行一組語句。下面介紹能夠迭代執行和退出循環的各種方法。
1)ITERATE語句
執行ITERATE語句將使嵌入該語句的LOOP、REPEAT或WHILE循環返回頂部，並在此執行。
其原型如下：
ITERATE label
2)LEAVE語句
在得到變數的值或特定任務的結果後，可能希望通過LEAVE命令立即退出循環或BEGIN/END塊。
其原型如下：
LEAVE label
3)LOOP語句
LOOP語句將不斷的迭代處理定義在其代碼塊中的一組語句，直到遇到LEAVE為止。
其原型如下：
[begin_label:] LOOP
Statement_list
END LOOP [end_label]
4)REPEAT語句
REPEAT語句在操作上幾乎與WHILE相同，很想C語言中的DO….WHERE語句。
其原型如下：
REPEAT
Statement_list
UNTIL condition
END REPEAT
5)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
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 ;
調用存儲過程
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 |
+------+------+--------+-------+--------+-------+-------+