MySQL數(shù)據(jù)庫應(yīng)用與開發(fā)技術(shù) 課件 第07章-存儲過程

MySQL數(shù)據(jù)庫應(yīng)用與開發(fā)技術(shù)第7章存儲過程本單元教學(xué)目標(biāo)了解存儲過程的種類認(rèn)識、了解存儲過程的作用掌握存儲過程的開發(fā)語法熟練使用數(shù)據(jù)庫IDE開發(fā)、調(diào)試存儲過程

存儲過程概念（一）SQL語句：SQL語句在執(zhí)行的時候需要先編譯，然后執(zhí)行存儲過程：存儲過程（StoredProcedure）是一組為了完成特定功能的SQL語句集經(jīng)編譯后存儲在數(shù)據(jù)庫中用戶通過指定存儲過程的名字與參數(shù)（如果該存儲過程帶有參數(shù)）來調(diào)用執(zhí)行它。3

存儲過程概念（二）一個存儲過程是一個可編程的函數(shù)它在數(shù)據(jù)庫中創(chuàng)建并保存它由SQL語句和一些特殊的控制結(jié)構(gòu)組成存儲過程適用于以下場合在不同的平臺（應(yīng)用程序）上執(zhí)行相同的函數(shù)封裝特定功能是數(shù)據(jù)庫編程中面向?qū)ο蠓椒ǖ哪M4存儲過程優(yōu)點（一）(1).存儲過程增強了SQL語言的功能和靈活性存儲過程可以用流控制語句編寫，有很強的靈活性可以完成復(fù)雜的判斷和較復(fù)雜的運算(2).存儲過程是允許編程的標(biāo)準(zhǔn)組件被創(chuàng)建后，可以在程序中被多次調(diào)用，而不必重新編寫該存儲過程的SQL語句數(shù)據(jù)庫專業(yè)人員可以隨時對存儲過程進(jìn)行修改，對應(yīng)用程序源代碼毫無影響(3).存儲過程能實現(xiàn)較快的執(zhí)行速度如果某一操作包含大量的SQL代碼或分別被多次執(zhí)行，存儲過程比批處理執(zhí)行速度快很多，因存儲過程是預(yù)編譯的批處理的SQL語句在每次運行時要進(jìn)行編譯，速度相對慢5存儲過程優(yōu)點（二）(4).存儲過程能過減少網(wǎng)絡(luò)流量。當(dāng)同一操作所涉及的眾多SQL語句被編碼到存儲過程中在客戶機(jī)上調(diào)用該存儲過程時，網(wǎng)絡(luò)中只需傳送該調(diào)用語句從而大大減少了網(wǎng)絡(luò)流量并降低了網(wǎng)絡(luò)負(fù)載(5).存儲過程被作為一種安全機(jī)制來充分利用系統(tǒng)管理員通過執(zhí)行某一存儲過程的權(quán)限進(jìn)行限制能夠?qū)崿F(xiàn)對相應(yīng)的數(shù)據(jù)的訪問權(quán)限的限制，避免了非授權(quán)用戶對數(shù)據(jù)的訪問，從而保證了數(shù)據(jù)的安全6存儲過程的創(chuàng)建MySQL格式：CREATEPROCEDURE

存儲過程名

