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

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

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

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

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

5、執(zhí)行存儲過程時，相同的輸入會得到相同的輸出。not deterministic表示結果是非確定的，相同的輸入可能得到不同的輸出。默認情況下，結果是非確定的。11.2.1 創(chuàng)建和使用存儲過程或函數(shù) 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ù)的語句。默認情況下，

6、系統(tǒng)會指定為contains SQL。SQL security definer | invoker ：指明誰有權限來執(zhí)行。definer表示只有定義者自己才能夠執(zhí)行；invoker表示調(diào)用者可以執(zhí)行。默認情況下，系統(tǒng)指定的權限是definer。11.2.1 創(chuàng)建和使用存儲過程或函數(shù)comment string：注釋信息。技巧：創(chuàng)建存儲過程時，系統(tǒng)默認指定contains SQL，表示存儲過程中使用了SQL語句。但是，如果存儲過程中沒有使用SQL語句，最好設置為no SQL。而且，存儲過程中最好在comment部分對存儲過程進行簡單的注釋，以便以后在閱讀存儲過程的代碼時更加方便。調(diào)用存儲過程的語

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

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

9、函數(shù)3、delimiter命令在存儲過程中，可能要輸入較多的語句，切語句中含分號。如果還以分號作為結束標志，那么執(zhí)行完第一個分號語句后，就會認為程序結束。這顯然不符合我們的要求。那么，我們可以用MySQL delimiter來改變默認的結束標志。delimiter $說明，$是用戶定義的結束符，通常使用一些特殊的符號。當使用delimiter命令時，應該避免使用反斜杠字符，因為那是MySQL轉移字符。11.2.1 創(chuàng)建和使用存儲過程或函數(shù)【例11-1】把結束符改為#，執(zhí)行SELECT 1+1#，如下10.2.1 創(chuàng)建視圖【例11-2】下面是一個存儲過程的簡單例子，根據(jù)學號查詢學生的姓名。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存儲過程，首先我們定義一個用戶變量name，用call調(diào)用getnamebysno存儲過程，結果放到name中，最后輸出name的值。10.2.1 創(chuàng)建視圖【例11-3】 下面創(chuàng)建一個名為name_from_student的存儲函數(shù)。說明：rutur

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

12、,str2 varchar(6);11.2.2 變量2)用set語句給變量賦值set var_name = exper,var_name = exper其中var_name參數(shù)是變量的名稱；expr參數(shù)是賦值的表達式。可為多個變量賦值。用逗號隔開。可以用下列命令在存儲過程中給局部變量賦值：set str1=abc,str2=123;set可以直接申明用戶變量，不需要聲明類型，declare必須指定類型 ；set 位置可以任意, declare 必須在復合語句的開頭，在任何其他語句之前；declare 定義的變量的作用范圍是begin end塊內(nèi)，只能在塊中使用。set 定義的變量用戶變量。在變

13、量定義時，變量名稱前使用符號修飾，如set var=12。11.2.2 變量3) 使用select語句給變量賦值select col_name,. . . into var_name, . . . table_expr其中col_name是列名，var_name是要賦值的變量名稱。table_var是select語句中的from字句及后面【例11-4】定義一個存儲過程，作用是輸出連個字符串拼接后的值10.2.2 刪除視圖如果我們直接用調(diào)用它，會輸出null,因為我們沒有定義str1和str2需定義str1和str2后再調(diào)用，如下所示：11.2.3 定義條件和處理條件的定義和處理主要用于定義在處

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

15、以用兩種不同的方法來定義，代碼如下：方法一：使用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關鍵字來定義處理程序。其基本語法如下：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 游標的使用游標就是一個cursor，就是一個標識，用來標識數(shù)據(jù)取到什么地方了。可以把它理解成數(shù)組中的下標。游標(cursor)具有以下特性： （1）只讀的，不能更新的；（2）不滾動的；（3）不敏感的，不敏感意為服務器可以或不可以復制它的結果表 。11.2.4 游標的使用游標(cursor)必須在聲明處理程序之前被聲明，并且變量和條件必須在聲明游標或處理程序之前被聲明。1）聲明游標declare cursornam

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

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

21、tement_listend loop end_label【例11-9】 loop語句的應用add_num: LOOPSET count=count+1;END LOOP add_num11.2.5 流程的控制4）leave語句level label【例11-10】leave語句的應用add_num: LOOPSET count=count+1;IF count=10 THEN level add_num;END LOOP add_num11.2.5 流程的控制5）itebate語句itebate label【例11-11】itebate語句的應用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語句的應用begin_label: repeatstatement_list until search_confitionend repeat end_label【例11-12】repeat語句的使用SET count=count+1;UNTIL count=10;END REPEAT11.2.5 流程的控制7）while語句的應用begin_label: wh

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