項(xiàng)目10 事務(wù)與存儲(chǔ)過程

事務(wù)與存儲(chǔ)過程項(xiàng)目10【能力目標(biāo)】

掌握事務(wù)的相關(guān)概?念。

掌握事務(wù)的使用方?法。

掌握存儲(chǔ)過程的使?用。

熟悉程序流程控制基本語(yǔ)?句【素養(yǎng)目標(biāo)】培養(yǎng)縝密的思維方式和較強(qiáng)的分析能力。。目標(biāo)學(xué)習(xí)導(dǎo)航圖10-1項(xiàng)目10所講內(nèi)容在數(shù)據(jù)庫(kù)系統(tǒng)開發(fā)中的位置管理事務(wù)10.110.1.1了解事務(wù)的概念1．事務(wù)的基本概念事務(wù)可以理解為由多條SQL語(yǔ)句組成，用以完成一個(gè)業(yè)務(wù)功能的共同體。事務(wù)可以保證數(shù)據(jù)的一致性，事務(wù)處理是將多個(gè)操作或者命令一起執(zhí)行，所有操作或命令全部執(zhí)行成功才意味著該事務(wù)的成功，任何一個(gè)操作或命令失敗都意味著該事務(wù)失敗。只有多個(gè)操作全部成功，事務(wù)才能成功結(jié)束，并且會(huì)進(jìn)行提交（COMMIT）；如果任何一個(gè)操作失敗，則強(qiáng)制回滾（ROLLBACK）到初始狀態(tài)。在設(shè)置事務(wù)之前，需要認(rèn)真思考確保操作對(duì)象數(shù)據(jù)一致性的有效預(yù)防和保護(hù)措施，培養(yǎng)縝密的思維方式和較強(qiáng)的分析能?力。素養(yǎng)小貼士10.1.1了解事務(wù)的概念①

MySQL中只有使用了InnoDB數(shù)據(jù)庫(kù)引擎的數(shù)據(jù)庫(kù)或數(shù)據(jù)表才支持事務(wù)，使用事務(wù)時(shí)可以設(shè)置自動(dòng)提交功能是否開啟。②事務(wù)處理可以用來(lái)維護(hù)數(shù)據(jù)庫(kù)的完整性，保證成批的SQL語(yǔ)句要么全部執(zhí)行，要么全部不執(zhí)?行。

③事務(wù)可以用來(lái)管理INSERT、UPDATE、DELETE語(yǔ)句。DROP、ALTER語(yǔ)句不能通過事務(wù)處理，會(huì)直接提?交。10.1.1了解事務(wù)的概念2．事務(wù)分類事務(wù)一般分為兩種：隱式事務(wù)和顯式事務(wù)。在MySQL中，事務(wù)默認(rèn)是自動(dòng)提交的，所以說(shuō)每條DML語(yǔ)句（INSERT、UPDATE、DELETE）實(shí)際上都是一次執(zhí)行事務(wù)的過程。①隱式事務(wù)：沒有開啟和結(jié)束的標(biāo)志，默認(rèn)執(zhí)行完SQL語(yǔ)句就自動(dòng)提交。例如，我們經(jīng)常使用的INSERT、UPDATE、DELETE語(yǔ)句就屬于隱式事務(wù)。②顯式事務(wù)：需要顯式地開啟、關(guān)閉，然后執(zhí)行一系列操作，最后如果全部操作都成功執(zhí)行，則提交事務(wù)；如果操作有異常，則回滾事務(wù)中的所有操作。10.1.1了解事務(wù)的概念3.事務(wù)的四大基本特性ACID

（1）原子性（Atomicity）

事務(wù)包含的所有操作要么全部成功，要么全部失敗回滾，事務(wù)是一個(gè)不可分割的整體。

（2）一致性（Consistency）

事務(wù)開始前和結(jié)束后，數(shù)據(jù)庫(kù)的完整性約束沒有被破壞。

（3）隔離性（Isolation）

同一時(shí)間，只允許一個(gè)事務(wù)請(qǐng)求同一數(shù)據(jù)，不同的事務(wù)之間彼此沒有任何干?擾。

（4）持久性（Durability）