([過程參數(shù)[,...]])BEGIN…過程控制語句（Transaction-SQL）…END7存儲過程案例｜創(chuàng)建User表CREATEDATABASEIFNOTEXISTSmydb;USEmydb;DROPTABLEIFEXISTS`user`;CREATETABLE`user`(`user_id`int(10)unsignedNOTNULLauto_increment,`user_name`varchar(45)NOTNULL,`pass_word`varchar(45)NOTNULL,`email`varchar(45)NOTNULL,`phone`varchar(45)NOTNULL,`sex`char(1)NOTNULL,`score`int(10)unsignedNOTNULL,PRIMARYKEY(`user_id`))ENGINE=InnoDBAUTO_INCREMENT=11DEFAULTCHARSET=utf8;INSERTINTO`user`(`user_id`,`user_name`,`pass_word`,`email`,`phone`,`sex`,`score`)VALUES(1,'LiMing','LiMing','LiMing@','83278904','0',60),(2,'ZhuangPing','ZhuangPing','ZhuangPing@','83278678','0',70),(3,'LuMei','LuMei','LuMei@','83278904','1',80),(4,'QiaoBing','QiaoBing','QiaoBing@','83278452','1',70),(5,'Kerry','Kerry','Kerry@','83278678','1',50),(6,'Jetty','Jetty','Jetty@','83278904','0',90),(7,'Lucy','Lucy','Lucy@','83278904','0',40),(8,'Honey','Honey','Honey@','83278904','1',80),(9,'Wendy','Wendy','Wendy@','83278452','0',50),(10,'Rose','Rose','Rose@','83278904','0',30);8存儲過程案例｜開發(fā)編碼DELIMITER//CREATEPROCEDUREproc1(insint)BEGINupdateusersetscore=swhereuser_idin(2,4,6,8,10);END//DELIMITER;9案例語句解釋DELIMITER//表示把MySQL分隔符修改為："http://"MySQL默認(rèn)以“;”為分隔符DELIMITER;用完了之后要把分隔符還原為：";"存儲過程根據(jù)需要可能會有輸入、輸出、輸入輸出參數(shù)本案例中有一個輸入?yún)?shù)s，類型是int型如果有多個參數(shù)用","分割開過程體的開始與結(jié)束使用BEGIN與END進(jìn)行標(biāo)識。10存儲過程調(diào)用調(diào)用格式如下：call+存儲過程名+參數(shù)callproc1(100);調(diào)用后看到user表中user_id為：2、4、6、8、10的記錄的score字段值變?yōu)榱?00刪除存儲過程格式：drop+PROCEDURE+存儲過程名dropPROCEDUREproc1;11存儲過程參數(shù)存儲過程共有三種參數(shù)類型,IN,OUT,INOUT,形式如：CREATEPROCEDURE([[IN|OUT|INOUT]參數(shù)名數(shù)據(jù)類形...])IN輸入?yún)?shù):表示該參數(shù)值必須在調(diào)用存儲過程時指定，在存儲過程中修改該參數(shù)值將不被返回，為默認(rèn)值OUT輸出參數(shù):該值可在存儲過程內(nèi)部被改變，并可返回INOUT輸入輸出參數(shù):調(diào)用時指定，并且可被改變和返回12IN參數(shù)例子DELIMITER//CREATEPROCEDUREdemo_in_parameter(IN

p_id

int,INp_score_add

int)BEGINupdateusersetscore=(score+p_score_add)whereuser_id=p_id;END;//DELIMITER;13存儲過程調(diào)用｜IN參數(shù)直接調(diào)用：CALLdemo_in_parameter(10,5000);通過預(yù)定義參數(shù)變量調(diào)用：先定義兩個參數(shù)變量set@p_id=10;set@p_score_add=5000;再把變量作為參數(shù)調(diào)用CALLdemo_in_parameter(@p_id,@p_score_add);

調(diào)用后可看到user_id

為10的score字段的

值增加了500014OUT參數(shù)例子DELIMITER//CREATEPROCEDUREdemo_out_parameter(OUT

p_out

int)BEGINSELECTCOUNT(*)INTOp_outFROMuser;END//DELIMITER;15存儲過程調(diào)用｜OUT參數(shù)通過預(yù)定義輸出參數(shù)變量調(diào)用：先定義輸出參數(shù)變量set@p_out=0;再把變量作為參數(shù)調(diào)用CALLdemo_out_parameter(@p_out);

可以看到：調(diào)用前p_out的變量值為0調(diào)用后p_out的變量值為1016INOUT參數(shù)例子DELIMITER//CREATEPROCEDUREdemo_inout_parameter(INOUT

p_inout_num

int,INOUT

p_inout_strvarchar(50))BEGINDECLAREidintdefault0;SETid=p_inout_num;UPDATEuserSETemail=p_inout_strWHEREuser_id=id;SELECTscoreINTOp_inout_numFROMuserWHEREuser_id=id;SELECTphoneINTOp_inout_strFROMuserWHEREuser_id=id;END//DELIMITER;17INOUT參數(shù)例子通過預(yù)定義輸出參數(shù)變量調(diào)用：先定義輸出參數(shù)變量set@p_inout_num=5;set@p_inout_str='Test@163.com';再把變量作為參數(shù)調(diào)用calldemo_inout_parameter(@p_inout_num,@p_inout_str);18INOUT參數(shù)例子｜結(jié)果分析

調(diào)用前：

p_inout_num

的變量值為5

p_inout_str

的變量值為‘Test@163.com’調(diào)用后：

