《MySQL數(shù)據(jù)庫應(yīng)用案例教程》教學(xué)課件第13章存儲過程和函數(shù)

1、13存儲過程和函數(shù)第章第1頁，共47頁。13.1存儲過程和函數(shù)概述第2頁，共47頁。針對表的一個完整操作往往不是單條SQL語句就能實(shí)現(xiàn)的，而是需要一組SQL語句來實(shí)現(xiàn)。例如，要完成一個購買商品的訂單的處理，一般需要考慮以下幾步：可將一個完整操作中所包含的多條SQL語句創(chuàng)建為存儲過程或函數(shù)，以方便應(yīng)用。（1）在生成訂單之前，首先需要查看商品庫存中是否有相應(yīng)商品；（2）如果商品庫存中不存在相應(yīng)商品，需要向供應(yīng)商訂貨；（3）如果商品庫存中存在相應(yīng)商品，需要預(yù)定商品，并修改庫存數(shù)量。存儲過程和函數(shù)可以簡單地理解為一組經(jīng)過編譯并保存在數(shù)據(jù)庫中的SQL語句的集合，可以隨時被調(diào)用。允許標(biāo)準(zhǔn)組件式編程：存儲過

2、程和函數(shù)在創(chuàng)建后可以在程序中被多次調(diào)用。較快的執(zhí)行速度：如果某一操作包含大量的事務(wù)處理代碼，并且被多次執(zhí)行，那么存儲過程要比批處理的執(zhí)行速度快很多。減少網(wǎng)絡(luò)流量：對于大量的SQL語句，將其組織成存儲過程，會比一條一條的調(diào)用SQL語句要大大節(jié)省網(wǎng)絡(luò)流量，降低網(wǎng)絡(luò)負(fù)載。安全：數(shù)據(jù)庫管理員通過設(shè)置執(zhí)行某一存儲過程的權(quán)限，從而限制相應(yīng)數(shù)據(jù)的訪問權(quán)限，避免非授權(quán)用戶對數(shù)據(jù)的訪問，保證數(shù)據(jù)的安全。第3頁，共47頁。13.2創(chuàng)建并調(diào)用存儲過程和函數(shù)第4頁，共47頁。創(chuàng)建存儲過程使用SQL語句CREATE PROCEDURE來實(shí)現(xiàn)，其語法形式如下：CREATE PROCEDURE proc_name ( pr

3、oc_parameter, )characteristic routine_bodyproc_parameter表示存儲過程的參數(shù)，參數(shù)形式如下：存儲程序可以分為存儲過程和函數(shù)。存儲過程和函數(shù)的操作主要包括創(chuàng)建存儲過程和函數(shù)、調(diào)用存儲過程和函數(shù)、查看存儲過程和函數(shù)，以及修改和刪除存儲過程和函數(shù)。13.2.1 創(chuàng)建存儲過程 IN | OUT | INOUT parameter_name TYPEcharacteristic表示存儲過程的特性，可取值及其意義如下：LANGUAGE SQL：表示存儲過程的routine_body部分使用SQL語言編寫。NOTDETERMINISTIC：DETERMI

4、NISTIC表示存儲過程的執(zhí)行結(jié)果是確定的；默認(rèn)為NOT DETERMINISTIC，表示執(zhí)行結(jié)果不確定。CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA：指明子程序使用SQL語句的限制。SQL SECURITY DEFINER | INVOKER ：指定可執(zhí)行存儲過程的用戶，DEFINER表示只有創(chuàng)建者才能執(zhí)行，INVOKER表示擁有權(quán)限的調(diào)用者可以執(zhí)行。COMMENT string：表示存儲過程或者函數(shù)的注釋信息。第5頁，共47頁。13.2.1 創(chuàng)建存儲過程【實(shí)例13-1】創(chuàng)建一個名為proc的簡單存儲過程，用于獲取goo

5、ds表中的記錄數(shù)。在創(chuàng)建存儲過程前首先登錄MySQL，并選擇數(shù)據(jù)庫db_shop。然后執(zhí)行以下語句：mysql DELIMITER $mysql CREATE PROCEDURE proc(OUT num INT) - BEGIN - SELECT COUNT(*) INTO num FROM goods; - END $Query OK, 0 rows affected (0.03 sec)mysql DELIMITER; 提示“DELIMITER $”的作用是將語句的結(jié)束符“;”修改為“$”，這樣存儲過程中的SQL語句結(jié)束符“;”就不會被MySQL解釋成語句的結(jié)束而提示錯誤。在存儲過程創(chuàng)建