持久性是指一個(gè)事務(wù)一旦被提交了，對(duì)數(shù)據(jù)庫(kù)中數(shù)據(jù)的改變就是永久性的，即便是在數(shù)據(jù)庫(kù)系統(tǒng)遇到故障的情況下也不會(huì)丟失提交事務(wù)的操?作。10.1.1了解事務(wù)的概念4．事務(wù)的并發(fā)問題當(dāng)多個(gè)線程都開啟事務(wù)操作數(shù)據(jù)庫(kù)中的數(shù)據(jù)時(shí)，可能會(huì)產(chǎn)生如下幾種問題。（1）臟讀臟讀又稱無(wú)效數(shù)據(jù)的讀出，是指在數(shù)據(jù)庫(kù)訪問過程中，事務(wù)（A）將某一值修改，然后事務(wù)（B）讀取該值，此后事務(wù)（A）因?yàn)槟撤N原因撤銷對(duì)該值的修改，導(dǎo)致事務(wù)（B）所讀取到的數(shù)據(jù)是無(wú)效的。（2）不可重復(fù)讀取不可重復(fù)讀取是指在某事務(wù)處理過程中對(duì)數(shù)據(jù)進(jìn)行讀取時(shí)，由于該事務(wù)的更新操作導(dǎo)致多次讀取的數(shù)據(jù)發(fā)生了改變。（3）幻讀幻讀又稱幻象讀取是指在某事務(wù)處理數(shù)據(jù)過程中，由于該事務(wù)的插入或刪除操作導(dǎo)致在多次讀取過程中讀取到不存在或者消失的數(shù)據(jù)。SETautocommit=1;10.1.2

提交事務(wù)1．事務(wù)提交狀態(tài)查詢查詢當(dāng)前自動(dòng)提交功能的狀態(tài)可以使用如下命令。也可以使用以下命令。要設(shè)置自動(dòng)提交功能可以使用SELECT@@autocommit;SHOWVARIABLESLIKE'autocommit';圖10-2自動(dòng)提交狀態(tài)查詢10.1.2

提交事務(wù)2．使用COMMIT命令提交事務(wù)關(guān)閉事務(wù)的自動(dòng)提交后，需通過COMMIT命令進(jìn)行事務(wù)的提交。SHOWVARIABLESLIKE'autocommit';SETautocommit=0;STARTTRANSACTION;UPDATEaccountSETmoney=money+100WHEREname='A';UPDATEaccountSETmoney=money-100WHEREname='B';COMMIT;注意

如果事務(wù)被掛起沒有提交，則有很多操作命令在后續(xù)執(zhí)行時(shí)會(huì)自動(dòng)提交被掛起的事務(wù)，即隱式地結(jié)束當(dāng)前會(huì)話中活動(dòng)的任何事務(wù)，就像在之前執(zhí)行了提交一?樣。10.1.3

回滾事務(wù)事務(wù)回滾后，數(shù)據(jù)庫(kù)中的數(shù)據(jù)不會(huì)發(fā)生任何變化。回滾事務(wù)使用ROLLBACK命令實(shí)現(xiàn)。此處以表10-1所示數(shù)據(jù)為例講解ROLLBACK命?令。（1）首先查看自動(dòng)提交狀態(tài)，命令如?下。

可以看到@@autocommit的值為1，表示自動(dòng)提交功能開啟，這里將自動(dòng)提交功能關(guān)?閉。idnamemoney1tom10002peter1000表10-1表mytest.test數(shù)據(jù)SELECT@@autocommit;SETautocommit=0;10.1.3

回滾事務(wù)

（2）創(chuàng)建一個(gè)實(shí)驗(yàn)數(shù)據(jù)庫(kù)mytest，在數(shù)據(jù)庫(kù)中創(chuàng)建數(shù)據(jù)表test，在表中插入2條記錄，相關(guān)代碼如?下。CREATEDATABASEmytest;USEmytestCREATETABLEtest(nameCHAR(8)NOTNULL,moneyINT(10)NOTNULL);INSERTINTOtestVALUES('tom',1000);INSERTINTOtestVALUES('peter',1000);10.1.3

回滾事務(wù)

（3）執(zhí)行數(shù)據(jù)修改事?務(wù)。

查看目前臨時(shí)執(zhí)行結(jié)果，命令如?下。STARTTRANSACTION;UPDATEtestSETmoney=money-100WHEREname='tom';UPDATEtestSETmoney=money+100WHEREname='peter';SELECT*FROMtest;圖10-4

事務(wù)操作的臨時(shí)結(jié)果10.1.3