p_inout_num

的變量值為50

對應(yīng)score字段值

p_inout_str的變量值為‘83278678’

對應(yīng)phone字段值19課堂練習(xí)1寫一個存儲過程，實現(xiàn)以下功能：把上面USRE表中積分（score）大于60的用戶同步到一個VIP_USER表中insertintonew_table

(column1,column2)

selectcolumn1,column2fromold_table給積分60以下的女性會員（sex字段值為0），每人加5分返回以上操作以后男會員的平均分與女會員的平均分20變量mysql存儲過程中，定義變量有兩種方式：會話變量：也叫用戶變量，使用set直接賦值，變量名以@開頭例如:set@num=1;可以在一個客戶端會話的任何地方聲明，作用域是整個會話。存儲過程變量：以DECLARE關(guān)鍵字聲明的變量，只能在存儲過程中使用，例如：DECLAREmynumINTDEFAULT0;

主要用在存儲過程中，或者是給存儲傳參數(shù)中。兩者的區(qū)別是：以DECLARE聲明的變量都會被初始化為NULL而會話變量（即@開頭的變量）則不會被再初始化在一個會話內(nèi)，只須初始化一次會話斷開后，會話變量也就消失21存儲過程變量變量定義DECLARE+變量名+數(shù)據(jù)類型+[DEFAULTvalue];數(shù)據(jù)類型為MySQL的數(shù)據(jù)類型如:int,float,date,varchar(length)默認(rèn)值：DEFAULTvalue，可有可無必須定義在代碼最前面例如:DECLAREmy_int

intdefault4000000;DECLAREmy_numericnumber(8,2)DEFAULT9.95;DECLAREmy_datedateDEFAULT'1999-12-31';DECLAREmy_datetime

datetimeDEFAULT'1999-12-3123:59:59';DECLAREmy_varcharvarchar(255)DEFAULT'Thiswillnotbepadded';

22存儲過程變量DECLARE關(guān)鍵字必須放在代碼最前面

錯誤的定義方式：

第2個DECLARE放在其它代碼后面

正確的定義方式：

兩個DECLARE都放在代碼的最前面23存儲過程變量變量賦值SET變量名=表達(dá)式值

例如:SETmy_int=100SETmy_numeric=11.02SETmy_date='2009-11-21'SETmy_datetime=‘2009-11-2120:50:50'SETmy_varchar='Hello'變量取值直接調(diào)用變量名即可取得變量值例如:INSERTINTOtable1VALUES(my_int)上面的INSERT語句直接用前面定義的my_int

變量名，即可取得變量值10024變量案例DELIMITER//DROPPROCEDUREIFEXISTSproc_declare_demo//CREATEPROCEDUREproc_declare_demo(INp_inINTEGER)BEGINDECLAREmystrCHAR(10);

IFp_in=17THENSETmystr='--birds--';ELSESETmystr='--beasts--';ENDIF;INSERTINTO`user`(`user_name`,`pass_word`,`email`,`phone`,`sex`,`score`)VALUES(mystr,mystr,'----@----','81234567','0',111);END//DELIMITER;

25變量作用域變量的作用范圍：往上回溯，從最靠近變量的第一個begin開始往下延伸，到與上面begin相匹配的end結(jié)束內(nèi)部的變量比外部變量在其作用域范圍內(nèi)享有更高的優(yōu)先權(quán)26變量作用域案例DELIMITER//DROPPROCEDUREIFEXISTSproc3//CREATEPROCEDUREproc3()begindeclarex1varchar(5)default'outer';

begindeclarex1varchar(5)default'inner';

selectx1;

end;selectx1;

end;//DELIMITER;27課程練習(xí)2寫一個存儲過程，實現(xiàn)以下功能：給上面用戶表(User表)，增加一個備注字段(mark)ALTERTABLE`user`ADDCOLUMN`mark`VARCHAR(45)AFTER`score`用DECLARE定義幾個存儲過程變量變量1值為：“不活躍會員”變量2值為：“普通會員”變量3值為：“高級會員”根據(jù)上面定義的變量填充mark字段如果積分在60分以下，填充變量1的值如果積分在60-79分，填充變量2的值如果積分在80分以上，填充變量3的值返回以上操作以后每個等級會員的個數(shù)28條件語句｜if…thenif…then…elseif…then…else語句if后面跟判斷條件elseif后面跟判斷條件then后面跟條件分支語句塊可以有多個elseif…then語句塊，也可以沒有else表示以上條件均不滿足時會執(zhí)行的語句塊整個條件語句塊的最后面以endif表示結(jié)束29條件語句案例｜if…thenDELIMITER//DROPPROCEDUREIFEXISTSproc_if_else//CREATEPROCEDUREproc_if_else(INif_parameterint)begindeclarecondition_paraint;setcondition_para=if_parameter;if

