數(shù)據(jù)庫(kù)存儲(chǔ)過(guò)程

1、知識(shí)回顧 觸發(fā)器是一種特殊的存儲(chǔ)過(guò)程，它包括了大量的SQL語(yǔ)句。 由事件觸發(fā)而自動(dòng)執(zhí)行。在往表中插入記錄更新記錄或者刪除記錄時(shí)被自動(dòng)地激活。所以觸發(fā)器可以用來(lái)實(shí)現(xiàn)對(duì)表實(shí)施復(fù)雜的完整性約束語(yǔ)法格式如下。語(yǔ)法格式如下。create trigger 觸發(fā)器名觸發(fā)器名 觸發(fā)時(shí)間觸發(fā)時(shí)間 觸發(fā)事件觸發(fā)事件 on 表名表名 for each rowbegin觸發(fā)程序觸發(fā)程序endBeforeAfter Insert UpdatedeleteSelect語(yǔ)句刪除刪除coursecourse表中表中C C語(yǔ)言程序設(shè)計(jì)的課程信息語(yǔ)言程序設(shè)計(jì)的課程信息Delete from course where course

2、_name=C語(yǔ)言程序設(shè)計(jì)語(yǔ)言程序設(shè)計(jì)MySQLMySQL數(shù)據(jù)庫(kù)基礎(chǔ)與實(shí)例教程數(shù)據(jù)庫(kù)基礎(chǔ)與實(shí)例教程 之之存儲(chǔ)過(guò)程與游標(biāo)存儲(chǔ)過(guò)程與游標(biāo)1 12 23 34 4錯(cuò)誤觸發(fā)條件和錯(cuò)誤處理錯(cuò)誤觸發(fā)條件和錯(cuò)誤處理存儲(chǔ)過(guò)程存儲(chǔ)過(guò)程游標(biāo)游標(biāo)預(yù)處理預(yù)處理SQL語(yǔ)句語(yǔ)句5 5存儲(chǔ)程序的說(shuō)明存儲(chǔ)程序的說(shuō)明內(nèi)容內(nèi)容 存儲(chǔ)過(guò)程存儲(chǔ)過(guò)程是一組為了完成特定任務(wù)的SQL語(yǔ)句集，是利用語(yǔ)言所編寫(xiě)的程序。存儲(chǔ)過(guò)程經(jīng)編譯和優(yōu)化后存儲(chǔ)在數(shù)據(jù)庫(kù)服務(wù)器中。也可以看作是一個(gè)也可以看作是一個(gè)“加工作坊加工作坊”，它接收，它接收“調(diào)用調(diào)用者者”傳遞過(guò)來(lái)的傳遞過(guò)來(lái)的“原料原料”（ in參數(shù)），然后將這參數(shù)），然后將這些些“原料原料”“”“加工處

3、理加工處理”成成“產(chǎn)品產(chǎn)品”（ 存儲(chǔ)過(guò)程存儲(chǔ)過(guò)程的的out參數(shù)或參數(shù)或inout參數(shù)），再把參數(shù)），再把“產(chǎn)品產(chǎn)品”返回給返回給“調(diào)用者調(diào)用者”。 8.1 8.1 存儲(chǔ)過(guò)程存儲(chǔ)過(guò)程1 12 23 3存儲(chǔ)過(guò)程的調(diào)用存儲(chǔ)過(guò)程的調(diào)用創(chuàng)建存儲(chǔ)過(guò)程的語(yǔ)法格式創(chuàng)建存儲(chǔ)過(guò)程的語(yǔ)法格式“選課系統(tǒng)選課系統(tǒng)”的存儲(chǔ)過(guò)程的存儲(chǔ)過(guò)程45查看存儲(chǔ)過(guò)程的定義查看存儲(chǔ)過(guò)程的定義刪除存儲(chǔ)過(guò)程刪除存儲(chǔ)過(guò)程6存儲(chǔ)過(guò)程與函數(shù)的比較存儲(chǔ)過(guò)程與函數(shù)的比較 創(chuàng)建存儲(chǔ)過(guò)程的語(yǔ)法格式如下。創(chuàng)建存儲(chǔ)過(guò)程的語(yǔ)法格式如下。create procedure 存儲(chǔ)過(guò)程名（參數(shù)存儲(chǔ)過(guò)程名（參數(shù)1，參，參數(shù)數(shù)2，）存儲(chǔ)過(guò)程選項(xiàng)存儲(chǔ)過(guò)程選項(xiàng)begin存儲(chǔ)過(guò)

4、程語(yǔ)句塊存儲(chǔ)過(guò)程語(yǔ)句塊;end;8.1.1 8.1.1 創(chuàng)建存儲(chǔ)過(guò)程的語(yǔ)法格式創(chuàng)建存儲(chǔ)過(guò)程的語(yǔ)法格式 存儲(chǔ)過(guò)程選項(xiàng)由以下一種或幾種選項(xiàng)組合而成。存儲(chǔ)過(guò)程選項(xiàng)由以下一種或幾種選項(xiàng)組合而成。具體含義請(qǐng)查看函數(shù)選項(xiàng)的內(nèi)容。具體含義請(qǐng)查看函數(shù)選項(xiàng)的內(nèi)容。language sql| not deterministic| contains sql | no sql | reads sql data | modifies sql data | sql security definer | invoker | comment 注釋注釋8.1.1 8.1.1 創(chuàng)建存儲(chǔ)過(guò)程的語(yǔ)法格式創(chuàng)建存儲(chǔ)過(guò)程的語(yǔ)法格式 與函數(shù)

5、相同之處在于：存儲(chǔ)過(guò)程的參數(shù)也與函數(shù)相同之處在于：存儲(chǔ)過(guò)程的參數(shù)也是局部變量，也需要提供參數(shù)的數(shù)據(jù)類型；是局部變量，也需要提供參數(shù)的數(shù)據(jù)類型；與函數(shù)不同的是，存儲(chǔ)過(guò)程有三種類型的參與函數(shù)不同的是，存儲(chǔ)過(guò)程有三種類型的參數(shù)：數(shù)：in參數(shù)、參數(shù)、out參數(shù)以及參數(shù)以及inout參數(shù)。參數(shù)。8.1.1 8.1.1 創(chuàng)建存儲(chǔ)過(guò)程的語(yǔ)法格式創(chuàng)建存儲(chǔ)過(guò)程的語(yǔ)法格式 in代表輸入?yún)?shù)（默認(rèn)情況下為代表輸入?yún)?shù)（默認(rèn)情況下為in參數(shù)），參數(shù)），表示該參數(shù)的值必須由調(diào)用程序指定；表示該參數(shù)的值必須由調(diào)用程序指定； out代表輸出參數(shù)，表示該參數(shù)的值經(jīng)存儲(chǔ)代表輸出參數(shù)，表示該參數(shù)的值經(jīng)存儲(chǔ)過(guò)程計(jì)算后，將過(guò)程計(jì)算后

