簡單地說，存儲過程就是一條或者多條SQL語句的組合，可視為批文件，但是其作用又不僅限于批處理。
(1)創建存儲過程
(2)創建存儲函數
(3)變量的使用
(4)定義條件和處理程序
(5)光標的使用
(6)流程控制的使用
(1)創建存儲過程
創建存儲過程需要使用create procedure 語句，基本語法格式如下：
create procedure sp_name( [ proc_parameter ] )[ characteristics ... ] routine_body
create procedure 為用來創建存儲函數的關鍵字；sp_name為存儲過程的名稱；proc_parameter為存儲過程的參數列表，列表形式如下：
[in | out | inout] param_name type
- in表示輸入參數
- out表示輸出參數
- inout表示既可以輸入也可以輸出
- param_name表示參數名稱；type表示參數的類型
characteristics指定存儲過程的特征，有以下取值：
-
language SQL ： 說明routine_body部分是由SQL語句組成的，當前系統支持的語言為SQL，SQL是language特性的唯一值。
-
[not] deterministic: 指明存儲過程執行的結果是否正確。deterministic表示每次執行存儲過程時，相同的輸入會得到相同的輸出；而not deterministic表示相同的輸入可能得到不同的輸出。默認為not deterministic。
-
{contains SQL | no SQL | reads SQL date | modifies SQL date } ：指明子程序使用SQL語句的限制。contains SQL表明子程序包含SQL語句；no SQL表明子程序不包含SQ；reads SQL data 表明子程序包含讀數據的語句；modifies SQL data表明子程序包含寫數據的語句。默認為contatins SQL。
-
SQL security {definer | invoker}：指明誰有權限來執行。definer表示只有定義者才能執行；invoker表示擁有權限的調用者可以執行。默認為definer。
-
comment ‘string’：注釋信息，可以用來描述存儲過程或函數。
routine_body是SQL代碼的內容，可以用begin…end來表示SQL代碼的開始和結束。
【例1】創建查看fruits表的存儲過程，代碼語句如下：
create procedure proc() BEGIN select * from fruits; END ;
這個代碼創建了一個查看fruits表的存儲過程，代碼執行過程如下:
mysql> delimiter //mysql> create procedure Proc() -> begin -> select * from fruits; -> end //Query OK, 0 rows affected (0.36 sec)mysql> delimiter ;
- 提示:“delimiter //“語句的作用是將MySQL的結束符設置為//，因為MySQL默認語句結束符號為分號”;”，這樣做是為了避免與存儲過程中SQL語句結束符相沖突。存儲過程定義完之后再使用”delimiter ；“恢復默認結束符。使用delimiter命令時，應避免使用反斜杠””，因為反斜杠是MySQL中的轉義字符。
【例2】創建名稱為CountProc的存儲過程，代碼如下：
create procedure CountProc (OUT paraml int)beginselect count(*) into paraml from fruits;end;
上述代碼創建了一個獲取fruits表記錄條數的存儲過程,名稱是CountProc，count(*)計算后把結果放入參數paraml中。代碼的執行結果如下：
mysql> delimiter //mysql> create procedure CountProc(OUT paraml int ) -> begin -> select count(*) into paraml from fruits; -> end //Query OK, 0 rows affected (0.08 sec)mysql> delimiter ;
(2)創建存儲函數
創建存儲函數，需要使用create function語句，基本語法如下：
create function func_name ( [ func_parameter] ) returns type [characteristic ...] routine_body
- create function為用來創建存儲函數的關鍵字
- func_name表示存儲函數的名稱
- func_parameter為存儲過程的參數列表，參數列表形式為：[in | out | inout] param_name type
其中，in表示輸入參數，out表示輸出參數，inout表示既可以輸入也可以輸出param_name表示參數名稱，type表示參數的類型；returns type語句表示函數返回數據的類型；characteristic指定存儲函數的特性，取值與創建存儲過程時相同。
【例3】創建存儲函數，名稱為NameByZip，該函數返回select語句的查詢結果，數值類型為字符串型，代碼如下：
create function NameByZip()returns char( 50)return(select s_name from suppliers where s_call ='48075');
代碼的執行結果如下;
mysql> delimiter //mysql> create function NameByZip() -> returns char(50) -> return (select s_name from suppliers where s_call = '48075'); -> //Query OK, 0 rows affected (0.06 sec)mysql> delimiter;
如果在存儲函數中的ruturn語句返回一個類型不同于函數的returns子句中指定類型的值，返回值將被強制為恰當的類型。
- 注意：指定參數為in、out或inout只對procedure是合法的。(function中總是默認為in參數。)returns子句只能對function做指定，對函數而言是強制的。它用來指定函數的返回類型，而且函數體必須包含一個return value語句。
(3)變量的使用
變量可以在子程序中聲明并使用，這些變量的作用范圍是在begin…end程序中的。
1.定義變量
在存儲過程中使用declar語句定義變量，語法格式如下：
declare var_name[,varname]... date_type [default value];
var_name為局部變量的名稱。default value子句給變量提供一個默認值。值除了可以被聲明為一個常數之外，還可以被指定為一個表達式。如果沒有default子句，初始值為null。
【例4】定義名稱為myparam的變量，類型為int類型，默認值為100，代碼如下：
declare myparam int default 100;
2.為變量賦值
set var_name = expr [,var_name = expr]...;
存儲程序中的set語句是一般set語句的擴展版本。被參考變量可能是子程序內聲明的變量，或者是全局服務器變量，如系統變量或者用戶變量。
【例5】聲明3個變量，分別為var1，var2和var3，數據類型為int，使用set為變量賦值，代碼如下：
declare var1，var2，var3 int;set var1 = 10, var2 =20;set var3 = var1 + var2;
MySQL中還可以通過select…into為一個或多個變量賦值，語句如下：
select col_name[,...] into var_name[,...] table_expr;
這個select語法把選定的列直接存儲到對應位置的變量。col_name表示字段名稱;var_name表示定義的變量名稱；table_expr表示查詢條件表達式，包括表名稱和where子句。
【例6】聲明變量fruitname和fruitprice，通過select…into語句查詢指定記錄并為變量賦值,代碼如下:
declare fruitname char(50);declare fruitprice decimal(8,2);select f_name,f_price into fruitname,fruitpricefrom fruits where f_id='a1;
(4)定義條件和處理程序
特定條件需要特定處理。定義條件是事先定義程序執行過程中遇到的問題，處理程序定義了在遇到這些問題時應當采取的處理方式，并且保證存儲過程或函數在遇到警告或錯誤時能繼續執行。這樣就增強了存儲程序處理問題的能力，避免程序異常停止運行。
1.定義條件
定義條件使用declare語句，語法格式如下：
declare conditon_name Condition for [condition_type][condition_type];SQLSTATE [value] sqlstate_value | mysql_error_code
- condition_name表示條件的名稱
- condition_type表示條件的類型
- sqlstate_value和mysql_error_code都可以表示MySQL的錯誤
- sqlstate_value為長度為5的字符類型錯誤代碼
- mysql_error_code為數值類型錯誤代碼
例如：ERROR1142(42000)中，sqlstate_value的值是42000，mysql_error_code的值為1142。
這個語句指定需要特殊處理的條件。它將一個名字和指定的錯誤條件關聯起來。這個名字可以隨后被用在定義處理程序的declare handler語句中。
【例7】定義”error 1148(42000)”錯誤，名稱為command_not_allowed。可以用兩種不同的方法來定義，代碼如下：
[方法一]：使用sqlstate_valuedeclare command_not_allowed condition for sqlstate '42000'[]方法二]:使用mysql_error_codedeclare command_not_allowed condition for 1148
2.定義處理程序
定義處理程序時，使用declare語句的語法如下：
declare handler_type handler for condition_value[,...] sp_statement handler_type: continue|exit|undo condition_value: sqlstate[value] sqlstate_value |condition_name |sqlwarning |not found |sqlexception |mysql_error_code
其中，
- handler_type為錯誤處理方式，參數取3個值：continue、exit和undo。
- continue表示遇到錯誤不處理，繼續執行；
- exit遇到錯誤馬上退出；
- undo表示遇到錯誤后撤回之前的操作，MySQL中暫時不支持這樣的操作。
condition_value表示錯誤類型，可以有以下取值：
- sqlstate[value] sqlstate_value包含5個字符串錯誤值
- condition_name表示declare condition定義的錯誤條件名稱
- sqlwarning匹配所有以01開頭的sqlstate錯誤代碼
- notfound 匹配所有以02開頭的sqlstate錯誤代碼
- sqlexception匹配所有沒有被sqlwarning或not found捕獲的sqlstate錯誤代碼
- mysql_error_code匹配數值類型錯誤代碼
sp_statement參數為程序語句段，表示在遇到定義的錯誤時，需要執行的存儲過程或函數。
【例8】定義處理程序的幾種方式如下：
方法1：捕獲sqlstate_valuedeclare continue handler for sqlstate '42S02' set @info='No_SUCH_TABLE';方法2:捕獲mysql_error_codedeclare continue handler for 1146 set @info='No_SUCH_TABLE';方法3:先定義條件,然后調用declare no_such_table condition for 1146;declare continue handler for NO_SUCH_TABLE SET @info='NO_SUCH_TABLE';方法4:使用sqlwarningdeclare exit handler for sqlwarning set @info='ERROR';方法5:使用not founddeclare exit handler for not found set @info=' NO_SUCH_TABLE ';方法6:使用sqlexceptiondeclare exit handler forsqlexception set @info='ERROR';
上述代碼是6種定義處理程序的方法。
第一種，捕獲sqlstate_value值。如果遇到sqlstate_value值為”42S02″，執行continue操作，并且輸出”NO_SUCH_TABLE”信息。
第二種，捕獲mysql_error_code值。如果遇到mysql_error_code值為1146，就執行continue操作，并且輸出”NO_SUCH_TABLE”信息。
第三種，先定義條件再調用條件。這里先定義no_such_table條件，遇到1146錯誤就執行continue操作。
第四種，使用sqlwarning。sqlwarning捕獲所有以01開頭的sqlstate_value值，然后執行exit操作，并且輸出”ERROE”信息。
第五種，使用not found。not found捕獲所有以02開頭的sqlstate_value值，然后執行exit操作，并且輸出”NO_SUCH_TABLE”信息。
第六種，使用SQLEXCEPTION。sqlexception捕獲所有沒有被sqlwarning或not found捕獲的sqlstate_value值，然后執行exit操作，并且輸出”ERROR”信息。
【例9】定義條件和處理程序，具體執行的過程如下：
mysql> create table test.t(s1 int,primary key (s1));Query OK, 0 rows affected (0.14 sec)mysql> delimiter //mysql> create procedure handlerdemo() -> begin -> declare continue handler for sqlstate '23000' set @x2=1; -> set @x =1; -> insert into test.t values(1); -> set @x=2; -> insert into test.t values(1); -> set @x=3; -> end; -> //Query OK, 0 rows affected (0.06 sec)[調用存儲過程]mysql> delimiter ;mysql> call handlerdemo();Query OK, 0 rows affected (0.08 sec)[查看調用過程結果]mysql> select @x;+------+| @x |+------+| 3 |+------+1 row in set (0.00 sec)
可以看到，@x 是一個用戶變量，執行結果@x等于3，這表明MySQL被執行到程序末尾。
- “var_name”表示用戶變量，使用set語句為其賦值。用戶變量與連接有關，一個客戶端定義的變量不能被其他客戶端看到或使用。當客戶端退出時，該客戶端連接的所有變量將自動釋放。
(5)光標的使用
MySQL中光標只能在存儲過程和函數中使用。
查詢語句可能返回多條記錄，如果數據量非常大，需要在存儲過程和存儲函數中使用光標來逐條讀取查詢結果集中的記錄。光標必須在聲明處理程序之前被聲明，并且變量和條件還必須在聲明光標或處理程序之前被聲明。
1.聲明光標
MySQL中使用declare關鍵字來聲明光標，語法形式如下：
declare cursor_name cursor for select_statement
其中，cursor_name參數表示光標的名稱；select_statement表示select語句的內容，返回一個用于創建光標的結果集。
【例10】聲明名稱為cursor_fruit的光標，代碼如下：
declare cursor_fruit cursor for select f_name,f_price from fruits;
該代碼中光標名稱為cursor_fruit，select語句部分從fruits表匯總查詢出f_name和f_price字段的值。
2.打開光標
open cursor_name{光標名稱}
這個語句打開先前聲明的名稱為cursor_name的光標。
【例11】打開名稱為cursor_fruit的光標，代碼如下：
open cursor_fruit ;
3.使用光標
使用光標的語法格式:
fetch cursor_name into var_name [，var_name ] ... {參數名稱}
其中，cursor_name參數表示光標的名稱；var_name表示將光標中的select語句查詢出來的信息存入該參數中，var_name必須在聲明光標之前就定義好。
【例12】使用名稱為cursor_fruit的光標。將查詢出來的數據存入fruit_name和fruit_price兩個變量中，代碼如下:
fetch cursor_fruit into fruit_name,fruit_price;
4.關閉光標
關閉光標的語法格式:
close cursor_name(光標名稱)
這個語句關閉先前打開的光標。
如果未被明確地關閉，那么光標將在它被聲明的復合語句的末尾被關閉。
【例13】關閉名稱為cursor_fruit的光標，代碼如下：
close cursor_fruit;
(6)流程控制的使用
流程控制語句用來根據條件控制語句的執行。MySQL中用來構造控制流程的語句有IF語句、case語句、loop語句、leave語句、iterate語句、repeat語句和while語句。每個流程中可能包含一個單獨語句，或者是使用begin…end構造的符合語句，構造可以被嵌套。
1.if語句
if語句包含多個條件判斷，根據判斷的結果為true或false執行相應的語句，語法格式如下：
if expr_condition then statement_list [elseif expr_condition then statement_list]... [else statement_list]end if
如果expr_condition求值為真，相應的SQL語句列表被執行；如果沒有expr_condition匹配，則else子句里的語句列表被執行。statement_list列表可包括一個或多個語句。
MySQL中還有一個if()函數，它不同于這里描述的if語句。
【例14】if語句示例
if val is null then select ‘val is null’; else select 'val is not null';end if
該示例判斷val值是否為空，如果為空輸出字符串”val is null”；否則輸出字符串”val is not null”。if語句都需要使用end if來結束。
2.case語句
case是另一個進行條件判斷的語句，有兩種語句格式，第一種：
case case_expr when when_value then statement_list [when when_value then statement_list]... [else statement_list]end case
- case_expr表示條件判斷的表達式，決定了哪一個when語句會被執行
- when_value表示表達式可能的值。
- 如果某個when_value表達式和case_expr表達式結果相同，則執行對應的then關鍵字后面的statement_list中的語句。
- statement_list表示不同when_value值的執行語句。
【例15】使用case流程控制語句的第1種格式，判斷val值等于1、等于2或者兩者都不等，SQL語句如下：
case val when 1 then select ‘val is 1’； when 2 then select ‘val is 2’； else select ‘val is not 1 or 2’；end case;
當val值為1時，輸出字符串”val is 1″;當val值為2時，輸出字符串”val is 2″;否則輸出字符串”val is not 1 or 2″。
case語句的第2種格式如下：
case when expr_condition then statement_list [when expr_condition then statement_list] [else statement_list]end case
- expr_condition表示條件判斷語句
- statement_list表示不同條件的執行語句
該語句中，when語句將被逐個執行，直到某個expr_condition表達式為真，則執行對應then關鍵字后面的statement_list語句。如果沒有條件匹配，else子句里的語句被執行。
注意：存儲程序中的case語句和case控制流程函數的區別：
存儲程序中的case語句不能有else null子句，并且用end case替代end來終止。
【例16】使用case流程控制語句的第2種格式，判斷val是否為空、小于0、大于0或者等于0，SQL語句如下：
case when val is null then select ‘val is null’; when val < 0 then select 'val is less than 0'; when val > 0 then select 'val is greater than 0'; else select 'val is 0';end case;
當val值為空時，輸出字符串”val is null”;當val值小于0時，輸出字符串”val is less than 0″;當val值大于0時，輸出字符串”val is greater than 0″;否則輸出字符串”val is 0″。
3.loop語句
loop循環語句用來重復執行某些語句，與if和case語句相比，loop只是創建一個循環操作過的過程，并不進行條件判斷。退出循環過程使用leave子句。loop語法格式如下:
[loop_label:] loop statement_listend loop [loop_label]
loop_label表示loop語句的標注名稱，該參數可省略。statement_list參數表示需要循環執行的語句。
【例17】使用loop語句進行循環操作，id值小于等于10之前，將重復執行循環過程，SQL語句如下：
declare id int default 10add_loop:loopset id = id +1; if >=10 then leave add_loop; end if;end loop add_ loop;
該示例循環執行id加1的操作。當id值小于10時，循環重復執行。當id值大于或等于10時，使用leave語句退出循環。loop循環都以end loop結束。
4.leave語句
leave語句用來退出任何被標注的流程控制構造，leave語句基本格式如下：
leave label
其中，label參數表示循環的標志。leave和begin…end或循環一起被使用。
【例18】使用leave語句退出循環，代碼如下：
add_num:loopset @count=@count+1;if @count=50 then leave add_num;end loop add_num;
該示例循環執行count加1的操作，當count的值等于50時，使用leave語句跳出循環。
5.iterate語句
iterater label語句將執行順序轉到語句段開頭處，語法格式如下：
iterate label
iterate只可以出現在loop、repeat和while語句內。iterate的意思為”再次循環”，label參數表示循環的標志。iterate語句必須跟在循環標志前面。
【例19】iterate語句示例：
create procedure doiterate()begin declare p1 int default 0; declare p1 int default 0; my_loop:loop; set p1 = p1 + 1; if p1 < 10 then iterate my_loop; elseif p1 > 20 then leave my_loop; end if; select 'p1 is between 10 and 20';end loop my_loop;end
首先定義p1=0，當p1的值小于10時重復執行p1加1操作；當p1大于等于10并且小于等于20時，打印消息”p1 is between 10 and 20″;當p1大于20時，退出循環。
6.repeat語句
repeat語句創建一個帶條件判斷的循環過程，每次語句執行完畢之后，會對條件表達式進行判斷，若表達式為真，則循環結束；否則重復執行循環中的語句。repeat語句的語法格式如下：
[repeat_label:] repeat statement_list until expr_conditionend repeat [repeat_label]
repeat_label為repeat語句的標注名稱，該參數可以省略；repeat語句內的語句或語句群被重復，直至expr_condition為真。
【例20】repeat語句示例，id值等于10之前，將重復執行循環過程，代碼如下：
declare id int default 0；repeatset id = id + 1； until id >= 10end repeat;
該示例循環執行id加1的操作。當id值小于10時，循環重復執行；當id值大于或者等于10時，退出循環。repeat循環都以end repeat結束。
7.while語句
while語句創建一個帶條件判斷的循環過程，與repeat不同，while在執行語句執行時，先對指定的表達式進行判斷，如果為真，就執行循環內的語句，否則退出循環。while語句的基本格式如下：
[while_label:] while expr_condition do statement_listend while [while_label]
- while_label為while語句的標注名稱
- expr_condition為進行判斷的表達式，如果表達式結果為真，while語句內的語句或語句群被執行，直至expr_condition為假，退出循環。
【例21】while語句示例，i值小于10時，將重復執行循環過程，代碼如下：
declare i int default 0；while i <10 doset i = i + 1;end while;
相關免費學習推薦：mysql數據庫(視頻)
