數(shù)據(jù)庫原理與開發(fā)技術(shù) 課件 8.2 MySQL存儲(chǔ)過程

8.2MySQL存儲(chǔ)過程（StoreProcedure）本章主要內(nèi)容：（1）MySQL的技術(shù)架構(gòu)：四層架構(gòu)、存儲(chǔ)引擎、系統(tǒng)數(shù)據(jù)庫、

日志、性能優(yōu)化（2）掌握MySQL存儲(chǔ)過程的編程，包括游標(biāo)對(duì)象Cursor的使用（3）掌握MySQL觸發(fā)器的編程（4）掌握Python對(duì)MySQL數(shù)據(jù)庫的插入、修改、刪除、查詢操作8.2MySQL存儲(chǔ)過程（StoreProcedure）

SQL是一種非過程性的語言，具有很強(qiáng)的表達(dá)能力。但它不是一種真正的編程語言，沒有流程控制。而存儲(chǔ)過程的加入，彌補(bǔ)了其不足。

現(xiàn)在數(shù)據(jù)庫應(yīng)用系統(tǒng)，一般采取兩種數(shù)據(jù)處理模式：（1）Client/Server：客戶端/服務(wù)器。

（2）Browse/Server：瀏覽器/服務(wù)器。很多數(shù)據(jù)庫操作方面的工作，可以在客戶端完成，也可以在服務(wù)端完成。在服務(wù)器端，數(shù)據(jù)庫中可以存放程序，存儲(chǔ)過程就是其中之一。

8.2.1存儲(chǔ)過程的概念定義8.1存儲(chǔ)過程是使用SQL語句和流程控制語句編寫的模塊，該模塊經(jīng)編譯和優(yōu)化后，存儲(chǔ)在數(shù)據(jù)庫服務(wù)器端的數(shù)據(jù)庫中，使用時(shí)調(diào)用即可。存儲(chǔ)過程的優(yōu)點(diǎn)主要有：（1）提高運(yùn)行速度。第一次調(diào)用存儲(chǔ)過程時(shí)，需要進(jìn)行編譯（即：語法、語義分析），編譯通過后保存在數(shù)據(jù)庫中，以后調(diào)用時(shí)，可直接執(zhí)行，不用再編譯。相比之下，交互執(zhí)行的SQL語句是解釋執(zhí)行的，速度較慢。

（2）增強(qiáng)了SQL的功能和靈活性。

（3）可以降低網(wǎng)絡(luò)的通信流量。

（4）減輕了程序編寫的工作量。

（5）間接實(shí)現(xiàn)了安全控制功能。

8.2.2存儲(chǔ)過程的創(chuàng)建與調(diào)用在MySQL中，創(chuàng)建存儲(chǔ)過程的語法為：123456Delimiter//CreateProcedure存儲(chǔ)過程名（[In|Out|InOut參數(shù)

變量數(shù)據(jù)類型

]）

BeginDeclare局部變量名

變量類型

[default默認(rèn)值];

語句塊

End;//語法說明：（1）由于MySQL默認(rèn)每條語句的結(jié)束符為分號(hào)“；”，為了避免沖突，使用“Delimiter//”和“//”作為創(chuàng)建存儲(chǔ)過程的開始和結(jié)束。

8.2.2存儲(chǔ)過程的創(chuàng)建與調(diào)用語法說明：（1）由于MySQL默認(rèn)每條語句的結(jié)束符為分號(hào)“；”，為了避免沖突，使用“Delimiter//”和“//”作為創(chuàng)建存儲(chǔ)過程的開始和結(jié)束。Delimiter也可以指定其它符號(hào)作為界定符，例如“$$”。

（2）存儲(chǔ)過程的參數(shù)分3種，其中In表示輸入?yún)?shù)，Out表示為輸出參數(shù)，InOut表示該參數(shù)既可為輸入，也可為輸出。多個(gè)參數(shù)之間用逗號(hào)“,”分隔，參數(shù)的數(shù)據(jù)類型可以是MySQL數(shù)據(jù)庫支持的任意數(shù)據(jù)類型。存儲(chǔ)過程也可以沒有參數(shù)。