6、，將out參數(shù)的計(jì)算結(jié)果返回給調(diào)參數(shù)的計(jì)算結(jié)果返回給調(diào)用程序；用程序； inout代表即是輸入?yún)?shù)，又是輸出參數(shù)，代表即是輸入?yún)?shù)，又是輸出參數(shù)，表示該參數(shù)的值即可以由調(diào)用程序指定，又可表示該參數(shù)的值即可以由調(diào)用程序指定，又可以將以將inout參數(shù)的計(jì)算結(jié)果返回給調(diào)用程序。參數(shù)的計(jì)算結(jié)果返回給調(diào)用程序。8.1.1 8.1.1 創(chuàng)建存儲(chǔ)過(guò)程的語(yǔ)法格式創(chuàng)建存儲(chǔ)過(guò)程的語(yǔ)法格式例如創(chuàng)建存儲(chǔ)過(guò)程：根據(jù)學(xué)生的學(xué)號(hào)返回選課門(mén)數(shù)例如創(chuàng)建存儲(chǔ)過(guò)程：根據(jù)學(xué)生的學(xué)號(hào)返回選課門(mén)數(shù)delimiter $create procedure sc(in student_no1 int,out choose_number in

7、t)reads sql databeginselect count(*) into choose_number from choose where student_no=student_no1;end$delimiter ;8.1.1 8.1.1 創(chuàng)建存儲(chǔ)過(guò)程的語(yǔ)法格式創(chuàng)建存儲(chǔ)過(guò)程的語(yǔ)法格式 調(diào)用存儲(chǔ)過(guò)程須使用調(diào)用存儲(chǔ)過(guò)程須使用call關(guān)鍵字，另外還要關(guān)鍵字，另外還要向存儲(chǔ)過(guò)程傳遞向存儲(chǔ)過(guò)程傳遞in參數(shù)、參數(shù)、out參數(shù)或者參數(shù)或者inout參數(shù)。參數(shù)。例如：例如： set student_no = 2012001;set choose_number = 0;call sc(student_n

8、o,choose_number);select choose_number;8.1.2 8.1.2 存儲(chǔ)過(guò)程的調(diào)用存儲(chǔ)過(guò)程的調(diào)用 存儲(chǔ)過(guò)程中的存儲(chǔ)過(guò)程中的in參數(shù)與參數(shù)與out參數(shù)的數(shù)據(jù)類型參數(shù)的數(shù)據(jù)類型都為整數(shù)，也可以將這兩個(gè)參數(shù)簡(jiǎn)化為一個(gè)都為整數(shù)，也可以將這兩個(gè)參數(shù)簡(jiǎn)化為一個(gè)inout參數(shù)。參數(shù)。 8.1.2 8.1.2 存儲(chǔ)過(guò)程的調(diào)用存儲(chǔ)過(guò)程的調(diào)用delimiter $create procedure sc(inout number int)reads sql databeginselect count(*) into number from choose where student_n

9、o=number ;end$delimiter ;8.1.2 8.1.2 存儲(chǔ)過(guò)程的調(diào)用存儲(chǔ)過(guò)程的調(diào)用set number = 2012001;call get_choose_number1_proc(number);select number;8.1.2 8.1.2 存儲(chǔ)過(guò)程的調(diào)用存儲(chǔ)過(guò)程的調(diào)用 可以使用下面四種方法查看存儲(chǔ)過(guò)程的定義、可以使用下面四種方法查看存儲(chǔ)過(guò)程的定義、權(quán)限、字符集等信息。權(quán)限、字符集等信息。1使用使用show procedure status命令查看存儲(chǔ)過(guò)命令查看存儲(chǔ)過(guò)程的定義。程的定義。2查看某個(gè)數(shù)據(jù)庫(kù)（例如查看某個(gè)數(shù)據(jù)庫(kù)（例如choose數(shù)據(jù)庫(kù)）中的數(shù)據(jù)庫(kù)）中的所

10、有存儲(chǔ)過(guò)程名，可以使用下面的所有存儲(chǔ)過(guò)程名，可以使用下面的SQL語(yǔ)句。語(yǔ)句。select name from c where db = choose and type = procedure;8.1.4 8.1.4 查看存儲(chǔ)過(guò)程的定義查看存儲(chǔ)過(guò)程的定義3使用使用MySQL命令命令“show create procedure 存儲(chǔ)過(guò)程名存儲(chǔ)過(guò)程名;”可以查看指定數(shù)據(jù)庫(kù)指定存儲(chǔ)過(guò)程可以查看指定數(shù)據(jù)庫(kù)指定存儲(chǔ)過(guò)程的詳細(xì)信息。的詳細(xì)信息。例如查看例如查看get_choose_number_proc()存儲(chǔ)過(guò)程存儲(chǔ)過(guò)程的詳細(xì)信息，可以使用的詳細(xì)信息，可以使用“show create p

11、rocedure get_choose_number_procG” 8.1.4 8.1.4 查看存儲(chǔ)過(guò)程的定義查看存儲(chǔ)過(guò)程的定義4存儲(chǔ)過(guò)程的信息都保存在存儲(chǔ)過(guò)程的信息都保存在information_schema數(shù)據(jù)庫(kù)中的數(shù)據(jù)庫(kù)中的routines表中，表中，可以使用可以使用select語(yǔ)句查詢存儲(chǔ)過(guò)程的相關(guān)信息語(yǔ)句查詢存儲(chǔ)過(guò)程的相關(guān)信息.例如下面的例如下面的SQL語(yǔ)句查看的是語(yǔ)句查看的是get_choose_number_proc()存儲(chǔ)過(guò)程的相關(guān)信存儲(chǔ)過(guò)程的相關(guān)信息。息。select * from information_schema.routines where routine_name=

