電工與電子技術(shù)電子商務(wù)電子課件數(shù)據(jù)庫(kù)原理及應(yīng)用教程（基于Linux的MySQL和NoSQL應(yīng)用）第11章ppt

1、電子課件數(shù)據(jù)庫(kù)原理及應(yīng)用教程（基于Linux的MySQL和NoSQL應(yīng)用）第11章第11章 MySQL存儲(chǔ)過(guò)程與函數(shù)數(shù)據(jù)庫(kù)原理及應(yīng)用教程（基于Linux的MySQL和NoSQL應(yīng)用）CONTENTS存儲(chǔ)過(guò)程與函數(shù)簡(jiǎn)介1存儲(chǔ)過(guò)程與函數(shù)操作2系統(tǒng)函數(shù)3小結(jié)4存儲(chǔ)過(guò)程與函數(shù)簡(jiǎn)介111.1.1 概念存儲(chǔ)過(guò)程（stored procedure）是一組為了完成特定功能的SQL語(yǔ)句集，經(jīng)編譯后存儲(chǔ)在數(shù)據(jù)庫(kù)中，用戶(hù)通過(guò)指定存儲(chǔ)過(guò)程的名字并給定參數(shù)（如果該存儲(chǔ)過(guò)程帶有參數(shù)）來(lái)調(diào)用執(zhí)行它。優(yōu)點(diǎn)：(2)存儲(chǔ)過(guò)程允許標(biāo)準(zhǔn)組件是編程。(1)存儲(chǔ)過(guò)程增強(qiáng)了SQL語(yǔ)言的功能和靈活性。(3)存儲(chǔ)過(guò)程能實(shí)現(xiàn)較快的執(zhí)行速度。(4

2、)存儲(chǔ)過(guò)程能過(guò)減少網(wǎng)絡(luò)流量。(5)存儲(chǔ)過(guò)程可被作為一種安全機(jī)制來(lái)充分利用。11.1.2 存儲(chǔ)過(guò)程和函數(shù)區(qū)別對(duì)于存儲(chǔ)過(guò)程來(lái)說(shuō)可以返回參數(shù)，如記錄集，而函數(shù)只能返回值或者表對(duì)象。存儲(chǔ)過(guò)程實(shí)現(xiàn)的功能要復(fù)雜一點(diǎn)，而函數(shù)的實(shí)現(xiàn)功能針對(duì)性比較強(qiáng)。存儲(chǔ)過(guò)程，可以使用非確定函數(shù)，不允許在用戶(hù)定義函數(shù)主體中內(nèi)置非確定函數(shù)存儲(chǔ)過(guò)程一般是作為一個(gè)獨(dú)立的部分來(lái)執(zhí)行（execute 語(yǔ)句執(zhí)行），而函數(shù)可以作為查詢(xún)語(yǔ)句的一個(gè)部分來(lái)調(diào)用（select調(diào)用），由于函數(shù)可以返回一個(gè)表對(duì)象，因此它可以在查詢(xún)語(yǔ)句中位于from關(guān)鍵字的后面。 SQL語(yǔ)句中不可用存儲(chǔ)過(guò)程，而可以使用函數(shù)。存儲(chǔ)過(guò)程與函數(shù)操作211.2.1 創(chuàng)建和使用存

3、儲(chǔ)過(guò)程或函數(shù)1、存儲(chǔ)過(guò)程： 創(chuàng)建存儲(chǔ)過(guò)程的語(yǔ)法格式：create procedure sp_name (proc_parameter，.)characteristic . routine_body 其中，sp_name參數(shù)是存儲(chǔ)過(guò)程的名稱(chēng)；proc_parameter表示存儲(chǔ)過(guò)程的參數(shù)列表； characteristic參數(shù)指定存儲(chǔ)過(guò)程的特性；routine_body參數(shù)是SQL代碼的內(nèi)容，可以用beginend來(lái)標(biāo)志SQL代碼的開(kāi)始和結(jié)束。11.2.1 創(chuàng)建和使用存儲(chǔ)過(guò)程或函數(shù)proc_parameter中的每個(gè)參數(shù)由3部分組成。這3部分分別是輸入輸出類(lèi)型、參數(shù)名稱(chēng)和參數(shù)類(lèi)型。 in | o