condition_para=1

then

INSERTINTO`user`(`user_name`,`pass_word`,`email`,`phone`,`sex`,`score`)VALUES('if_user','if_user','if_user@','88888888','0',100);elseifcondition_para=2then

update`user`setscore=200whereuser_id<=5;elseifcondition_para=3then

update`user`setscore=400whereuser_id>5;elseupdate`user`setscore=1000;endif;end;//DELIMITER;30課程練習(xí)3寫一個存儲過程：如果輸入?yún)?shù)值為1把user表數(shù)據(jù)同步到user_info表如果輸入?yún)?shù)值為2刪除user_info表數(shù)據(jù)如果輸入?yún)?shù)值為3把user表數(shù)據(jù)同步到user_bak表如果輸入?yún)?shù)值為4刪除user_bak表數(shù)據(jù)如果輸入?yún)?shù)值為5同時刪除user_info

、user_bak表數(shù)據(jù)user、user_info

、user_bak

三個表結(jié)構(gòu)相同用if…then…elseif…then…else語句實現(xiàn)31條件語句｜case…whencase…when…then…else語句case后面跟條件變量when后面跟條件值then后面跟條件分支語句塊可以有多個when…then語句塊，也可以只有有一個else表示以上條件均不滿足時會執(zhí)行的語句塊整個條件語句塊最后面以endcase表示結(jié)束32條件語句案例｜case…whenDELIMITER//DROPPROCEDUREIFEXISTSproc_case_when//CREATEPROCEDUREproc_case_when(INcase_parameterint)begindeclarecondition_paraint;setcondition_para=case_parameter;case

condition_parawhen

1

then

INSERTINTO`user`(`user_name`,`pass_word`,`email`,`phone`,`sex`,`score`)VALUES('case_user','case_user','case_user@','7777777','0',200);when

2

then

update`user`setscore=300whereuser_id<=5;when

3

then

update`user`setscore=600whereuser_id>5;else

update`user`setscore=2000;endcase;end;//DELIMITER;33課程練習(xí)4用case…when…then…else語句改寫課程練習(xí)334循環(huán)語句｜while…dowhile…do語句while后面跟條件表達(dá)式do后面跟條件分支語句塊整個條件語句塊最后面以endwhile表示結(jié)束35循環(huán)語句案例｜while…doDELIMITER//CREATEPROCEDUREproc_while_do()begindeclareaint;seta=0;while

a<100

do

INSERTINTO`user`(`user_name`,`pass_word`,`email`,`phone`,`sex`,`score`)VALUES('case_user','case_user','case_user@','7777777','0',a);seta=a+1;endwhile;end;//DELIMITER;

36課程練習(xí)5用while…do循環(huán)語句往User_Info表、User_Bak表各插入500條記錄User_Info、User_Bak表與User表結(jié)構(gòu)相同37循環(huán)語句｜repeat…untilrepeat…until語句until后面跟條件表達(dá)式（跳出循環(huán)的條件）repeat后面跟條件分支語句塊整個條件語句塊最后面以endrepeat表示結(jié)束先執(zhí)行repeat操作后檢查until條件語句，而while則是執(zhí)行前進(jìn)行檢查。38循環(huán)語句案例｜repeat…untilDELIMITER//CREATEPROCEDUREproc_repeat_until()begindeclarebint;setb=0;repeatINSERTINTO`user`(`user_name`,`pass_word`,`email`,`phone`,`sex`,`score`)VALUES('repeat_user','repeat_user','repeat_user@','11111111','0',b);setb=b+1;until

b>=200

endrepeat;end;//DELIMITER;