（3）存儲(chǔ)過程內(nèi)部的局部變量，用Declare語句聲明，并可賦予默認(rèn)值。

（4）變量的賦值，用“Set變量名

=值;”語句完成。

（5）用語句“Select變量1,變量2;”可以輸出變量的值，多個(gè)變量之間用逗號(hào)“,”分隔。

（6）用語句“Call存儲(chǔ)過程名（[參數(shù)值]）”對(duì)存儲(chǔ)過程進(jìn)行調(diào)用。

8.2.2存儲(chǔ)過程的創(chuàng)建與調(diào)用例8-3針對(duì)例8-2中，恢復(fù)的股票日交易數(shù)據(jù)庫，編寫一個(gè)簡單的存儲(chǔ)過程，查詢2021-05-06日的所有銀行股票的交易信息。1234567Delimiter//CreateProcedurecp_getBank()BeginSelecta.cStockNo,b.vcStockName,mOpen,mHigh,mLow,mClosefromtrDaya,smStockbwherea.cStockNo=b.cStockNoandcDay='20210506'andb.vcStockNamelike'%銀行%';End;//

8.2.2存儲(chǔ)過程的創(chuàng)建與調(diào)用在MySQLWorkBench中，創(chuàng)建及調(diào)用存儲(chǔ)過程的方法，如圖8-16所示。該存儲(chǔ)過程沒有參數(shù)，調(diào)用后，點(diǎn)擊執(zhí)行，直接輸出查詢語句的結(jié)果。

8.2.3存儲(chǔ)過程中的變量類別在MySQL的存儲(chǔ)過程中，有3種類型的變量可以使用。1.用戶變量在存儲(chǔ)過程中，以"@"開始，形式為"@變量名"的變量，稱為用戶變量。它是基于會(huì)話變量實(shí)現(xiàn)的，可以暫存值，并傳遞給同一連接里的下一條SQL使用的變量。當(dāng)客戶端連接退出時(shí)，變量會(huì)被釋放。用戶變量跟MySQL客戶端是綁定的，設(shè)置的變量，只對(duì)當(dāng)前用戶使用的客戶端生效。也叫會(huì)話變量（SessionVariables）?？捎孟旅嬲Z句，顯示所有的會(huì)話變量，及其當(dāng)前的取值：

ShowSessionVariables;

8.2.3存儲(chǔ)過程中的變量類別2.系統(tǒng)變量在存儲(chǔ)過程中，以"@@"開始，形式為"@@變量名"的變量，稱為系統(tǒng)變量，也稱全局變量（GlobalVariables）?？捎孟旅嬲Z句，顯示所有的系統(tǒng)變量，及其當(dāng)前的取值：全局變量影響

MySQL服務(wù)的整體運(yùn)行方式，會(huì)話變量影響具體客戶端連接的操作。每一個(gè)客戶端成功連接服務(wù)器后，都會(huì)產(chǎn)生與之對(duì)應(yīng)的會(huì)話。會(huì)話期間，MySQL服務(wù)實(shí)例會(huì)在服務(wù)器內(nèi)存中生成與該會(huì)話對(duì)應(yīng)的會(huì)話變量，這些會(huì)話變量的初始值是全局變量值的拷貝。

ShowGlobalVariables;

8.2.3存儲(chǔ)過程中的變量類別3.局部變量在存儲(chǔ)過程中，以"Declare變量名"聲明的變量，稱為局部變量。

例8-4針對(duì)例8-2中，恢復(fù)的股票日交易數(shù)據(jù)庫，編寫一個(gè)帶參數(shù)的存儲(chǔ)過程，查詢給定交易日期的股票交易記錄數(shù)，輸出記錄數(shù)。12345Delimiter//CreateProceduregetCount(instrDayChar(8),outiCountInt)BeginSelectcount(*)intoiCountfromtrDaywherecDay=strDay;End;//