12、 get_choose_number_procG8.1.4 8.1.4 查看存儲(chǔ)過(guò)程的定義查看存儲(chǔ)過(guò)程的定義存儲(chǔ)過(guò)程與函數(shù)之間的共同特點(diǎn)：存儲(chǔ)過(guò)程與函數(shù)之間的共同特點(diǎn)： 應(yīng)用程序調(diào)用存儲(chǔ)過(guò)程或者函數(shù)時(shí)，應(yīng)用程序調(diào)用存儲(chǔ)過(guò)程或者函數(shù)時(shí)，只需要提供存儲(chǔ)過(guò)程名或者函數(shù)名，以及只需要提供存儲(chǔ)過(guò)程名或者函數(shù)名，以及參數(shù)信息，無(wú)需將若干條參數(shù)信息，無(wú)需將若干條MySQL命令或命令或SQL語(yǔ)句發(fā)送到語(yǔ)句發(fā)送到MySQL服務(wù)器，節(jié)省了服務(wù)器，節(jié)省了網(wǎng)絡(luò)開(kāi)銷(xiāo)。網(wǎng)絡(luò)開(kāi)銷(xiāo)。8.1.6 8.1.6 存儲(chǔ)過(guò)程與函數(shù)的比較存儲(chǔ)過(guò)程與函數(shù)的比較存儲(chǔ)過(guò)程與函數(shù)之間的共同特點(diǎn)在于：存儲(chǔ)過(guò)程與函數(shù)之間的共同特點(diǎn)在于： 存儲(chǔ)過(guò)程或

13、者函數(shù)可以重復(fù)使用，可以存儲(chǔ)過(guò)程或者函數(shù)可以重復(fù)使用，可以減少數(shù)據(jù)庫(kù)開(kāi)發(fā)人員，尤其是應(yīng)用程序開(kāi)發(fā)減少數(shù)據(jù)庫(kù)開(kāi)發(fā)人員，尤其是應(yīng)用程序開(kāi)發(fā)人員的工作量。人員的工作量。 使用存儲(chǔ)過(guò)程或者函數(shù)可以增強(qiáng)數(shù)據(jù)的使用存儲(chǔ)過(guò)程或者函數(shù)可以增強(qiáng)數(shù)據(jù)的安全訪問(wèn)控制?？梢栽O(shè)定只有某些數(shù)據(jù)庫(kù)用安全訪問(wèn)控制。可以設(shè)定只有某些數(shù)據(jù)庫(kù)用戶才具有某些存儲(chǔ)過(guò)程或者函數(shù)的執(zhí)行權(quán)。戶才具有某些存儲(chǔ)過(guò)程或者函數(shù)的執(zhí)行權(quán)。8.1.6 8.1.6 存儲(chǔ)過(guò)程與函數(shù)的比較存儲(chǔ)過(guò)程與函數(shù)的比較存儲(chǔ)過(guò)程與函數(shù)之間的不同：存儲(chǔ)過(guò)程與函數(shù)之間的不同： 函數(shù)必須有且僅有一個(gè)返回值，且必須函數(shù)必須有且僅有一個(gè)返回值，且必須指定返回值數(shù)據(jù)類型（返回值類型

14、目前僅僅指定返回值數(shù)據(jù)類型（返回值類型目前僅僅支持字符串、數(shù)值類型）。存儲(chǔ)過(guò)程可以沒(méi)支持字符串、數(shù)值類型）。存儲(chǔ)過(guò)程可以沒(méi)有返回值，也可以有返回值，甚至可以有多有返回值，也可以有返回值，甚至可以有多個(gè)返回值，所有的返回值需要使用個(gè)返回值，所有的返回值需要使用out或者或者inout參數(shù)定義。參數(shù)定義。 8.1.6 8.1.6 存儲(chǔ)過(guò)程與函數(shù)的比較存儲(chǔ)過(guò)程與函數(shù)的比較存儲(chǔ)過(guò)程與函數(shù)之間的不同之處在于：存儲(chǔ)過(guò)程與函數(shù)之間的不同之處在于： 函數(shù)體內(nèi)計(jì)算結(jié)果必須賦值給某個(gè)變量，函數(shù)體內(nèi)計(jì)算結(jié)果必須賦值給某個(gè)變量，也就是使用也就是使用select語(yǔ)句時(shí)不能有返回結(jié)果。語(yǔ)句時(shí)不能有返回結(jié)果。存儲(chǔ)過(guò)程則沒(méi)有

15、這方面的限制，存儲(chǔ)過(guò)程甚存儲(chǔ)過(guò)程則沒(méi)有這方面的限制，存儲(chǔ)過(guò)程甚至可以返回多個(gè)結(jié)果集。至可以返回多個(gè)結(jié)果集。8.1.6 8.1.6 存儲(chǔ)過(guò)程與函數(shù)的比較存儲(chǔ)過(guò)程與函數(shù)的比較存儲(chǔ)過(guò)程與函數(shù)之間的不同之處在于：存儲(chǔ)過(guò)程與函數(shù)之間的不同之處在于： 函數(shù)可以直接嵌入到函數(shù)可以直接嵌入到SQL語(yǔ)句（例如語(yǔ)句（例如select語(yǔ)句中）或者語(yǔ)句中）或者M(jìn)ySQL表達(dá)式中，最重要的是函數(shù)表達(dá)式中，最重要的是函數(shù)可以用于擴(kuò)展標(biāo)準(zhǔn)的可以用于擴(kuò)展標(biāo)準(zhǔn)的SQL語(yǔ)句。存儲(chǔ)過(guò)程一般需語(yǔ)句。存儲(chǔ)過(guò)程一般需要單獨(dú)調(diào)用，并不會(huì)嵌入到要單獨(dú)調(diào)用，并不會(huì)嵌入到SQL語(yǔ)句中使用（例語(yǔ)句中使用（例如如select語(yǔ)句中），調(diào)用時(shí)需要使用