4、ut | inout param_name type 其中，in表示輸入?yún)?shù)；out表示輸出參數(shù)； inout表示既可以是輸入，也可以是輸出； param_name參數(shù)是存儲(chǔ)過(guò)程的參數(shù)名稱(chēng)；type參數(shù)指定存儲(chǔ)過(guò)程的參數(shù)類(lèi)型，該類(lèi)型可以是MySQL數(shù)據(jù)庫(kù)的任意數(shù)據(jù)類(lèi)型。11.2.1 創(chuàng)建和使用存儲(chǔ)過(guò)程或函數(shù)characteristic參數(shù)有多個(gè)取值。其取值說(shuō)明如下：language SQL：說(shuō)明routine_body部分是由SQL語(yǔ)言的語(yǔ)句組成，這也是數(shù)據(jù)庫(kù)系統(tǒng)默認(rèn)的語(yǔ)言。not deterministic：指明存儲(chǔ)過(guò)程的執(zhí)行結(jié)果是否是確定的。deterministic表示結(jié)果是確定的。每次

5、執(zhí)行存儲(chǔ)過(guò)程時(shí)，相同的輸入會(huì)得到相同的輸出。not deterministic表示結(jié)果是非確定的，相同的輸入可能得到不同的輸出。默認(rèn)情況下，結(jié)果是非確定的。11.2.1 創(chuàng)建和使用存儲(chǔ)過(guò)程或函數(shù) 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表示子程序中包含寫(xiě)數(shù)據(jù)的語(yǔ)句。默認(rèn)情況下，

6、系統(tǒng)會(huì)指定為contains SQL。SQL security definer | invoker ：指明誰(shuí)有權(quán)限來(lái)執(zhí)行。definer表示只有定義者自己才能夠執(zhí)行；invoker表示調(diào)用者可以執(zhí)行。默認(rèn)情況下，系統(tǒng)指定的權(quán)限是definer。11.2.1 創(chuàng)建和使用存儲(chǔ)過(guò)程或函數(shù)comment string：注釋信息。技巧：創(chuàng)建存儲(chǔ)過(guò)程時(shí)，系統(tǒng)默認(rèn)指定contains SQL，表示存儲(chǔ)過(guò)程中使用了SQL語(yǔ)句。但是，如果存儲(chǔ)過(guò)程中沒(méi)有使用SQL語(yǔ)句，最好設(shè)置為no SQL。而且，存儲(chǔ)過(guò)程中最好在comment部分對(duì)存儲(chǔ)過(guò)程進(jìn)行簡(jiǎn)單的注釋?zhuān)员阋院笤陂喿x存儲(chǔ)過(guò)程的代碼時(shí)更加方便。調(diào)用存儲(chǔ)過(guò)程的語(yǔ)

7、法格式：call sp_name(parameter，) 說(shuō)明：sp_name為存儲(chǔ)過(guò)程的名稱(chēng)，如果要調(diào)用某個(gè)特定數(shù)據(jù)庫(kù)的存儲(chǔ)過(guò)程，則需要在前面加上該數(shù)據(jù)庫(kù)的名稱(chēng)。parameter為調(diào)用該存儲(chǔ)過(guò)程所用的參數(shù)，這條語(yǔ)句中的參數(shù)個(gè)數(shù)必須總是等于存儲(chǔ)過(guò)程的參數(shù)個(gè)數(shù)。11.2.1 創(chuàng)建和使用存儲(chǔ)過(guò)程或函數(shù)創(chuàng)建存儲(chǔ)函數(shù)語(yǔ)法格式：create function sp_name (func_parameter，.) returns type characteristic . routine_body 其中，sp_name參數(shù)是存儲(chǔ)函數(shù)的名稱(chēng)；func_parameter表示存儲(chǔ)函數(shù)的參數(shù)列表；return