8.2.3存儲(chǔ)過程中的變量類別在MySQLWorkBench中，用會(huì)話參數(shù)“@cc”表示調(diào)用存儲(chǔ)過程的輸出參數(shù)，如圖8-17所示。查看會(huì)話參數(shù)“@cc”的值，語句為：Select@cc;8.2.4存儲(chǔ)過程中的程序控制1.If分支條件控制（1）單分支選擇結(jié)構(gòu)（3）多分支選擇結(jié)構(gòu)If條件表達(dá)式

then

語句塊;EndIf;If條件表達(dá)式1then

語句塊1;ElseIf條件表達(dá)式2then

語句塊2;ElseIf條件表達(dá)式3then

語句塊3;…Else

語句塊4;EndIf;（2）雙分支選擇結(jié)構(gòu)If條件表達(dá)式1then

語句塊1;ElseIf條件表達(dá)式2then

語句塊2;EndIf;8.2.4存儲(chǔ)過程中的程序控制2.Case分支控制格式1格式2Case變量When值1then語句1;When值2then語句2;

…Else語句n;EndCase;CaseWhen條件表達(dá)式1then語句1;When條件表達(dá)式2then語句2;

…Else語句n;EndCase;8.2.4存儲(chǔ)過程中的程序控制3.While循環(huán)和Repeat循環(huán)While循環(huán)結(jié)構(gòu)Repeat循環(huán)結(jié)構(gòu)While條件表達(dá)式

do

語句塊;Endwhile;Repeat

語句塊;Until條件表達(dá)式

EndRepeat在While循環(huán)中，先對(duì)指定的表達(dá)式進(jìn)行判斷，如果為真，則執(zhí)行循環(huán)體內(nèi)的語句，否則退出循環(huán)。在Repeat循環(huán)中，先至少一遍執(zhí)行循環(huán)體內(nèi)的語句，再進(jìn)行循環(huán)判斷，如果為真，則繼續(xù)執(zhí)行循環(huán)體內(nèi)的語句，否則退出循環(huán)。8.2.5MySQL存儲(chǔ)過程中的事務(wù)與異常處理1.MySQL存儲(chǔ)過程對(duì)異常的處理方式任何程序在執(zhí)行過程中，可能出現(xiàn)各種錯(cuò)誤。程序在運(yùn)行時(shí)出現(xiàn)錯(cuò)誤或意外，稱為異常。任何數(shù)據(jù)庫管理系統(tǒng)，都有相應(yīng)的異常處理機(jī)制，其內(nèi)容主要包括3點(diǎn)：

（1）錯(cuò)誤的類別。對(duì)各種各樣的錯(cuò)誤進(jìn)行分門別類，類別不同，其處理的方式也不一樣。

（2）錯(cuò)誤的編號(hào)。對(duì)每一種可能的錯(cuò)誤分配一個(gè)代碼，進(jìn)行編號(hào)。

（3）錯(cuò)誤的提示信息。對(duì)每一個(gè)編號(hào)的錯(cuò)誤，綁定對(duì)應(yīng)的錯(cuò)誤信息。MySQL對(duì)異常的處理方式是：事先定義好“條件”和“處理程序”，使程序在執(zhí)行過程中遇到各種問題時(shí)，按照事先已經(jīng)定義好的方式，進(jìn)行處理，避免程序異常終止。8.2.5MySQL存儲(chǔ)過程中的事務(wù)與異常處理2.定義條件

MySQL使用關(guān)鍵詞Declare來定義條件，其語法格式如下：

Declarecondition_nameConditionForcondition_value;參數(shù)說明：

1）condition_name：表示定義的條件名稱；2）condition_value：表示條件的類型。condition_value的語法格式為： SQLState[Value]sqlstate_value|mysql_error_codeSQLState為保留字，[Value]可有可無，sqlstate_value和mysql_error_code表示MySQL的錯(cuò)誤。8.2.5MySQL存儲(chǔ)過程中的事務(wù)與異常處理3.定義處理程序

