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

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

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

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

5、執行存儲過程時，相同的輸入會得到相同的輸出。not deterministic表示結果是非確定的，相同的輸入可能得到不同的輸出。默認情況下，結果是非確定的。11.2.1 創建和使用存儲過程或函數 contains SQL | no SQL | reads SQL data | modifies SQL data ：指明子程序使用SQL語句的限制。contains SQL表示子程序包含SQL語句，但不包含讀或寫數據的語句；no SQL表示子程序中不包含SQL語句；reads SQL data表示子程序中包含讀數據的語句；modifies SQL data表示子程序中包含寫數據的語句。默認情況下，

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

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

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

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

11、n子句中包含select語句時，select語句的返回結果只能是一行且只有一列值。10.2.1 創建視圖可以像調用系統函數一樣，直接調用自定義函數，如下：11.2.2 變量1)declare 語句申明局部變量 declare var_name1 ,var_name2 . . . type default value 其中var_name1, var_name2參數是聲明的變量的名稱，這里可以定義多個變量。type參數用來指明變量的類型；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參數是變量的名稱；expr參數是賦值的表達式?？蔀槎鄠€變量賦值。用逗號隔開?？梢杂孟铝忻钤诖鎯^程中給局部變量賦值：set str1=abc,str2=123;set可以直接申明用戶變量，不需要聲明類型，declare必須指定類型 ；set 位置可以任意, declare 必須在復合語句的開頭，在任何其他語句之前；declare 定義的變量的作用范圍是begin end塊內，只能在塊中使用。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 刪除視圖如果我們直接用調用它，會輸出null,因為我們沒有定義str1和str2需定義str1和str2后再調用，如下所示：11.2.3 定義條件和處理條件的定義和處理主要用于定義在處

14、理過程中遇到問題時，相應的處理步驟。1、定義條件declare condition_name condition for condition_valuecondition_valueSQLstatevalue SQLstate_value| MySQL_error_codecondition_name參數表示的是所有定義的條件，condition_value是用來實現設置條件的類型，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;方法三：先定義條件，然后調用 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，就是一個標識，用來標識數據取到什么地方了?？梢园阉斫獬蓴到M中的下標。游標(cursor)具有以下特性： （1）只讀的，不能更新的；（2）不滾動的；（3）不敏感的，不敏感意為服務器可以或不可以復制它的結果表 。11.2.4 游標的使用游標(cursor)必須在聲明處理程序之前被聲明，并且變量和條件必須在聲明游標或處理程序之前被聲明。1）聲明游標declare cursornam

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

20、ch cursor_name into var_ name ， var_name 11.2.4 游標的使用4）關閉游標 游標使用完以后，要及時關閉。關閉游標使用close語句【例11-6】利用游標讀取student表中總人數，此功能可以直接使用count函數直接完成，此實例主要為演示游標的使用方法。close cursorname10.2.4 修改視圖定義10.2.4 修改視圖定義注意：游標只能在存儲過程或存儲函數中使用，例中語句無法單獨運行。調用如下：11.2.5 流程的控制3）loop語句 loop語句可以使用某些特定的語句重復執行，實現簡單的循環。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 查看存儲過程或函數1、查看存儲過程或函數的狀態【例11-14】查看studentcount 存儲過程的狀態（表單查看）show procedure | function status like pattern;11.2.6 查看存儲過程或函數2、查看存儲過程或函數的具體信息【例11-15】查看numofstudent 自定義函數的具體信息，包含函數的名稱、定義、字符集等信息。（表單查看）show create procedure | function sp_name; 11.2.6 查看存儲過程或函數3、查