回滾事務(wù)

（4）使用ROLLBACK命令回滾事?務(wù)。

執(zhí)行回滾命令后再次查看表數(shù)據(jù)，發(fā)現(xiàn)表數(shù)據(jù)并沒有發(fā)生變?化。ROLLBACK;10.1.4了解事務(wù)的隔離級(jí)別

事務(wù)隔離是數(shù)據(jù)庫(kù)處理的基礎(chǔ)之一。隔離是事務(wù)四大基本特性縮寫ACID中的I。數(shù)據(jù)庫(kù)事務(wù)的隔離級(jí)別有4種，由低到高分別為READUNCOMMITTED、READCOMMITTED、REPEATABLEREAD、SERIALIZABLE。①READUNCOMMITTED：所有事務(wù)都可以看到其他未提交事務(wù)的執(zhí)行結(jié)果，即另一個(gè)未提交事務(wù)的數(shù)據(jù)。②READCOMMITTED：一個(gè)事務(wù)要等另一個(gè)事務(wù)提交后才能讀取數(shù)據(jù)。因?yàn)橥皇聞?wù)的其他實(shí)例在該實(shí)例處理期間可能會(huì)有新的COMMIT，所以同一SELECT可能返回不同結(jié)果。③REPEATABLEREAD：這是MySQL默認(rèn)的事務(wù)隔離級(jí)別，就是在開始讀取數(shù)據(jù)（事務(wù)開啟）時(shí)，不再允許修改操作。它確保同一事務(wù)的多個(gè)實(shí)例在并發(fā)讀取數(shù)據(jù)時(shí)會(huì)看到同樣的數(shù)據(jù)行，但可能導(dǎo)致幻讀。④SERIALIZABLE：最高的事務(wù)隔離級(jí)別，在該級(jí)別下，事務(wù)串行化順序執(zhí)行，可以避免臟讀、不可重復(fù)讀與幻讀。但是這種事務(wù)隔離級(jí)別可能導(dǎo)致大量的超時(shí)現(xiàn)象和鎖競(jìng)爭(zhēng)，效率低下，比較消耗數(shù)據(jù)庫(kù)性能。10.1.4了解事務(wù)的隔離級(jí)別隔離級(jí)別臟讀不可重復(fù)讀幻讀READUNCOMMITTED可能可能可能READCOMMITTED不可能可能可能REPEATABLEREAD不可能不可能可能SERIALIZABLE不可能不可能不可能

不同隔離級(jí)別在并發(fā)時(shí)可能出現(xiàn)的問題如表所?示。表10-2不同隔離級(jí)別在并發(fā)時(shí)可能出現(xiàn)的問題10.1.4了解事務(wù)的隔離級(jí)別

查看當(dāng)前的事務(wù)隔離級(jí)別，命令如?下。也可以使用如下命令。圖10-5

查看當(dāng)前事務(wù)級(jí)別SELECT@@transaction_isolation;SHOWVARIABLESLIKE'transaction_isolation';10.1.4了解事務(wù)的隔離級(jí)別

如果需要更改當(dāng)前的事務(wù)隔離級(jí)別，則可以使用SETTRANSACTION語(yǔ)句更改單個(gè)會(huì)話或所有后續(xù)連接的隔離級(jí)別，其語(yǔ)法格式如?下。SET[GLOBAL|SESSION]TRANSACTION

TRANSACTION_CHARACTERISTIC[,TRANSACTION_CHARACTERISTIC]...其中：TRANSACTION_CHARACTERISTIC:{ISOLATIONLEVELLevel}Level:{REPEATABLEREAD|READCOMMITTED|READUNCOMMITTED|SERIALIZABLE}注意

不允許在同一SETTRANSACTION語(yǔ)句中指定多個(gè)隔離級(jí)別子句。在事務(wù)開始后也不能更改事務(wù)隔離級(jí)別，否則會(huì)出?錯(cuò)。10.1.4了解事務(wù)的隔離級(jí)別

在事務(wù)開始后更改事務(wù)級(jí)別的代碼如?下。圖10-6事務(wù)開始后更改事務(wù)級(jí)別的執(zhí)行結(jié)果STARTTRANSACTION;SETTRANSACTIONISOLATIONLEVELSERIALIZABLE;10.1.4了解事務(wù)的隔離級(jí)別