MySQL使用關(guān)鍵詞Declare來定義處理程序，其語法格式如下：

Declarehandler_typeHandlerForcondition_value[,…]proc_statement;參數(shù)說明：

1）handler_type：指明錯(cuò)誤的處理方式，有3種取值：Continue、Exit、Undo，分別表示遇到錯(cuò)誤不進(jìn)行處理、馬上退出、撤回之前的操作，其中Undo目前MySQL暫不支持；2）condition_value：表示錯(cuò)誤的類型，取值有下面6種情況。

①SQLState[Value]sqlstate_value：包含5個(gè)字符的字符串錯(cuò)誤值。

②condition_name：表示DeclareCondition定義的錯(cuò)誤條件名稱。

③SQLwarning：表示警告，匹配所有以01開頭的SQLState錯(cuò)誤代碼。

④Notfound：表示沒有發(fā)現(xiàn)，匹配所有以02開頭的SQLState錯(cuò)誤代碼。

⑤SQLException：表示意外出錯(cuò)，匹配所有沒有被SQLwarning或Notfound捕獲的SQLState錯(cuò)誤代碼。

⑥mysql_error_code：表示意外出錯(cuò)，匹配數(shù)值類型的錯(cuò)誤代碼。3）proc_statement：為程序語句段，表示在遇到定義的錯(cuò)誤時(shí)，需要執(zhí)行的存儲(chǔ)過程或函數(shù)。8.2.6存儲(chǔ)過程中使用事務(wù)在一段程序體中，如果出現(xiàn)多條SQL語句，且邏輯上是一個(gè)整體，此時(shí)就要顯示定義一個(gè)事務(wù)，并在程序中捕獲意外錯(cuò)誤，進(jìn)行提交或回滾。其語法格式如表8-5所示。declarehandler_typehandlerforcondition_valuesetflag=1;/*聲明一個(gè)處理程序變量*/StartTransaction;SQL語句塊Ifflag!=1then/*沒有發(fā)生意外*/Commit;ElseRollback;Endif8.2.7存儲(chǔ)過程中的光標(biāo)（Cursor）在存儲(chǔ)過程內(nèi)，當(dāng)查詢語句的結(jié)果有多條記錄時(shí)，程序可以根據(jù)需要對(duì)結(jié)果集進(jìn)行滾動(dòng)而依次逐條讀取其中的記錄，這就是光標(biāo)，也稱游標(biāo)。光標(biāo)的操作分4步：聲明光標(biāo)變量、打開光標(biāo)、使用和關(guān)閉光標(biāo)。其語法結(jié)構(gòu)，如表8-6所示。declare

游標(biāo)名

cursorfor查詢語句;/*定義游標(biāo)*/Declarecontinuehandlerfornotfoundsetflag=1;/*聲明一個(gè)游標(biāo)遍歷狀態(tài)變量*/Open

游標(biāo)名;/*打開游標(biāo)*/fetch

游標(biāo)名

into

變量名;/*獲取游標(biāo)當(dāng)前記錄的變量值*/whileflag!=1do

語句塊；fetch游標(biāo)名

into變量名;/*獲取游標(biāo)當(dāng)前記錄的變量值*/endwhile;/*當(dāng)

flag等于1時(shí)表明遍歷已完成，退出循環(huán)*/Close

游標(biāo)名;/*關(guān)閉游標(biāo)*/

8.2.6存儲(chǔ)過程的完整案例例8-7本章數(shù)據(jù)附件“insertSql_Chinese.txt”收錄了常見的6836個(gè)漢字編碼的insert語句。利用這些數(shù)據(jù)，編寫一個(gè)存儲(chǔ)過程，輸入一串漢字，輸出為這串漢字的拼音碼。上機(jī)時(shí)，先根據(jù)下面SQL語句創(chuàng)建漢字編碼表，再運(yùn)行“insertSql_Chinese.txt”的插入語句。12345678CreateTablesmChinese/*------------------------漢字編碼表---------------------------*/(cChineseNo char(6)notnull /*漢字編碼*/

cChineseNamechar(2)notnull, /*漢字名稱*/cPY char(1)notnull,

/*拼音碼*/cWB char(4)notnull,

/*五筆碼*/constraintsmChinese_PKprimarykey(cChineseNo));