39課程練習(xí)6用repeat…until語句改寫課程練習(xí)540單元作業(yè)1用存儲過程對商品銷售明細(xì)表（order_detail）作如下統(tǒng)計操作：按月統(tǒng)計出7、8月份的銷售金額，把數(shù)據(jù)匯總到月銷售匯總表（order_month）按商品類型統(tǒng)計出各種類型商品銷售金額，把數(shù)據(jù)匯總到類型銷售匯總表（order_total_type）按月份及商品類型統(tǒng)計出7、8月份各種類型商品銷售金額，把數(shù)據(jù)匯總到月類型銷售匯總表（order_total_type_month）參考語句：insertintonew_table

(column1,column2)

selectcolumn1,column2fromold_table41建表腳本CREATEDATABASEIFNOTEXISTSmydb;USEmydb;DROPTABLEIFEXISTS`order_detail`;CREATETABLE`order_detail`(`order_id`int(10)unsignedNOTNULLauto_increment,`commodity`varchar(45)NOTNULL,`commodity_num`int(10)unsignedNOTNULL,`order_money`floatNOTNULL,`order_time`datetimeNOTNULL,`commodity_type`varchar(45)defaultNULL,PRIMARYKEY(`order_id`))ENGINE=InnoDBAUTO_INCREMENT=15DEFAULTCHARSET=utf8;INSERTINTO`order_detail`(`order_id`,`commodity`,`commodity_num`,`order_money`,`order_time`,`commodity_type`)VALUES(1,'襯衣',9,900,'2015-07-0212:13:20','服裝'),(2,'帽子',30,600,'2015-08-0712:13:40','服裝'),(3,'褲子',8,640,'2015-08-1711:13:20','服裝'),(4,'大米',100,350,'2015-07-2012:13:20','食品'),(5,'零食',50,200,'2015-08-1112:13:40','食品'),(6,'蔬菜',30,90,'2015-07-0912:12:30','食品'),(7,'面包',20,80,'2015-08-2512:13:40','食品'),(8,'風(fēng)車',10,70,'2015-07-2112:13:30','玩具'),(9,'汽球',40,60,'2015-08-1312:13:45','玩具'),(10,'鋼筆',25,500,'2015-07-1012:13:35','文具'),(11,'鉛筆',150,300,'2015-08-1412:13:45','文具'),(12,'毛筆',10,50,'2015-07-0311:13:42','文具'),(13,'作業(yè)本',200,400,'2015-08-1812:13:15','文具'),(14,'文件夾',50,150,'2015-08-1712:10:45','文具');DROPTABLEIFEXISTS`order_month`;CREATETABLE`order_month`(`id`int(10)unsignedNOTNULLauto_increment,`month`varchar(45)NOTNULL,`total_money`floatNOTNULL,PRIMARYKEY(`id`))ENGINE=InnoDBDEFAULTCHARSET=utf8;DROPTABLEIFEXISTS`order_total_type`;CREATETABLE`order_total_type`(`id`int(10)unsignedNOTNULLauto_increment,`commodity_type`varchar(45)NOTNULL,`total_money`floatNOTNULL,PRIMARYKEY(`id`))ENGINE=InnoDBDEFAULTCHARSET=utf8;DROPTABLEIFEXISTS`order_total_type_month`;CREATETABLE`order_total_type_month`(`id`int(10)unsignedNOTNULLauto_increment,`month`varchar(45)NOTNULL,`commodity_type`varchar(45)NOTNULL,`total_money`floatNOTNULL,PRIMARYKEY(`id`))ENGINE=InnoDBDEFAULTCHARSET=utf8;42單元作業(yè)2用存儲過程實現(xiàn)以下功能創(chuàng)建一個業(yè)務(wù)表創(chuàng)建表前先判斷此表是否存在，如果存在先刪除字段滿足如下要求ID自增業(yè)務(wù)名稱字段為字符串類型訂閱時間字段為日期類型收費方式字段(0:按流量收取、1:按小時收取、2:按日收取、3:按月收到、4:按年收取)是否生效字段(0:未生效、1:已生效)用循環(huán)往業(yè)務(wù)表插入1000條記錄按ID字段排序（升序）檢索出251-300行業(yè)務(wù)數(shù)據(jù)收費方式字段顯示“按流量收取”或“按小時收取”或“按日收取”或“按年收取”是否生效字段顯示“未生效”或“已生效”43單元作業(yè)3用存儲過程統(tǒng)計各個學(xué)生是否達(dá)到最低修讀要求:若course_student表中成績字段(course_score)大于60分，則學(xué)生取得該門課程學(xué)分每門課程的學(xué)分(credit)在course表中可查詢到若某學(xué)生的所