6、完成后，應(yīng)使用“DELIMITER ;”語句將結(jié)束符修改為默認(rèn)結(jié)束符。第6頁，共47頁。13.2.2 創(chuàng)建存儲函數(shù)創(chuàng)建存儲函數(shù)使用SQL語句CREATE FUNCTION來實(shí)現(xiàn)，其語法形式如下：CREATE FUNCTION func_name ( parameter_name , )RETURNS TYPEcharacteristic routine_body【實(shí)例13-2】創(chuàng)建一個名為func的簡單存儲函數(shù)，用于獲取goods表中的記錄數(shù)。在創(chuàng)建存儲函數(shù)前首先登錄MySQL，并選擇數(shù)據(jù)庫db_shop。SQL語句的執(zhí)行結(jié)果如下：mysql DELIMITER $mysql CREATE F

7、UNCTION func() - RETURNS INT(11) - RETURN (SELECT COUNT(*) FROM goods) - $Query OK, 0 rows affected (0.03 sec)mysql DELIMITER; 提示RETURNS TYPE子句對于存儲函數(shù)而言是必須存在的，如果RETURN子句返回值的數(shù)據(jù)類型與RETURNS TYPE子句指定的數(shù)據(jù)類型不同，MySQL會將返回值強(qiáng)制轉(zhuǎn)換為RETURNS TYPE子句指定的類型。第7頁，共47頁。13.2.3 調(diào)用存儲過程和函數(shù)通常使用關(guān)鍵字CALL調(diào)用存儲過程，其語法形式如下：CALL procedur

8、e_name(parameter,);存儲過程必須使用關(guān)鍵字CALL調(diào)用，而存儲函數(shù)與MySQL內(nèi)置函數(shù)的調(diào)用相同，使用關(guān)鍵字SELECT。調(diào)用存儲過程1【實(shí)例13-3】調(diào)用實(shí)例13-1創(chuàng)建的存儲過程proc()，查看其返回值。首先登錄MySQL，并選擇數(shù)據(jù)庫db_shop。SQL語句及其執(zhí)行結(jié)果如下：mysql CALL proc(num);Query OK, 1 row affected (0.06 sec)mysql SELECT num;+-+| num|+-+| 10 |+-+1 row in set (0.01 sec)調(diào)用存儲過程的執(zhí)行結(jié)果與直接執(zhí)行查詢語句SELECT COUN

9、T(*) FROM goods;的執(zhí)行結(jié)果相同，但是存儲過程的好處在于處理邏輯都封裝在數(shù)據(jù)庫端，調(diào)用者不需要了解中間的處理邏輯，當(dāng)處理邏輯發(fā)生變化時，只需要修改存儲過程即可，而對調(diào)用者的程序完全沒有影響。第8頁，共47頁。13.2.3 調(diào)用存儲過程和函數(shù)通常使用關(guān)鍵字SELECT調(diào)用存儲函數(shù)，其語法形式如下：SELECT function_name(parameter,);【實(shí)例13-4】調(diào)用實(shí)例13-2創(chuàng)建的存儲函數(shù)func()，查看其返回值。首先登錄MySQL，并選擇數(shù)據(jù)庫db_shop。SQL語句及其執(zhí)行結(jié)果如下：mysql SELECT func();+-+| func()|+-+|

10、10 |+-+1 row in set (0.04 sec)調(diào)用存儲函數(shù)2第9頁，共47頁。使用Navicat for MySQL也可以創(chuàng)建存儲過程和函數(shù)，具體操作如下。13.2.4 使用圖形化工具創(chuàng)建存儲過程和函數(shù)步驟1 使用Navicat for MySQL連接MySQL后，雙擊需要操作的數(shù)據(jù)庫“test_db”，然后單擊“函數(shù)”按鈕。步驟2 單擊“新建函數(shù)”按鈕，選擇需要創(chuàng)建的類型，此處選擇創(chuàng)建存儲過程，如圖13-1所示。圖13-1 選擇類型第10頁，共47頁。13.2.4 使用圖形化工具創(chuàng)建存儲過程和函數(shù)步驟3 在編輯區(qū)填寫存儲過程需要的參數(shù)，單擊編輯區(qū)左下方的“+”按鈕可以添加參數(shù)，