在語(yǔ)句中使用關(guān)鍵字GLOBAL，表示此語(yǔ)句應(yīng)用于之后的所有SESSION，而當(dāng)前已經(jīng)存在的SESSION不受影響，也就是此語(yǔ)句將應(yīng)用于當(dāng)前SESSION內(nèi)之后的所有事務(wù)。如果沒有使用GLOBAL，那么此語(yǔ)句將應(yīng)用于當(dāng)前SESSION內(nèi)的下一個(gè)還未開始的事?務(wù)。

使用示例如?下。

要查看更改后的事務(wù)隔離級(jí)別，可以重新登錄后查?看。SETGLOBALTRANSACTIONISOLATIONLEVELREPEATABLEREAD;存儲(chǔ)過程10.210.2.1創(chuàng)建存儲(chǔ)過程在MySQL中創(chuàng)建存儲(chǔ)過程的語(yǔ)法格式如下。其中，各參數(shù)的含義如下。①DEFINER：默認(rèn)為當(dāng)前用戶②SP_NAME：自定義存儲(chǔ)過程名③PROC_PARAMETER：[IN|OUT|INOUT]PARAM_NAMETYPE。默認(rèn)情況下參數(shù)是IN。IN參數(shù)表示將值傳入過程，過程可能會(huì)修改該值。OUT參數(shù)將一個(gè)值從過程傳出給調(diào)用方，它的初始值在過程中為空。INOUT參數(shù)由調(diào)用者初始化，可以由過程修改傳出。④ROUTINE_BODY：過程體，由有效的SQL語(yǔ)句組成。⑤CHARACTERISTIC：存儲(chǔ)過程的一些特性。CREATE[DEFINER=USER]PROCEDURESP_NAME([PROC_PARAMETER[,...]])[CHARACTERISTIC...]ROUTINE_BODY10.2.1創(chuàng)建存儲(chǔ)過程【例10-1】創(chuàng)建存儲(chǔ)過程，并使用存儲(chǔ)過程統(tǒng)計(jì)數(shù)據(jù)庫(kù)ssms的表student中的學(xué)生人數(shù)。USEssms;DELIMITER$$CREATEPROCEDUREstu_num(OUTn1int)BEGINSELECTcount(*)INTOn1FROMstudent;END$$DELIMITER;CALLstu_num(@a);SELECT@a;圖10-7使用存儲(chǔ)過程統(tǒng)計(jì)學(xué)生人數(shù)10.2.1創(chuàng)建存儲(chǔ)過程注意在存儲(chǔ)過程的定義中，如果包含多條語(yǔ)句需要使用BEGIN…END結(jié)構(gòu)，則只有單行語(yǔ)句才可以省略。在存儲(chǔ)過程中使用多行語(yǔ)句及分號(hào)時(shí)，必須使用DELIMITER臨時(shí)重新定義分隔符，以便能夠正常完成定義過程。分隔符可以由單個(gè)或多個(gè)字符組成。應(yīng)該避免使用反斜杠（\）字符，因?yàn)檫@是MySQL的轉(zhuǎn)義字?符。10.2.1創(chuàng)建存儲(chǔ)過程【例10-2】創(chuàng)建存儲(chǔ)過程，并使用存儲(chǔ)過程統(tǒng)計(jì)數(shù)據(jù)庫(kù)ssms表student中總學(xué)分大于45的學(xué)生人?數(shù)。DELIMITER$$CREATEPROCEDUREstu_num_tc(INtc1int,OUTn1int)BEGINSELECTcount(*)FROMstudentWHERETotal_Credit>tc1;END$$DELIMITER;

CALLstu_num_tc(45,@a);圖10-8使用存儲(chǔ)過程統(tǒng)計(jì)總學(xué)分大于45的學(xué)生人數(shù)10.2.2

使用變量

在存儲(chǔ)過程中可以定義和使用變量，這些變量為局部變量，只在BEGIN…END代碼塊中有效，執(zhí)行完該代碼塊，變量就消失了?？梢允褂肈ECLARE語(yǔ)句定義局部變量，用DEFAULT語(yǔ)句指明默認(rèn)值。定義變量的語(yǔ)法格式如?下。定義變量的語(yǔ)法格式如下。其中VAR_NAME為所定義的變量名稱，TYPE為變量類型，DEFAULT子句指明變量的默認(rèn)值為VALUE，如果省略DEFAULT子句，則變量初始值為NULL。DECLAREVAR_NAME[,VAR_NAME]...TYPE[DEFAULTvalue]10.2.2