16、語(yǔ)句中），調(diào)用時(shí)需要使用call關(guān)鍵字。關(guān)鍵字。8.1.6 8.1.6 存儲(chǔ)過(guò)程與函數(shù)的比較存儲(chǔ)過(guò)程與函數(shù)的比較存儲(chǔ)過(guò)程與函數(shù)之間的不同之處在于：存儲(chǔ)過(guò)程與函數(shù)之間的不同之處在于： 函數(shù)中的函數(shù)體限制比較多，比如函數(shù)體內(nèi)函數(shù)中的函數(shù)體限制比較多，比如函數(shù)體內(nèi)不能使用以顯式或隱式方式打開(kāi)、開(kāi)始或結(jié)束事不能使用以顯式或隱式方式打開(kāi)、開(kāi)始或結(jié)束事務(wù)的語(yǔ)句，如務(wù)的語(yǔ)句，如start transaction、commit、rollback或者或者set autocommit=0等語(yǔ)句；不能在等語(yǔ)句；不能在函數(shù)體內(nèi)使用預(yù)處理函數(shù)體內(nèi)使用預(yù)處理SQL語(yǔ)句（稍后講解）。存語(yǔ)句（稍后講解）。存儲(chǔ)過(guò)程的限制相對(duì)就

17、比較少，基本上所有的儲(chǔ)過(guò)程的限制相對(duì)就比較少，基本上所有的SQL語(yǔ)句或語(yǔ)句或MySQL命令都可以在存儲(chǔ)過(guò)程中使用。命令都可以在存儲(chǔ)過(guò)程中使用。8.1.6 8.1.6 存儲(chǔ)過(guò)程與函數(shù)的比較存儲(chǔ)過(guò)程與函數(shù)的比較存儲(chǔ)過(guò)程與函數(shù)之間的不同之處在于：存儲(chǔ)過(guò)程與函數(shù)之間的不同之處在于： 應(yīng)用程序（例如應(yīng)用程序（例如Java、PHP等應(yīng)用程序）調(diào)用函等應(yīng)用程序）調(diào)用函數(shù)時(shí)，通常將函數(shù)封裝到數(shù)時(shí)，通常將函數(shù)封裝到SQL字符串（例如字符串（例如select語(yǔ)語(yǔ)句）中進(jìn)行調(diào)用；句）中進(jìn)行調(diào)用； 應(yīng)用程序（例如應(yīng)用程序（例如Java、PHP等應(yīng)用程序）調(diào)用存儲(chǔ)等應(yīng)用程序）調(diào)用存儲(chǔ)過(guò)程時(shí)，必須使用過(guò)程時(shí)，必須使用ca

18、ll關(guān)鍵字進(jìn)行調(diào)用，如果應(yīng)用程序關(guān)鍵字進(jìn)行調(diào)用，如果應(yīng)用程序希望獲取存儲(chǔ)過(guò)程的返回值，應(yīng)用程序必須給存儲(chǔ)過(guò)希望獲取存儲(chǔ)過(guò)程的返回值，應(yīng)用程序必須給存儲(chǔ)過(guò)程的程的out參數(shù)或者參數(shù)或者inout參數(shù)傳遞參數(shù)傳遞MySQL會(huì)話變量，才會(huì)話變量，才能通過(guò)該會(huì)話變量獲取存儲(chǔ)過(guò)程的返回值。能通過(guò)該會(huì)話變量獲取存儲(chǔ)過(guò)程的返回值。8.1.6 8.1.6 存儲(chǔ)過(guò)程與函數(shù)的比較存儲(chǔ)過(guò)程與函數(shù)的比較 默認(rèn)情況下，存儲(chǔ)程序運(yùn)行過(guò)程中（例如存儲(chǔ)默認(rèn)情況下，存儲(chǔ)程序運(yùn)行過(guò)程中（例如存儲(chǔ)過(guò)程或者函數(shù)）發(fā)生錯(cuò)誤時(shí)，過(guò)程或者函數(shù)）發(fā)生錯(cuò)誤時(shí)，MySQL將自動(dòng)終止將自動(dòng)終止存儲(chǔ)程序的執(zhí)行。存儲(chǔ)程序運(yùn)行過(guò)程中發(fā)生錯(cuò)誤存儲(chǔ)程序的執(zhí)

19、行。存儲(chǔ)程序運(yùn)行過(guò)程中發(fā)生錯(cuò)誤時(shí)，數(shù)據(jù)庫(kù)開(kāi)發(fā)人員有時(shí)希望自己控制程序的運(yùn)時(shí)，數(shù)據(jù)庫(kù)開(kāi)發(fā)人員有時(shí)希望自己控制程序的運(yùn)行流程，并不希望行流程，并不希望MySQL將自動(dòng)終止存儲(chǔ)程序的將自動(dòng)終止存儲(chǔ)程序的執(zhí)行，執(zhí)行，MySQL的錯(cuò)誤處理機(jī)制可以幫助數(shù)據(jù)庫(kù)開(kāi)的錯(cuò)誤處理機(jī)制可以幫助數(shù)據(jù)庫(kù)開(kāi)發(fā)人員自行控制程序流程。發(fā)人員自行控制程序流程。8.2 8.2 錯(cuò)誤觸發(fā)條件和錯(cuò)誤處理錯(cuò)誤觸發(fā)條件和錯(cuò)誤處理1 12 23 3存儲(chǔ)程序運(yùn)行過(guò)程中存儲(chǔ)程序運(yùn)行過(guò)程中（例如存儲(chǔ)過(guò)程或者函（例如存儲(chǔ)過(guò)程或者函數(shù)）發(fā)生錯(cuò)誤時(shí)數(shù)）發(fā)生錯(cuò)誤時(shí)MySQL的錯(cuò)誤處理機(jī)制的錯(cuò)誤處理機(jī)制可以幫助數(shù)據(jù)庫(kù)開(kāi)發(fā)人可以幫助數(shù)據(jù)庫(kù)開(kāi)發(fā)人員自行控制程序