11、單擊“”按鈕可以刪除參數(shù)，如果存儲過程沒有參數(shù)，直接單擊“完成”按鈕即可，如圖13-2所示。圖13-2 填寫參數(shù)第11頁，共47頁。13.2.4 使用圖形化工具創(chuàng)建存儲過程和函數(shù)步驟4 在BEGINEND語句中編輯需要執(zhí)行的SQL語句，如圖13-3所示。圖13-3 編輯SQL語句第12頁，共47頁。13.2.4 使用圖形化工具創(chuàng)建存儲過程和函數(shù)步驟5 在“高級”選項(xiàng)卡中可以設(shè)置存儲程序的特性，最后單擊“保存”按鈕，輸入名稱確定即可，如圖13-4所示。圖13-4 保存存儲過程第13頁，共47頁。13.3關(guān)于存儲過程和函數(shù)的表達(dá)式第14頁，共47頁。13.3.1 變量變量是表達(dá)式中最基本的元素，可

12、用于存儲臨時數(shù)據(jù)。本節(jié)簡單介紹變量的分類，以及在存儲過程和函數(shù)中應(yīng)用變量的方法。本節(jié)詳細(xì)介紹存儲過程和函數(shù)中所包含的表達(dá)式語句。此處的表達(dá)式同其他高級語言中一樣，主要由變量、運(yùn)算符和流程控制語句構(gòu)成。用戶變量（User-Defined Variables）：帶有前綴，只能被定義它的用戶使用，作用于當(dāng)前整個連接，當(dāng)前連接斷開后，所定義的用戶變量會被全部釋放。用戶變量不用提前定義就可以直接使用。局部變量（Local Variables）：沒有前綴，一般用于SQL語句塊中，比如存儲過程的BEGINEND中。局部變量使用前需要先通過DECLARE聲明。如沒有聲明，則初始值為NULL。系統(tǒng)變量（Serv

13、er System Variables）：帶有前綴，MySQL有許多已經(jīng)設(shè)置默認(rèn)值的系統(tǒng)變量。系統(tǒng)變量包含全局變量和會話變量。全局變量會影響整個服務(wù)器，而會話變量只影響個人客戶端連接。變量的分類1第15頁，共47頁。13.3.1 變量在存儲過程和函數(shù)中應(yīng)用變量2局部變量可以在子程序中定義并應(yīng)用，其作用范圍是BEGINEND語句塊。1）定義變量在存儲過程中使用DECLARE語句定義局部變量，其語法形式如下：DECLARE var_name, type DEFAULT value;例如，定義一個INT類型的變量，名稱為var1：DECLARE var1 INT; 提示變量的定義必須在復(fù)合語句開頭，

14、并且在任何其他語句前面。也就是說，DECLARE語句在存儲過程和函數(shù)中使用時，必須出現(xiàn)在BEGINEND語句塊的最前面，并且變量名不區(qū)分大小寫?？梢砸淮温暶鞫鄠€相同類型的變量。第16頁，共47頁。13.3.1 變量2）為變量賦值定義變量之后，可以使用SET關(guān)鍵字為變量賦值，語法形式如下：SET var_name = expr ,var_name = expr ;為前面定義的變量var1賦值，具體如下：SET var1 = 3;變量值可以為常量或者表達(dá)式。另外，也可以使用SELECTINTO查詢語句將查詢結(jié)果賦給變量，這要求查詢結(jié)果必須只有一行，具體語法形式如下：SELECT col_name,

15、 INTO var_name, FROM table_name;第17頁，共47頁。13.3.1 變量mysql DELIMITER $mysql CREATE PROCEDURE proc1() - BEGIN - DECLARE var1,var2,var3,g_id INT; - SET var1=1,var2=2; - SET var3=var1+var2; - SELECT id into g_id FROM goods WHERE id=1; - END $Query OK, 0 rows affected (0.10 sec)mysql DELIMITER;【實(shí)例13-5】執(zhí)行S

16、QL語句，定義變量并為其賦值。首先登錄MySQL，并選擇數(shù)據(jù)庫db_shop。然后創(chuàng)建存儲過程并定義變量，SQL語句及其執(zhí)行結(jié)果如下：第18頁，共47頁。13.3.2 定義條件和處理程序條件和處理程序是MySQL提供的一種異常處理機(jī)制，定義條件是事先定義程序執(zhí)行過程中可能會遇到的問題；定義處理程序是定義在遇到問題時執(zhí)行的相應(yīng)處理方法，并且保證存儲過程和函數(shù)在遇到問題時不終止。定義條件1在MySQL中定義條件使用DECLARECONDITION語句，其語法形式如下：DECLARE condition_name CONDITION FOR condition_type;mysql_error_co

