MySQL必知必會(huì)筆記存儲(chǔ)過程游標(biāo)觸mysql創(chuàng)建存儲(chǔ)過第二十三章使用存儲(chǔ)過程MySQL5中添加了存儲(chǔ)過程的支持。大多數(shù)SQL語句都是針對一個(gè)或多個(gè)表的單條語句。并非所有的操作都怎么簡單。經(jīng)常會(huì)有一個(gè)完整的操作需要多條才能完成存儲(chǔ)過程簡單來說，就是為以后的使用而保存的一條或多條MySQL語句的集合。可將其視為批文件。雖然他們的作用不僅限于批處理。為什么要使用存儲(chǔ)過程：優(yōu)點(diǎn)通過吧處理封裝在容易使用的單元中，簡化復(fù)雜的操作的步驟越多，出錯(cuò)的可能性越大。防止錯(cuò)誤保證了數(shù)據(jù)的一致性。代碼，使用它的人員不會(huì)改自己的代碼了都。提高性能，因?yàn)槭褂么鎯?chǔ)過程比使用單條SQL存在一些職能用在單個(gè)請求中的MySQL寫功能更強(qiáng)更靈活的代碼換句話說3個(gè)主要好處簡單、安全、高性能缺點(diǎn)SQL技能，更豐富的經(jīng)驗(yàn)。你可能沒有創(chuàng)建存儲(chǔ)過程的安全訪問權(quán)限建，允許用戶使用存儲(chǔ)過程，但不允許創(chuàng)建存儲(chǔ)過程存儲(chǔ)過程是非常有用的，應(yīng)該盡可能的使用它們執(zhí)行存儲(chǔ)過程MySQL稱存儲(chǔ)過程的執(zhí)行為調(diào)用，因此MySQL執(zhí)行存儲(chǔ)過程的語句為CALL.CALL接受存儲(chǔ)過程的名字以及需要傳遞給它的任意參數(shù)CALLproductpricing(@pricelow,@pricehigh,@priceaverage);//執(zhí)行名為productpricing的存儲(chǔ)過程，它計(jì)算并返回產(chǎn)品的最低、最高和平均價(jià)格創(chuàng)建存儲(chǔ)過程CREATEPROCEDURE存儲(chǔ)過程名()一個(gè)例子說明：一個(gè)返回產(chǎn)品平均價(jià)格的存儲(chǔ)過程如下代碼：CREATEPROCEDUREproductpricing()BEGINSELECTAvg(prod_price)ASpriceaverageFROMproducts;END;創(chuàng)建存儲(chǔ)過程名為productpricing，如果存儲(chǔ)過程需要接受參數(shù)，可以在中列舉出來。即使沒有參數(shù)后面仍然要跟（BEGIN和END語句用來限定存儲(chǔ)過程體，過程體本身是個(gè)簡單的SELECT在MYSQL處理這段代碼時(shí)會(huì)創(chuàng)建一個(gè)新的存儲(chǔ)過程productpricing。沒有返回?cái)?shù)據(jù)。因?yàn)檫@段代碼時(shí)創(chuàng)建而不是使用存儲(chǔ)過程。Mysql命令行客戶機(jī)的分隔符默認(rèn)的MySQL語句分隔符為分號(hào);。Mysql命令行實(shí)用程序也是;作為語句分隔符。如果命令行實(shí)用程序要解釋存儲(chǔ)過程自身的;字符，則他們最終不會(huì)成為存儲(chǔ)過程的成分，這會(huì)使存儲(chǔ)過程中的SQL出現(xiàn)句法錯(cuò)誤解決方法是臨時(shí)更改命令實(shí)用程序的語句分隔符DELIMITER// 定義新的語句分隔符CREATEPROCEDUREproductpricing()BEGINSELECTAvg(prod_price)ASpriceaverageFROMproducts;END//DELIMITER; 改回原來的語句分隔符為;\符號(hào)外，任何字符都可以作為語句分隔符CALLproductpricing();//使用productpricing存儲(chǔ)過程刪除存儲(chǔ)過程DROPPROCEDUREproductpricing; 存儲(chǔ)過程名為了刪除存儲(chǔ)過程不存在時(shí)刪除產(chǎn)生錯(cuò)誤，可以判斷僅存儲(chǔ)過程存在時(shí)刪除DROPPROCEDUREIFEXISTS使用參數(shù)Productpricing只是一個(gè)簡單的存儲(chǔ)過程，他簡單地顯示SELECT語句的結(jié)果。一般存儲(chǔ)過程并不顯示結(jié)果，而是把結(jié)果返回給你指定的變量CREATEPROCEDUREOUTp1DECIMAL(8,2),OUTphOUTpa)BEGINSELECTMin(prod_price)INTOp1FROMproducts;SELECTMax(prod_price)INTOphFROMproducts;SELECTAvg(prod_price)INTOpaFROMproducts;END;OUT指出相應(yīng)的參（返回給調(diào)用者M(jìn)ySQL支持傳遞給存儲(chǔ)過程從存儲(chǔ)過程中傳出、如這里所用）和對存儲(chǔ)過程傳入和傳出）的代碼位于BEGINENDSELECT通過INTO）調(diào)用修改過的存儲(chǔ)過程必須指定3個(gè)變量名：CALLproductpricing(@pricelow,@pricehigh,@priceaverage);這條CALL語句給出3個(gè)參數(shù)，它們是存儲(chǔ)過程將保存結(jié)果的3個(gè)變量的名字變量名所有的MySQL變量都必須以@開始使用變量SELECT@priceaverage;SELECT@pricelow,@pricehigh,@priceaverage; 3給變量的值下面是另一個(gè)例子，這次使用IN和OUT參數(shù)。ordertotal接受訂單號(hào)，并返回該訂單的合計(jì)CREATEPROCEDUREordertotal(INonumberINT,OUTototalDECIMAL(8,2))BEGINSELECTSum(item_price*quantity)FROMorderitemsWHEREorder_num=onumberINTOototal;END;//onumber定義為IN，因?yàn)橛唵翁?hào)時(shí)被傳入存儲(chǔ)過程，ototal定義為OUT，因?yàn)橐獜拇鎯?chǔ)過程中返回合計(jì)，SELECT語句使用這兩個(gè)參數(shù)，WHERE子句使用onumber選擇正確的行，INTO使用ototal存儲(chǔ)計(jì)算出來的合計(jì)為了調(diào)用這個(gè)新的過程，可以使用下列語句：CALLordertotal(2005@total); 這樣查詢其他的訂單總計(jì)可直接改變訂單號(hào)即可SELECT@total;建立智能的存儲(chǔ)過程上面的存儲(chǔ)過程基本都是封裝MySQL簡單的SELECT語句，但存儲(chǔ)過程的威力在它包含業(yè)務(wù)邏輯和智能處理時(shí)才顯示出來顧客（或許是你所在區(qū)的顧客。那么需要做下面的事情：獲得合計(jì)（與以前一樣）吧營業(yè)稅有條件地添加到合計(jì)返回合計(jì)（帶或不帶稅存儲(chǔ)過程的完整工作如下：--Name:ordertotal--Parameters:onumber=訂單號(hào)-- taxable=0為沒有-- ototalordertotal(INonumberINT,INtaxableBOOLEAN,OUTototalDECIMAL(8,2)--COMMENT()中的內(nèi)容將在SHOWPROCEDURESTATUSordertotal()中顯示，其備注作用)COMMENT'Obtainordertotal,optionallyaddingtax'BEGIN--定義totalDECLAREtaxrateINTDEFAULT6;--獲得訂單的合計(jì)，并將結(jié)果存儲(chǔ)到局部變量total中SELECTSum(item_price*quantity)FROMorderitemsWHEREorder_num=onumberINTOtotal;--判斷是否需要增加營業(yè)稅，如為真，這增加6%的營業(yè)稅IFtaxableTHENSELECTtotal+(total/100*taxrate)INTOtotal;ENDIF;--把局部變量total中才合計(jì)傳給ototal中SELECTtotalINTOototal;END;此存儲(chǔ)過程有很大的變動(dòng)，首先，增加了注釋（。在存儲(chǔ)過程復(fù)雜性增加時(shí)，這樣很重要。在存儲(chǔ)體中，用DECLAREDECLARE（這個(gè)例子中taxrateSELECT語句已經(jīng)改變，因此其結(jié)果存儲(chǔ)到total局部變量中而不是ototalIFSELECTSELECTtotal（增加了或沒有增加的）保存到ototalCOMMENT關(guān)鍵字本列中的存儲(chǔ)過程在CREATEPROCEDURE語句中包含了一個(gè)COMMENT值，他不是必需的，但如果給出，將在SHOWPROCEDURESTATUSIF語句這個(gè)例子中給出了MySQL的IF語句的基本用法。IF語句還支持ELSEIF和ELSE子句（前者還使用THEN子句，后者不使用）檢查存儲(chǔ)過程為顯示用來創(chuàng)建一個(gè)存儲(chǔ)過程的CREATE語句，使用SHOWCREATEPROCEDURESHOWCREATEPROCEDUREordertotal;為了獲得包括何時(shí)、有誰創(chuàng)建等詳細(xì)信息的存儲(chǔ)過程列表。使用SHOWPROCEDURESTATUSLIKEPROCEDURESTATUSLIKE''ordertotal;第二十四章使用游標(biāo)MySQL5添加了對游標(biāo)的支持只能用于存儲(chǔ)過程mysql（0行或多行，使用簡單的SELECT1次行地處理所有行的簡單方法（相對于成批處理他們）有時(shí)，需要在檢索出來的行中前進(jìn)或后退一行或多行。這就是使用游標(biāo)的原因。游標(biāo)（cursor）是一個(gè)存儲(chǔ)在MYSQL服務(wù)器上的數(shù)據(jù)庫查詢，它不是一條SELECT語句，而是被據(jù)。更改。使用游標(biāo)使用游標(biāo)涉及幾個(gè)明確的步驟：在能夠使用游標(biāo)前，必須聲明（定義）它只是定義要使用的SELECT一旦聲明后，必須打開游標(biāo)以供使用。這個(gè)過程用錢嗎定義的SELECT語句吧數(shù)據(jù)實(shí)際檢索出來對于填有數(shù)據(jù)的游標(biāo)，根據(jù)需要取出（檢索）的各行END創(chuàng)建游標(biāo)游標(biāo)可用DECLARE語句創(chuàng)建。DECLARESELECT據(jù)需要選擇帶有WHERE和其他子句。如：下面第一名為ordernumbers所有訂單的SELECTCREATEPROCEDUREprocessorders()BEGINDECLAREordernumbersCURSORFORSELECTorder_numFROMorders;END;存儲(chǔ)過程處理完成后，游標(biāo)就消失，因?yàn)樗窒抻诖鎯?chǔ)過程打開和關(guān)閉游標(biāo)CREATEPROCEDUREprocessorders()BEGINDECLAREordernumbersCURSORFORSELECTorder_numFROMorders;Openordernumbers;Closeordernumbers;//CLOSE釋放游標(biāo)使用的所有內(nèi)部內(nèi)存和資源，因此，每個(gè)游標(biāo)不需要時(shí)都應(yīng)該關(guān)閉END;使用游標(biāo)數(shù)據(jù)在一個(gè)游標(biāo)被打開后，可以使用FETCHFETCH指定檢索什么數(shù)據(jù)（所需的要列指針，使下一條FETCHPHPeach()函數(shù)循環(huán)檢索數(shù)據(jù)，從第一行到最后一行CREATEPROCEDUREprocessorders()BEGIN--聲明局部變量DECLAREdoneBOOLEANDEFAULT0;DECLAREoINT;DECLAREordernumbersCURSORFORSELECTorder_numFROMorders;--當(dāng)SQLSTATE為02000時(shí)設(shè)置done值為1DECLARECONTINUEHANDLERFORSQLSTATE'02000'SETdone=1;--打開游標(biāo)Openordernumbers;--開始循環(huán)REPEAT--把當(dāng)前行的值賦給聲明的局部變量o中FETCHordernumbersINTOo;--當(dāng)done為真時(shí)停止循環(huán)UNTILdoneENDREPEAT;--關(guān)閉游標(biāo)Closeordernumbers;//CLOSE釋放游標(biāo)使用的所有內(nèi)部內(nèi)存和資源，因此，每個(gè)游標(biāo)不需要時(shí)都應(yīng)該關(guān)閉END;語句中定義了CONTINUEHANDLER,它是在條件出現(xiàn)時(shí)被執(zhí)行的代碼。這里，它指出當(dāng)SQLSTATE'02000SETdone=SQLSTATE'02000沒有更多的行供循環(huán)時(shí)，出現(xiàn)這個(gè)條件。DECLARE語句次序用DECLARE語句定義局部變量必須在定義任意游標(biāo)或句柄之前定義，而句柄必須在游標(biāo)之后定義。不遵守此規(guī)則就會(huì)出錯(cuò)重復(fù)和循環(huán) 除這里使用REPEAT語句外還支持循環(huán)語句它可用來重復(fù)執(zhí)行代碼直到使用LEAVE語句手動(dòng)退出為止。通常REPEAT語句的語法使它更適合于對游標(biāo)進(jìn)行的循環(huán)。為了把這些內(nèi)容組織起來，這次吧取出的數(shù)據(jù)進(jìn)行某種實(shí)際的處理CREATEPROCEDUREprocessorders()BEGIN--聲明局部變量DECLAREdoneBOOLEANDEFAULT0;DECLAREoINT;DECLAREtDECIMAL(8,2)DECLAREordernumbersCURSORFORSELECTorder_numFROMorders;--當(dāng)SQLSTATE為02000時(shí)設(shè)置done值為1DECLARECONTINUEHANDLERFORSQLSTATE'02000'SETdone=1;--創(chuàng)建一個(gè)ordertotals的表CREATETABLEIFNOTEXISTSordertotals(order_numINT,totalDECIMAL(8,2))--打開游標(biāo)Openordernumbers;--開始循環(huán)REPEAT--把當(dāng)前行的值賦給聲明的局部變量o中FETCHordernumbersINTOo;--用上文講到的ordertotal存儲(chǔ)過程并傳入?yún)?shù)，返回營業(yè)稅計(jì)算后的合計(jì)傳給t變量CALLordertotal(o,1,t)--把訂單號(hào)和合計(jì)插入到新建的ordertotals表中INSERTINTOordertotals(order_num,total)VALUES(o,t);--當(dāng)done為真時(shí)停止循環(huán)UNTILdoneENDREPEAT;--關(guān)閉游標(biāo)Closeordernumbers;//CLOSE每個(gè)游標(biāo)不需要時(shí)都應(yīng)該關(guān)閉END;最后SELECT*FROMordertotals就能查看結(jié)果了第二十五章使用觸發(fā)器MySQL5版本后支持觸發(fā)器只有表支持觸發(fā)器，視圖不支持觸發(fā)器MySQL語句在需要的時(shí)被執(zhí)行，存儲(chǔ)過程也是如此，但是如果你想要某條語句（或某些語句）在事件發(fā)生時(shí)自動(dòng)執(zhí)行，那該怎么辦呢：例如：是否為大寫每當(dāng)訂購一個(gè)產(chǎn)品時(shí)，都從庫存數(shù)量中減少訂購的數(shù)量無論何時(shí)刪除一行，都在某個(gè)存檔中保留一個(gè)副本MySQLMySQL（BEGIN和END的一組語句）DELETEINSERTUPDATE其他的MySQL語句不支持觸發(fā)器創(chuàng)建觸發(fā)器創(chuàng)建觸發(fā)器需要給出4條信息唯一的觸發(fā)器名；//保存每個(gè)數(shù)據(jù)庫中的觸發(fā)器名唯一觸發(fā)器關(guān)聯(lián)的表；觸發(fā)器應(yīng)該響應(yīng)的活動(dòng)INSERTUPDATE）觸發(fā)器何時(shí)執(zhí)行（BEFORE后是創(chuàng)建觸發(fā)器用CREATETRIGGERCREATETRIGGERnewproductAFTERINSERTONproductsFOREACHROWSELECT'Productadded'創(chuàng)建新觸發(fā)器newproduct，它將在INSERTFOREACHproductaddedFOREACHROW針對每個(gè)行都有作用，避免了INSERT一次插入多條語句觸發(fā)器定義規(guī)則觸發(fā)器按每個(gè)表每個(gè)事件每次地定義，每個(gè)表每個(gè)事件每次只允許定義一個(gè)觸發(fā)6個(gè)觸發(fā)器（每條INSERTUPDATE和DELETE個(gè)觸發(fā)器不能與多個(gè)事件或多個(gè)表關(guān)聯(lián)，所以，如果你需要一個(gè)對INSERT和UPDATE觸發(fā)器失敗如果BEFORE(之前)觸發(fā)器失敗，則MySQL將不執(zhí)行SQL語句的請求操作，此外，如果BEFORE觸發(fā)器或語句本身失敗，MySQL將不執(zhí)行AFTER(之后)觸發(fā)器刪除觸發(fā)器DROPTRIGGERnewproduct；觸發(fā)器不能更新或覆蓋，所以修改觸發(fā)器只能先刪除再創(chuàng)建使用觸發(fā)器我們來看看每種觸發(fā)器以及它們的差別INSERT觸發(fā)器INSERT觸發(fā)器在INSERT語句執(zhí)行之前或之后執(zhí)行。需要知道以下幾點(diǎn)：INSERTNEWBEFOREINSERTNEW（允許更改插入的值）對于AUTO_INCREMENTINSERT0，在INSERT含新的自動(dòng)生成值提示：通常BEFORE用于數(shù)據(jù)驗(yàn)證和凈化（的數(shù)據(jù)UPDATEDELETE觸發(fā)器DELETE觸發(fā)器在語句執(zhí)行之前還是之后執(zhí)行，需要知道以下幾點(diǎn)：DELETEOLDOLD例子演示適用OLD保存將要除的行到一個(gè)存檔表中CREATETRIGGERdeleteorderBEFOREDELETEONordersFOREACHROWBEGININSERTINTOarchive_o