8、s type指定返回值的類(lèi)型；characteristic參數(shù)指定存儲(chǔ)函數(shù)的特性；routine_body參數(shù)是SQL代碼的內(nèi)容，可以用beginend來(lái)標(biāo)志SQL代碼的開(kāi)始和結(jié)束。11.2.1 創(chuàng)建和使用存儲(chǔ)過(guò)程或函數(shù)func_parameter可以由多個(gè)參數(shù)組成，其中每個(gè)參數(shù)由參數(shù)名稱(chēng)和參數(shù)類(lèi)型組成，其形式如下：param_name type其中，param_name參數(shù)是存儲(chǔ)函數(shù)的參數(shù)名稱(chēng)；type參數(shù)指定存儲(chǔ)函數(shù)的參數(shù)類(lèi)型，該類(lèi)型可以是MySQL數(shù)據(jù)庫(kù)的任意數(shù)據(jù)類(lèi)型。調(diào)用存儲(chǔ)函數(shù)語(yǔ)法格式： select sp_name(func_parameter，)11.2.1 創(chuàng)建和使用存儲(chǔ)過(guò)程或

9、函數(shù)3、delimiter命令在存儲(chǔ)過(guò)程中，可能要輸入較多的語(yǔ)句，切語(yǔ)句中含分號(hào)。如果還以分號(hào)作為結(jié)束標(biāo)志，那么執(zhí)行完第一個(gè)分號(hào)語(yǔ)句后，就會(huì)認(rèn)為程序結(jié)束。這顯然不符合我們的要求。那么，我們可以用MySQL delimiter來(lái)改變默認(rèn)的結(jié)束標(biāo)志。delimiter $說(shuō)明，$是用戶(hù)定義的結(jié)束符，通常使用一些特殊的符號(hào)。當(dāng)使用delimiter命令時(shí)，應(yīng)該避免使用反斜杠字符，因?yàn)槟鞘荕ySQL轉(zhuǎn)移字符。11.2.1 創(chuàng)建和使用存儲(chǔ)過(guò)程或函數(shù)【例11-1】把結(jié)束符改為#，執(zhí)行SELECT 1+1#，如下10.2.1 創(chuàng)建視圖【例11-2】下面是一個(gè)存儲(chǔ)過(guò)程的簡(jiǎn)單例子，根據(jù)學(xué)號(hào)查詢(xún)學(xué)生的姓名。del

10、imiter $CREATE PROCEDURE getnamebysno (IN xh CHAR (10),OUT NAME CHAR (20)BEGIN SELECT sname INTO NAME FROM student WHERE sno = xh ;END$ delimiter ; 10.2.1 創(chuàng)建視圖可以調(diào)用getnamebysno存儲(chǔ)過(guò)程，首先我們定義一個(gè)用戶(hù)變量name，用call調(diào)用getnamebysno存儲(chǔ)過(guò)程，結(jié)果放到name中，最后輸出name的值。10.2.1 創(chuàng)建視圖【例11-3】 下面創(chuàng)建一個(gè)名為name_from_student的存儲(chǔ)函數(shù)。說(shuō)明：rutur

11、n子句中包含select語(yǔ)句時(shí)，select語(yǔ)句的返回結(jié)果只能是一行且只有一列值。10.2.1 創(chuàng)建視圖可以像調(diào)用系統(tǒng)函數(shù)一樣，直接調(diào)用自定義函數(shù)，如下：11.2.2 變量1)declare 語(yǔ)句申明局部變量 declare var_name1 ,var_name2 . . . type default value 其中var_name1, var_name2參數(shù)是聲明的變量的名稱(chēng)，這里可以定義多個(gè)變量。type參數(shù)用來(lái)指明變量的類(lèi)型；defalut value字句將變量默認(rèn)值設(shè)置為value，沒(méi)有使用default字句，默認(rèn)是null可以用下列命令申明兩個(gè)字符型變量：declare str1

