MYSQL存儲過程技術(shù)ppt課件

1、存儲過程的概念為什么運用存儲過程根本創(chuàng)建、刪除語法存儲過程中的變量BEGIN.END復(fù)合語句 流程控制構(gòu)造語句注釋語法運用權(quán)限條件和異常處置程序游標(biāo)存儲過程的概念：存儲過程的概念：所謂的存儲過程就是存儲在數(shù)據(jù)庫當(dāng)中的可以執(zhí)行特定任務(wù)所謂的存儲過程就是存儲在數(shù)據(jù)庫當(dāng)中的可以執(zhí)行特定任務(wù)(查詢和更新查詢和更新)的一的一組組SQL代碼的程序段。代碼的程序段。 與自定義函數(shù)的區(qū)別：與自定義函數(shù)的區(qū)別：自定義函數(shù)有且只需一個前往值，就像普通的函數(shù)一樣，可以直接在表達(dá)式中自定義函數(shù)有且只需一個前往值，就像普通的函數(shù)一樣，可以直接在表達(dá)式中嵌入調(diào)用。嵌入調(diào)用。 存儲過程可以沒有前往值，也可以有恣意個輸出參數(shù)

2、，必需單獨調(diào)用。存儲過程可以沒有前往值，也可以有恣意個輸出參數(shù)，必需單獨調(diào)用。 執(zhí)行的本質(zhì)都一樣。只是函數(shù)有如只能前往一個變量的限制。而存儲過程可以執(zhí)行的本質(zhì)都一樣。只是函數(shù)有如只能前往一個變量的限制。而存儲過程可以前往多個。而函數(shù)是可以嵌入在前往多個。而函數(shù)是可以嵌入在sql中運用的，可以在中運用的，可以在select中調(diào)用，而存儲中調(diào)用，而存儲過程不行。過程不行。函數(shù)限制比較多，比如不能用暫時表，只能用表變量。還有一些函數(shù)都不可用函數(shù)限制比較多，比如不能用暫時表，只能用表變量。還有一些函數(shù)都不可用等等。而存儲過程的限制相對就比較少。等等。而存儲過程的限制相對就比較少。 普通來說，存儲過程實

3、現(xiàn)的功能要復(fù)雜一點，而函數(shù)的實現(xiàn)的功能針對性比較普通來說，存儲過程實現(xiàn)的功能要復(fù)雜一點，而函數(shù)的實現(xiàn)的功能針對性比較強。強。對于存儲過程來說可以前往參數(shù)，而函數(shù)只能前往值或者表對象。對于存儲過程來說可以前往參數(shù)，而函數(shù)只能前往值或者表對象。存儲過程普通是作為一個獨立的部分來執(zhí)行，而函數(shù)可以作為查詢語句的一個存儲過程普通是作為一個獨立的部分來執(zhí)行，而函數(shù)可以作為查詢語句的一個部分來調(diào)用，由于函數(shù)可以前往一個表對象，因此它可以在查詢語句中位于部分來調(diào)用，由于函數(shù)可以前往一個表對象，因此它可以在查詢語句中位于FROM關(guān)鍵字的后面。關(guān)鍵字的后面。為什么運用存儲過程：為什么運用存儲過程：存儲過程的優(yōu)點：

4、存儲過程的優(yōu)點： 存儲過程只在發(fā)明時進展編譯，以后每次執(zhí)行存儲過程都不需再重新編譯，而存儲過程只在發(fā)明時進展編譯，以后每次執(zhí)行存儲過程都不需再重新編譯，而普通普通SQL語句每執(zhí)行一次就編譯一次語句每執(zhí)行一次就編譯一次,所以運用存儲過程可提高數(shù)據(jù)庫執(zhí)行速所以運用存儲過程可提高數(shù)據(jù)庫執(zhí)行速度。度。 當(dāng)對數(shù)據(jù)庫進展復(fù)雜操作時如對多個表進展當(dāng)對數(shù)據(jù)庫進展復(fù)雜操作時如對多個表進展Update、Insert、Query、Delete時，可將此復(fù)雜操作用存儲過程封裝起來與數(shù)據(jù)庫提供的事務(wù)處置結(jié)時，可將此復(fù)雜操作用存儲過程封裝起來與數(shù)據(jù)庫提供的事務(wù)處置結(jié)合一同運用。合一同運用。 存儲過程可以反復(fù)運用，可減少數(shù)