17、de：表示數(shù)值類型錯誤代碼。sqlstate_value：表示長度為5的字符串類型錯誤代碼。下面以名為“not_found_database”的條件“ERROR 1049(42000)”的定義為例，來看看這兩種形式的區(qū)別。DECLARE not_found_database CONDITION FOR 1049;DECLARE not_found_database CONDITION FOR SQLSTATE 42000; 提示數(shù)值類型的錯誤代碼不要使用0，因?yàn)?表示成功而不是錯誤；字符串類型的錯誤代碼不要使用00，因?yàn)?0表示成功而不是錯誤。第19頁，共47頁。13.3.2 定義條件和處理程

18、序定義處理程序2在定義條件之后，可以使用DECLAREHANDLER語句定義處理程序，語法形式如下：DECLARE handler_type HANDLER FOR condition_value. statementCONTINUE：表示遇到錯誤不處理，程序繼續(xù)執(zhí)行。EXIT：表示遇到錯誤立即退出程序。UNDO：表示遇到錯誤后撤回之前的操作，目前MySQL暫不支持該操作。mysql_error_code：表示數(shù)值類型的錯誤代碼。sqlstate_value：表示包含5個字符的字符串錯誤值。condition_name：表示使用DECLARECONDITION語句定義的條件名。SQLWARNI

19、NG：匹配所有以01開頭的SQLSTATE錯誤代碼。NOT FOUND：匹配所有以02開頭的SQLSTATE錯誤代碼。SQLEXCEPTION：匹配所有未被SQLWARNING和NOT FOUND捕獲的SQLSTATE錯誤代碼。第20頁，共47頁。13.3.2 定義條件和處理程序statement為程序語句段，表示在遇到定義的異常條件時，需要執(zhí)行的存儲過程或函數(shù)。DECLARE CONTINUE HANDLER FOR SQLSTATE 42s02 SET info=can not find;方法一：捕獲sqlstate_valueDECLARE CONTINUE HANDLER FOR 11

20、46 SET info=can not find;方法二：捕獲mysql_error_codeDECLARE can_not_find CONDITION FOR 1146 ;DECLARE CONTINUE HANDLER FOR can_not_find SET info=can not find;方法三：先定義條件，然后再調(diào)用條件DECLARE EXIT HANDLER FOR SQLWARNING SET info=error;方法四：使用SQLWARNINGDECLARE EXIT HANDLER FOR NOT FOUND SET info=can not find;方法五：使用N

21、OT FOUND第21頁，共47頁。13.3.2 定義條件和處理程序DECLARE EXIT HANDLER FOR SQLEXCEPTION SET info=error;方法六：使用SQLEXCEPTIONmysql DELIMITER $mysql CREATE PROCEDURE handlerdemo() - BEGIN - DECLARE CONTINUE HANDLER FOR SQLSTATE 23000 SET x2 = 1; - SET x = 1; - INSERT INTO t_handler VALUES (1); - SET x = 2; - INSERT INTO