8.2.6存儲(chǔ)過程的完整案例編程思路：對(duì)給定的一串漢字進(jìn)行循環(huán)，利用子字符串函數(shù)Substring()，從左邊開始，每次取一個(gè)漢字，通過漢字編碼表，查詢該漢字的拼音碼，然后利用字符串拼接函數(shù)concat()，將每個(gè)漢字的拼音碼拼接起來，賦值給輸出變量。注意源代碼15行，concat()函數(shù)的用法。1234567891011DELIMITER//CREATEPROCEDUREcs_ChineseTest(instrNamevarchar(60),outPYCodevarchar(60))/*定義存儲(chǔ)過程

cs_ChineseTest，輸入一串漢字strName，輸出對(duì)應(yīng)的拼音碼：PYCode*/BegindeclarePYchar(1);/*單個(gè)漢字的首個(gè)拼音碼*/declarecChinesechar(2);/*單個(gè)漢字*/declareiForintdefault0;/*循環(huán)變量*/declareiLengthintdefault0;/*中文詞組

strName所含漢字個(gè)數(shù)(長度)*/

declarevcPYCodevarchar(50)default'';/*中文詞組

strName的拼音碼*/setstrName=ltrim(rtrim(strName));/*set:設(shè)置變量的值，刪除前后空格*/setiLength=CHAR_LENGTH(strName);/*中文strName所含漢字個(gè)數(shù)(長度)*/

8.2.6存儲(chǔ)過程的完整案例編程思路：對(duì)給定的一串漢字進(jìn)行循環(huán)，利用子字符串函數(shù)Substring()，從左邊開始，每次取一個(gè)漢字，通過漢字編碼表，查詢該漢字的拼音碼，然后利用字符串拼接函數(shù)concat()，將每個(gè)漢字的拼音碼拼接起來，賦值給輸出變量。注意源代碼15行，concat()函數(shù)的用法。1213141516171819

whileiFor<=iLengthdosetcChinese=Substring(strName,iFor,1);/*依次從左邊開始，每次取出一個(gè)漢字*/selectcPYintoPYfromsmchinesewherecChineseName=cChinese;/*漢字的第一個(gè)拼音*/setPYCode=concat(if(isnull(PYCode),'',PYCode),if(isnull(PY),'',PY));selectcChinese,PYCode,PY; /*select:輸出變量的值，用于測試*/setiFor=iFor+1;/*每循環(huán)一次，計(jì)數(shù)變量加1*/Endwhile;end;//

8.2.6存儲(chǔ)過程的完整案例調(diào)用存儲(chǔ)過程：callcs_ChineseTest('中華人民共和國',@PY);

查看會(huì)話變量的值：select@PY;

從輸出的參數(shù)可以看出，“中華人民共和國”對(duì)應(yīng)的拼音碼為“ZHRMGHG”，即：每個(gè)漢字的第一個(gè)拼音字母。

8.2.6存儲(chǔ)過程的完整案例例8-8本章數(shù)據(jù)附件“insertSql_book.txt”收錄了一些圖書的insert語句。利用這些數(shù)據(jù)，編寫一個(gè)存儲(chǔ)過程，根據(jù)每本書的書名，生成書名的拼音碼，并對(duì)表的拼音碼字段vcPYCode進(jìn)行更新。其中，圖書基本信息表的結(jié)構(gòu)如下：12345678910