使用變量【例10-3】定義變量a和b，并賦予默認(rèn)值為0。變量的賦值可以使用SET或SELECT…INTO語(yǔ)句來(lái)完成。【例10-4】將學(xué)生人數(shù)賦給變量stu_num?；蛘咦⒁?/p>

DECLARE命令不能單獨(dú)使用，需要放入存儲(chǔ)過程中才能正常使?用。DECLAREa,bintDEFAULT0;DECLAREstu_numintDEFAULT0SETstu_num=21;SELECTcount(*)INTOstu_numFROMstudent;10.2.3定義條件和處理程序1．定義條件定義條件的語(yǔ)法格式如下。

其中，各參數(shù)的含義如?下。①

CONDITION_NAME：條件的名?稱。②

CONDITION_VALUE：條件的類型，其格式如下。SQLSTATE[VALUE]SQLSTATE_VALUE|MYSQL_ERROR_CODESQLSTATE_VALUE參數(shù)和MYSQL_ERROR_CODE參數(shù)都可以表示MySQL的錯(cuò)誤。常見的ERROR1146（42s02）錯(cuò)誤如?下。SQLSTATE_VALUE值是42s02，MYSQL_ERROR_CODE值是1146，具體內(nèi)容是數(shù)據(jù)庫(kù)ssms中不存在數(shù)據(jù)表st_table。DECLARECONDITION_NAMECONDITIONFORCONDITION_VALUESQLSTATE[VALUE]SQLSTATE_VALUE

|MYSQL_ERROR_CODEerror1146(42s02):table'ssms.st_table'doesn'texist10.2.3定義條件和處理程序

為錯(cuò)誤“ERROR1146（42s02）”定義條件“no_such_table”可以使用以下命?令。

或者DECLAREno_such_tableconditionFOR1146;DECLAREno_such_tableconditionFORsqlstate'42s02';10.2.3定義條件和處理程序2．定義處理程序定義處理程序的語(yǔ)法格式如下。其中，各參數(shù)的含義如下。①HANDLER_ACTION：可以取CONTINUE或EXIT，其中CONTINUE表示繼續(xù)執(zhí)行當(dāng)前程序，EXIT表示執(zhí)行終止。②CONDITION_VALUE：表示激活處理程序的條件。其值可以為MYSQL_ERROR_CODE、SQLSTATE_VALUE、CONDITION_NAME、SQLWARNING、NOTFOUND、SQLEXCEPTION，不同激活條件值的含義如表10-3所?示。③STATEMENT：表示一些存儲(chǔ)過程或執(zhí)行語(yǔ)句。DECLAREHANDLER_ACTIONHANDLER

FORCONDITION_VALUE[,CONDITION_VALUE]...

STATEMENT10.2.3定義條件和處理程序激活條件值含義MYSQL_ERROR_CODE錯(cuò)誤代碼數(shù)字SQLSTATE_VALUE包含5個(gè)字符的SQLSTATE值CONDITION_NAMEDECLARE定義的條件名稱SQLWARNING以“01”開頭的SQLSTATE值NOTFOUND以“02”開頭的SQLSTATE值SQLEXCEPTION不以“00”、“01”或“02”開頭的SQLSTATE值表10-3不同激活條件值的含義10.2.3定義條件和處理程序【例10-5】為調(diào)用的表不存在錯(cuò)誤時(shí)定義一個(gè)處理程序。DELIMITER$$CREATEPROCEDUREstu_num_condition(OUTn1int)BEGINDECLAREno_such_tableconditionFOR1146;DECLAREEXITHANDLERFORno_such_tableSET@str='tablenameerror';SELECTcount(*)INTOn1FROMsss;END$$DELIMITER;CALLstu_num_condition(@a);SELECT@str;圖10-9表不存在處理結(jié)果10.2.4光標(biāo)（游標(biāo)）的使用過程光標(biāo)的使用過程包括聲明光標(biāo)、打開光標(biāo)、使用光標(biāo)和關(guān)閉光標(biāo)。1．聲明光標(biāo)聲明光標(biāo)的語(yǔ)法格式如下。

