.jpg)
mysql中創(chuàng)建存儲(chǔ)過(guò)程和函數(shù)分別使用create procedure和create function
使用CALL語(yǔ)句來(lái)調(diào)用存儲(chǔ)過(guò)程，存儲(chǔ)過(guò)程也可以調(diào)用其他存儲(chǔ)過(guò)程
函數(shù)可以從語(yǔ)句外調(diào)用，能返回標(biāo)量值
創(chuàng)建存儲(chǔ)過(guò)程
語(yǔ)法
CREATE?PROCEDURE?sp_name?([?proc_parameter?])?[?characteristics..]?routine_body
?proc_parameter指定存儲(chǔ)過(guò)程的參數(shù)列表，列表形式如下：
[IN|OUT|INOUT]?param_name?type
其中in表示輸入?yún)?shù)，out表示輸出參數(shù)，inout表示既可以輸入也可以輸出；param_name表示參數(shù)名稱；type表示參數(shù)的類型
該類型可以是MYSQL數(shù)據(jù)庫(kù)中的任意類型
有以下取值：
characteristic:? ????LANGUAGE?SQL? ??|?[NOT]?DETERMINISTIC? ??|?{?CONTAINS?SQL?|?NO?SQL?|?READS?SQL?DATA?|?MODIFIES?SQL?DATA?}? ??|?SQL?SECURITY?{?DEFINER?|?INVOKER?}? ??|?COMMENT?'string'? routine_body:? ????Valid?SQL?procedure?statement?or?statements
LANGUAGE SQL ：說(shuō)明routine_body部分是由SQL語(yǔ)句組成的，當(dāng)前系統(tǒng)支持的語(yǔ)言為SQL，SQL是LANGUAGE特性的唯一值
[NOT] DETERMINISTIC ：指明存儲(chǔ)過(guò)程執(zhí)行的結(jié)果是否正確。DETERMINISTIC 表示結(jié)果是確定的。每次執(zhí)行存儲(chǔ)過(guò)程時(shí)，相同的輸入會(huì)得到
相同的輸出。
[NOT] DETERMINISTIC 表示結(jié)果是不確定的，相同的輸入可能得到不同的輸出。如果沒(méi)有指定任意一個(gè)值，默認(rèn)為[NOT] DETERMINISTIC?
CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA：指明子程序使用SQL語(yǔ)句的限制。
CONTAINS SQL表明子程序包含SQL語(yǔ)句，但是不包含讀寫數(shù)據(jù)的語(yǔ)句；
NO SQL表明子程序不包含SQL語(yǔ)句；
READS SQL DATA：說(shuō)明子程序包含讀數(shù)據(jù)的語(yǔ)句；
MODIFIES SQL DATA表明子程序包含寫數(shù)據(jù)的語(yǔ)句。
默認(rèn)情況下，系統(tǒng)會(huì)指定為CONTAINS SQL
SQL SECURITY { DEFINER | INVOKER } ：指明誰(shuí)有權(quán)限來(lái)執(zhí)行。DEFINER 表示只有定義者才能執(zhí)行
INVOKER 表示擁有權(quán)限的調(diào)用者可以執(zhí)行。默認(rèn)情況下，系統(tǒng)指定為DEFINER?
COMMENT ‘string’ ：注釋信息，可以用來(lái)描述存儲(chǔ)過(guò)程或函數(shù)
routine_body是SQL代碼的內(nèi)容，可以用BEGIN…END來(lái)表示SQL代碼的開始和結(jié)束。
下面的語(yǔ)句創(chuàng)建一個(gè)查詢t1表全部數(shù)據(jù)的存儲(chǔ)過(guò)程
DROP?PROCEDURE?IF?EXISTS?Proc;? DELIMITER?// CREATE?PROCEDURE?Proc()? BEGIN ??SELECT?*?FROM?t3; END// DELIMITER?; CALL?Proc();
t3表是我們上一節(jié)創(chuàng)建的表
這里的邏輯是
1、先判斷是否有Proc() 這個(gè)存儲(chǔ)過(guò)程，有就drop掉
2、創(chuàng)建Proc() 存儲(chǔ)過(guò)程
3、執(zhí)行Proc()?存儲(chǔ)過(guò)程
注意：“DELIMITER //”語(yǔ)句的作用是將MYSQL的結(jié)束符設(shè)置為//，因?yàn)镸YSQL默認(rèn)的語(yǔ)句結(jié)束符為分號(hào);，為了避免與存儲(chǔ)過(guò)程中SQL語(yǔ)句結(jié)束符相沖突，需要使用DELIMITER 改變存儲(chǔ)過(guò)程的結(jié)束符，并以“END//”結(jié)束存儲(chǔ)過(guò)程。存儲(chǔ)過(guò)程定義完畢之后再使用DELIMITER ;恢復(fù)默認(rèn)結(jié)束符。DELIMITER 也可以指定其他符號(hào)為結(jié)束符！！！！！！！！！
?如果你是這樣寫的話，就會(huì)得到如下錯(cuò)誤，初學(xué)者很容易犯這個(gè)錯(cuò)誤，包括本人
CREATE?PROCEDURE?Proc()? BEGIN ??SELECT?*?FROM?t3; END Query:?CREATE?PROCEDURE?Proc()?BEGIN?SELECT?*?FROM?t3 Error?Code:?1064 You?have?an?error?in?your?SQL?syntax;? check?the?manual?that?corresponds?to?your?MySQL?server?version?for?the?right?syntax?to?use?near?''?at?line?3 Execution?Time?:?0?sec Transfer?Time??:?0?sec Total?Time?????:?0.001?sec --------------------------------------------------- Query:?END? Error?Code:?1064
創(chuàng)建名為CountProc的存儲(chǔ)過(guò)程，代碼如下：
DROP?PROCEDURE?IF?EXISTS?CountProc;? DELIMITER?// CREATE?PROCEDURE?CountProc(OUT?param1?INT) BEGIN SELECT????COUNT(*)?INTO??param1?FROM?s_visit_log; END// DELIMITER?; CALL?CountProc(@aaa); SELECT?@aaa;
上面代碼的作用是創(chuàng)建一個(gè)獲取t3表記錄數(shù)的存儲(chǔ)過(guò)程，名稱是CountProc，
COUNT(*)計(jì)算后把結(jié)果放入?yún)?shù)param1中。
注意：當(dāng)使用DELIMITER命令時(shí)，應(yīng)該避免使用反斜杠（）字符，因?yàn)榉葱备苁荕YSQL的轉(zhuǎn)義字符！！！
存儲(chǔ)函數(shù)
創(chuàng)建存儲(chǔ)函數(shù)，需要使用CREATE FUNCTION語(yǔ)句，基本語(yǔ)法如下：
CREATE?FUNCTION?func_name([func_parameter]) RETURNS?TYPE [characteristics...]?routine_body
CREATE FUNCTION為用來(lái)創(chuàng)建存儲(chǔ)函數(shù)的關(guān)鍵字；func_name表示存儲(chǔ)函數(shù)的名稱
func_parameter為存儲(chǔ)函數(shù)的參數(shù)列表，參數(shù)列表如下
[IN|OUT|INOUT]PARAM_NAMETYPE
其中，IN表示輸入?yún)?shù)，OUT表示輸出參數(shù)，INOUT表示既可以輸入也可以輸出；
param_name表示參數(shù)名稱；type表示參數(shù)類型，該類型可以是MYSQL數(shù)據(jù)庫(kù)中的任意類型
RETURNS TYPE語(yǔ)句表示函數(shù)返回?cái)?shù)據(jù)的類型；characteristics：指定存儲(chǔ)函數(shù)的特性，取值與創(chuàng)建存儲(chǔ)過(guò)程時(shí)相同
創(chuàng)建存儲(chǔ)函數(shù)，名稱為NameByT，該函數(shù)返回SELECT語(yǔ)句的查詢結(jié)果，數(shù)值類型為字符串型
DELIMITER?// CREATE?FUNCTION?NameByT() RETURNS?CHAR(50) RETURN?(SELECT?NAME?FROM?t3?WHERE?id=2); // DELIMITER?;
注意：RETURNS CHAR(50)數(shù)據(jù)類型的時(shí)候，RETURNS 是有S的，而RETURN (SELECT NAME FROM t3 WHERE id=2)的時(shí)候RETURN是沒(méi)有S的
所以有時(shí)候大家可能覺(jué)得MYSQL很煩，誰(shuí)不知是自己寫錯(cuò)了
這里有一個(gè)方法，就是利用SQLYOG的代碼格式化功能，選中要格式化的代碼，然后按F12，如果能格式化，證明你的代碼沒(méi)有問(wèn)題，如果不能格式化
證明你寫的代碼有問(wèn)題！！！
不加s的話就會(huì)出現(xiàn)語(yǔ)法錯(cuò)誤了
Query:?create?function?NameByT()?return?char(50)?return?(select?name?from?t3?where?id=2) Error?Code:?1064 You?have?an?error?in?your?SQL?syntax;?check?the?manual?that?corresponds?to?your?MySQL?server?version?for?the?right?syntax?to?use?near?'return?char(50) return?(select?name?from?t3?where?id=2)'?at?line?2 Execution?Time?:?0?sec Transfer?Time??:?0?sec Total?Time?????:?0.003?sec -----------------------------
調(diào)用函數(shù)
SELECT?nameByT()
如果在存儲(chǔ)函數(shù)中的RETURN語(yǔ)句返回一個(gè)類型不同于函數(shù)的RETURNS子句中指定類型的值，返回值將被強(qiáng)制轉(zhuǎn)換為恰當(dāng)?shù)念愋汀?/p>
例如，如果一個(gè)函數(shù)返回一個(gè)SET或ENUM值，但是RETURN語(yǔ)句返回一個(gè)整數(shù)，對(duì)于SET成員集的相應(yīng)ENUM成員，從函數(shù)返回的值
是字符串。
指定參數(shù)為IN、OUT、INOUT只對(duì)PROCEDURE是合法的。
（FUNCTION中總是默認(rèn)是IN參數(shù)）RETURNS子句對(duì)FUNCTION做指定，對(duì)函數(shù)而言這是強(qiáng)制的。
他用來(lái)指定函數(shù)的返回類型，而且函數(shù)體必須包含一個(gè)RETURN value語(yǔ)句
變量的使用
變量可以在子程序中聲明并使用，這些變量的作用范圍是在BEGIN…END程序中
1、定義變量
在存儲(chǔ)過(guò)程中定義變量
DECLARE?var_name[,varname]...date_type[DEFAULT?VALUE];
var_name為局部變量的名稱。DEFAULT VALUE子句給變量提供一個(gè)默認(rèn)值。值除了可以被聲明為一個(gè)常數(shù)外，還可以被指定為一個(gè)表達(dá)式。
如果沒(méi)有DEFAULT子句，初始值為NULL
DECLARE?MYPARAM?INT?DEFAULT?100;
2、為變量賦值
定義變量之后，為變量賦值可以改變變量的默認(rèn)值，MYSQL中使用SET語(yǔ)句為變量賦值
SET?var_name=expr[,var_name=expr]...
在存儲(chǔ)過(guò)程中的SET語(yǔ)句是一般SET語(yǔ)句的擴(kuò)展版本。
被SET的變量可能是子程序內(nèi)的變量，或者是全局服務(wù)器變量，如系統(tǒng)變量或者用戶變量
他運(yùn)行SET a=x,b=y,….
聲明3個(gè)變量，分別為var1，var2和var3
DECLARE?var1,var2,var3?INT; SET?var1=10,var2=20; SET?var3=var1+var2;
?MYSQL中還可以通過(guò)SELECT…INTO為一個(gè)或多個(gè)變量賦值
DECLARE?NAME?CHAR(50); DECLARE?id?DECIMAL(8,2); SELECT?id,NAME?INTO?id?,NAME?FROM?t3?WHERE?id=2;
定義條件和處理程序
特定條件需要特定處理。這些條件可以聯(lián)系到錯(cuò)誤，以及子程序中的一般流程控制。定義條件是事先定義程序執(zhí)行過(guò)程中遇到的問(wèn)題，
處理程序定義了在遇到這些問(wèn)題時(shí)候應(yīng)當(dāng)采取的處理方式，并且保證存儲(chǔ)過(guò)程或函數(shù)在遇到警告或錯(cuò)誤時(shí)能繼續(xù)執(zhí)行。
這樣可以增強(qiáng)存儲(chǔ)程序處理問(wèn)題的能力，避免程序異常停止運(yùn)行
1、定義條件
DECLARE?condition_name?CONDITION?FOR[condition_type] [condition_type]: SQLSTATE[VALUE]?sqlstate_value?|mysql_error_code
condition_name：表示條件名稱
condition_type：表示條件的類型
sqlstate_value和mysql_error_code都可以表示mysql錯(cuò)誤
sqlstate_value為長(zhǎng)度5的字符串錯(cuò)誤代碼
mysql_error_code為數(shù)值類型錯(cuò)誤代碼，例如：ERROR1142(42000)中，sqlstate_value的值是42000，
mysql_error_code的值是1142
這個(gè)語(yǔ)句指定需要特殊處理?xiàng)l件。他將一個(gè)名字和指定的錯(cuò)誤條件關(guān)聯(lián)起來(lái)。
這個(gè)名字隨后被用在定義處理程序的DECLARE HANDLER語(yǔ)句中
定義ERROR1148(42000)錯(cuò)誤，名稱為command_not_allowed。
可以用兩種方法定義
//方法一：使用sqlstate_value DECLARE?command_not_allowed?CONDITION?FOR?SQLSTATE?'42000' //方法二：使用mysql_error_code DECLARE?command_not_allowed?CONDITION?FOR?SQLSTATE?1148
2．定義處理程序
MySQL中可以使用DECLARE關(guān)鍵字來(lái)定義處理程序。其基本語(yǔ)法如下：
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參數(shù)指明錯(cuò)誤的處理方式，該參數(shù)有3個(gè)取值。這3個(gè)取值分別是CONTINUE、EXIT和UNDO。
CONTINUE表示遇到錯(cuò)誤不進(jìn)行處理，繼續(xù)向下執(zhí)行；
EXIT表示遇到錯(cuò)誤后馬上退出；
UNDO表示遇到錯(cuò)誤后撤回之前的操作，MySQL中暫時(shí)還不支持這種處理方式。
注意：通常情況下，執(zhí)行過(guò)程中遇到錯(cuò)誤應(yīng)該立刻停止執(zhí)行下面的語(yǔ)句，并且撤回前面的操作。
但是，MySQL中現(xiàn)在還不能支持UNDO操作。
因此，遇到錯(cuò)誤時(shí)最好執(zhí)行EXIT操作。如果事先能夠預(yù)測(cè)錯(cuò)誤類型，并且進(jìn)行相應(yīng)的處理，那么可以執(zhí)行CONTINUE操作。
condition_value參數(shù)指明錯(cuò)誤類型，該參數(shù)有6個(gè)取值。
sqlstate_value和mysql_error_code與條件定義中的是同一個(gè)意思。
condition_name是DECLARE定義的條件名稱。
SQLWARNING表示所有以01開頭的sqlstate_value值。
NOT FOUND表示所有以02開頭的sqlstate_value值。
SQLEXCEPTION表示所有沒(méi)有被SQLWARNING或NOT FOUND捕獲的sqlstate_value值。
sp_statement表示一些存儲(chǔ)過(guò)程或函數(shù)的執(zhí)行語(yǔ)句。
下面是定義處理程序的幾種方式。代碼如下：
//方法一：捕獲sqlstate_value? DECLARE?CONTINUE?HANDLER?FOR?SQLSTATE?'42000' SET?@info='CAN?NOT?FIND';? //方法二：捕獲mysql_error_code? DECLARE?CONTINUE?HANDLER?FOR?1148SET?@info='CAN?NOT?FIND';? //方法三：先定義條件，然后調(diào)用? DECLARE?can_not_find?CONDITION?FOR?1146?;? DECLARE?CONTINUE?HANDLER?FOR?can_not_find?SET? @info='CAN?NOT?FIND';? //方法四：使用SQLWARNING? DECLARE?EXIT?HANDLER?FOR?SQLWARNING?SET?@info='ERROR';? //方法五：使用NOT?FOUND? DECLARE?EXIT?HANDLER?FOR?NOT?FOUND?SET?@info='CAN?NOT?FIND';? //方法六：使用SQLEXCEPTION? DECLARE?EXIT?HANDLER?FOR?SQLEXCEPTION?SET?@info='ERROR';
上述代碼是6種定義處理程序的方法。
第一種方法是捕獲sqlstate_value值。如果遇到sqlstate_value值為42000，執(zhí)行CONTINUE操作，并且輸出”CAN NOT FIND”信息。
第二種方法是捕獲mysql_error_code值。如果遇到mysql_error_code值為1148，執(zhí)行CONTINUE操作，并且輸出”CAN NOT FIND”信息。
第三種方法是先定義條件，然后再調(diào)用條件。這里先定義can_not_find條件，遇到1148錯(cuò)誤就執(zhí)行CONTINUE操作。
第四種方法是使用SQLWARNING。SQLWARNING捕獲所有以01開頭的sqlstate_value值，然后執(zhí)行EXIT操作，并且輸出”ERROR”信息。
第五種方法是使用NOT FOUND。NOT FOUND捕獲所有以02開頭的sqlstate_value值，然后執(zhí)行EXIT操作，并且輸出”CAN NOT FIND”信息。
第六種方法是使用SQLEXCEPTION。SQLEXCEPTION捕獲所有沒(méi)有被SQLWARNING或NOT FOUND捕獲的sqlstate_value值，然后執(zhí)行EXIT操作，并且輸出”ERROR”信息
定義條件和處理程序
CREATE?TABLE?t8(s1?INT,PRIMARY?KEY(s1)) DELIMITER?// CREATE?PROCEDURE?handlerdemo() BEGIN DECLARE?CONTINUE?HANDLER?FOR?SQLSTATE?'23000'?SET?@X2=1; SET?@X=1; INSERT?INTO?t8?VALUES(1); SET?@X=2; INSERT?INTO?t8?VALUES(1); SET?@X=3; END; // DELIMITER?; /*?調(diào)用存儲(chǔ)過(guò)程*/ CALL?handlerdemo(); /*?查看調(diào)用存儲(chǔ)過(guò)程結(jié)果*/ SELECT?@X
?
@X是一個(gè)用戶變量，執(zhí)行結(jié)果@X等于3，這表明MYSQL執(zhí)行到程序的末尾。
如果DECLARE CONTINUE HANDLER FOR SQLSTATE ‘23000’ SET @X2=1;，這一行不存在
第二個(gè)INSERT因PRIMARY KEY約束而失敗之后，MYSQL可能已經(jīng)采取EXIT策略，并且SELECT?@X可能已經(jīng)返回2
注意：@X表示用戶變量，使用SET語(yǔ)句為其賦值，用戶變量與連接有關(guān)，一個(gè)客戶端定義的變量不能被其他客戶端所使用
即有作用域的，該客戶端退出時(shí)，客戶端連接的所有變量將自動(dòng)釋放
這里的變量跟SQLSERVER沒(méi)有什么區(qū)別，都是用來(lái)存儲(chǔ)臨時(shí)值的
MYSQL這里的條件和預(yù)定義程序其實(shí)跟SQLSERVER的自定義錯(cuò)誤是一樣的
光標(biāo)
MYSQL里叫光標(biāo)，SQLSERVER里叫游標(biāo)，實(shí)際上一樣的
查詢語(yǔ)句可能查詢出多條記錄，在存儲(chǔ)過(guò)程和函數(shù)中使用光標(biāo)來(lái)逐條讀取查詢結(jié)果集中的記錄。
光標(biāo)的使用包括聲明光標(biāo)、打開光標(biāo)、使用光標(biāo)和關(guān)閉光標(biāo)。光標(biāo)必須聲明在處理程序之前，并且聲明在變量和條件之后。
1．聲明光標(biāo)
MySQL中使用DECLARE關(guān)鍵字來(lái)聲明光標(biāo)。其語(yǔ)法的基本形式如下：?
DECLARE?cursor_name?CURSOR?FOR?select_statement?;
其中，cursor_name參數(shù)表示光標(biāo)的名稱；select_statement參數(shù)表示SELECT語(yǔ)句的內(nèi)容，返回一個(gè)用于創(chuàng)建光標(biāo)的結(jié)果集
下面聲明一個(gè)名為cur_employee的光標(biāo)。代碼如下：
DECLARE?cur_employee?CURSOR?FOR?SELECT?name,?age?FROM?employee?;
上面的示例中，光標(biāo)的名稱為cur_employee；SELECT語(yǔ)句部分是從employee表中查詢出name和age字段的值。
2．打開光標(biāo)
MySQL中使用OPEN關(guān)鍵字來(lái)打開光標(biāo)。其語(yǔ)法的基本形式如下：
OPEN??cursor_name?;
其中，cursor_name參數(shù)表示光標(biāo)的名稱。
下面打開一個(gè)名為cur_employee的光標(biāo)，代碼如下：?
OPEN??cur_employee?;
3．使用光標(biāo)
MySQL中使用FETCH關(guān)鍵字來(lái)使用光標(biāo)。其語(yǔ)法的基本形式如下：
FETCH?cur_employee?INTO?var_name[,var_name…]?;
其中，cursor_name參數(shù)表示光標(biāo)的名稱；var_name參數(shù)表示將光標(biāo)中的SELECT語(yǔ)句查詢出來(lái)的信息存入該參數(shù)中。var_name必須在聲明光標(biāo)之前就定義好。
下面使用一個(gè)名為cur_employee的光標(biāo)。將查詢出來(lái)的數(shù)據(jù)存入emp_name和emp_age這兩個(gè)變量中，代碼如下：
FETCH??cur_employee?INTO?emp_name,?emp_age?;
上面的示例中，將光標(biāo)cur_employee中SELECT語(yǔ)句查詢出來(lái)的信息存入emp_name和emp_age中。emp_name和emp_age必須在前面已經(jīng)定義。
4．關(guān)閉光標(biāo)
MySQL中使用CLOSE關(guān)鍵字來(lái)關(guān)閉光標(biāo)。其語(yǔ)法的基本形式如下：
CLOSE??cursor_name?;
其中，cursor_name參數(shù)表示光標(biāo)的名稱。
【示例14-11】 下面關(guān)閉一個(gè)名為cur_employee的光標(biāo)。代碼如下：
CLOSE??cur_employee?;
上面的示例中，關(guān)閉了這個(gè)名稱為cur_employee的光標(biāo)。關(guān)閉之后就不能使用FETCH來(lái)使用光標(biāo)了。
注意：MYSQL中，光標(biāo)只能在存儲(chǔ)過(guò)程和函數(shù)中使用！！
到目前為止存儲(chǔ)函數(shù)，存儲(chǔ)過(guò)程、變量、條件、預(yù)定義程序、光標(biāo)跟SQLSERVER差不多，只不過(guò)語(yǔ)法不同，結(jié)構(gòu)不同
剛開始的時(shí)候會(huì)有不適應(yīng)
流程控制的使用
存儲(chǔ)過(guò)程和函數(shù)中可以使用流程控制來(lái)控制語(yǔ)句的執(zhí)行。
MySQL中可以使用IF語(yǔ)句、CASE語(yǔ)句、LOOP語(yǔ)句、LEAVE語(yǔ)句、ITERATE語(yǔ)句、REPEAT語(yǔ)句和WHILE語(yǔ)句來(lái)進(jìn)行流程控制。
每個(gè)流程中可能包含一個(gè)單獨(dú)語(yǔ)句，或者是使用BEGIN…END構(gòu)造的復(fù)合語(yǔ)句，構(gòu)造可以被嵌套
1．IF語(yǔ)句
IF語(yǔ)句用來(lái)進(jìn)行條件判斷。根據(jù)是否滿足條件，將執(zhí)行不同的語(yǔ)句。其語(yǔ)法的基本形式如下：
IF?search_condition?THEN?statement_list? [ELSEIF?search_condition?THEN?statement_list]?...? [ELSE?statement_list]? END?IF
其中，search_condition參數(shù)表示條件判斷語(yǔ)句；statement_list參數(shù)表示不同條件的執(zhí)行語(yǔ)句。
注意：MYSQL還有一個(gè)IF()函數(shù)，他不同于這里描述的IF語(yǔ)句
下面是一個(gè)IF語(yǔ)句的示例。代碼如下：?
IF?age>20?THEN?SET?@count1=@count1+1;?? ELSEIF?age=20?THEN?SET?@count2=@count2+1;?? ELSE?SET?@count3=@count3+1;?? END?IF;
該示例根據(jù)age與20的大小關(guān)系來(lái)執(zhí)行不同的SET語(yǔ)句。
如果age值大于20，那么將count1的值加1；如果age值等于20，那么將count2的值加1；
其他情況將count3的值加1。IF語(yǔ)句都需要使用END IF來(lái)結(jié)束。
2．CASE語(yǔ)句
CASE語(yǔ)句也用來(lái)進(jìn)行條件判斷，其可以實(shí)現(xiàn)比IF語(yǔ)句更復(fù)雜的條件判斷。CASE語(yǔ)句的基本形式如下：
CASE?case_value? WHEN?when_value?THEN?statement_list? [WHEN?when_value?THEN?statement_list]?...? [ELSE?statement_list]? END?CASE
其中，case_value參數(shù)表示條件判斷的變量；
when_value參數(shù)表示變量的取值；
statement_list參數(shù)表示不同when_value值的執(zhí)行語(yǔ)句。
CASE語(yǔ)句還有另一種形式。該形式的語(yǔ)法如下：
CASE? WHEN?search_condition?THEN?statement_list? [WHEN?search_condition?THEN?statement_list]?...? [ELSE?statement_list]? END?CASE
其中，search_condition參數(shù)表示條件判斷語(yǔ)句；
statement_list參數(shù)表示不同條件的執(zhí)行語(yǔ)句。
下面是一個(gè)CASE語(yǔ)句的示例。代碼如下：
CASE?age? WHEN?20?THEN?SET?@count1=@count1+1;? ELSE?SET?@count2=@count2+1;? END?CASE?;
代碼也可以是下面的形式：
CASE? WHEN?age=20?THEN?SET?@count1=@count1+1;? ELSE?SET?@count2=@count2+1;? END?CASE?;
本示例中，如果age值為20，count1的值加1；否則count2的值加1。CASE語(yǔ)句都要使用END CASE結(jié)束。
注意：這里的CASE語(yǔ)句和“控制流程函數(shù)”里描述的SQL CASE表達(dá)式的CASE語(yǔ)句有輕微不同。這里的CASE語(yǔ)句不能有ELSE NULL子句
并且用END CASE替代END來(lái)終止！！
3．LOOP語(yǔ)句
LOOP語(yǔ)句可以使某些特定的語(yǔ)句重復(fù)執(zhí)行，實(shí)現(xiàn)一個(gè)簡(jiǎn)單的循環(huán)。
但是LOOP語(yǔ)句本身沒(méi)有停止循環(huán)的語(yǔ)句，必須是遇到LEAVE語(yǔ)句等才能停止循環(huán)。
LOOP語(yǔ)句的語(yǔ)法的基本形式如下：
[begin_label:]?LOOP? statement_list? END?LOOP?[end_label]
其中，begin_label參數(shù)和end_label參數(shù)分別表示循環(huán)開始和結(jié)束的標(biāo)志，這兩個(gè)標(biāo)志必須相同，而且都可以省略；
statement_list參數(shù)表示需要循環(huán)執(zhí)行的語(yǔ)句。
下面是一個(gè)LOOP語(yǔ)句的示例。代碼如下：
add_num:?LOOP?? SET?@count=@count+1;?? END?LOOP?add_num?;
該示例循環(huán)執(zhí)行count加1的操作。因?yàn)闆](méi)有跳出循環(huán)的語(yǔ)句，這個(gè)循環(huán)成了一個(gè)死循環(huán)。
LOOP循環(huán)都以END LOOP結(jié)束。
4．LEAVE語(yǔ)句
LEAVE語(yǔ)句主要用于跳出循環(huán)控制。其語(yǔ)法形式如下：
LEAVE?label
其中，label參數(shù)表示循環(huán)的標(biāo)志。
下面是一個(gè)LEAVE語(yǔ)句的示例。代碼如下：?
add_num:?LOOP? SET?@count=@count+1;? IF?@count=100?THEN? LEAVE?add_num?;? END?LOOP?add_num?;
該示例循環(huán)執(zhí)行count加1的操作。當(dāng)count的值等于100時(shí)，則LEAVE語(yǔ)句跳出循環(huán)。
5．ITERATE語(yǔ)句
ITERATE語(yǔ)句也是用來(lái)跳出循環(huán)的語(yǔ)句。但是，ITERATE語(yǔ)句是跳出本次循環(huán)，然后直接進(jìn)入下一次循環(huán)。
ITERATE語(yǔ)句只可以出現(xiàn)在LOOP、REPEAT、WHILE語(yǔ)句內(nèi)。
ITERATE語(yǔ)句的基本語(yǔ)法形式如下：
ITERATE?label
其中，label參數(shù)表示循環(huán)的標(biāo)志。
下面是一個(gè)ITERATE語(yǔ)句的示例。代碼如下：
add_num:?LOOP? SET?@count=@count+1;? IF?@count=100?THEN? LEAVE?add_num?;? ELSE?IF?MOD(@count,3)=0?THEN? ITERATE?add_num;? SELECT?*?FROM?employee?;? END?LOOP?add_num?;
該示例循環(huán)執(zhí)行count加1的操作，count值為100時(shí)結(jié)束循環(huán)。如果count的值能夠整除3，則跳出本次循環(huán)，不再執(zhí)行下面的SELECT語(yǔ)句。
說(shuō)明：LEAVE語(yǔ)句和ITERATE語(yǔ)句都用來(lái)跳出循環(huán)語(yǔ)句，但兩者的功能是不一樣的。
LEAVE語(yǔ)句是跳出整個(gè)循環(huán)，然后執(zhí)行循環(huán)后面的程序。而ITERATE語(yǔ)句是跳出本次循環(huán)，然后進(jìn)入下一次循環(huán)。
使用這兩個(gè)語(yǔ)句時(shí)一定要區(qū)分清楚。
6．REPEAT語(yǔ)句
REPEAT語(yǔ)句是有條件控制的循環(huán)語(yǔ)句。當(dāng)滿足特定條件時(shí)，就會(huì)跳出循環(huán)語(yǔ)句。REPEAT語(yǔ)句的基本語(yǔ)法形式如下：
[begin_label:]?REPEAT? statement_list? UNTIL?search_condition? END?REPEAT?[end_label]
其中，statement_list參數(shù)表示循環(huán)的執(zhí)行語(yǔ)句；search_condition參數(shù)表示結(jié)束循環(huán)的條件，滿足該條件時(shí)循環(huán)結(jié)束。
下面是一個(gè)ITERATE語(yǔ)句的示例。代碼如下：
REPEAT? SET?@count=@count+1;? UNTIL?@count=100? END?REPEAT?;
該示例循環(huán)執(zhí)行count加1的操作，count值為100時(shí)結(jié)束循環(huán)。
REPEAT循環(huán)都用END REPEAT結(jié)束。
7．WHILE語(yǔ)句
WHILE語(yǔ)句也是有條件控制的循環(huán)語(yǔ)句。但WHILE語(yǔ)句和REPEAT語(yǔ)句是不一樣的。
WHILE語(yǔ)句是當(dāng)滿足條件時(shí)，執(zhí)行循環(huán)內(nèi)的語(yǔ)句。
WHILE語(yǔ)句的基本語(yǔ)法形式如下：
[begin_label:]?WHILE?search_condition?DO? statement_list? END?WHILE?[end_label]
其中，search_condition參數(shù)表示循環(huán)執(zhí)行的條件，滿足該條件時(shí)循環(huán)執(zhí)行；
statement_list參數(shù)表示循環(huán)的執(zhí)行語(yǔ)句。
下面是一個(gè)ITERATE語(yǔ)句的示例。代碼如下：
WHILE?@count<p>該示例循環(huán)執(zhí)行count加1的操作，count值小于100時(shí)執(zhí)行循環(huán)。</p><p>如果count值等于100了，則跳出循環(huán)。WHILE循環(huán)需要使用END WHILE來(lái)結(jié)束。</p><hr><p><span style="color:#ff9900"><strong>調(diào)用存儲(chǔ)過(guò)程和函數(shù)</strong></span></p><p>存儲(chǔ)過(guò)程和存儲(chǔ)函數(shù)都是存儲(chǔ)在服務(wù)器端的SQL語(yǔ)句的集合，要使用這些已經(jīng)定義好的存儲(chǔ)過(guò)程和存儲(chǔ)函數(shù)就必須要通過(guò)調(diào)用的方式來(lái)實(shí)現(xiàn)</p><p>存儲(chǔ)過(guò)程是通過(guò)CALL語(yǔ)句來(lái)調(diào)用的。而存儲(chǔ)函數(shù)的使用方法與MySQL內(nèi)部函數(shù)的使用方法是一樣的</p><p>執(zhí)行存儲(chǔ)過(guò)程和存儲(chǔ)函數(shù)需要擁有EXECUTE權(quán)限</p><p>EXECUTE權(quán)限的信息存儲(chǔ)在information_schema數(shù)據(jù)庫(kù)下面的<strong>USER_PRIVILEGES</strong>表中</p><p>調(diào)用存儲(chǔ)過(guò)程</p><p>MySQL中使用CALL語(yǔ)句來(lái)調(diào)用存儲(chǔ)過(guò)程。調(diào)用存儲(chǔ)過(guò)程后，數(shù)據(jù)庫(kù)系統(tǒng)將執(zhí)行存儲(chǔ)過(guò)程中的語(yǔ)句。</p><p>然后，將結(jié)果返回給輸出值。</p><p>CALL語(yǔ)句的基本語(yǔ)法形式如下：</p><pre class="brush:sql;toolbar:false;">CALL?sp_name([parameter[,…]])?;
其中，sp_name是存儲(chǔ)過(guò)程的名稱；parameter是指存儲(chǔ)過(guò)程的參數(shù)。
CALL?proc()
調(diào)用存儲(chǔ)函數(shù)
在MySQL中，存儲(chǔ)函數(shù)的使用方法與MySQL內(nèi)部函數(shù)的使用方法是一樣的。
換言之，用戶自己定義的存儲(chǔ)函數(shù)與MySQL內(nèi)部函數(shù)是一個(gè)性質(zhì)的。
區(qū)別在于，存儲(chǔ)函數(shù)是用戶自己定義的，而內(nèi)部函數(shù)是MySQL的開發(fā)者定義的。
下面定義一個(gè)存儲(chǔ)函數(shù)，然后調(diào)用這個(gè)存儲(chǔ)函數(shù)。
代碼執(zhí)行如下：
--?創(chuàng)建存儲(chǔ)函數(shù)? DELIMITER?// CREATE?FUNCTION?name_from_t3(id?INT?) RETURNS?CHAR(80)?? RETURN?(SELECT??NAME??FROM??t3??WHERE??id=id?); // DELIMITER?; SELECT?name_from_t3(2);
上述存儲(chǔ)函數(shù)的作用是根據(jù)輸入的id值到t3表中查詢記錄。
查詢出id字段的值等于id的記錄。然后將該記錄的name字段的值返回。
查看存儲(chǔ)過(guò)程和函數(shù)
存儲(chǔ)過(guò)程和函數(shù)創(chuàng)建以后，可以查看存儲(chǔ)過(guò)程和函數(shù)的狀態(tài)和定義。
通過(guò)SHOW STATUS語(yǔ)句來(lái)查看存儲(chǔ)過(guò)程和函數(shù)的狀態(tài)，也可以通過(guò)SHOW CREATE語(yǔ)句來(lái)查看存儲(chǔ)過(guò)程和函數(shù)的定義。
通過(guò)查詢information_schema數(shù)據(jù)庫(kù)下的Routines表來(lái)查看存儲(chǔ)過(guò)程和函數(shù)的信息
1、SHOW STATUS語(yǔ)句查看存儲(chǔ)過(guò)程和函數(shù)的狀態(tài)
MySQL中可以通過(guò)SHOW STATUS語(yǔ)句查看存儲(chǔ)過(guò)程和函數(shù)的狀態(tài)。其基本語(yǔ)法形式如下：
SHOW?{?PROCEDURE?|?FUNCTION?}?STATUS?[?LIKE??'?pattern?'?]?;
其中，PROCEDURE參數(shù)表示查詢存儲(chǔ)過(guò)程；FUNCTION參數(shù)表示查詢存儲(chǔ)函數(shù)；
LIKE ‘ pattern ‘參數(shù)用來(lái)匹配存儲(chǔ)過(guò)程或函數(shù)的名稱。
下面查詢名為name_from_t3的函數(shù)的狀態(tài)。代碼執(zhí)行如下：
SHOW??FUNCTION?STATUS?LIKE?'%name_from_t3%' Db?????? Name?????????? Type?????? Definer????????? Modified????????????? Created?????????????? Security_type?? Comment?? character_set_client?? collation_connection?? Database?Collation ------?? ------------?? --------?? --------------?? -------------------?? -------------------?? -------------?? -------?? --------------------?? --------------------?? ------------------ school?? name_from_t3?? FUNCTION?? root@localhost?? 2014-06-21?18:52:39?? 2014-06-21?18:52:39?? DEFINER????????????????? utf8?????????????????? utf8_general_ci??????? utf8_general_ci
查詢結(jié)果顯示了函數(shù)的創(chuàng)建時(shí)間、修改時(shí)間和字符集等信息。
注意：SHOW STATUS語(yǔ)句只能查看存儲(chǔ)過(guò)程或函數(shù)是操作哪一個(gè)數(shù)據(jù)庫(kù)、存儲(chǔ)過(guò)程或函數(shù)的名稱、類型、誰(shuí)定義的、創(chuàng)建和修改時(shí)間、字符編碼等信息。
但是，這個(gè)語(yǔ)句不能查詢存儲(chǔ)過(guò)程或函數(shù)的具體定義。如果需要查看詳細(xì)定義，需要使用SHOW CREATE語(yǔ)句
2、SHOW CREATE語(yǔ)句查看存儲(chǔ)過(guò)程和函數(shù)的定義
MySQL中可以通過(guò)SHOW CREATE語(yǔ)句查看存儲(chǔ)過(guò)程和函數(shù)的狀態(tài)。其基本語(yǔ)法形式如下：
SHOW?CREATE?{?PROCEDURE?|?FUNCTION?}?sp_name?;
其中，PROCEDURE參數(shù)表示查詢存儲(chǔ)過(guò)程；
FUNCTION參數(shù)表示查詢存儲(chǔ)函數(shù)；
sp_name參數(shù)表示存儲(chǔ)過(guò)程或函數(shù)的名稱
下面查詢名為name_from_t3的函數(shù)的定義。代碼執(zhí)行如下
SHOW?CREATE?FUNCTION??name_from_t3 Function?????? sql_mode?? Create? Function??????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????character_set_client??collation_connection??Database?Collation ------------??--------??-----------------------------------------------------------------------------------------------------------------------------------------------??--------------------??--------------------??------------------ name_from_t3???????????? CREATE?DEFINER=`root`@`localhost`?FUNCTION?`name_from_t3`(id?INT?)?RETURNS?char(80)?CHARSET?utf8?????????????????????????????????????????????????utf8??????????????????utf8_general_ci???????utf8_general_ci??? ????????????????????????RETURN?(SELECT??NAME??FROM??t3??WHERE??id=id?)
3、從information_schema.Routines表中查看存儲(chǔ)過(guò)程和函數(shù)的信息
存儲(chǔ)過(guò)程和函數(shù)的信息存儲(chǔ)在information_schema數(shù)據(jù)庫(kù)下的Routines表中。可以通過(guò)查詢?cè)摫淼挠涗泚?lái)查詢存儲(chǔ)過(guò)程和函數(shù)的信息。
其基本語(yǔ)法形式如下：
SELECT?*?FROM?information_schema.Routines? WHERE?ROUTINE_NAME='?sp_name?'?;
其中，ROUTINE_NAME字段中存儲(chǔ)的是存儲(chǔ)過(guò)程和函數(shù)的名稱；
sp_name參數(shù)表示存儲(chǔ)過(guò)程或函數(shù)的名稱。
下面從Routines表中查詢名為name_from_t3函數(shù)的信息。
代碼執(zhí)行如下：
SELECT?*?FROM?information_schema.Routines??? WHERE?ROUTINE_NAME='name_from_t3'? SPECIFIC_NAME?? ROUTINE_CATALOG?? ROUTINE_SCHEMA?? ROUTINE_NAME?? ROUTINE_TYPE?? DATA_TYPE?? CHARACTER_MAXIMUM_LENGTH?? CHARACTER_OCTET_LENGTH?? NUMERIC_PRECISION?? NUMERIC_SCALE?? CHARACTER_SET_NAME?? COLLATION_NAME??? DTD_IDENTIFIER?? ROUTINE_BODY?? ROUTINE_DEFINITION?????????????????????????????? EXTERNAL_NAME?? EXTERNAL_LANGUAGE?? PARAMETER_STYLE?? IS_DETERMINISTIC?? SQL_DATA_ACCESS??SQL_PATH??SECURITY_TYPE??CREATED?????????????? LAST_ALTERED?????????SQL_MODE??ROUTINE_COMMENT??DEFINER????????? CHARACTER_SET_CLIENT??COLLATION_CONNECTION??DATABASE_COLLATION -------------??---------------??--------------??------------?? ------------??---------??------------------------??----------------------??-----------------??-------------? ?------------------??---------------??--------------??------------??----------------------------------------------??-------------??-----------------??---------------??----------------??---------------??--------??-------------??-------------------??-------------------??--------??---------------??--------------??--------------------??--------------------??------------------ name_from_t3???def?????????????? school?????????? name_from_t3??FUNCTION?????? char????????????????????????????? 80????????????????????? 240????????????? (NULL)????????? (NULL)??utf8???????????????? utf8_general_ci??char(80)????????SQL??????????? RETURN?(SELECT??NAME??FROM??t3??WHERE??id=id?)??(NULL)????????? (NULL)????????????? SQL?????????????? NO???????????????? CONTAINS?SQL????? (NULL)???? DEFINER???????? 2014-06-21?18:52:39?? 2014-06-21?18:52:39????????????????????????????? root@localhost??utf8?????????????????? utf8_general_ci??????? utf8_general_ci
查詢結(jié)果顯示name_from_t3的詳細(xì)信息。
注意：在information_schema數(shù)據(jù)庫(kù)下的Routines表中，存儲(chǔ)著所有存儲(chǔ)過(guò)程和函數(shù)的定義。
如果使用SELECT語(yǔ)句查詢Routines表中的存儲(chǔ)過(guò)程和函數(shù)的定義時(shí)，一定要使用ROUTINE_NAME字段指定存儲(chǔ)過(guò)程或函數(shù)的名稱。
否則，將查詢出所有的存儲(chǔ)過(guò)程或函數(shù)的定義。
修改存儲(chǔ)過(guò)程和函數(shù)
修改存儲(chǔ)過(guò)程和函數(shù)是指修改已經(jīng)定義好的存儲(chǔ)過(guò)程和函數(shù)。
MySQL中通過(guò)ALTER PROCEDURE語(yǔ)句來(lái)修改存儲(chǔ)過(guò)程。
通過(guò)ALTER FUNCTION語(yǔ)句來(lái)修改存儲(chǔ)函數(shù)。
MySQL中修改存儲(chǔ)過(guò)程和函數(shù)的語(yǔ)句的語(yǔ)法形式如下：
ALTER?{PROCEDURE?|?FUNCTION}?sp_name?[characteristic?...]?? characteristic:?? {?CONTAINS?SQL?|?NO?SQL?|?READS?SQL?DATA?|?MODIFIES?SQL?DATA?}?? |?SQL?SECURITY?{?DEFINER?|?INVOKER?}?? |?COMMENT?'string'
其中，sp_name參數(shù)表示存儲(chǔ)過(guò)程或函數(shù)的名稱；
characteristic參數(shù)指定存儲(chǔ)函數(shù)的特性。
CONTAINS SQL表示子程序包含SQL語(yǔ)句，但不包含讀或?qū)憯?shù)據(jù)的語(yǔ)句；
NO SQL表示子程序中不包含SQL語(yǔ)句；
READS SQL DATA表示子程序中包含讀數(shù)據(jù)的語(yǔ)句；
MODIFIES SQL DATA表示子程序中包含寫數(shù)據(jù)的語(yǔ)句。
SQL SECURITY { DEFINER | INVOKER }指明誰(shuí)有權(quán)限來(lái)執(zhí)行。
DEFINER表示只有定義者自己才能夠執(zhí)行；
INVOKER表示調(diào)用者可以執(zhí)行。
COMMENT ‘string’是注釋信息。
說(shuō)明：修改存儲(chǔ)過(guò)程使用ALTER PROCEDURE語(yǔ)句，修改存儲(chǔ)函數(shù)使用ALTER FUNCTION語(yǔ)句。
但是，這兩個(gè)語(yǔ)句的結(jié)構(gòu)是一樣的，語(yǔ)句中的所有參賽都是一樣的。
而且，它們與創(chuàng)建存儲(chǔ)過(guò)程或函數(shù)的語(yǔ)句中的參數(shù)也是基本一樣的。
修改存儲(chǔ)過(guò)程和函數(shù)，只能修改他們的權(quán)限，目前MYSQL還不提供對(duì)已存在的存儲(chǔ)過(guò)程和函數(shù)代碼的修改
如果要修改，只能通過(guò)先DROP掉，然后重新建立新的存儲(chǔ)過(guò)程和函數(shù)來(lái)實(shí)現(xiàn)
在SQLYOG里選中選中函數(shù)，然后右鍵ALTER FUNCTION的時(shí)候，也是這樣，先DROP掉，然后重新建立新的函數(shù)來(lái)實(shí)現(xiàn)
DELIMITER?$$ DROP?FUNCTION?IF?EXISTS?`name_from_t3`$$ CREATE?DEFINER=`root`@`localhost`?FUNCTION?`name_from_t3`(id?INT?)?RETURNS?CHAR(80)?CHARSET?utf8 RETURN?(SELECT??NAME??FROM??t3??WHERE??id=id?)$$ DELIMITER?;
刪除存儲(chǔ)過(guò)程和函數(shù)
刪除存儲(chǔ)過(guò)程和函數(shù)指刪除數(shù)據(jù)庫(kù)中已經(jīng)存在的存儲(chǔ)過(guò)程和函數(shù)。
MySQL中使用DROP?PROCEDURE語(yǔ)句來(lái)刪除存儲(chǔ)過(guò)程。通過(guò)DROP FUNCTION語(yǔ)句來(lái)刪除存儲(chǔ)函數(shù)。?
DROP?{?PROCEDURE|?FUNCTION?}?sp_name;
其中，sp_name參數(shù)表示存儲(chǔ)過(guò)程或函數(shù)的名稱
下面刪除存儲(chǔ)過(guò)程Proc和存儲(chǔ)函數(shù)name_from_t3。刪除存儲(chǔ)過(guò)程的代碼如下：
DROP??PROCEDURE??Proc;
刪除存儲(chǔ)函數(shù)name_from_employee的代碼如下：
DROP??FUNCTION??name_from_t3;
刪除完畢之后，查詢information_schema.Routines來(lái)確認(rèn)上面的刪除是否成功
SELECT?*?FROM?information_schema.Routines WHERE?ROUTINE_NAME='proc'?OR?ROUTINE_NAME='name_from_t3';
總結(jié)
TIPS:
存儲(chǔ)過(guò)程里面是可以調(diào)用其他存儲(chǔ)過(guò)程的，使用CALL語(yǔ)句調(diào)用其他存儲(chǔ)過(guò)程就可以了
存儲(chǔ)過(guò)程參數(shù)列表里的參數(shù)名盡量不要和數(shù)據(jù)庫(kù)中表的字段名一樣，否則有可能出錯(cuò)
存儲(chǔ)過(guò)程的參數(shù)可以使用中文，在定義存儲(chǔ)過(guò)程的時(shí)候加上character set gbk就可以了
例如?
CREATE?PROCEDURE?useInfo(IN?u_name?VARCHAR(50)?CHARACTER?SET?gbk,OUT?u_age?INT)
補(bǔ)充
DECLARE 語(yǔ)句要寫在存儲(chǔ)過(guò)程里面，否則會(huì)報(bào)錯(cuò)！！
DROP?PROCEDURE?IF?EXISTS?Proc; DELIMITER?// CREATE?PROCEDURE?Proc()? BEGIN DECLARE?command_not_allowed?CONDITION?FOR?SQLSTATE?'42000'?; END// DELIMITER?; CALL?Proc();
.png)