CreateTablebook /*----------------------------圖書基本信息表-----------------------------*/ (cBookNo char(8) notnull, /*圖書編號(hào)*/vcBookName varchar(60) notnull, /*書名*/vcPYCode

varchar(50) null, /*拼音碼*/vcWBCode varchar(50) null, /*五筆碼*/

cStatus

char(1) notnulldefault'1',/*當(dāng)前狀態(tài):1-在庫,2-借出*/bActive tinyint

default1notnull,/*是否有效*/constraintbook_pkprimarykey(cBookNo));

8.2.6存儲(chǔ)過程的完整案例編程思路：先根據(jù)查詢圖書的Select語句定義一個(gè)游標(biāo)，打開游標(biāo)后，對(duì)游標(biāo)的每條記錄進(jìn)行循環(huán)，獲取當(dāng)前記錄的圖書編號(hào)和書名，然后根據(jù)書名，從左邊開始，每次取一個(gè)漢字，通過漢字編碼表，查詢該漢字的拼音碼，然后利用字符串拼接函數(shù)concat()，將每個(gè)漢字的拼音碼拼接起來，生成書名的拼音碼，再修改書的拼音碼vcPYCode的值。難點(diǎn)解析：（例8-8源代碼）

在打開游標(biāo)前，聲明了一個(gè)意外錯(cuò)誤的條件變量“flag”（源代碼第16行），一旦游標(biāo)讀取下一行失敗，則設(shè)置flag=1。但是，由于循環(huán)過程中，還執(zhí)行了別的Select查詢語句，它們會(huì)誤導(dǎo)第16行代碼的執(zhí)行，這樣，第32行源代碼“setflag=0;”就不能缺少，否則，循環(huán)會(huì)過早結(jié)束。

8.2.6存儲(chǔ)過程的完整案例123456789101112131415161718Delimiter//CreateProcedurecs_PYWBcode()/*------------------------------------------------------------------------------------------------------------------

根據(jù)book中所有記錄的書名vcBookName，生成拼音碼，并修改表中的字段vcPYCode的值----------------------------------------------------------------------------------------------------------------------*/BegindeclareBookNovarchar(8);/*書的編號(hào)*/declareBookNamevarchar(60);/*書名*/declarePYchar(1);/*單個(gè)漢字的首個(gè)拼音碼*/declarecChinesevarchar(2);/*單個(gè)漢字*/declareiForintdefault0;/*循環(huán)變量*/

declareiLengthintdefault0;/*書名BookName所含漢字個(gè)數(shù)(長度)*/declarePYCodevarchar(50);/*書名BookName所對(duì)應(yīng)的拼音碼*/declareflagintdefault0;/*游標(biāo)中，處理程序的指示器變量*/declarecurTempcursorforselectcBookNo,vcBookNamefromBook;/*定義游標(biāo)*/

declarecontinuehandlerfornotfoundsetflag=1;/*定義捕獲意外錯(cuò)誤的處理過程變量*/opencurTemp;/*打開游標(biāo)*/fetchcurTempintoBookNo,BookName; /*獲取游標(biāo)當(dāng)前記錄的變量值*/

8.2.6存儲(chǔ)過程的完整案例19202122232425262728293031323334353637

whileflag<>1do/*對(duì)游標(biāo)中的每一條記錄，進(jìn)行循環(huán)*/setBookName=ltrim(rtrim(BookName));setiLength=CHAR_LENGTH(BookName);setiFor=0;setPYCode='';repeat/*第二層循壞：根據(jù)當(dāng)前的書名BookName，逐個(gè)漢字生成拼音碼*/setcChinese=Substring(BookName,iFor,1);selectcPYintoPYfromsmchinesewherecChineseName=cChinese;setPYCode=concat(if(isnull(PYCode),'',PYCode),if(isnull(PY),'',PY));setiFor=iFor+1;untiliFor>iLengthendrepeat;/*結(jié)束第二層的循環(huán)*/selectPYCode,BookNo,BookName;/*select:輸出當(dāng)前變量值，用于測試*/updateBooksetvcPYCode=PYCodewherecBookNo=BookNo;/*更新書的拼音碼*/setflag=0;/*這個(gè)不能少，否則不循壞，因?yàn)檠h(huán)內(nèi)還執(zhí)行了查詢,*//*如果該查詢沒有記錄返回，也會(huì)把flag改為1*/fetchcurTempintoBookNo,BookName; endwhile;/*當(dāng)flag等于1時(shí)表明遍歷已完成，退出循環(huán)*/closecurTemp;/*關(guān)閉游標(biāo)*/end;//