20、流程。員自行控制程序流程。自定義錯(cuò)誤觸發(fā)條件自定義錯(cuò)誤觸發(fā)條件自定義錯(cuò)誤處理程序自定義錯(cuò)誤處理程序自定義錯(cuò)誤處理程序說(shuō)明自定義錯(cuò)誤處理程序說(shuō)明8.2 8.2 錯(cuò)誤觸發(fā)條件和錯(cuò)誤處理錯(cuò)誤觸發(fā)條件和錯(cuò)誤處理 自定義錯(cuò)誤處理程序時(shí)需要使用自定義錯(cuò)誤處理程序時(shí)需要使用declare關(guān)鍵關(guān)鍵字，語(yǔ)法格式如下。字，語(yǔ)法格式如下。declare 錯(cuò)誤處理類型錯(cuò)誤處理類型 handler for 錯(cuò)誤觸發(fā)條錯(cuò)誤觸發(fā)條件件 自定義錯(cuò)誤處理程序自定義錯(cuò)誤處理程序;8.2.1 8.2.1 自定義錯(cuò)誤處理程序自定義錯(cuò)誤處理程序 錯(cuò)誤處理類型的取值要么是錯(cuò)誤處理類型的取值要么是continue，要么是，要么是exit。

21、 當(dāng)錯(cuò)誤處理類型是當(dāng)錯(cuò)誤處理類型是continue時(shí)，表示錯(cuò)誤發(fā)生時(shí)，表示錯(cuò)誤發(fā)生后，后，MySQL立即執(zhí)行自定義錯(cuò)誤處理程序，然后立即執(zhí)行自定義錯(cuò)誤處理程序，然后忽略該錯(cuò)誤繼續(xù)執(zhí)行其他忽略該錯(cuò)誤繼續(xù)執(zhí)行其他MySQL語(yǔ)句。語(yǔ)句。 當(dāng)錯(cuò)誤處理類型是當(dāng)錯(cuò)誤處理類型是exit時(shí)，表示錯(cuò)誤發(fā)生后，時(shí)，表示錯(cuò)誤發(fā)生后，MySQL立即執(zhí)行自定義錯(cuò)誤處理程序，然后立刻立即執(zhí)行自定義錯(cuò)誤處理程序，然后立刻停止其他停止其他MySQL語(yǔ)句的執(zhí)行。語(yǔ)句的執(zhí)行。8.2.1 8.2.1 自定義錯(cuò)誤處理程序自定義錯(cuò)誤處理程序 錯(cuò)誤觸發(fā)條件：表示滿足什么條件時(shí)，自定義錯(cuò)誤觸發(fā)條件：表示滿足什么條件時(shí)，自定義錯(cuò)誤處理程序開(kāi)

22、始運(yùn)行，錯(cuò)誤觸發(fā)條件定義了自錯(cuò)誤處理程序開(kāi)始運(yùn)行，錯(cuò)誤觸發(fā)條件定義了自定義錯(cuò)誤處理程序運(yùn)行的時(shí)機(jī)。定義錯(cuò)誤處理程序運(yùn)行的時(shí)機(jī)。 錯(cuò)誤觸發(fā)條件有錯(cuò)誤觸發(fā)條件有3種取值：種取值：MySQL錯(cuò)誤代碼、錯(cuò)誤代碼、ANSI標(biāo)準(zhǔn)錯(cuò)誤代碼以及自定義錯(cuò)誤觸發(fā)條件。例標(biāo)準(zhǔn)錯(cuò)誤代碼以及自定義錯(cuò)誤觸發(fā)條件。例如如1452是是MySQL錯(cuò)誤代碼，它對(duì)應(yīng)于錯(cuò)誤代碼，它對(duì)應(yīng)于ANSI標(biāo)準(zhǔn)標(biāo)準(zhǔn)錯(cuò)誤代碼錯(cuò)誤代碼23000，自定義錯(cuò)誤觸發(fā)條件稍后講解。，自定義錯(cuò)誤觸發(fā)條件稍后講解。8.2.1 8.2.1 自定義錯(cuò)誤處理程序自定義錯(cuò)誤處理程序 自定義錯(cuò)誤處理程序：錯(cuò)誤發(fā)生后，自定義錯(cuò)誤處理程序：錯(cuò)誤發(fā)生后，MySQL會(huì)立即執(zhí)行自

23、定義錯(cuò)誤處理程序中的會(huì)立即執(zhí)行自定義錯(cuò)誤處理程序中的MySQL語(yǔ)句，語(yǔ)句，自定義錯(cuò)誤處理程序也可以是一個(gè)自定義錯(cuò)誤處理程序也可以是一個(gè)begin-end語(yǔ)語(yǔ)句塊。句塊。任務(wù)布置任務(wù)布置4：上機(jī)操作，完成本書(shū)場(chǎng)景描述：上機(jī)操作，完成本書(shū)場(chǎng)景描述4：自：自定義錯(cuò)誤處理程序的任務(wù)要求。定義錯(cuò)誤處理程序的任務(wù)要求。8.2.1 8.2.1 自定義錯(cuò)誤處理程序自定義錯(cuò)誤處理程序 自定義錯(cuò)誤觸發(fā)條件允許數(shù)據(jù)庫(kù)開(kāi)發(fā)人員為自定義錯(cuò)誤觸發(fā)條件允許數(shù)據(jù)庫(kù)開(kāi)發(fā)人員為MySQL錯(cuò)誤代碼或者錯(cuò)誤代碼或者ANSI標(biāo)準(zhǔn)錯(cuò)誤代碼命名，標(biāo)準(zhǔn)錯(cuò)誤代碼命名，語(yǔ)法格式如下。語(yǔ)法格式如下。declare 錯(cuò)誤觸發(fā)條件錯(cuò)誤觸發(fā)條件 con

24、dition for MySQL錯(cuò)錯(cuò)誤代碼或者誤代碼或者ANSI標(biāo)準(zhǔn)錯(cuò)誤代碼標(biāo)準(zhǔn)錯(cuò)誤代碼;8.2.2 8.2.2 自定義錯(cuò)誤觸發(fā)條件自定義錯(cuò)誤觸發(fā)條件例如代碼片段：例如代碼片段：declare continue handler for 1452beginset error1 = 外鍵約束錯(cuò)誤外鍵約束錯(cuò)誤!;end;可以替換成代碼片段：可以替換成代碼片段：8.2.2 8.2.2 自定義錯(cuò)誤觸發(fā)條件自定義錯(cuò)誤觸發(fā)條件declare foreign_key_error condition for sqlstate 23000;declare continue handler for foreign_