12、,str2 varchar(6);11.2.2 變量2)用set語(yǔ)句給變量賦值set var_name = exper,var_name = exper其中var_name參數(shù)是變量的名稱(chēng)；expr參數(shù)是賦值的表達(dá)式?？蔀槎鄠€(gè)變量賦值。用逗號(hào)隔開(kāi)?？梢杂孟铝忻钤诖鎯?chǔ)過(guò)程中給局部變量賦值：set str1=abc,str2=123;set可以直接申明用戶(hù)變量，不需要聲明類(lèi)型，declare必須指定類(lèi)型 ；set 位置可以任意, declare 必須在復(fù)合語(yǔ)句的開(kāi)頭，在任何其他語(yǔ)句之前；declare 定義的變量的作用范圍是begin end塊內(nèi)，只能在塊中使用。set 定義的變量用戶(hù)變量。在變

13、量定義時(shí)，變量名稱(chēng)前使用符號(hào)修飾，如set var=12。11.2.2 變量3) 使用select語(yǔ)句給變量賦值select col_name,. . . into var_name, . . . table_expr其中col_name是列名，var_name是要賦值的變量名稱(chēng)。table_var是select語(yǔ)句中的from字句及后面【例11-4】定義一個(gè)存儲(chǔ)過(guò)程，作用是輸出連個(gè)字符串拼接后的值10.2.2 刪除視圖如果我們直接用調(diào)用它，會(huì)輸出null,因?yàn)槲覀儧](méi)有定義str1和str2需定義str1和str2后再調(diào)用，如下所示：11.2.3 定義條件和處理?xiàng)l件的定義和處理主要用于定義在處

14、理過(guò)程中遇到問(wèn)題時(shí)，相應(yīng)的處理步驟。1、定義條件declare condition_name condition for condition_valuecondition_valueSQLstatevalue SQLstate_value| MySQL_error_codecondition_name參數(shù)表示的是所有定義的條件，condition_value是用來(lái)實(shí)現(xiàn)設(shè)置條件的類(lèi)型，SQLstate_value和MySQL_error_code用來(lái)設(shè)置條件的錯(cuò)誤。11.2.3 定義條件和處理【例11-5】 下面定義error 1111 (13d12)這個(gè)錯(cuò)誤，名稱(chēng)為can_not_find?？?/p>

15、以用兩種不同的方法來(lái)定義，代碼如下：方法一：使用SQLstate_value DECLARE can_not_find CONDITION FOR SQLSTATE 13d12;方法二：使用MySQL_error_code DECLARE can_not_find CONDITION FOR 1111;11.2.3 定義條件和處理2)定義處理程序MySQL中可以使用declare關(guān)鍵字來(lái)定義處理程序。其基本語(yǔ)法如下：declare handler_type handler for condition_value,. sp_statement handler_type: continue | e

16、xit | undo condition_value: SQLstate value SQLstate_value |condition_name | SQLwarning | not found | SQLexception | MySQL_error_code11.2.3 定義條件和處理下面是定義處理程序的幾種方式。代碼如下：方法一：捕獲SQLstate_value DECLARE CONTINUE HANDLER FOR SQLSTATE 42s02SET info = can not find;方法二：捕獲MySQL_error_code DECLARE CONTINUE HANDLE

17、R FOR 1146SET info = can not find;方法三：先定義條件，然后調(diào)用 DECLARE can_not_find CONDITION FOR 1146;DECLARE CONTINUE HANDLER FOR can_not_findSET info = can not find;11.2.3 定義條件和處理方法四：使用SQLwarning DECLARE EXIT HANDLER FOR SQLWARNINGSET info = error; 方法五：使用not found DECLARE EXIT HANDLER FOR NOT foundSET info = c

18、an not find;方法六：使用SQLexception DECLARE EXIT HANDLER FOR SQLEXCEPTIONSET info = error;11.2.4 游標(biāo)的使用游標(biāo)就是一個(gè)cursor，就是一個(gè)標(biāo)識(shí)，用來(lái)標(biāo)識(shí)數(shù)據(jù)取到什么地方了。可以把它理解成數(shù)組中的下標(biāo)。游標(biāo)(cursor)具有以下特性： （1）只讀的，不能更新的；（2）不滾動(dòng)的；（3）不敏感的，不敏感意為服務(wù)器可以或不可以復(fù)制它的結(jié)果表 。11.2.4 游標(biāo)的使用游標(biāo)(cursor)必須在聲明處理程序之前被聲明，并且變量和條件必須在聲明游標(biāo)或處理程序之前被聲明。1）聲明游標(biāo)declare cursornam