聲明一個(gè)名為cur_student的光標(biāo)的語(yǔ)句如下。2．打開光標(biāo)使用關(guān)鍵字OPEN打開光標(biāo)，語(yǔ)法格式如下。打開cur_student光標(biāo)的語(yǔ)句如下。DECLARECURSOR_NAMECURSORFORSELECT_STATEMENTDECLAREcur_studentCURSORFORSELECTname,majorFROMstudent;OPENCURSOR_NAMEOPENcur_student;10.2.4光標(biāo)（游標(biāo)）的使用過程3．使用光標(biāo)使用關(guān)鍵字FETCH來(lái)使用光標(biāo)，其語(yǔ)法格式如下。FETCH語(yǔ)句獲取與指定光標(biāo)（已打開）關(guān)聯(lián)的SELECT語(yǔ)句的下一行，如果數(shù)據(jù)行存在，則將獲取的字段值存儲(chǔ)在變量中。SELECT語(yǔ)句檢索的列數(shù)必須與FETCH語(yǔ)句中指定的輸出變量數(shù)匹配。如果沒有更多的行可用，則會(huì)出現(xiàn)SQLSTATE值為“02000”的“無(wú)數(shù)據(jù)”錯(cuò)誤。

將光標(biāo)cur_student查詢的一條數(shù)據(jù)存入變量a和b。FETCH[[NEXT]FROM]CURSOR_NAMEINTOVAR_NAME[,VAR_NAME]...FETCHcur_studentINTOa,b;10.2.4光標(biāo)（游標(biāo)）的使用過程4．關(guān)閉光標(biāo)使用關(guān)鍵字CLOSE關(guān)閉光標(biāo)，語(yǔ)法格式如下。關(guān)閉cur_student光標(biāo)使用如下語(yǔ)句。此語(yǔ)句用于關(guān)閉以前打開的光標(biāo)，如果光標(biāo)未打開，則會(huì)發(fā)生錯(cuò)誤。如果未顯式關(guān)閉，則光標(biāo)將在聲明它的BEGIN…END語(yǔ)句塊結(jié)束時(shí)關(guān)閉。CLOSECURSOR_NAMECLOSEcur_student;10.2.4光標(biāo)（游標(biāo)）的使用過程【例10-6】使用光標(biāo)統(tǒng)計(jì)數(shù)據(jù)表course中所有課程的總學(xué)分。DELIMITER$$CREATEPROCEDUREcredits()BEGINDECLAREtotalintDEFAULT0;

DECLAREcreditsCURSORFORSELECTsum(Credit)FROMcourse;OPENcredits;FETCHcreditsINTOtotal;CLOSEcredits;SELECTtotal;END$$DELIMITER;CALLcredits();10.2.5使用流程控制MySQL中的流程控制語(yǔ)句有IF語(yǔ)句、CASE語(yǔ)句、LOOP語(yǔ)句、REPEAT語(yǔ)句、WHILE語(yǔ)句、LEAVE語(yǔ)句和ITERATE語(yǔ)句。1．IF語(yǔ)句IF語(yǔ)句用來(lái)進(jìn)行條件判斷，其語(yǔ)法格式如下。IF語(yǔ)句可以有THEN、ELSE和ELSEIF子句，并以ENDIF結(jié)尾。IFSEARCH_CONDITIONTHENSTATEMENT_LIST[ELSEIFSEARCH_CONDITIONTHENSTATEMENT_LIST]...[ELSESTATEMENT_LIST]ENDIF10.2.5使用流程控制【例10-7】判斷數(shù)字正、負(fù)和零，分別返回1、-1和0。DELIMITER$$CREATEPROCEDUREpm(nINT)BEGINDECLAREsint;IFn>0THENSETs=1;ELSEIFn=0THENSETs=0;ELSESETs=-1;ENDIF;SELECTs;END$$DELIMITER;CALLpm(-6);10.2.5使用流程控制2.CASE語(yǔ)句CASE語(yǔ)句也是用來(lái)進(jìn)行條件判斷的，其語(yǔ)法結(jié)構(gòu)如下。CASE語(yǔ)句還有另外一種語(yǔ)法結(jié)構(gòu)。CASECASE_VALUEWHENWHEN_VALUETHENSTATEMENT_LIST[WHENWHEN_VALUETHENSTATEMENT_LIST]...[ELSESTATEMENT_LIST]ENDCASECASEWHENSEARCH_CONDITIONTHENSTATEMENT_LIST[WHENSEARCH_CONDITIONTHENSTATEMENT_LIST]...[ELSESTATEMENT_LIST]ENDCASE10.2.5使用流程控制【例10-8】創(chuàng)建存儲(chǔ)過程，使用存儲(chǔ)過程按照參數(shù)值不同輸出其結(jié)果字符串。DELIMITER$$CREATEPROCEDUREpm1(innint)BEGINCASEnWHEN0THENSELECT'zero';WHEN1THENSELECT'one';ELSESELECT'no';ENDCASE;END$$DELIMITER;CALLpm1(0);10.2.5使用流程控制3．LOOP語(yǔ)句LOOP語(yǔ)句可以實(shí)現(xiàn)一個(gè)簡(jiǎn)單的循環(huán)構(gòu)造，通常使用LEAVE語(yǔ)句來(lái)退出循環(huán)，否則會(huì)導(dǎo)致死循環(huán)。LOOP語(yǔ)句的語(yǔ)法格式如下。[BEGIN_LABEL:]LOOP