5、據(jù)庫開發(fā)人員的任務(wù)量。存儲過程可以反復(fù)運用，可減少數(shù)據(jù)庫開發(fā)人員的任務(wù)量。 平安性高，可設(shè)定只需某些用戶才具有對指定存儲過程的運用權(quán)。平安性高，可設(shè)定只需某些用戶才具有對指定存儲過程的運用權(quán)。根本的創(chuàng)建、刪除語法：根本的創(chuàng)建、刪除語法：CREATE PROCEDURE sp_name (proc_parameter,.) characteristic . routine_body 默許地，子程序與當(dāng)前數(shù)據(jù)庫關(guān)聯(lián)。要明確地把子程序與一個給定數(shù)據(jù)庫關(guān)聯(lián)默許地，子程序與當(dāng)前數(shù)據(jù)庫關(guān)聯(lián)。要明確地把子程序與一個給定數(shù)據(jù)庫關(guān)聯(lián)起來，可以在創(chuàng)建子程序的時候指定其名字為起來，可以在創(chuàng)建子程序的時候指定其名字為

6、db_name.sp_name。 sp_name 存儲過程的名字存儲過程的名字proc_parameter指定參數(shù)為指定參數(shù)為IN， OUT，或，或INOUTcharacteristic 特征特征routine_body 包含合法的包含合法的SQL過程語句。過程語句。DROP PROCEDURE | FUNCTION IF EXISTS sp_name這個語句被用來移除一個存儲程序或函數(shù)。即，從效力器移除一個制定的子程這個語句被用來移除一個存儲程序或函數(shù)。即，從效力器移除一個制定的子程序。在序。在MySQL 5.1中，他必需有中，他必需有ALTER ROUTINE權(quán)限才可用此子程序。這個權(quán)限才