25、key_errorbeginset error1 = 外鍵約束錯(cuò)誤外鍵約束錯(cuò)誤!;end;8.2.2 8.2.2 自定義錯(cuò)誤觸發(fā)條件自定義錯(cuò)誤觸發(fā)條件 自定義錯(cuò)誤觸發(fā)條件以及自定義錯(cuò)誤處理程序自定義錯(cuò)誤觸發(fā)條件以及自定義錯(cuò)誤處理程序可以在觸發(fā)器、函數(shù)以及存儲(chǔ)過(guò)程中使用?？梢栽谟|發(fā)器、函數(shù)以及存儲(chǔ)過(guò)程中使用。 參與軟件項(xiàng)目的多個(gè)數(shù)據(jù)庫(kù)開(kāi)發(fā)人員，如果每參與軟件項(xiàng)目的多個(gè)數(shù)據(jù)庫(kù)開(kāi)發(fā)人員，如果每個(gè)人都自建一套錯(cuò)誤觸發(fā)條件以及錯(cuò)誤處理程序，個(gè)人都自建一套錯(cuò)誤觸發(fā)條件以及錯(cuò)誤處理程序，極易造成極易造成MySQL錯(cuò)誤管理混亂。實(shí)際開(kāi)發(fā)過(guò)程中，錯(cuò)誤管理混亂。實(shí)際開(kāi)發(fā)過(guò)程中，建議數(shù)據(jù)庫(kù)開(kāi)發(fā)人員建立清晰的錯(cuò)誤處理

26、規(guī)范，建議數(shù)據(jù)庫(kù)開(kāi)發(fā)人員建立清晰的錯(cuò)誤處理規(guī)范，必要時(shí)可以將自定義錯(cuò)誤觸發(fā)條件、自定義錯(cuò)誤必要時(shí)可以將自定義錯(cuò)誤觸發(fā)條件、自定義錯(cuò)誤處理程序封裝在一個(gè)存儲(chǔ)程序中。處理程序封裝在一個(gè)存儲(chǔ)程序中。8.2.3 8.2.3 自定義錯(cuò)誤處理程序說(shuō)明自定義錯(cuò)誤處理程序說(shuō)明1 12 2 數(shù)據(jù)庫(kù)開(kāi)發(fā)人員編寫(xiě)存儲(chǔ)過(guò)數(shù)據(jù)庫(kù)開(kāi)發(fā)人員編寫(xiě)存儲(chǔ)過(guò)程（或者函數(shù)）等存儲(chǔ)程序時(shí)，程（或者函數(shù)）等存儲(chǔ)程序時(shí)，有 時(shí) 需 要 存 儲(chǔ) 程 序 中 的有 時(shí) 需 要 存 儲(chǔ) 程 序 中 的MySQL代碼掃描代碼掃描select結(jié)果結(jié)果集中的數(shù)據(jù)，并對(duì)結(jié)果集中的集中的數(shù)據(jù)，并對(duì)結(jié)果集中的每條記錄進(jìn)行簡(jiǎn)單處理，通過(guò)每條記錄進(jìn)行簡(jiǎn)單處理，

27、通過(guò)MySQL的游標(biāo)機(jī)制可以解決的游標(biāo)機(jī)制可以解決此類問(wèn)題。此類問(wèn)題。游標(biāo)在游標(biāo)在“網(wǎng)絡(luò)選課網(wǎng)絡(luò)選課”系統(tǒng)中的使用系統(tǒng)中的使用使用使用游標(biāo)游標(biāo)8.3 8.3 游標(biāo)游標(biāo) 游標(biāo)的使用可以概括為聲明游標(biāo)、打開(kāi)游標(biāo)、游標(biāo)的使用可以概括為聲明游標(biāo)、打開(kāi)游標(biāo)、從游標(biāo)中提取數(shù)據(jù)以及關(guān)閉游標(biāo)。從游標(biāo)中提取數(shù)據(jù)以及關(guān)閉游標(biāo)。8.3.1 8.3.1 使用游標(biāo)使用游標(biāo)1聲明游標(biāo)聲明游標(biāo)聲明游標(biāo)需要使用聲明游標(biāo)需要使用declare語(yǔ)句，其語(yǔ)法格式如下。語(yǔ)句，其語(yǔ)法格式如下。declare 游標(biāo)名游標(biāo)名 cursor for select語(yǔ)句語(yǔ)句使用使用declare語(yǔ)句聲明游標(biāo)后，此時(shí)與該游標(biāo)對(duì)應(yīng)語(yǔ)句聲明游標(biāo)后，此

28、時(shí)與該游標(biāo)對(duì)應(yīng)的的select語(yǔ)句并沒(méi)有執(zhí)行，語(yǔ)句并沒(méi)有執(zhí)行，MySQL服務(wù)器內(nèi)存中服務(wù)器內(nèi)存中并不存在與并不存在與select語(yǔ)句對(duì)應(yīng)的結(jié)果集。語(yǔ)句對(duì)應(yīng)的結(jié)果集。8.3.1 8.3.1 使用游標(biāo)使用游標(biāo)2打開(kāi)游標(biāo)打開(kāi)游標(biāo)打開(kāi)游標(biāo)需要使用打開(kāi)游標(biāo)需要使用open語(yǔ)句，其語(yǔ)法格式如下。語(yǔ)句，其語(yǔ)法格式如下。open 游標(biāo)名游標(biāo)名使用使用open語(yǔ)句打開(kāi)游標(biāo)后，與該游標(biāo)對(duì)應(yīng)的語(yǔ)句打開(kāi)游標(biāo)后，與該游標(biāo)對(duì)應(yīng)的select語(yǔ)句將被執(zhí)行，語(yǔ)句將被執(zhí)行，MySQL服務(wù)器內(nèi)存中將存服務(wù)器內(nèi)存中將存放與放與select語(yǔ)句對(duì)應(yīng)的結(jié)果集。語(yǔ)句對(duì)應(yīng)的結(jié)果集。8.3.1 8.3.1 使用游標(biāo)使用游標(biāo)3從游標(biāo)中提取數(shù)據(jù)