STATEMENT_LISTENDLOOP[END_LABEL]DELIMITER$$CREATEPROCEDUREsum100()BEGINDECLAREsumintDEFAULT0;DECLAREnintDEFAULT0;LABEL1:LOOPSETn=n+1;IFn>100THENLEAVELABEL1;ENDIF;SETsum=sum+n;ENDLOOPLABEL1;SELECTsum;END$$DELIMITER;CALLsum100;10.2.5使用流程控制【例10-9】使用LOOP語(yǔ)句求100以內(nèi)整數(shù)的和。10.2.5使用流程控制4．REPEAT語(yǔ)句REPEAT語(yǔ)句為條件循環(huán)語(yǔ)句，執(zhí)行循環(huán)直到條件表達(dá)式為TRUE。因此REPEAT語(yǔ)句至少會(huì)進(jìn)入一次循環(huán)。REPEAT語(yǔ)句的語(yǔ)法格式如下。[BEGIN_LABEL:]REPEATSTATEMENT_LISTUNTILSEARCH_CONDITIONENDREPEAT[END_LABEL]DELIMITER$$CREATEPROCEDUREdorepeat()BEGINSET@x=1;REPEATSET@x=@x*2;UNTIL@x>100ENDREPEAT;END$$DELIMITER;CALLdorepeat();SELECT@x;10.2.5使用流程控制【例10-10】變量的初始值為1，使用REPEAT語(yǔ)句循環(huán)乘2直到積大于100。10.2.5使用流程控制5．WHILE語(yǔ)句WHILE語(yǔ)句是有條件的循環(huán)語(yǔ)句，其語(yǔ)法格式如下。WHILE循環(huán)判斷的條件在前，只要循環(huán)條件表達(dá)式的值為TRUE，WHILE語(yǔ)句中的語(yǔ)句列表STATEMENT_LIST就會(huì)重復(fù)執(zhí)行。[BEGIN_LABEL:]WHILESEARCH_CONDITIONDOSTATEMENT_LISTENDWHILE[END_LABEL]10.2.5使用流程控制6．LEAVE語(yǔ)句LEAVE語(yǔ)句用于跳出當(dāng)前存儲(chǔ)過程，不執(zhí)行存儲(chǔ)過程中剩余的代碼。其基本語(yǔ)法格式如下。7．ITERATE語(yǔ)句ITERATE語(yǔ)句在循環(huán)語(yǔ)句中使用，用于跳出本次循環(huán)，進(jìn)入下一次循環(huán)。其基本語(yǔ)法格式如下。LEAVE語(yǔ)句和ITERATE語(yǔ)句都用于跳出循環(huán)，LEAVE語(yǔ)句是跳出整個(gè)循環(huán)，執(zhí)行循環(huán)之后的程序；而ITERATE語(yǔ)句則是僅跳出本次循環(huán)，然后進(jìn)入下一次循環(huán)。IEAVELABELITERATELABEL使用存儲(chǔ)過程10.310.3.1調(diào)用存儲(chǔ)過程在MySQL中使用CALL語(yǔ)句調(diào)用存儲(chǔ)過程，其語(yǔ)法格式如下。