7、可用此子程序。這個權(quán)限被自動授予子程序的創(chuàng)建者。權(quán)限被自動授予子程序的創(chuàng)建者。 IF EXISTS 子句是一個子句是一個MySQL的擴展。假設(shè)程序或函數(shù)不存在，它防止發(fā)生的擴展。假設(shè)程序或函數(shù)不存在，它防止發(fā)生錯誤。錯誤。根本的創(chuàng)建、調(diào)用、刪除語法：根本的創(chuàng)建、調(diào)用、刪除語法：delimiter /DROP PROCEDURE IF EXISTS test /CREATE PROCEDURE test /* 存儲過程名存儲過程名 */(IN inparms INT, OUT outparams varchar(32) /* 輸入?yún)?shù)輸入?yún)?shù) */BEGIN /* 語句塊頭語句塊頭 */ DEC

8、LARE var CHAR(10); /* 變量聲明變量聲明 */ IF inparms = 1 THEN /* IF條件開場條件開場*/ SET var = hello; /* 賦值賦值 */ ELSE SET var = world; END IF; /* IF終了終了 */ INSERT INTO t1 VALUES (var); /* SQL語句語句 */ SELECT name FROM t1 LIMIT 1 INTO outparams;END/delimiter ; call test(1, out);存儲過程的變量：存儲過程的變量： 聲明變量：聲明變量：DECLARE var_

9、name,. type DEFAULT value這個語句被用來聲明部分變量。要給變量提供一個默許值，需求包含一個這個語句被用來聲明部分變量。要給變量提供一個默許值，需求包含一個DEFAULT子句。值可以被指定為一個表達(dá)式，不需求為一個常數(shù)。假設(shè)沒有子句。值可以被指定為一個表達(dá)式，不需求為一個常數(shù)。假設(shè)沒有DEFAULT子句，初始值為子句，初始值為NULL。 部分變量的作用范圍在它被聲明的部分變量的作用范圍在它被聲明的BEGIN . END塊內(nèi)。它可以被用在嵌套的塊內(nèi)。它可以被用在嵌套的塊中，除了那些用一樣名字聲明變量的塊。塊中，除了那些用一樣名字聲明變量的塊。 變量賦值，變量賦值，SET語句

10、：語句：SET var_name = expr , var_name = expr .也可以用語句替代也可以用語句替代SET來為用戶變量分配一個值。在這種情況下，分配符必需來為用戶變量分配一個值。在這種情況下，分配符必需為為:=而不能用而不能用=，由于在非，由于在非SET語句中語句中=被視為一個比較被視為一個比較 操作符，如下所示：操作符，如下所示：mysql SET t1=0, t2=0, t3=0;mysql SELECT t1:=0,t2:=0,t3:=0;對于運用對于運用select語句為變量賦值的情況，假設(shè)前往結(jié)果為空，即沒有記錄，此語句為變量賦值的情況，假設(shè)前往結(jié)果為空，即沒有記錄

11、，此時變量的值為上一次變量賦值時的值，假設(shè)沒有對變量賦過值，那么為時變量的值為上一次變量賦值時的值，假設(shè)沒有對變量賦過值，那么為NULL。 變量賦值，變量賦值，SELECT . INTO語句語句SELECT col_name,. INTO var_name,. table_expr這個這個SELECT語法把語法把選定的列直接存儲到變量。因此，只需單一的行可以被取回。選定的列直接存儲到變量。因此，只需單一的行可以被取回。SELECT id,data INTO x,y FROM test.t1 LIMIT 1;BEGIN.END復(fù)合語句：復(fù)合語句： begin_label: BEGIN state

12、ment_list END end_label 存儲子程序可以運用存儲子程序可以運用BEGIN . END復(fù)合語句來包含多個語句。復(fù)合語句來包含多個語句。statement_list 代表一個或多個語句的列表。代表一個或多個語句的列表。statement_list之內(nèi)每個語句都之內(nèi)每個語句都必需用分號；來結(jié)尾。必需用分號；來結(jié)尾。 復(fù)合語句可以被標(biāo)志。除非復(fù)合語句可以被標(biāo)志。除非begin_label存在，否那么存在，否那么end_label不能被給出，不能被給出，并且假設(shè)二者都存在，他們必需是同樣的。并且假設(shè)二者都存在，他們必需是同樣的。 運用多重語句需求客戶端能發(fā)送包含語句定界符運用多重語

13、句需求客戶端能發(fā)送包含語句定界符;的查詢字符串。這個符號在的查詢字符串。這個符號在命令行客戶端被用命令行客戶端被用delimiter命令來處置。改動查詢結(jié)尾定界符命令來處置。改動查詢結(jié)尾定界符;比如改動為比如改動為/使得使得; 可被用在子程序體中?？杀挥迷谧映绦蝮w中。流程控制構(gòu)造語句：流程控制構(gòu)造語句： IF語句語句IF search_condition THEN statement_list ELSEIF search_condition THEN statement_list . ELSE statement_listEND IF IF實現(xiàn)了一個根本的條件構(gòu)造。假設(shè)實現(xiàn)了一個根本的條件構(gòu)造

14、。假設(shè)search_condition求值為真，相應(yīng)的求值為真，相應(yīng)的SQL語句列表被執(zhí)行。假設(shè)沒有語句列表被執(zhí)行。假設(shè)沒有search_condition匹配，在匹配，在ELSE子句里的語句列子句里的語句列表被執(zhí)行。表被執(zhí)行。statement_list可以包括一個或多個語句。可以包括一個或多個語句。 舉例：舉例： DELIMITER /CREATE PROCEDURE p1(IN parameter1 INT) BEGIN DECLARE variable1 INT; SET variable1 = parameter1 + 1; IF variable1 = 0 THEN INSERT

15、INTO t VALUES (17); END IF; IF parameter1 = 0 THEN UPDATE t SET s1 = s1 + 1; ELSE UPDATE t SET s1 = s1 + 2; END IF;END; /DELIMITER ;流程控制構(gòu)造語句：流程控制構(gòu)造語句： CASE語句語句CASE case_value WHEN when_value THEN statement_list WHEN when_value THEN statement_list . ELSE statement_listEND CASE Or: CASE WHEN search_co

16、ndition THEN statement_list WHEN search_condition THEN statement_list . ELSE statement_listEND CASE 存儲程序的存儲程序的CASE語句實現(xiàn)一個復(fù)雜的條件構(gòu)造。假設(shè)語句實現(xiàn)一個復(fù)雜的條件構(gòu)造。假設(shè)search_condition 求值求值為真，相應(yīng)的為真，相應(yīng)的SQL被執(zhí)行。假設(shè)沒有搜索條件匹配，在被執(zhí)行。假設(shè)沒有搜索條件匹配，在ELSE子句里的語句被子句里的語句被執(zhí)行。執(zhí)行。舉例：舉例：CREATE PROCEDURE p2 (IN parameter1 INT)BEGIN DECLARE var

17、iable1 INT; SET variable1 = parameter1 + 1; CASE variable1 WHEN 0 THEN INSERT INTO t VALUES (17); WHEN 1 THEN INSERT INTO t VALUES (18); ELSE INSERT INTO t VALUES (19); END CASE;END; /流程控制構(gòu)造語句：流程控制構(gòu)造語句： 循環(huán)語句循環(huán)語句WHILE END WHILELOOP END LOOPREPEAT END REPEATGOTO前三種是規(guī)范的循環(huán)方式，至于前三種是規(guī)范的循環(huán)方式，至于GOTO就如就如C言語里

18、的言語里的GOTO一樣，盡量少用！一樣，盡量少用！在循環(huán)中還交叉一些循環(huán)控制語句，如在循環(huán)中還交叉一些循環(huán)控制語句，如LEAVE類似類似C言語的言語的break、ITERATE類似類似C言語的言語的continue等。等。 LEAVE語句語句LEAVE label 這個語句被用來退出任何被標(biāo)注的流程控制構(gòu)造。它和這個語句被用來退出任何被標(biāo)注的流程控制構(gòu)造。它和BEGIN . END或循環(huán)一同被運用?；蜓h(huán)一同被運用。ITERATE語句語句ITERATE label ITERATE只可以出如今只可以出如今LOOP, REPEAT, 和和WHILE語句內(nèi)。語句內(nèi)。ITERATE意思為：再次循環(huán)。意

19、思為：再次循環(huán)。流程控制構(gòu)造語句：流程控制構(gòu)造語句： 循環(huán)語句循環(huán)語句WHILE END WHILE 舉例：舉例：CREATE PROCEDURE p4 ()BEGIN DECLARE v INT; SET v = 0; WHILE v = 5 THEN LEAVE loop_label; END IF; END LOOP;END; /begin_label: LOOP statement_listEND LOOP end_labelLOOP允許某特定語句或語句群的反復(fù)執(zhí)行，實現(xiàn)一個簡單的循環(huán)構(gòu)造。在循允許某特定語句或語句群的反復(fù)執(zhí)行，實現(xiàn)一個簡單的循環(huán)構(gòu)造。在循環(huán)內(nèi)的語句不斷反復(fù)直到循環(huán)被退

20、出，退出通常伴隨著一個環(huán)內(nèi)的語句不斷反復(fù)直到循環(huán)被退出，退出通常伴隨著一個LEAVE 語句。語句。流程控制構(gòu)造語句：流程控制構(gòu)造語句： 循環(huán)語句循環(huán)語句REPEAT END REPEAT 舉例：舉例：CREATE PROCEDURE p6 ()BEGIN DECLARE v INT; SET v = 0; REPEAT INSERT INTO t VALUES (v); SET v = v + 1; UNTIL v = 5 END REPEAT;END; /功能與功能與WHILE差不多，差別是在執(zhí)行一次后檢查，而差不多，差別是在執(zhí)行一次后檢查，而WHILE是在開場時檢查，是在開場時檢查，累死累

21、死DOWHILE功能。功能。流程控制構(gòu)造語句：流程控制構(gòu)造語句：補充：迭代補充：迭代ITERATE語句語句CREATE PROCEDURE p7 ()BEGIN DECLARE v INT; SET v = 0; loop_label: LOOP IF v = 3 THEN SET v = v + 1; ITERATE loop_label; END IF; INSERT INTO t VALUES (v); SET v = v + 1; IF v = 5 THEN LEAVE loop_label; END IF; END LOOP;END; /注釋語法：注釋語法：mysql存儲過程可運用兩

22、種風(fēng)格的注釋存儲過程可運用兩種風(fēng)格的注釋雙模杠：雙模杠：-，該風(fēng)格普通用于單行注釋，該風(fēng)格普通用于單行注釋c風(fēng)格：風(fēng)格：/* 注釋內(nèi)容注釋內(nèi)容 */， 普通用于多行注釋普通用于多行注釋運用權(quán)限：運用權(quán)限：關(guān)于存儲過程的權(quán)限管理，一種是關(guān)于存儲過程的權(quán)限管理，一種是SQL SECURITY INVOKER，一種是，一種是SQL SECURITY DEFINER,也就是一種是調(diào)用者權(quán)限，一種是定義者權(quán)限，假設(shè)運也就是一種是調(diào)用者權(quán)限，一種是定義者權(quán)限，假設(shè)運用第一種，那么執(zhí)行的時候是以執(zhí)行者本身的權(quán)限來操作存儲過程中包含的表。用第一種，那么執(zhí)行的時候是以執(zhí)行者本身的權(quán)限來操作存儲過程中包含的表。假

23、設(shè)是第二種，那么執(zhí)行的時候，是以該存儲過程的定義者權(quán)限來操作。假設(shè)是第二種，那么執(zhí)行的時候，是以該存儲過程的定義者權(quán)限來操作。條件和異常處置程序：條件和異常處置程序：DECLARE handler_type HANDLER FOR condition_value,. sp_statement handler_type: CONTINUE | EXIT condition_value: SQLSTATE VALUE sqlstate_value | condition_name | SQLWARNING | NOT FOUND | SQLEXCEPTION這個語句指定每個可以處置一個或多個條件的

24、處置程序。假設(shè)產(chǎn)生一個或多個這個語句指定每個可以處置一個或多個條件的處置程序。假設(shè)產(chǎn)生一個或多個條件，指定的語句被執(zhí)行。條件，指定的語句被執(zhí)行。 對一個對一個CONTINUE處置程序，當(dāng)前子程序的執(zhí)行在執(zhí)行處置程序語句之后繼處置程序，當(dāng)前子程序的執(zhí)行在執(zhí)行處置程序語句之后繼續(xù)。對于續(xù)。對于EXIT處置程序，當(dāng)前處置程序，當(dāng)前BEGIN.END復(fù)合語句的執(zhí)行被終止。復(fù)合語句的執(zhí)行被終止。UNDO 處置程序類型語句還不被支持。處置程序類型語句還不被支持。SQLWARNING是對一切以是對一切以01開頭的開頭的SQLSTATE代碼的速記。代碼的速記。NOT FOUND是對一切以是對一切以02開頭的開

25、頭的SQLSTATE代碼的速記。代碼的速記。SQLEXCEPTION是對一切沒有被是對一切沒有被SQLWARNING或或NOT FOUND捕獲的捕獲的SQLSTATE代碼的速記。代碼的速記。聲明自定義條件：聲明自定義條件：DECLARE condition_name CONDITION FOR condition_valuecondition_value: SQLSTATE VALUE sqlstate_value條件和異常處置程序：條件和異常處置程序：舉例：舉例：CREATE TABLE test.t (s1 int,primary key (s1);delimiter /CREATE PR

26、OCEDURE handlerdemo ()BEGIN DECLARE CONTINUE HANDLER FOR SQLSTATE 23000 SET x2 = 1; SET x = 1; INSERT INTO test.t VALUES (1); SET x = 2; INSERT INTO test.t VALUES (1); SET x = 3;END;/delimiter ;游標(biāo)：游標(biāo)：聲明游標(biāo)聲明游標(biāo)DECLARE cursor_name CURSOR FOR select_statement這個語句聲明一個光標(biāo)。也可以在子程序中定義多個光標(biāo)，但是一個塊中的每這個語句聲明一個光標(biāo)。也可以在子程序中定義多個光標(biāo)，但是一個塊中的每一個光標(biāo)必需有獨一的名字。一個光標(biāo)必需有獨一的名字。翻開游標(biāo)翻開游標(biāo)OPEN cursor_name這個語句翻開先前聲明的光標(biāo)。這個語句翻開先前聲明的光標(biāo)。游標(biāo)游標(biāo)FETCHFETCH