8.2.6存儲(chǔ)過程的完整案例

例8-9利用例3-2中的教師表T、學(xué)生表S、圖書表Book、借（還）書數(shù)據(jù)表smBorrow的表結(jié)構(gòu)，編寫一個(gè)含有事務(wù)的存儲(chǔ)過程，要求：在smBorrow插入一條記錄時(shí)，同時(shí)將對(duì)應(yīng)的圖書的狀態(tài)cStatus改為“借出”。存儲(chǔ)過程的輸入?yún)?shù)為cBorrowNo（學(xué)生編號(hào)）、cBookNo（圖書編號(hào)）。如果借書成功，輸出參數(shù)為“借書成功！”，如果借書失敗，輸出參數(shù)為執(zhí)行SQL語句的錯(cuò)誤信息。1234567891011

CreateTablebook /*------------------------圖書基本信息表-----------------------------------*/ (cBookNo char(8) notnullprimarykey,/*圖書編號(hào)*/vcBookName varchar(60)notnull, /*書名*/

cStatus char(1) notnulldefault‘1’/*當(dāng)前狀態(tài):1-在庫,2-借出*/);CreateTablesmBorrow/*------------------圖書借(還)信息表----------------------------------*/(iID intnotnullauto_increment,/*借書流水號(hào)*/dtBorrowDatedatetimenotnulldefaultnow(),/*借書日期*/cBookNo char(8)notnull, /*圖書編號(hào)*/cBorrowNo char(8) null, /*借閱人編號(hào)*/cTypechar(1)notnulldefault'1',/*借閱人類別:1-學(xué)生,2-教師*/);

8.2.6存儲(chǔ)過程的完整案例編程思路：先定義一個(gè)處理程序的指示器變量flag，它能捕獲存儲(chǔ)過程內(nèi)所有SQL語句執(zhí)行出錯(cuò)情況。然后聲明一個(gè)事務(wù)。如果所有的SQL語句執(zhí)行完后，flag還是等于0，就提交事務(wù)；否則，表名至少有一條SQL語句執(zhí)行出錯(cuò)，則回滾事務(wù)。12345678910Delimiter//CreateProcedurecp_BorrowBook(inBorrowNochar(8),inBookNochar(8),outvcMemovarchar(255))/*---------------輸入?yún)?shù)：BorrowNo-借書人編號(hào)，BookNo-所借書的編號(hào)

----------------------------------輸出參數(shù)：vcMemo-SQL語句執(zhí)行信息---------------------------------------------*/BeginDeclarec_Statuschar(1)default'1';/*所借書的狀態(tài)：1-在庫，2-借出*/Declareflagchar(1)default'0';/*捕獲錯(cuò)誤時(shí)的指示器變量*/DeclareexithandlerforSQLExceptionsetflag='1',vcMemo='SQL語句執(zhí)行意外錯(cuò)誤！';StartTransaction;/*開始事務(wù)*/SelectcStatusintoc_StatusfromBookwherecBookNo=BookNo;/*獲取書的狀態(tài)*/

8.2.6存儲(chǔ)過程的完整案例調(diào)用存儲(chǔ)過程：callcp_BorrowBook('0107101','B0000002',@vcMemo);