通過語(yǔ)法格式可以看出，可以調(diào)用不帶參數(shù)的存儲(chǔ)過程。其中，SP_NAME為存儲(chǔ)過程名，PARAMETER為存儲(chǔ)過程的參?數(shù)。CALLSP_NAME([PARAMETER[,...]])CALLSP_NAME[()]10.3.1調(diào)用存儲(chǔ)過程【例10-11】存儲(chǔ)過程的調(diào)用示例。DELIMITER$$CREATEPROCEDUREp(OUTver_pvarchar(25),INOUTincr_pint)BEGINSELECTversion()INTOver_p;SETincr_p=incr_p+1;END$$DELIMITER;SET@n=10;CALLp(@version,@n);SELECT@version,@n;10.3.2查看存儲(chǔ)過程1．查看存儲(chǔ)過程的創(chuàng)建語(yǔ)句

要查看存儲(chǔ)過程的創(chuàng)建語(yǔ)句，可以使用SHOW命令，其語(yǔ)法格式如?下。【例10-12】查看存儲(chǔ)過程p的創(chuàng)建信息。SHOWCREATEPROCEDUREPROC_NAMESHOWCREATEPROCEDUREp\G;10.3.2查看存儲(chǔ)過程2.查看存儲(chǔ)過程狀態(tài)

要查看存儲(chǔ)過程的狀態(tài)，同樣可以使用SHOW命令，其語(yǔ)法格式如?下。【例10-13】查看存儲(chǔ)過程p的狀態(tài)。SHOWPROCEDURESTATUS[LIKE'PATTERN'|WHEREEXPR]SHOWPROCEDURESTATUSLIKE'p'\G;10.3.2查看存儲(chǔ)過程【例10-14】查看數(shù)據(jù)庫(kù)ssms中所有存儲(chǔ)過程的信?息。SHOWPROCEDURESTATUSWHEREdb='ssms'\G10.3.3修改存儲(chǔ)過程修改存儲(chǔ)過程使用ALTERPROCEDURE語(yǔ)句，其語(yǔ)法格式如下。其中CHARACTERISTIC的語(yǔ)法格式如下。

ALTERPROCEDUREPROC_NAME[CHARACTERISTIC...]COMMENT'STRING'|LANGUAGESQL|{CONTAINSSQL|NOSQL|READSSQLDATA|MODIFIESSQLDATA}|SQLSECURITY{DEFINER|INVOKER}10.3.3修改存儲(chǔ)過程【例10-15】修改存儲(chǔ)過程p，將描述信息COMMENT修改為“myprocedure”。ALTERPROCEDUREpCOMMENT'myprocedure';SHOWPROCEDURESTATUSLIKE'p'\G;

10.3.4刪除存儲(chǔ)過程刪除存儲(chǔ)過程可以通過DROP語(yǔ)句實(shí)現(xiàn)，其語(yǔ)法結(jié)構(gòu)如下?！纠?0-16】刪除存儲(chǔ)過程p。DROPPROCEDURE[IFEXISTS]SP_NAMEDROPPROCEDUREp;DROPPROCEDUREIFEXISTSp;SHOWWARNINGS;【知識(shí)拓展】1．如何進(jìn)行函數(shù)的創(chuàng)建和使用？

函數(shù)與存儲(chǔ)過程的定義、使用方法類似，函數(shù)與存儲(chǔ)過程最大的區(qū)別就是函數(shù)調(diào)用有返回值，調(diào)用存儲(chǔ)過程用CALL語(yǔ)句，而調(diào)用函數(shù)直接引用函數(shù)名和參數(shù)即可。IN、OUT、INOUT3個(gè)參數(shù)前的關(guān)鍵詞只適用于存儲(chǔ)過?程。

定義函數(shù)的語(yǔ)法格式如?下。

其中，各參數(shù)的含義如?下。SP_NAME表示函數(shù)的名稱，RETURNS子句中的TYPE表示函數(shù)返回值的類型。程序體中使用RETURN子句指明返回值。其他參數(shù)和存儲(chǔ)過程相同，在此不再重復(fù)說(shuō)?明。CREATE[DEFINER=USER]FUNCTIONSP_NAME([FUNC_PARAMETER[,...]])RETURNSTYPE[CHARACTERISTIC...]ROUTINE_BODY【知識(shí)拓展】【例10-17】定義函數(shù)hello()，輸出連接后的字符串。CREATEFUNCTIONhello(sCHAR(20))RETURNSchar(50)DETER