22、 t_handler VALUES (1); - SET x = 3; - END $Query OK, 0 rows affected (0.03 sec)mysql DELIMITER ;【實(shí)例13-6】執(zhí)行SQL語句，在存儲過程中定義條件和處理程序，體驗(yàn)異常處理機(jī)制在存儲過程中的作用。步驟1 登錄MySQL，并選擇任一數(shù)據(jù)庫，之后執(zhí)行以下SQL語句，創(chuàng)建t_handler表。CREATE TABLE t_handler(s1 INT, PRIMARY KEY (s1);步驟2 在存儲過程和函數(shù)中定義條件和處理程序，SQL語句及其執(zhí)行結(jié)果如下。第22頁，共47頁。13.3.2 定義條件和處

23、理程序mysql CALL handlerdemo();Query OK, 0 rows affected (0.11 sec)mysql SELECT X;+-+| X |+-+| 3|+-+1 row in set (0.00 sec)步驟3 調(diào)用存儲過程，查看變量x的值，SQL語句及其執(zhí)行結(jié)果如下。mysql DELIMITER $mysql CREATE PROCEDURE handlerdemo2 () - BEGIN - SET x = 1; - INSERT INTO t_handler VALUES (1); - SET x = 2; - INSERT INTO t_handl

24、er VALUES (1); - SET x = 3; - END $Query OK, 0 rows affected (0.00 sec)mysql DELIMITER ;步驟4 在存儲過程中不定義條件和處理程序，SQL語句及其執(zhí)行結(jié)果如下。第23頁，共47頁。13.3.2 定義條件和處理程序mysql CALL handlerdemo2 ();ERROR 1062 (23000): Duplicate entry 1 for key PRIMARYmysql SELECT X;+-+| X |+-+| 1 |+-+1 row in set (0.00 sec)步驟5 調(diào)用存儲過程，查看變

25、量x的值，SQL語句及其執(zhí)行結(jié)果如下。由結(jié)果可以看出，當(dāng)在存儲過程中定義條件和處理程序時，程序在遇到錯誤后會繼續(xù)執(zhí)行直到最后一步，為變量x賦值為3；而當(dāng)存儲過程中沒有定義條件和處理程序時，程序在遇到錯誤后會立即終止，此時變量x的值為1。第24頁，共47頁。13.3.3 游標(biāo)的使用在存儲過程和函數(shù)中，當(dāng)查詢語句返回多條記錄時，可以使用游標(biāo)對結(jié)果集進(jìn)行逐條讀取。本節(jié)介紹定義、打開、使用和關(guān)閉游標(biāo)的方法。定義游標(biāo)1在MySQL中，使用DECLARE關(guān)鍵字來定義游標(biāo)，其語法形式如下：DECLARE cursor_name CURSOR FOR select_statement;打開游標(biāo)2打開游標(biāo)的關(guān)鍵

26、字為OPEN，其語法形式如下：OPEN cursor_name;使用游標(biāo)3使用游標(biāo)的關(guān)鍵字是FETCH，其語法形式如下：FETCH cursor_name INTO var_name , var_name 關(guān)閉游標(biāo)4關(guān)閉游標(biāo)的關(guān)鍵字為CLOSE，其語法形式如下：CLOSE cursor_name;第25頁，共47頁。13.3.3 游標(biāo)的使用mysql DELIMITER $mysql CREATE PROCEDURE proc9(IN flag VARCHAR(10),OUT gname VARCHAR(30),OUT gprice DECIMAL(7,2) ) - BEGIN - DECLA

27、RE g_id INT; - DECLARE g_name VARCHAR(30); - DECLARE g_price DECIMAL(7,2); - DECLARE g_cursor CURSOR FOR SELECT id,name,price FROM db_shop.goods; - DECLARE EXIT HANDLER FOR NOT FOUND CLOSE g_cursor; - OPEN g_cursor; - REPEAT - FETCH g_cursor INTO g_id,g_name,g_price; - IF g_id=flag THEN - SELECT g_n

28、ame,g_price INTO gname,gprice; - END IF; - UNTIL g_id=flag - END REPEAT; - CLOSE g_cursor; - END $Query OK, 0 rows affected (0.12 sec)mysql DELIMITER ;【實(shí)例13-7】創(chuàng)建存儲過程，并在存儲過程中使用游標(biāo)。在創(chuàng)建存儲過程前首先登錄MySQL，并選擇數(shù)據(jù)庫db_shop。SQL語句及其執(zhí)行結(jié)果如下：第26頁，共47頁。13.3.3 游標(biāo)的使用mysql CALL proc9(5,gname,gprice);Query OK, 1 row affec

29、ted (0.06 sec)mysql SELECT gname,gprice;+-+-+| gname| gprice |+-+-+| 果汁 | 2.50 |+-+-+1 row in set (0.01 sec)調(diào)用存儲過程，查看結(jié)果：第27頁，共47頁。13.3.4 流程控制的使用流程控制語句是指可以控制程序運(yùn)行順序的指令，程序運(yùn)行順序主要包括順序執(zhí)行、條件執(zhí)行和循環(huán)執(zhí)行。MySQL支持的流程控制語句包括IF語句、CASE語句、LOOP語句、REPEAT語句、WHILE語句、LEAVE語句、ITERATE語句和RETURN語句。IF語句1IF實(shí)現(xiàn)條件判斷，語句中可以包含多個判斷條件，系統(tǒng)

30、會根據(jù)條件的結(jié)果是否為TRUE執(zhí)行相應(yīng)的操作，語法形式如下：IF search_condition THEN statement_list ELSEIF search_condition THEN statement_list . ELSE statement_listEND IF第28頁，共47頁。13.3.4 流程控制的使用CASE語句2CASE語句可以實(shí)現(xiàn)比IF語句更復(fù)雜的條件構(gòu)造，該語句有兩種使用形式。第1種語法形式如下：CASE case_expr WHEN when_value THEN statement_list WHEN when_value THEN statement_l

31、ist . ELSE statement_listEND CASECASE語句的第2種語法形式如下：CASE WHEN search_condition THEN statement_list WHEN search_condition THEN statement_list . ELSE statement_listEND CASE第29頁，共47頁。13.3.4 流程控制的使用LOOP語句和LEAVE語句3LOOP語句可以實(shí)現(xiàn)簡單的循環(huán)，使得系統(tǒng)能夠重復(fù)執(zhí)行循環(huán)結(jié)構(gòu)內(nèi)的語句列表。該語句列表由一條或多條語句組成，每條語句使用分號（;）隔開。語法形式如下：loop_label: LOOP st

32、atement_listEND LOOP end_label如果不在statement_list中增加退出循環(huán)的語句，LOOP語句可以實(shí)現(xiàn)簡單的死循環(huán)。使用LEAVE語句可以退出循環(huán)。語法形式如下：LEAVE label;REPEAT語句4REPEAT語句可以實(shí)現(xiàn)一個帶條件判斷的循環(huán)結(jié)構(gòu)。語法形式如下：repeat_label: REPEAT statement_listUNTIL search_conditionEND REPEAT repeat_label第30頁，共47頁。13.3.4 流程控制的使用WHILE語句5WHILE語句同樣可以實(shí)現(xiàn)一個帶條件判斷的循環(huán)結(jié)構(gòu)，但與REPEAT語句

33、不同的是，WHILE語句會先對條件進(jìn)行判斷，如果為TRUE，才會執(zhí)行需要循環(huán)的操作，否則終止循環(huán)，語法形式如下：while_label: WHILE search_condition DO statement_listEND WHILE while_labelITERATE語句6ITERATE語句只可以出現(xiàn)在LOOP語句、REPEAT語句和WHILE語句中，意義為再次執(zhí)行循環(huán)，語法形式如下：ITERATE label;第31頁，共47頁。13.3.4 流程控制的使用mysql DELIMITER $mysql CREATE PROCEDURE proc8(p1 int) - BEGIN - l

34、abel1: LOOP - SET p1 = p1 + 1; - IF p1 ITERATE label1; - END IF; - LEAVE label1; - END LOOP label1; - SET y = p1; - END $Query OK, 0 rows affected (0.00 sec)mysql DELIMITER ;【實(shí)例13-8】創(chuàng)建存儲過程，并在存儲過程中使用ITERATE語句。在創(chuàng)建存儲過程前首先登錄MySQL，并選擇數(shù)據(jù)庫db_shop。SQL語句及其執(zhí)行結(jié)果如下：mysql CALL proc8(1);Query OK, 0 rows affected

35、(0.01 sec)mysql SELECT y;+-+| y |+-+| 10 |+-+1 row in set (0.00 sec)調(diào)用存儲過程，查看結(jié)果。第32頁，共47頁。13.4查看存儲過程和函數(shù)第33頁，共47頁。創(chuàng)建完存儲過程和函數(shù)后，MySQL存儲了其狀態(tài)信息和定義語句，用戶可以分別使用SHOW STATUS和SHOW CREATE語句進(jìn)行查看，也可以在系統(tǒng)數(shù)據(jù)庫information_schema中查看。使用SHOW STATUS語句可以查看存儲過程和函數(shù)的狀態(tài)?；菊Z法形式如下：13.4.1 查看存儲過程和函數(shù)的狀態(tài)SHOW PROCEDURE | FUNCTION STA

36、TUS LIKE pf_name ;【實(shí)例13-9】執(zhí)行SQL語句，查看存儲過程proc的基本信息。首先登錄MySQL，然后執(zhí)行SHOW STATUS語句，執(zhí)行結(jié)果如下：mysql SHOW PROCEDURE STATUS LIKE proc G* 1. row * Db: db_shop Name: proc Type: PROCEDURE Definer: rootlocalhost Modified: 2018-06-09 15:06:48 Created: 2018-06-09 15:06:48 Security_type: DEFINER Comment: character_se

37、t_client: gbk collation_connection: gbk_chinese_ci Database Collation: utf8_general_ci1 row in set (0.02 sec)第34頁，共47頁。使用SHOW CREATE語句可以查看存儲過程和函數(shù)的定義語句，語法形式如下：13.4.2 查看存儲過程和函數(shù)的定義SHOW CREATE PROCEDURE | FUNCTION pf_name;【實(shí)例13-10】執(zhí)行SQL語句，查看存儲過程proc的定義語句。首先登錄MySQL，然后執(zhí)行SHOW CREATE語句，執(zhí)行結(jié)果如下：mysql SHOW CRE

38、ATE PROCEDURE db_c G* 1. row * Procedure: proc sql_mode: STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION Create Procedure: CREATE DEFINER=rootlocalhost PROCEDURE proc(OUT num INT)BEGINSELECT COUNT(*) INTO num FROM goods;ENDcharacter_set_client: gbkcollation_connection: gbk_chinese_ci Database Col

39、lation: utf8_general_ci1 row in set (0.00 sec)第35頁，共47頁。在MySQL中，存儲過程和函數(shù)的信息存儲在系統(tǒng)數(shù)據(jù)庫information_schema中的routines表中，查看存儲過程和函數(shù)詳細(xì)信息的語法形式如下：13.4.3 查看存儲過程和函數(shù)的信息SELECT * FROM information_schema.routines WHERE ROUTINE_NAME=pf_name;【實(shí)例13-11】執(zhí)行SQL語句，查看存儲過程proc的詳細(xì)信息。登錄MySQL，并執(zhí)行SQL語句，執(zhí)行結(jié)果如下：mysql SELECT * FROM i

40、nformation_schema.routines WHERE ROUTINE_NAME=proc AND ROUTINE_TYPE=PROCEDURE G* 1. row * SPECIFIC_NAME: proc ROUTINE_CATALOG: def ROUTINE_SCHEMA: db_shop ROUTINE_NAME: proc ROUTINE_TYPE: PROCEDURE DATA_TYPE:CHARACTER_MAXIMUM_LENGTH: NULL CHARACTER_OCTET_LENGTH: NULL NUMERIC_PRECISION: NULL第36頁，共47頁

41、。13.4.3 查看存儲過程和函數(shù)的信息 NUMERIC_SCALE: NULL DATETIME_PRECISION: NULL CHARACTER_SET_NAME: NULL COLLATION_NAME: NULL DTD_IDENTIFIER: NULL ROUTINE_BODY: SQL ROUTINE_DEFINITION: BEGINSELECT COUNT(*) INTO num FROM goods;END EXTERNAL_NAME: NULL EXTERNAL_LANGUAGE: NULL PARAMETER_STYLE: SQL IS_DETERMINISTIC: N

42、O SQL_DATA_ACCESS: CONTAINS SQL SQL_PATH: NULL SECURITY_TYPE: DEFINER CREATED: 2018-06-09 15:06:48 LAST_ALTERED: 2018-06-09 15:06:48 SQL_MODE: STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION ROUTINE_COMMENT: DEFINER: rootlocalhost CHARACTER_SET_CLIENT: gbk COLLATION_CONNECTION: gbk_chinese_ci DATABASE_CO

43、LLATION: utf8_general_ci1 row in set (0.05 sec)第37頁，共47頁。13.5修改和刪除存儲過程和函數(shù)第38頁，共47頁。在MySQL中，使用ALTER關(guān)鍵字可以修改存儲過程和函數(shù)，基本語法形式如下：ALTER PROCEDURE | FUNCTION pf_name characteristic;13.5.1 修改存儲過程和函數(shù)characteristic表示存儲過程和函數(shù)的特性，其可取值有CONTAINS SQL，NO SQL，READS SQL DATA，MODIFIES SQL DATA，SQL SECURITY DEFINER | INVOKER ，各值的意義與創(chuàng)建存儲過程和函數(shù)時相同。 提示修改存儲過程使用ALTER PROCEDURE語句，修改存儲函數(shù)使用ALTER FUNCTION語句，這兩個語句結(jié)構(gòu)相同，參數(shù)也一樣。并且它們與創(chuàng)建存儲過程和函數(shù)的語句中的參數(shù)也基本一樣?！緦?shí)例13-12】執(zhí)行SQL語句，修改存