19、e cursor for select _ statementselect _statement是一個(gè)select語(yǔ)句，返回的是一行或多行的數(shù)據(jù)。這個(gè)語(yǔ)句聲明一個(gè)游標(biāo)，也可以在存儲(chǔ)過(guò)程中定義多個(gè)游標(biāo)，但是一個(gè)塊中的每一個(gè)游標(biāo)必須有唯一的名字。 特別提醒，這里的select子句不能有into子句。11.2.4 游標(biāo)的使用2）打開(kāi)游標(biāo) 聲明游標(biāo)后，要使用游標(biāo)從中提取數(shù)據(jù)，就必須先打開(kāi)游標(biāo)。open cursor_ name 在程序中，一個(gè)游標(biāo)可以打開(kāi)多次，由于其他的用戶(hù)或程序本身已經(jīng)更新了表，所以每次打開(kāi)結(jié)果可能不同。3）讀取數(shù)據(jù) 游標(biāo)打開(kāi)后，就可以使用fetch into語(yǔ)句從中讀取數(shù)據(jù)。fet

20、ch cursor_name into var_ name ， var_name 11.2.4 游標(biāo)的使用4）關(guān)閉游標(biāo) 游標(biāo)使用完以后，要及時(shí)關(guān)閉。關(guān)閉游標(biāo)使用close語(yǔ)句【例11-6】利用游標(biāo)讀取student表中總?cè)藬?shù)，此功能可以直接使用count函數(shù)直接完成，此實(shí)例主要為演示游標(biāo)的使用方法。close cursorname10.2.4 修改視圖定義10.2.4 修改視圖定義注意：游標(biāo)只能在存儲(chǔ)過(guò)程或存儲(chǔ)函數(shù)中使用，例中語(yǔ)句無(wú)法單獨(dú)運(yùn)行。調(diào)用如下：11.2.5 流程的控制3）loop語(yǔ)句 loop語(yǔ)句可以使用某些特定的語(yǔ)句重復(fù)執(zhí)行，實(shí)現(xiàn)簡(jiǎn)單的循環(huán)。begin_label: loopsta

21、tement_listend loop end_label【例11-9】 loop語(yǔ)句的應(yīng)用add_num: LOOPSET count=count+1;END LOOP add_num11.2.5 流程的控制4）leave語(yǔ)句level label【例11-10】leave語(yǔ)句的應(yīng)用add_num: LOOPSET count=count+1;IF count=10 THEN level add_num;END LOOP add_num11.2.5 流程的控制5）itebate語(yǔ)句itebate label【例11-11】itebate語(yǔ)句的應(yīng)用add_num: LOOPSET count=

22、count+1;IF count=10 THEN LEVEL add_num;ELSEIF MOD(count,2)=0 THEN ITERATE add_num;END LOOP add_num11.2.5 流程的控制6）repeat語(yǔ)句的應(yīng)用begin_label: repeatstatement_list until search_confitionend repeat end_label【例11-12】repeat語(yǔ)句的使用SET count=count+1;UNTIL count=10;END REPEAT11.2.5 流程的控制7）while語(yǔ)句的應(yīng)用begin_label: wh

23、ile search_condition dostatement_listend while end_label【例11-13】while語(yǔ)句的應(yīng)用WHILE count10 DOSET count=count+1;END WHILE11.2.6 查看存儲(chǔ)過(guò)程或函數(shù)1、查看存儲(chǔ)過(guò)程或函數(shù)的狀態(tài)【例11-14】查看studentcount 存儲(chǔ)過(guò)程的狀態(tài)（表單查看）show procedure | function status like pattern;11.2.6 查看存儲(chǔ)過(guò)程或函數(shù)2、查看存儲(chǔ)過(guò)程或函數(shù)的具體信息【例11-15】查看numofstudent 自定義函數(shù)的具體信息，包含函數(shù)的名稱(chēng)、定義、字符集等信息。（表單查看）show create procedure | function sp_name; 11.2.6 查看存儲(chǔ)過(guò)程或函數(shù)3、查