《Mysql應(yīng)用MySQL存儲(chǔ)過(guò)程和函數(shù)的操作(十二)》要點(diǎn):
本文介紹了Mysql應(yīng)用MySQL存儲(chǔ)過(guò)程和函數(shù)的操作(十二),希望對(duì)您有用。如果有疑問(wèn),可以聯(lián)系我們。
MYSQL應(yīng)用數(shù)據(jù)庫(kù)對(duì)象表時(shí)存儲(chǔ)和操作數(shù)據(jù)的邏輯結(jié)構(gòu),而數(shù)據(jù)庫(kù)對(duì)象存儲(chǔ)過(guò)程和函數(shù),則是用來(lái)實(shí)現(xiàn)將一組關(guān)于表操作的sql語(yǔ)句當(dāng)作一個(gè)整體來(lái)執(zhí)行.在數(shù)據(jù)庫(kù)系統(tǒng)中,當(dāng)調(diào)用存儲(chǔ)過(guò)程和函數(shù)時(shí),則會(huì)執(zhí)行這些對(duì)象中所設(shè)置的sql語(yǔ)句組,從而實(shí)現(xiàn)相應(yīng)功能.
1. 為什么使用存儲(chǔ)過(guò)程和函數(shù)的操作?
??? 有時(shí)針對(duì)表的一個(gè)完整操作往往不是單條sql語(yǔ)句就可以實(shí)現(xiàn)的,而是需要一組sql語(yǔ)句來(lái)實(shí)現(xiàn).在具體應(yīng)用當(dāng)中,一個(gè)完整的操作會(huì)包含多條sql語(yǔ)句,在執(zhí)行過(guò)程中需要根據(jù)前面sql語(yǔ)句的執(zhí)行結(jié)果有選擇地執(zhí)行后面sql語(yǔ)句.
??? 存儲(chǔ)過(guò)程和函數(shù)可以簡(jiǎn)單理解為一條或多條sql語(yǔ)句的集合.存儲(chǔ)過(guò)程和函數(shù)就是事先經(jīng)過(guò)編譯并存儲(chǔ)在數(shù)據(jù)庫(kù)中的一段sql語(yǔ)句集合.
??? 存儲(chǔ)過(guò)程和函數(shù)有什么區(qū)別呢?這兩者的主要區(qū)別在于函數(shù)必須有返回值,而存儲(chǔ)過(guò)程則沒(méi)有.存儲(chǔ)過(guò)程的參數(shù)類(lèi)型遠(yuǎn)遠(yuǎn)多于函數(shù)的參數(shù)類(lèi)型.?
MYSQL應(yīng)用關(guān)于存儲(chǔ)過(guò)程和函數(shù)的優(yōu)點(diǎn)如下:
????? 1. 存儲(chǔ)過(guò)程和函數(shù)允許標(biāo)準(zhǔn)組件式編程,提高了sql語(yǔ)句的重用性、共享性和可移植性.
????? 2. 存儲(chǔ)過(guò)程和函數(shù)能夠?qū)崿F(xiàn)較快的執(zhí)行速度,能夠減少網(wǎng)絡(luò)流量.
????? 3. 存儲(chǔ)過(guò)程和函數(shù)可以作為一種安全機(jī)制來(lái)利用.?
MYSQL應(yīng)用關(guān)于存儲(chǔ)過(guò)程和函數(shù)的缺點(diǎn)如下:
????? 1. 存儲(chǔ)過(guò)程和函數(shù)的編寫(xiě)比單句sql語(yǔ)句復(fù)雜,需要用戶有更高的技能和更豐富的經(jīng)驗(yàn).
????? 2. 在編寫(xiě)存儲(chǔ)過(guò)程和函數(shù)時(shí),需要?jiǎng)?chuàng)建這些數(shù)據(jù)庫(kù)對(duì)象的權(quán)限.=
MYSQL應(yīng)用2. 創(chuàng)建存儲(chǔ)過(guò)程和函數(shù)?
2.1 創(chuàng)建存儲(chǔ)過(guò)程語(yǔ)法形式:
MYSQL應(yīng)用語(yǔ)法形式如下:
MYSQL應(yīng)用
create procedure procedure_name([procedure_parameter[,...]])
[characteristic...] routine_body
//說(shuō)明:procedure_name參數(shù)表示所要?jiǎng)?chuàng)建的存儲(chǔ)過(guò)程的名字,procedure_parameter參數(shù)表示存儲(chǔ)過(guò)程的參數(shù),
characteristic參數(shù)表示存儲(chǔ)過(guò)程的特性,routine_body參數(shù)表示存儲(chǔ)過(guò)程的sql語(yǔ)句代碼,可以用begin...end來(lái)標(biāo)志sql語(yǔ)句的開(kāi)始和結(jié)束.
//注意:在具體創(chuàng)建存儲(chǔ)過(guò)程時(shí),存儲(chǔ)過(guò)程名不能和已經(jīng)存在的存儲(chǔ)過(guò)程名重復(fù),推薦存儲(chǔ)過(guò)程名為procedure_xxx或者proce_xxx;
//procedure_parameter 中每個(gè)參數(shù)的語(yǔ)法形式為:
[IN|OUT|INOUT] parameter_name type
//該語(yǔ)句中每個(gè)參數(shù)由三部分組成,分別為輸入/輸出類(lèi)型、參數(shù)名和參數(shù)類(lèi)型.
MYSQL應(yīng)用characteristic參數(shù)的取值為:
language sql
|[not] deterministic
|{constains sql | no sql | reads sql data|modifies sql data}
|sql security {definer | invoker}
|comment 'string'
MYSQL應(yīng)用??? 1. language sql,表示存儲(chǔ)過(guò)程的routine_body部分由sql語(yǔ)言的語(yǔ)句組成.為mysql軟件所有默認(rèn)的語(yǔ)句.
??? 2. [not] deterministic,表示存儲(chǔ)過(guò)程的執(zhí)行結(jié)果是否確定.如果值是deterministic表示執(zhí)行結(jié)果是確定的.即每次執(zhí)行存儲(chǔ)過(guò)程時(shí),如果輸入相同的參數(shù)將得到相同的輸出;如果值為not deterministic,表示執(zhí)行結(jié)果不確定,即相同的輸入可能得到不同的輸出.默認(rèn)值為deterministic.
??? 3. {contains sql|no sql|reads sql data|modifies sql data},表示sql語(yǔ)句的限制,如果值為contains sql表示可以包含sql語(yǔ)句,但不包含讀或?qū)憯?shù)據(jù)的語(yǔ)句;如果值為no sql表示不包含sql語(yǔ)句;如果值為reads sql data表示包含讀數(shù)據(jù)的語(yǔ)句;如果值為modifies sql data表示包含讀數(shù)據(jù)的語(yǔ)句.默認(rèn)值為contains sql.
??? 4. sql security{definer|invoker},設(shè)置誰(shuí)有權(quán)限來(lái)執(zhí)行.如果值為definer,表示只有定義者才能執(zhí)行,如果值為invoker表示調(diào)用者可以執(zhí)行.默認(rèn)值為definer.
??? 5. comment ‘string', 表示注釋語(yǔ)句.?
MYSQL應(yīng)用2.2 創(chuàng)建函數(shù)語(yǔ)法形式:
MYSQL應(yīng)用語(yǔ)法形式如下:
MYSQL應(yīng)用
create function function_name([function_parameter[,...]])
[characteristic...] routine_body
MYSQL應(yīng)用??? 上述語(yǔ)句中,function_name參數(shù)表示所要?jiǎng)?chuàng)建的函數(shù)的名字;function_parameter參數(shù)表示函數(shù)的參數(shù),characteristic參數(shù)表示函數(shù)的特性,該參數(shù)的取值與存儲(chǔ)過(guò)程中的取值相同.routine_body參數(shù)表示函數(shù)的sql語(yǔ)句代碼,可以用begin…end來(lái)表示sql語(yǔ)句的開(kāi)始和結(jié)束.
MYSQL應(yīng)用function_parameter中每個(gè)參數(shù)的語(yǔ)法形式如下:
parameter_name type
MYSQL應(yīng)用??? 在上述語(yǔ)句中每個(gè)參數(shù)由兩部分組成,分別為參數(shù)名和參數(shù)類(lèi)型.parameter_name表示參數(shù)名.type表示參數(shù)類(lèi)型.?
MYSQL應(yīng)用2.3 創(chuàng)建簡(jiǎn)單的存儲(chǔ)過(guò)程和函數(shù):
MYSQL應(yīng)用//查詢雇員表中所有雇員工資的存儲(chǔ)過(guò)程:
示例:
MYSQL應(yīng)用
mysql> delimiter $$
mysql> delimiter $$ create procedure proce_employee_sal()
comment '查詢所有雇員的工資'
begin
select sal from t_employee;
end $$
dilimiter ;
MYSQL應(yīng)用??? 通常在創(chuàng)建存儲(chǔ)過(guò)程時(shí),通過(guò)命令delimiter && 將sql語(yǔ)句的結(jié)束符由“;”符號(hào)修改成兩個(gè)美元符號(hào).這主要是因?yàn)閟ql語(yǔ)句中默認(rèn)語(yǔ)句結(jié)束符為分好(;),即存儲(chǔ)過(guò)程中的sql語(yǔ)句也需要用分號(hào)來(lái)結(jié)束,將結(jié)束符號(hào)修改成兩個(gè)美元符之后,就可以在執(zhí)行過(guò)程中避免沖突.不過(guò)最后不要忘記將通過(guò)命令“delimiter ;”將結(jié)束符修改為sql語(yǔ)句中默認(rèn)的結(jié)束符號(hào).
MYSQL應(yīng)用創(chuàng)建函數(shù)示例:
MYSQL應(yīng)用
delimiter $$
create function func_employee_sal (empno int(11))
returns double(10,2)
comment '查詢某個(gè)雇員的工資'
begin
return (
select sal from t_employee where t_employee.empno=empno;
)
end$$
delimiter ;
MYSQL應(yīng)用??? 創(chuàng)建了一個(gè)名為func_employee_sal的函數(shù),該函數(shù)擁有一個(gè)類(lèi)型為int(11),名為empno的參數(shù),返回值為double(10,2)類(lèi)型.select語(yǔ)句從t_employee表中查詢empnoo字段值等于所傳入?yún)?shù)empno值的記錄,同時(shí)將該條記錄的sal字段的值返回.
MYSQL應(yīng)用3. 關(guān)于存儲(chǔ)過(guò)程和函數(shù)的表達(dá)式?
MYSQL應(yīng)用3.1 操作變量:
??? 變量是表達(dá)式語(yǔ)句中最基本的元素,可以用來(lái)臨時(shí)存儲(chǔ)數(shù)據(jù).可以通過(guò)變量存儲(chǔ)從表中查詢到的數(shù)據(jù).?
MYSQL應(yīng)用??? 3.1.1 聲明變量:
MYSQL應(yīng)用語(yǔ)法形式如下:
declare var_name[,...] type [default value]
MYSQL應(yīng)用??? 在上述語(yǔ)句中,var_name參數(shù)表示要聲明的變量的名字;參數(shù)type表示所要聲明變量的類(lèi)型;default value用來(lái)實(shí)現(xiàn)設(shè)置變量的默認(rèn)值,如果無(wú)該語(yǔ)句默認(rèn)值為null.在具體聲明變量時(shí),可以同時(shí)定義多個(gè)變量.?
MYSQL應(yīng)用??? 3.1.2 賦值變量:
MYSQL應(yīng)用語(yǔ)法形式如下:
語(yǔ)法一:
set var_name=expr[,...]
語(yǔ)法二:
MYSQL應(yīng)用
select filed_name[,...] into var_name[,...]
from table_name
where condition
MYSQL應(yīng)用??? var_name參數(shù)表示所要賦值變量名字,參數(shù)expr是關(guān)于變量的賦值表達(dá)式.在為變量賦值時(shí),可以同時(shí)為多個(gè)變量賦值,各個(gè)變量的賦值語(yǔ)句之間用逗號(hào)隔開(kāi).
語(yǔ)法二中將查詢到的結(jié)果賦值給變量,參數(shù)filed_name表示查詢的字段名,參數(shù)var_name表示變量名.將查詢結(jié)果賦值給變量,該查詢語(yǔ)句的返回結(jié)果只能是單行.
MYSQL應(yīng)用示例:
MYSQL應(yīng)用
declare employee_sal int default 1000;
declare employee_sal int default 1000;
set employee_sal = 3500;
select sal into employee_sal from t_employee where empno=7556;
MYSQL應(yīng)用3.2 操作條件:
??? 3.2.1 定義條件:
MYSQL應(yīng)用語(yǔ)法形式如下:
MYSQL應(yīng)用
declare condition_name condition for condition_value
condition_value:
sqlstate[value] sqlstate_value
|mysql_error_code
MYSQL應(yīng)用condition_name參數(shù)表示所要定義的條件名稱(chēng);參數(shù)condition_value用來(lái)實(shí)現(xiàn)設(shè)置條件的類(lèi)型;參數(shù)sqlstate_value和mysql_error_code用來(lái)設(shè)置條件的錯(cuò)誤.?
MYSQL應(yīng)用??? 3.2.2 定義處理程序:
MYSQL應(yīng)用語(yǔ)法形式為:
MYSQL應(yīng)用
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
MYSQL應(yīng)用??? 這個(gè)語(yǔ)句指定每個(gè)可以處理一個(gè)或多個(gè)條件的處理程序.如果產(chǎn)生一個(gè)或多個(gè)條件,指定的語(yǔ)句被執(zhí)行.對(duì)一個(gè)continue處理程序,當(dāng)前子程序的執(zhí)行處理程序語(yǔ)句之后繼續(xù).對(duì)于exit處理程序,當(dāng)前begin…end復(fù)合語(yǔ)句的執(zhí)行被終止.undo處理程序類(lèi)型語(yǔ)句還不被支持.
1. sqlwarning是對(duì)所有以01開(kāi)頭的sqlstate代碼的速記.
2. not found是對(duì)所有以02開(kāi)頭的sqlstate代碼的速記.
3. sqlexception 是對(duì)所有沒(méi)有被sqlwarning或not found捕獲的sqlstate代碼的速記.?
MYSQL應(yīng)用3.3 使用游標(biāo):
??? mysql的查詢語(yǔ)句可以返回多條記錄結(jié)果,那么在表達(dá)式中如何遍歷這些記錄結(jié)果呢?mysql提供了游標(biāo)來(lái)實(shí)現(xiàn).通過(guò)指定由select語(yǔ)句返回的行集合(包括滿足該語(yǔ)句的where子句所列條件的所有行),由該語(yǔ)句返回完整的行集合叫結(jié)果集.應(yīng)用程序需要一種機(jī)制來(lái)一次處理結(jié)果集中的一行或連續(xù)的幾行,而游標(biāo)通過(guò)每次指定一條記錄完成與應(yīng)用程序的交互.
??? 游標(biāo)可以看做一種數(shù)據(jù)類(lèi)型,可以用來(lái)遍歷結(jié)果集,相當(dāng)是指針或數(shù)組的下標(biāo).處理結(jié)果集的方法可以通過(guò)游標(biāo)定位到結(jié)果集的某一行,從當(dāng)前結(jié)果集的位置搜索一行或者一部分行或者結(jié)果集中的當(dāng)前行進(jìn)行數(shù)據(jù)修改.
MYSQL應(yīng)用??? 3.3.1 聲明游標(biāo):
MYSQL應(yīng)用語(yǔ)法形式如下:
declare cursor_name cursor for select_statement;
MYSQL應(yīng)用??? 上述語(yǔ)句中,cursor_name參數(shù)表示有游標(biāo)的名稱(chēng),參數(shù)select_statement表示select語(yǔ)句.因?yàn)橛螛?biāo)需要遍歷結(jié)果集中的每一行,增加了服務(wù)器的負(fù)擔(dān),導(dǎo)致游標(biāo)的效率并不高.如果游標(biāo)操作的數(shù)據(jù)超過(guò)1萬(wàn)行,那么應(yīng)該采用其他方式,另外如果使用了游標(biāo),還應(yīng)盡量避免在游標(biāo)循環(huán)中進(jìn)行表連接操作.?
MYSQL應(yīng)用??? 3.3.2 打開(kāi)游標(biāo):
MYSQL應(yīng)用語(yǔ)法形式為:
open cursor_name
MYSQL應(yīng)用//注意,打開(kāi)一個(gè)游標(biāo)時(shí),游標(biāo)并不指向第一條記錄,而是指向第一條記錄的前邊.
MYSQL應(yīng)用??? 3.3.3 使用游標(biāo):
MYSQL應(yīng)用語(yǔ)法形式如下:
fetch cursor_name into var_name [,var_name] ...
MYSQL應(yīng)用??? 3.3.4 關(guān)閉游標(biāo):
MYSQL應(yīng)用語(yǔ)法形式如下:
close cursor_name
MYSQL應(yīng)用4. 修改存儲(chǔ)過(guò)程和函數(shù)?
??? 對(duì)于已經(jīng)創(chuàng)建好的存儲(chǔ)過(guò)程和函數(shù),當(dāng)使用一段時(shí)間后,就會(huì)需要進(jìn)行一些定義上的修改.可以通過(guò)alter procedure語(yǔ)句實(shí)現(xiàn)修改存儲(chǔ)過(guò)程,通過(guò)alter function語(yǔ)句實(shí)現(xiàn)修改函數(shù).
? 4.1 修改存儲(chǔ)過(guò)程:
MYSQL應(yīng)用語(yǔ)法形式如下:
MYSQL應(yīng)用
alter procedure procedure_name
[characteristic...]
MYSQL應(yīng)用??? procedure_name參數(shù)表示所要修改存儲(chǔ)過(guò)程的名字,而characteristic參數(shù)指定修改后存儲(chǔ)過(guò)程的特性,與定義存儲(chǔ)過(guò)程的該參數(shù)相比,取值只能是如下值:
MYSQL應(yīng)用
|(contains sql|no sql|reads sql data|modifys sql data)
|sql security {definer|invoker}
|comment ‘string'
)
MYSQL應(yīng)用? 4.2 修改函數(shù):
MYSQL應(yīng)用語(yǔ)法形式如下:
MYSQL應(yīng)用
alter function function_name
[characteristic...]
MYSQL應(yīng)用??? function_name參數(shù)表示所要修改函數(shù)的名字,而characteristic參數(shù)指定修改后的函數(shù)特性,與定義函數(shù)的該參數(shù)相比,取值只能是如下值:
|(contains sql|no sql|reads sql data|modifys sql data)
|sql security {definer|invoker}
|comment ‘string'
MYSQL應(yīng)用5. 刪除存儲(chǔ)過(guò)程和函數(shù)?
? 5.1 通過(guò)drop語(yǔ)句刪除存儲(chǔ)過(guò)程:
MYSQL應(yīng)用語(yǔ)法形式如下:
drop prcedure proce_name;
MYSQL應(yīng)用? 5.2 通過(guò)drop function語(yǔ)句刪除函數(shù):
MYSQL應(yīng)用語(yǔ)法形式如下:
drop function func_name;
MYSQL應(yīng)用以上就是本文的全部?jī)?nèi)容,希望對(duì)大家的學(xué)習(xí)有所幫助,也希望大家多多支持維易PHP.
轉(zhuǎn)載請(qǐng)注明本頁(yè)網(wǎng)址:
http://www.snjht.com/jiaocheng/3713.html