29、從游標(biāo)中提取數(shù)據(jù)從游標(biāo)中提取數(shù)據(jù)需要使用從游標(biāo)中提取數(shù)據(jù)需要使用fetch語(yǔ)句，其語(yǔ)法格語(yǔ)句，其語(yǔ)法格式如下。式如下。fetch 游標(biāo)名游標(biāo)名 into 變量名變量名1,變量名變量名2,說(shuō)明：變量名的個(gè)數(shù)必須與聲明游標(biāo)時(shí)使用的說(shuō)明：變量名的個(gè)數(shù)必須與聲明游標(biāo)時(shí)使用的select語(yǔ)句結(jié)果集中的字段個(gè)數(shù)保持一致。語(yǔ)句結(jié)果集中的字段個(gè)數(shù)保持一致。8.3.1 8.3.1 使用游標(biāo)使用游標(biāo) 第一次執(zhí)行第一次執(zhí)行fetch語(yǔ)句時(shí)，語(yǔ)句時(shí)，fetch語(yǔ)句從結(jié)果集語(yǔ)句從結(jié)果集中提取第一條記錄，再次執(zhí)行中提取第一條記錄，再次執(zhí)行fetch語(yǔ)句時(shí)，語(yǔ)句時(shí)，fetch語(yǔ)句從結(jié)果集中提取第二條記錄，語(yǔ)句從結(jié)果集中提取

30、第二條記錄，以此類以此類推。推。 fetch語(yǔ)句每次從結(jié)果集中僅僅提取一條記錄，語(yǔ)句每次從結(jié)果集中僅僅提取一條記錄，因此因此fetch語(yǔ)句需要循環(huán)語(yǔ)句的配合，才能實(shí)現(xiàn)整語(yǔ)句需要循環(huán)語(yǔ)句的配合，才能實(shí)現(xiàn)整個(gè)個(gè)“結(jié)果集結(jié)果集”的遍歷。的遍歷。8.3.1 8.3.1 使用游標(biāo)使用游標(biāo) 當(dāng)使用當(dāng)使用fetch語(yǔ)句從游標(biāo)中提取最后一條記錄后，語(yǔ)句從游標(biāo)中提取最后一條記錄后，再次執(zhí)行再次執(zhí)行fetch語(yǔ)句時(shí)，將產(chǎn)生語(yǔ)句時(shí)，將產(chǎn)生“ERROR 1329 (02000): No data to FETCH”錯(cuò)誤信息，數(shù)據(jù)庫(kù)錯(cuò)誤信息，數(shù)據(jù)庫(kù)開(kāi)發(fā)人員可以針對(duì)開(kāi)發(fā)人員可以針對(duì)MySQL錯(cuò)誤代碼錯(cuò)誤代碼1329，自定

31、，自定義錯(cuò)誤處理程序以便結(jié)束義錯(cuò)誤處理程序以便結(jié)束“結(jié)果集結(jié)果集”的遍歷。的遍歷。 注意：游標(biāo)錯(cuò)誤處理程序應(yīng)該放在聲明游標(biāo)語(yǔ)注意：游標(biāo)錯(cuò)誤處理程序應(yīng)該放在聲明游標(biāo)語(yǔ)句之后。游標(biāo)通常結(jié)合錯(cuò)誤處理程序一起使用，句之后。游標(biāo)通常結(jié)合錯(cuò)誤處理程序一起使用，用于結(jié)束用于結(jié)束“結(jié)果集結(jié)果集”的遍歷。的遍歷。8.3.1 8.3.1 使用游標(biāo)使用游標(biāo)4關(guān)閉游標(biāo)關(guān)閉游標(biāo)關(guān)閉游標(biāo)使用關(guān)閉游標(biāo)使用close語(yǔ)句，其語(yǔ)法格式如下。語(yǔ)句，其語(yǔ)法格式如下。close 游標(biāo)名游標(biāo)名關(guān)閉游標(biāo)的目的在于釋放游標(biāo)打開(kāi)時(shí)產(chǎn)生的結(jié)果關(guān)閉游標(biāo)的目的在于釋放游標(biāo)打開(kāi)時(shí)產(chǎn)生的結(jié)果集，節(jié)省集，節(jié)省MySQL服務(wù)器的內(nèi)存空間。游標(biāo)如果沒(méi)服務(wù)器

32、的內(nèi)存空間。游標(biāo)如果沒(méi)有被明確地關(guān)閉，游標(biāo)將在它被聲明的有被明確地關(guān)閉，游標(biāo)將在它被聲明的begin-end語(yǔ)句塊的末尾關(guān)閉。語(yǔ)句塊的末尾關(guān)閉。8.3.1 8.3.1 使用游標(biāo)使用游標(biāo)任務(wù)布置任務(wù)布置5：上機(jī)操作，完成本書(shū)場(chǎng)景描述：上機(jī)操作，完成本書(shū)場(chǎng)景描述5的任的任務(wù)要求，理解游標(biāo)的用法。務(wù)要求，理解游標(biāo)的用法。8.3.2 8.3.2 游標(biāo)在游標(biāo)在“網(wǎng)絡(luò)選課網(wǎng)絡(luò)選課”系統(tǒng)中的使用系統(tǒng)中的使用1 12 23 3 運(yùn)行期間，如果運(yùn)行期間，如果SQL語(yǔ)語(yǔ)句不能發(fā)生動(dòng)態(tài)地變化，這句不能發(fā)生動(dòng)態(tài)地變化，這種種SQL語(yǔ)句稱為靜態(tài)語(yǔ)句稱為靜態(tài)SQL語(yǔ)句。語(yǔ)句。 運(yùn)行期間，如果運(yùn)行期間，如果SQL語(yǔ)語(yǔ)句或句

