自行創(chuàng)建一個員工信息表info，里面記錄著員工姓名，年齡，地址以及工資，下面的實驗要使用。
mysql> create database testdb charset utf8;
mysql> use testdb;
mysql> create table info(ID int not null auto_increment primary key,NAME CHAR(8) NOT NULL,
?????? AGE INT NOT NULL,
?????? ADDRESS VARCHAR(20) NOT NULL,
?????? SALARY decimal(10,2) not null) ENGINE=innodb;
mysql> INSERT INTO info(NAME,AGE,ADDRESS,SALARY) VALUES('ZHANG',32,'Beijing',2000.00);
mysql> INSERT INTO info(NAME,AGE,ADDRESS,SALARY) VALUES('LI',25,'Shanghai',1500.00);
mysql> INSERT INTO info(NAME,AGE,ADDRESS,SALARY) VALUES('PENG',23,'Hangzhou',2000.00);
mysql> INSERT INTO info(NAME,AGE,ADDRESS,SALARY) VALUES('LIN',25,'Henan',6500.00);
mysql> INSERT INTO info(NAME,AGE,ADDRESS,SALARY) VALUES('WANG',27,'Hunan',8500.00);
mysql> INSERT INTO info(NAME,AGE,ADDRESS,SALARY) VALUES('WANG',22,'Hunan',4500.00);
mysql> INSERT INTO info(NAME,AGE,ADDRESS,SALARY) VALUES('GAO',24,'Hebei',10000.00);
存儲過程和存儲函數(shù)
簡單地說，存儲過程就是一條或者多條SQL語句的集合，可視為批文件，但是其作用不僅限于批處理。
存儲程序可以分為存儲過程和函數(shù)，MySQL中創(chuàng)建存儲過程和函數(shù)使用的語句分別是CREATE PROCEDURE和CREATE FUNCTION。使用CALL語句來調(diào)用存儲過程，只能用輸出變量返回值，函數(shù)可以從語句外調(diào)用（即通過引用函數(shù)名），也能返回標(biāo)量值。存儲過程也可以調(diào)用其他存儲過程。
存儲過程的好處：
1.由于數(shù)據(jù)庫執(zhí)行動作時，是先編譯后執(zhí)行的，然而存儲過程是一個編譯過的代碼塊，所以執(zhí)行效率要比T-SQL語句高。
2.一個存儲過程在程序在網(wǎng)絡(luò)中交互時可以替代大堆的T-SQL語句，所以也能降低網(wǎng)絡(luò)的通信量，提高通信速率。
3.通過存儲過程能夠使沒有權(quán)限的用戶在控制之下間接地存取數(shù)據(jù)庫，從而確保數(shù)據(jù)的安全。
4.可在生產(chǎn)環(huán)境下執(zhí)行，修復(fù)BUG。
創(chuàng)建存儲過程
創(chuàng)建存儲過程，需要使用CREATE PROCEDURE語句，基本語法格式如下：
CREATE [DEFINER = { user | CURRENT_USER }] PROCEDURE sp_name ([proc_parameter[,...]]) [characteristic ...] routine_body
CREATE PROCEDURE為用來創(chuàng)建存儲函數(shù)的關(guān)鍵字；sp_name為存儲過程的名稱；proc_parameter為指定存儲過程的參數(shù)列表，可省略，列表形式如下：
[ IN | OUT | INOUT ] param_name type
其中，IN表示輸入?yún)?shù)，OUT表示輸出參數(shù)，INOUT表示既可以輸入也可以輸出；param_name表示參數(shù)名稱；type表示參數(shù)的類型，該類型可以是MySQL數(shù)據(jù)庫中的任意類型。另外如果你傳入的參數(shù)中文的，那么還可以在“[ IN | OUT | INOUT ] param_name type”加入字符集，如“IN id INT character set utf8”。
Characteristics指定存儲過程的特性，有以下取值：
LANGUAGE SQL
說明routine_body部分是由SQL語句組成的，當(dāng)前系統(tǒng)支持的語言為SQL，SQL是LANGUAGE特性的唯一值。
[NOT] DETERMINISTIC
指明存儲過程執(zhí)行的結(jié)果是否正確，DETERMINISTIC表示結(jié)果是確定的。每次執(zhí)行存儲過程時，相同的輸入會得到相同的輸出。NOT DETERMINISTIC表示結(jié)果是不確定的，相同的輸入可能得到不同的輸出。如果沒有指定任意一個值，默認(rèn)為NOT DETERMINISTIC。
{ CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
指明子程序使用SQL語句的限制。CONTAINS SQL表明子程序包含SQL語句，但是不包含讀寫數(shù)據(jù)的語句；NO SQL表明子程序不包含SQL語句；READS SQL DATA說明子程序包含讀數(shù)據(jù)的語句；MODIFIES SQL DATA表明子程序包含寫數(shù)據(jù)的語句。默認(rèn)情況下，系統(tǒng)會指定為CONTAINS SQL。
SQL SECURITY { DEFINER | INVOKER}
指明誰有權(quán)限來執(zhí)行，DEFINER表示只有定義者才能執(zhí)行。INVOKER表示擁有權(quán)限的調(diào)用者可以執(zhí)行。默認(rèn)情況下，系統(tǒng)指定為DEFINER。
COMMENT ‘string’
注釋信息，可以用來描述存儲過程或函數(shù)。
routine_body
是SQL代碼的內(nèi)容，可以用BEGIN…END來表示SQL代碼的開始和結(jié)束。
編寫存儲過程并不是件簡單的事情，可能存儲過程中需要復(fù)雜的SQL語句，并且要有創(chuàng)建存儲過程的權(quán)限；但是使用存儲過程將簡化操作，減少冗余的操作步驟，同時，還可以減少操作過程中的失誤，提高效率，因此存儲過程時非常有用的。
下面是一個簡單存儲過程的實例：
mysql> use testdb;
mysql> delimiter $
mysql> create procedure test()
?????? begin
?????? select * from testdb.info;
?????? end $
mysql> delimiter ;
其中，“delimiter $”語句的作用是將MySQL的結(jié)束符設(shè)置為“$”符，因為MySQL默認(rèn)的語句結(jié)束符為分號“;”，為了避免與存儲過程中SQL語句結(jié)束符相沖突，所以就是用delimiter改變存儲過程的結(jié)束符，并以“END $”結(jié)束存儲過程，其中存儲過程定義完畢之后再使用“delimiter ;”恢復(fù)默認(rèn)結(jié)束符。
然后開始定義存儲過程，此存儲過程名為test，使用CREATE PROCEDURE test()語句定義。此存儲過程沒有參數(shù)，但是后面的()仍然需要。BEGIN和END語句用來限定存儲過程體，過程本身僅是一個簡單的SELECT語句。每次調(diào)用這個存儲過程的時候都會執(zhí)行SELECT語句查看表。當(dāng)然存儲過程也可以是很多語句的復(fù)雜的組合，就好像這個例子剛開始給出的那個語句一樣，其本身也可以調(diào)用其他的函數(shù)，來組成更加復(fù)雜的操作。
操作存儲過程
1）查看存儲過程
mysql> SHOW PROCEDURE STATUS\G
*************************** 1. row ***************************
Db: testdb
Name: test
Type: PROCEDURE
Definer: root@localhost
Modified: 2016-12-22 15:09:15
Created: 2016-12-22 15:09:15
Security_type: DEFINER
Comment:
character_set_client: utf8
collation_connection: utf8_general_ci
Database Collation: utf8_general_ci
1 row in set (0.00 sec)
存儲過程的默認(rèn)安全類型是DEFINER，并且Definer的用戶為創(chuàng)建這個存儲過程的用戶。
另外在mysql.proc中也有存儲過程的信息。
mysql> select * from mysql.proc\G
2）查看存儲過程創(chuàng)建過程
mysql> show create procedure test\G
*************************** 1. row ***************************
?????????? Procedure: test
????????????sql_mode: STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
????Create Procedure: CREATE DEFINER=`root`@`localhost` PROCEDURE `test`()
begin
select * from testdb.info;
end
??character_set_client: utf8
??collation_connection: utf8_general_ci
??Database Collation: utf8_general_ci
1 row in set (0.00 sec)
3）調(diào)用存儲過程
mysql> CALL test();
4）刪除存儲過程
mysql> DROP PROCEDURE procedure_name;
5）修改存儲過程權(quán)限
方法一：修改存儲過程的definer
mysql> update mysql.proc set definer='root'@'192.168.2.%' where db='db_name';
方法二：修改sql security
sql secuirty的值決定了調(diào)用存儲過程的方式，取值definer或者invoker（默認(rèn)是definer）。
definer：在執(zhí)行存儲過程前驗證definer對應(yīng)的用戶，如：’root’@’192.168.2.%’是否存在，以及是否具有執(zhí)行存儲過程的權(quán)限，若沒有則報錯。簡單來說就是執(zhí)行這個存儲過程的用戶不管是誰也不管有沒有權(quán)限執(zhí)行，只要’root’@’192.168.2.%’這個用戶存在且有執(zhí)行的權(quán)限即可。
invoker：在執(zhí)行存儲過程時判斷inovker，即調(diào)用該存儲過程的用戶是否有相應(yīng)權(quán)限，若沒有則報錯。簡單來說也就是根據(jù)執(zhí)行用戶本身權(quán)限為主。
修改語法：
mysql> alter procedure procedure_name sql security invoker;
也可以在創(chuàng)建存儲過程的時候給定definer=’test’@’%’，如下：
mysql> use testdb;
mysql> delimiter $
mysql> create definer='test'@'%' procedure test1()
?????? begin
?????? select * from testdb.info;
?????? end $
mysql> delimiter ;
這個時候由于是definer模式，所以如果你的’test’@’%’用戶沒有執(zhí)行這個存儲過程的權(quán)限或者此用戶不存在的話，其他用戶執(zhí)行這個存儲過程就會報錯。信息如下：
ERROR 1370 (42000): execute command denied to user 'tt'@'%' for routine 'testdb.test1'
下面再創(chuàng)建一個可以傳參的存儲過程。
mysql> use testdb;
mysql> delimiter $
mysql> create procedure param(n int)
????begin
????select * from testdb.info where salary > n;
?????? end $
mysql> delimiter ;
mysql> call param(5000);
創(chuàng)建一個param存儲過程，定義一個參數(shù)n，類型為int；然后執(zhí)行SQL語句的時候使用參數(shù)n作為條件，最后調(diào)用存儲過程時輸入?yún)?shù)值。
創(chuàng)建存儲函數(shù)
創(chuàng)建存儲函數(shù)，需要使用CREATE FUNCITON語句，基本語法格式如下：
CREATE FUNCTION fun_name ([fun_parameer])
RETURENS type
[characteristic …] routine_body
CREATE FUNCTION為用來創(chuàng)建存儲函數(shù)的關(guān)鍵字；fun_name表示存儲函數(shù)的名稱；fun_parameter為存儲過程的參數(shù)列表，參數(shù)列表形式如下：
[ IN | OUT | INOUT ] param_name type
其中，IN表示輸入?yún)?shù)，OUT表示輸出參數(shù)，INOUT表示既可以輸入也可以輸出；param_name表示參數(shù)名稱；type表示參數(shù)的類型，該類型可以是MySQL數(shù)據(jù)庫中的任意類型。
RETURNS type語句表示函數(shù)返回數(shù)據(jù)的類型；characteristic：指定存儲函數(shù)的特性，取值與創(chuàng)建存儲過程時相同，詳細(xì)的看存儲過程就行了。
下面創(chuàng)建一個存儲函數(shù)，名稱為fun，參數(shù)定義為空，該函數(shù)返回SELECT語句的查詢結(jié)果，數(shù)值為字符串型，代碼如下：
mysql> DELIMITER $
mysql> CREATE FUNCTION fun() RETURNS char(200)
????BEGIN
????RETURN (SELECT SALARY from testdb.info WHERE NAME="ZHANG");
?????? end$
mysql> DELIMITER ;
如果在存儲函數(shù)中的RETURN語句返回一個類型不同于函數(shù)的RETURNS字句中指定類型的值，返回值將被強制為恰當(dāng)?shù)念愋?。比如，如果一個函數(shù)返回一個ENUM或SET值，但是RETURN語句返回一個整數(shù)，對于SET成員集的相應(yīng)的ENUM成員，從函數(shù)返回的值時字符串。
MySQL存儲過程和函數(shù)有什么區(qū)別？在本質(zhì)上他們都是存儲程序，函數(shù)只能通過return語句返回單個值或者表對象；而存儲過程不允許執(zhí)行return，但是可以通過out參數(shù)返回多個值。函數(shù)限制比較多，不能用臨時表，只能用表變量，還有一個些函數(shù)都不可用等等；而存儲過程的限制相對就比較少。函數(shù)可以嵌入在SQL語句中使用，可以在SELECT語句中作為查詢語句的一個部分帶哦用；而存儲過程一般是作為一個獨立的部分來執(zhí)行。
變量的使用
在編寫存儲過程中，可以定義變量，變量可以在子程序中聲明并使用，這些變量的作用范圍是在BEGIN…END程序中。
1）定義變量
在存儲過程中使用DECLARE語句定義變量，語法格式如下：DECLARE var_name date_type [DEFAULT value];
var_name為局部變量的名稱，DEFAULT value子句給變量提供一個默認(rèn)值。值除了可以被聲明為一個常數(shù)之外，還可以被指定為一個表達(dá)式。如果沒有DEFAULT子句，初始值為NULL。
實例：定義名稱為myparam的變量，類型為INT類型，默認(rèn)值為100，代碼如下。
mysql> delimiter $
mysql> create procedure var1()
begin
??DECLARE myparam INT DEFAULT 100;
end$
2）輸出變量
在MySQL中輸出變量的方式為select @@log_bin;，但在存儲過程中輸出變量的方式為SELECT var_name;即可，具體代碼如下：
mysql> create procedure var2()
begin
??DECLARE myparam INT DEFAULT 100;
??SELECT myparam;
end$
mysql> call p2()\G
*************************** 1. row ***************************
var: 100
3）為變量賦值
定義變量之后，為變量賦值可以改變變量的默認(rèn)值，MySQL中使用SET語句為變量賦值，語法格式如下：
SET var_name=expr[,var_name=expr]…;
在存儲程序中的SET語句時一般SET語句的擴(kuò)展版本，被參考變量可能是子程序內(nèi)聲明的變量，或者是全局服務(wù)器變量，如系統(tǒng)變量或者用戶變量。
在存儲程序中的SET語句作為預(yù)先存在的SET語法的一部分來實現(xiàn)，這允許SET a=x，b=y，…這樣的擴(kuò)展語法。其中不同的變量類型（局域聲明變量即全局變量）可以被混合起來。這也允許把局部變量和一些只對系統(tǒng)變量有意義的選項合并起來。下面聲明三個變量，然后使用SET為變量賦值。代碼如下：
mysql> create procedure var3()
begin
??DECLARE var1,var2,var3 INT;
??SET var1 = 10, var2 = 20;
??SET var3 = var1+var2;
??SELECT var3;
end$
mysql> call var3()\G
*************************** 1. row ***************************
Var3: 30
MySQL中可以通過SELECT … INTO為一個或多個變量賦值，語法如下：
SELECT col INTO var_name FROM table_name
代碼如下：
mysql> create procedure var4()
begin
??DECLARE var INT;
??select count(*) into var from testdb.info;
??SELECT var;
end$
mysql> delimiter ;
mysql> call var4()\G
*************************** 1. row ***************************
var: 7
定義條件和處理程序
特定條件需要特定處理，這些條件可以聯(lián)系到錯誤，以及子程序中的一般流程控制。定義條件是事先定義程序執(zhí)行過程中遇到的問題，處理程序定義了在遇到這些問題時應(yīng)當(dāng)采取的處理方式，并且保證存儲過程或函數(shù)在遇到警告或錯誤時能繼續(xù)執(zhí)行。這樣可以增強存儲程序處理問題的能力，避免程序異常停止運行。如果需要可以通過DECLARE關(guān)鍵字來定義條件和處理程序。
流程控制的使用
在存儲過程中可以定義流程控制語句，流程控制語句用來根據(jù)條件控制語句的執(zhí)行，MySQL中的用來構(gòu)造控制流程的語句有：IF語句、CASE語句、LOOP語句、WHILE語句、LEAVE語句、ITERATE語句和REPEAT語句。每個流程中可能包含一個單獨語句，或者是使用BEGIN…END構(gòu)造的復(fù)合語句，構(gòu)造可以被嵌套。下面介紹這些流程控制語句
1）IF語句
IF語句包含多個條件判斷，根據(jù)判斷的結(jié)果為TRUE或FALSE執(zhí)行相應(yīng)的語句，語法格式如下：
IF condition THEN
??Statement
ELSELF condition THEN
??statement
ELSE
??statement
END IF
IF實現(xiàn)了一個基本的條件構(gòu)造，如果condition求值為真，響應(yīng)的SQL語句列表被執(zhí)行；如果沒有condition匹配，則ELSE字句里的語句列表被執(zhí)行。Statement可以包括一個或多個語句。如果非常熟悉Shell變量應(yīng)該很好理解這些語句，另外MySQL中還有一個IF()函數(shù)，它不同于這里描述的IF語句。
示例
mysql> use testdb;
mysql> delimiter $
mysql> create procedure pif(n int,j char(1))
begin
?????? if j='gao' then
???????? select * from testdb.info where salary > n;
?????? else
???????? select * from testdb.info where salary < n;
?????? end if;
end$
該示例中，create procedure pif(n int,j char(1))語句怎么使用在創(chuàng)建函數(shù)中有說明，IF主要是判斷j參數(shù)的值，如果為“gao”那么執(zhí)行第一條SQL語句，如果j的值不等“gao”那么就執(zhí)行第二條SQL語句，IF語句都需要使用END IF來結(jié)束。
mysql> call pif(5000,'gao');
#調(diào)用存儲過程pif并輸入?yún)?shù)n和j的值
2）CASE語句
CASE是另一個進(jìn)行條件判斷的語句，該語句有2中語句格式，第1種格式如下：
CASE case_expr
WHEN when_value THEN statement
[WHEN when_value THEN statement]
[ELSE statement]
END CASE
其中，case_expr參數(shù)表示條件判斷的表達(dá)式，決定了那一個WHEN子句會被執(zhí)行；when_value參數(shù)表示表達(dá)式可能的值，如果某個when_value表達(dá)式與case_expr表達(dá)式結(jié)果相同，則執(zhí)行對應(yīng)THEN關(guān)鍵字后的statement中的語句；statement參數(shù)表示不同when_value值的執(zhí)行語句。
示例
mysql> create procedure caseone()
begin
??declare val INT;
??set val = 1;
??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;
end$
mysql> call caseone()\G
*************************** 1. row ***************************
val is 1: val is 1
使用CASE判斷當(dāng)val值為1時，輸出字符串“val is 1”；當(dāng)val值為2時，輸出字符串“val is 2”；否則輸出字符串“val is not 1 or 2”。
CASE語句的第二種格式如下：
CASE
WHEN condition THEN statement
[WHEN condition THEN statement]
[ELSE statement]
END CASE
其中，condition參數(shù)表示條件判斷語句；statement參數(shù)表示不同條件的執(zhí)行語句。該語句中，WHEN語句將被逐個執(zhí)行，直到某個condition表達(dá)式為真，則執(zhí)行對應(yīng)THEN關(guān)鍵字后面的statement語句。如果沒有條件匹配，ELSE字句里的語句被執(zhí)行。
示例
mysql> create procedure casetwo ()
begin
??declare val INT;
??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;
end$
mysql> call casetwo()\G
*************************** 1. row ***************************
val is null: val is null
當(dāng)val值為空，輸出字符串“val is NULL”；當(dāng)val值小于0時，輸出字符串“val is less than 0”；當(dāng)val值大于0時，輸出字符串“val is greater than 0”；否則輸出字符串“val is 0”。
3）LOOP語句
LOOP循環(huán)語句用來重復(fù)執(zhí)行某些語句，與IF和CASE語句相比，LOOP只是創(chuàng)建一個循環(huán)操作的過程，并不進(jìn)行條件判斷。LOOP內(nèi)的語句一直重復(fù)執(zhí)行直到循環(huán)被退出，跳出循環(huán)過程，使用LEAVE子句，LOOP語句的基本格式如下：
[loop_label:] LOOP
Statement
END LOOP [loop_label]
其中，loop_label表示LOOP語句的標(biāo)注名稱，該參數(shù)可以省略；statement參數(shù)表示需要循環(huán)執(zhí)行的語句。
示例
mysql> create procedure loopone()
begin
??declare id int default 0;
??add_loop:loop
? set id = id+1;
? if id >= 10 then leave add_loop;
? end if;
??end loop add_loop;
end$
4）LEAVE語句
LEAVE語句用來退出任何被標(biāo)注的流程控制構(gòu)造，LEAVE語句基本格式如下：
LEAVE label
其中，label參數(shù)表示循環(huán)的標(biāo)志，LEAVE和BEGIN….END或循環(huán)一起使用。使用LEAVE語句退出循環(huán)，代碼如下：
Add_num:loop
Set @count=@count+1;
If @count=50 then leave add_num;
End loop add_num;
該循環(huán)執(zhí)行count加1的操作，當(dāng)count的值等于50時，使用LEAVE語句跳出循環(huán)。
5）ITERATE語句
ITERATE語句將執(zhí)行順序轉(zhuǎn)到語句段開頭處，語句基本格式如下：
ITERATE label
Iterate只可以出現(xiàn)在LOOP、REPEAT和WHILE語句內(nèi)。Iterate的意思為“在此循環(huán)”，label參數(shù)表示循環(huán)的標(biāo)志。Iterate語句必須跟在循環(huán)標(biāo)志前面。
示例
mysql> create procedure iterateone()
begin
declare var int default 0;
my_loop:loop
? set var=var+1;
? if var < 10 then iterate my_loop;
? elseif var > 20 then leave my_loop;
? end if;
? select 'var is between 10 and 20';
end loop my_loop;
end$
var等于0，如果var的值小于10時，重復(fù)執(zhí)行var加1操作；當(dāng)var大于等于10并且小于20時，打印消息“var is between 10 and 20”；當(dāng)var大于20時，退出循環(huán)。
6）REPEAT語句
REPEAT語句創(chuàng)建一個帶條件判斷的循環(huán)過程，每次語句執(zhí)行完畢之后，會對條件表達(dá)式進(jìn)行判斷，如果表達(dá)式為真，則循環(huán)結(jié)束；否則重復(fù)執(zhí)行循環(huán)中的語句。Repeat語句的基本格式如下：
[repeat_label]:REPEAT
Statement
UNTIL condition
End REPEAT [repeat_label]
示例：id值小于等于0之前，將重復(fù)執(zhí)行循環(huán)過程。
mysql> create procedure repeatone()
begin
declare id int default 0;
repeat
?? set id = id+1;
until id >= 10
end repeat;
select id;
mysql> call repeatone()\G
*************************** 1. row ***************************
id: 10
?7）WHILE語句
WHILE語句創(chuàng)建一個帶條件判斷的循環(huán)過程，與REPEAT不同，WHILE在執(zhí)行語句時，先對指定的表達(dá)式進(jìn)行判斷，如果為真，則執(zhí)行循環(huán)內(nèi)的語句，否則退出循環(huán)。WHILE語句的基本格式如下：
[while_label:]WHILE condition DO
Statement
END WHILE [while_label]
while_label為WHILE語句的標(biāo)注名稱，condition為進(jìn)行判斷的表達(dá)式，如果表達(dá)式結(jié)果為真，WHILE語句內(nèi)的語句或語句群被執(zhí)行，直至condition為假，退出循環(huán)。
示例：使用WHILE計算1+n的和
create procedure whileone(n smallint)
begin
declare i int;
declare s int;
set i = 1;
set s = 0;
while i <= n do
set s = s + i;
set i = i + 1;
end while;
select s;
end$
mysql> delimiter ;
mysql> call whileone(10)\G
*************************** 1. row ***************************
s: 55
光標(biāo)的使用
查詢語句可能返回多條記錄，如果數(shù)據(jù)量非常大，需要在存儲過程和存儲函數(shù)中使用光標(biāo)來逐條讀取查詢結(jié)果集中的記錄。在存儲過程中可以使用光標(biāo)對結(jié)果集進(jìn)行循環(huán)處理，應(yīng)用程序可以根據(jù)需要滾動或瀏覽其中的數(shù)據(jù)。光標(biāo)必須在聲明處理程序之前被聲明，并且變量和條件還必須在聲明光標(biāo)或處理程序之前被聲明。
1）聲明光標(biāo)
MySQL中使用DECLARE關(guān)鍵字來聲明光標(biāo)，其語法的基本形式如下：
DECLARE cursor_name CURSOR FOR select_statement
其中，cursor_name參數(shù)表示光標(biāo)的名稱；select_statement參數(shù)表示SELECT語句的內(nèi)容，返回一個用于創(chuàng)建光標(biāo)的結(jié)果集。
聲明名稱為cursor_first的光標(biāo)，代碼如下：
DECLARE cursor_first CURSOR FOR select * from testdb.info;
2）打開光標(biāo)
OPEN cursor_first;
3）讀取光標(biāo)
FETCH cursor_name INTO var_name [,var_name]…{參數(shù)名稱}
這個語句用指定的打開光標(biāo)讀取下一行，并且前進(jìn)光標(biāo)指針。其中，cursor_name參數(shù)表示光標(biāo)的名稱；var_name參數(shù)表示將光標(biāo)中的SELECT語句查詢出來的信息存入該參數(shù)中，var_name必須在聲明光標(biāo)之前就定義好。
使用名稱為cursor_first的光標(biāo)，將查詢出來的數(shù)據(jù)存入one_name和two_name這兩個變量中，代碼如下：
FETCH cursor_first INTO one_name,two_name;
4）關(guān)閉光標(biāo)
CLOSE cursor_name
這個語句關(guān)閉先前打開的光標(biāo)，如果未被明確地關(guān)閉，光標(biāo)在它被聲明的復(fù)合語句的末尾被關(guān)閉。
PS：MySQL中光標(biāo)只能在存儲過程和存儲函數(shù)中使用。
示例1：把testdb.info中ID字段的值賦給光標(biāo)mycursor，然后把mycursor讀取到變量a上，最后使用LOOP循環(huán)顯示a變量
mysql> delimiter $
mysql> CREATE PROCEDURE curdemo()
BEGIN
DECLARE a INT;
DECLARE mycursor CURSOR FOR SELECT ID FROM testdb.info;
OPEN mycursor;
LOOP
??FETCH mycursor INTO a;
??SELECT a;
END LOOP;
CLOSE mycursor;
END $
mysql> call curdemo()$
mysql> delimiter ;
調(diào)用存儲過程
存儲過程是通過CALL語句進(jìn)行調(diào)用的，語法如下：
CALL procedure_name([parameter])
CALL語句調(diào)用一個先前用CREATE PROCEDURE創(chuàng)建的存儲過程，其中procedure_name為存儲過程的名稱，[parameter]為存儲過程的參數(shù)，可選。下面頂一個countproc的存儲過程，然后調(diào)用這個存儲過程，代碼執(zhí)行如下：
mysql> delimiter $
mysql> create procedure countproc(IN sid INT,OUT num INT)
????begin
??????select count(*) into num from testdb.info where salary > 5000;
?????? end$
調(diào)用存儲過程
mysql> delimiter ;
mysql> call countproc(5000,@num);
查看返回結(jié)果
mysql> select @num\G
*************************** 1. row ***************************
@num: 3
該存儲過程返回了info表中工資高于5000的人數(shù)，把人數(shù)返回值存儲在num變量中，使用SELECT查看，返回結(jié)果為3.
查看存儲過程和存儲函數(shù)
SHOW STATUS語句可以查看存儲過程和函數(shù)的狀態(tài)，其基本語法結(jié)構(gòu)如下：
SHOW {PROCEDURE | FUNCTION} STATUS [LIKE ‘pattern’]
這個語句是一個MySQL的擴(kuò)展，它返回子程序的特征，如數(shù)據(jù)庫、名字、類型、創(chuàng)建者及創(chuàng)建和修改日期。如果沒有指定樣式，根據(jù)使用的語句，所有存儲程序或存儲函數(shù)的信息都被列出PROCEDURE和FUNCTION分別表示查看存儲過程和函數(shù)；LIKE語句表示匹配存儲過程或函數(shù)的名稱。代碼執(zhí)行如下：
mysql> SHOW PROCEDURE STATUS LIKE 'var%'\G
*************************** 1. row ***************************
Db: testdb
Name: var
Type: PROCEDURE
Definer: root@localhost
Modified: 2015-11-17 22:40:58
Created: 2015-11-17 22:40:58
Security_type: DEFINER
Comment:
character_set_client: utf8
collation_connection: utf8_general_ci
Database Collation: utf8_general_ci
除了SHOW STATUS之外，MySQL還可以使用SHOW CREATE語句查看存儲過程和函數(shù)的狀態(tài)。
SHOW CREATE {PROCEDURE | FUNCTION} name
這個語句時一個MySQL的擴(kuò)展，類似于SHOW CREATE TABLE，它返回一個可用來重新創(chuàng)建已命名子程序的確切字符串。PROCEDURE和FUNCTION分別表示查看存儲過程和函數(shù)；LIKE語句表示匹配存儲過程或函數(shù)的名稱。SHOW CREATE語句示例，代碼如下：
mysql> show create procedure var \G
*************************** 1. row ***************************
Procedure: var
sql_mode: STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
Create Procedure: CREATE DEFINER=`root`@`localhost` PROCEDURE `var`()
begin
DECLARE var INT;
select count(*) into var from testdb.info;
end
character_set_client: utf8
collation_connection: utf8_general_ci
Database Collation: utf8_general_ci
MySQL中存儲過程和函數(shù)的信息存儲在information_schema數(shù)據(jù)庫下的Routines表中?？梢酝ㄟ^查詢該表的記錄來查詢存儲過程和函數(shù)的信息。其基本語句形式如下：
select * from information_schema.Routines where ROUTINE_NAME='sp_name'\G
其中，ROUTINE_NAME字段中存儲的是存儲過程和函數(shù)的名稱；sp_name參數(shù)表示存儲過程或函數(shù)的名稱。從Routines表中查詢名稱為var的存儲函數(shù)的信息，代碼如下：
mysql> select * from information_schema.Routines where ROUTINE_NAME='var' AND ROUTINE_TYPE='PROCEDURE'\G
*************************** 1. row ***************************
SPECIFIC_NAME: var
ROUTINE_CATALOG: def
ROUTINE_SCHEMA: testdb
ROUTINE_NAME: var
ROUTINE_TYPE: PROCEDURE
DATA_TYPE:
CHARACTER_MAXIMUM_LENGTH: NULL
CHARACTER_OCTET_LENGTH: NULL
NUMERIC_PRECISION: NULL
NUMERIC_SCALE: NULL
DATETIME_PRECISION: NULL
CHARACTER_SET_NAME: NULL
COLLATION_NAME: NULL
DTD_IDENTIFIER: NULL
ROUTINE_BODY: SQL
ROUTINE_DEFINITION: begin
DECLARE var INT;
select count(*) into var from testdb.info;
end
EXTERNAL_NAME: NULL
EXTERNAL_LANGUAGE: NULL
PARAMETER_STYLE: SQL
IS_DETERMINISTIC: NO
SQL_DATA_ACCESS: MODIFIES SQL DATA
SQL_PATH: NULL
SECURITY_TYPE: INVOKER
CREATED: 2015-11-17 22:40:58
LAST_ALTERED: 2015-11-18 16:42:48
SQL_MODE: STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
ROUTINE_COMMENT:
DEFINER: root@localhost
CHARACTER_SET_CLIENT: utf8
COLLATION_CONNECTION: utf8_general_ci
DATABASE_COLLATION: utf8_general_ci
在information_schema數(shù)據(jù)庫下的Routines表中，存儲所有存儲過程和函數(shù)的定義。使用SELECT語句查詢Routines表中的存儲過程和函數(shù)的定義時，一定要使用ROUTINE_NAME字段指定存儲過程或函數(shù)的名稱。否則，將查詢出所有的存儲過程或函數(shù)的定義。如果有存儲過程和存儲函數(shù)名稱相同，則需要同時指定ROUTINE_TYPE字段表明查詢的是哪種類型的存儲程序。
修改存儲過程和函數(shù)
使用ALTER語句可以修改存儲過程或函數(shù)的特性，語法如下：
ALTER {PROCEDURE | FUNCTION} sp_name [characteristic…]
其中，sp_name參數(shù)表示存儲過程或函數(shù)的名稱，characteristic參數(shù)指定存儲函數(shù)的特性，可能的取值有：
CONTAINS SQL – 表示子程序包含SQL語句，但不包含讀或?qū)憯?shù)據(jù)的語句。
NO SQL – 表示子程序中不包含SQL語句。
READS SQL DATA – 表示子程序中包含讀數(shù)據(jù)的語句。
MODIFIES SQL DATA – 表示子程序中包含寫數(shù)據(jù)的語句。
SQL SECURITY { DEFINER | INVOKER } – 指明誰有權(quán)限來執(zhí)行。
DEFINER – 表示只有定義者自己才能夠執(zhí)行。
INVOKER – 表示調(diào)用者可以執(zhí)行。
COMMENT ‘string’ – 表示注釋信息。
下面修改存儲過程
mysql> delimiter $
mysql> ALTER PROCEDURE countproc
-> MODIFIES SQL DATA
-> SQL SECURITY INVOKER
-> $
mysql> delimiter ;
存儲過程中的代碼可以修改嗎？MySQL目前還不支持對已存在的存儲過程代碼的更改。如果必須要更改存儲過程，必須使用DROP語句刪除之后，再重新寫代碼。
轉(zhuǎn)自：http://www.ywnds.com/?p=3877