查看執(zhí)行信息：select@vcMemo;12345678910Ifc_Status='2'thensetvcMemo='要借的書已經(jīng)借出。不能重復(fù)借！';Rollback;Else/*cType='1'表示學(xué)生借書*/InsertintosmBorrow(cBorrowNo,cBookNo,cType)values(BorrowNo,BookNo,‘1’);UpdateBooksetcStatus=‘2’wherecBookNo=BookNo;/*將書的狀態(tài)修改為：2-借出*/Ifflag=‘0’then/*所有SQL語句都執(zhí)行成功*/Commit;/*提交事務(wù)*/setvcMemo='借書成功！';ElseRollback;/*回滾事務(wù)*/Endif;Endif;End;//

8.2.6存儲(chǔ)過程的完整案例例8-10根據(jù)例8-2中恢復(fù)的股票日交易表trDay的數(shù)據(jù)，編寫一個(gè)存儲(chǔ)過程，輸入?yún)?shù)為交易日，計(jì)算該日所有股票的乖離率Bias(6)和移動(dòng)平均MA(30)，其中涉及到表trDay中的字段為：

trDay(cDay，cStockNo，mClose，Bias6，MA1)

乖離率Bias(6)的計(jì)算公式為：

當(dāng)日收盤價(jià)-前6日(含當(dāng)日)的平均收盤價(jià)

30天移動(dòng)平均MA(30)的計(jì)算公式為：MA(30)=前30日(含當(dāng)日)的平均收盤價(jià)

Bias(6)=100*前6日(含當(dāng)日)的平均收盤價(jià)編程思路：聲明一個(gè)捕獲update語句執(zhí)行意外的變量err。一旦捕獲到update語句執(zhí)行出現(xiàn)意外，則退出游標(biāo)中的循環(huán)，并跟蹤輸出錯(cuò)誤信息。而如果循環(huán)正常結(jié)束后，還沒有捕獲到update語句執(zhí)行出現(xiàn)意外，則輸出變量設(shè)置為“成功”。30天移動(dòng)平均

例8-10計(jì)算該日所有股票的乖離率Bias(6)和移動(dòng)平均MA(30)123456789101112Delimiter//Createprocedurecp_MA30(instrDaychar(8),outvcMemovarchar(60))--vcMemo：計(jì)算結(jié)果提示/*----計(jì)算給定股票交易日strDay的6天乖離率Bias(6)和30天的移動(dòng)平均MA(30)------*/Begindeclarec_StockNochar(8);/*股票代碼：注意，數(shù)據(jù)類型及長度與表結(jié)構(gòu)要一樣*/declarem_Closedecimal(6,2);/*當(dāng)日收盤價(jià)：注意,局部變量名不要與表的列名一樣*/declareiCountintdefault0;/*交易天數(shù)*/declarebias_6decimal(6,2);/*6日乖離率*/declareMA_6decimal(6,2);/*前6日(含當(dāng)日)收盤價(jià)的平均值*/declareMA_30decimal(6,2);/*前30日（含當(dāng)日）收盤價(jià)的平均值*/declareflagintdefault0;/*游標(biāo)移動(dòng)是否結(jié)束：flag=1表示結(jié)束*/declareerrintdefault0;/*執(zhí)行update是否出現(xiàn)意外：err=1表示出現(xiàn)意外*/

例8-10計(jì)算該日所有股票的乖離率Bias(6)和移動(dòng)平均MA(30)1314151617181920212223

declarecurTempcursorforselectcStockNo,mClosefromtrDaywherecDay=strDay;/*定義游標(biāo)*/declarecontinuehandlerfornotfoundsetflag=1;/*定義捕獲意外錯(cuò)誤的處理過程變量*/declarecontinuehandlerforsqlexceptionseterr=1,vcMemo='update語句執(zhí)行錯(cuò)誤';/*捕獲意外*/

StartTransaction;/*聲明一個(gè)事務(wù)：要在打開游標(biāo)前*/opencurTemp;/*打開游標(biāo)*/fetchcurTempintoc_StockNo,m_Close; /*獲取游標(biāo)當(dāng)前記錄的變量值*/whileflag=0do/*對(duì)游標(biāo)中的每一條記錄，進(jìn)行循環(huán)*/sele