33、或SQL所帶的參數(shù)可以發(fā)所帶的參數(shù)可以發(fā)生動(dòng)態(tài)變化，這種生動(dòng)態(tài)變化，這種SQL語(yǔ)句語(yǔ)句稱為動(dòng)態(tài)稱為動(dòng)態(tài)SQL語(yǔ)句或者預(yù)處語(yǔ)句或者預(yù)處理理SQL語(yǔ)句語(yǔ)句 。“選課系統(tǒng)選課系統(tǒng)”中預(yù)處中預(yù)處理理SQL語(yǔ)句的使用語(yǔ)句的使用預(yù)處理預(yù)處理SQL語(yǔ)句語(yǔ)句使用步驟使用步驟預(yù)處理預(yù)處理SQL語(yǔ)句語(yǔ)句的復(fù)雜應(yīng)用的復(fù)雜應(yīng)用4靜態(tài)靜態(tài)SQL語(yǔ)句與語(yǔ)句與預(yù)處理預(yù)處理SQL語(yǔ)句語(yǔ)句8.4 8.4 預(yù)處理預(yù)處理SQLSQL語(yǔ)句語(yǔ)句 MySQL支持預(yù)處理支持預(yù)處理SQL語(yǔ)句，預(yù)處理語(yǔ)句，預(yù)處理SQL語(yǔ)語(yǔ)句的使用主要包含三個(gè)步驟：創(chuàng)建預(yù)處理句的使用主要包含三個(gè)步驟：創(chuàng)建預(yù)處理SQL語(yǔ)語(yǔ)句、執(zhí)行預(yù)處理句、執(zhí)行預(yù)處理SQL語(yǔ)句以

34、及釋放預(yù)處理語(yǔ)句以及釋放預(yù)處理SQL語(yǔ)語(yǔ)句。句。1創(chuàng)建預(yù)處理創(chuàng)建預(yù)處理SQL語(yǔ)句語(yǔ)句創(chuàng)建預(yù)處理創(chuàng)建預(yù)處理SQL語(yǔ)句的語(yǔ)法格式如下。語(yǔ)句的語(yǔ)法格式如下。prepare 預(yù)處理預(yù)處理SQL語(yǔ)句名語(yǔ)句名 from SQL字符串字符串8.4.1 8.4.1 預(yù)處理預(yù)處理SQLSQL語(yǔ)句使用步驟語(yǔ)句使用步驟2執(zhí)行預(yù)處理執(zhí)行預(yù)處理SQL語(yǔ)句語(yǔ)句使用使用execute命令可以執(zhí)行預(yù)處理命令可以執(zhí)行預(yù)處理SQL語(yǔ)句中定語(yǔ)句中定義的義的SQL語(yǔ)句，其語(yǔ)法格式如下。語(yǔ)句，其語(yǔ)法格式如下。execute 預(yù)處理名預(yù)處理名 using 填充數(shù)據(jù)填充數(shù)據(jù) ,填充數(shù)填充數(shù)據(jù)據(jù).8.4.1 8.4.1 預(yù)處理預(yù)處理SQLS

35、QL語(yǔ)句使用步驟語(yǔ)句使用步驟3釋放預(yù)處理釋放預(yù)處理SQL語(yǔ)句語(yǔ)句當(dāng)預(yù)處理當(dāng)預(yù)處理SQL語(yǔ)句不再使用時(shí)，可以使用語(yǔ)句不再使用時(shí)，可以使用deallocate語(yǔ)句將該預(yù)處理語(yǔ)句將該預(yù)處理SQL語(yǔ)句釋放。其語(yǔ)語(yǔ)句釋放。其語(yǔ)法格式如下。法格式如下。deallocate prepare 預(yù)處理名預(yù)處理名8.4.1 8.4.1 預(yù)處理預(yù)處理SQLSQL語(yǔ)句使用步驟語(yǔ)句使用步驟任務(wù)布置任務(wù)布置6：上機(jī)操作，完成本書(shū)場(chǎng)景描述：上機(jī)操作，完成本書(shū)場(chǎng)景描述6的任的任務(wù)要求，理解務(wù)要求，理解預(yù)處理預(yù)處理SQL語(yǔ)句語(yǔ)句的用法。的用法。8.4.2 “8.4.2 “選課系統(tǒng)選課系統(tǒng)”中預(yù)處理中預(yù)處理SQLSQL語(yǔ)句的使用

36、語(yǔ)句的使用任務(wù)布置任務(wù)布置7：上機(jī)操作，完成本書(shū)場(chǎng)景描述：上機(jī)操作，完成本書(shū)場(chǎng)景描述7的任的任務(wù)要求，理解務(wù)要求，理解預(yù)處理預(yù)處理SQL語(yǔ)句語(yǔ)句的用法。的用法。任務(wù)布置任務(wù)布置8：上機(jī)操作，完成本書(shū)場(chǎng)景描述：上機(jī)操作，完成本書(shū)場(chǎng)景描述8的任的任務(wù)要求，理解務(wù)要求，理解預(yù)處理預(yù)處理SQL語(yǔ)句語(yǔ)句的用法。的用法。8.4.3 8.4.3 預(yù)處理預(yù)處理SQLSQL語(yǔ)句的復(fù)雜應(yīng)用語(yǔ)句的復(fù)雜應(yīng)用知識(shí)回顧知識(shí)回顧視圖觸發(fā)器視圖觸發(fā)器 對(duì)于靜態(tài)對(duì)于靜態(tài)SQL語(yǔ)句而言，每次將其發(fā)送到語(yǔ)句而言，每次將其發(fā)送到MySQL服務(wù)實(shí)例時(shí)，服務(wù)實(shí)例時(shí)，MySQL服務(wù)實(shí)例都會(huì)對(duì)其服務(wù)實(shí)例都會(huì)對(duì)其進(jìn)行解析、執(zhí)行，然后將執(zhí)行結(jié)果返回給進(jìn)行解析、執(zhí)行，然后將執(zhí)行結(jié)果返回給MySQL客戶機(jī)?？蛻魴C(jī)。 對(duì)于預(yù)處理對(duì)于預(yù)處理SQL語(yǔ)句而言，預(yù)處理語(yǔ)句而言，預(yù)處理SQL語(yǔ)句創(chuàng)語(yǔ)句創(chuàng)建后，第一次運(yùn)行預(yù)處理建后，第一次運(yùn)行預(yù)處理SQL語(yǔ)句時(shí)，語(yǔ)句時(shí)，MySQL服服務(wù)實(shí)例會(huì)對(duì)其解析，解析成功后，將其保存到務(wù)實(shí)例會(huì)對(duì)其解析，解析成功后，將其保存到MySQL服務(wù)器緩存中，為今后每一次地執(zhí)行作好服務(wù)器緩存中，為今后每一次地執(zhí)行作好準(zhǔn)備（今后無(wú)需再次解析）。準(zhǔn)備（今后無(wú)需再次解析）。8.4.4 8.4.4 靜