存儲(chǔ)過(guò)程實(shí)例

存儲(chǔ)過(guò)程的優(yōu)點(diǎn)2007-11-0215:21存儲(chǔ)過(guò)程只在創(chuàng)造時(shí)進(jìn)行編譯即可，以后每次執(zhí)行存儲(chǔ)過(guò)程都不需再重新編譯，而我們通常使用的SQL語(yǔ)句每執(zhí)行一次就編譯一次，所以使用存儲(chǔ)過(guò)程可提高數(shù)據(jù)庫(kù)執(zhí)行速度。經(jīng)常會(huì)遇到復(fù)雜的業(yè)務(wù)邏輯和對(duì)數(shù)據(jù)庫(kù)的操作，這個(gè)時(shí)候就會(huì)用SP來(lái)封裝數(shù)據(jù)庫(kù)操作。當(dāng)對(duì)數(shù)據(jù)庫(kù)進(jìn)行復(fù)雜操作時(shí)（如對(duì)多個(gè)表進(jìn)行Update,Insert,Query,Delete時(shí)），可將此復(fù)雜操作用存儲(chǔ)過(guò)程封裝起來(lái)與數(shù)據(jù)庫(kù)提供的事務(wù)處理結(jié)合一起使用。可以極大的提高數(shù)據(jù)庫(kù)的使用效率，減少程序的執(zhí)行時(shí)間，這一點(diǎn)在較大數(shù)據(jù)量的數(shù)據(jù)庫(kù)的操作中是非常重要的。在代碼上看，SQL語(yǔ)句和程序代碼語(yǔ)句的分離，可以提高程序代碼的可讀性。存儲(chǔ)過(guò)程可以設(shè)置參數(shù)，可以根據(jù)傳入?yún)?shù)的不同重復(fù)使用同一個(gè)存儲(chǔ)過(guò)程，從而高效的提高代碼的優(yōu)化率和可讀性。安全性高,可設(shè)定只有某此用戶才具有對(duì)指定存儲(chǔ)過(guò)程的使用權(quán)存儲(chǔ)過(guò)程的種類：（1） 系統(tǒng)存儲(chǔ)過(guò)程：以sp_開(kāi)頭，用來(lái)進(jìn)行系統(tǒng)的各項(xiàng)設(shè)定.取得信息.相關(guān)管理工作，如sp_help就是取得指定對(duì)象的相關(guān)信息。（2） 擴(kuò)展存儲(chǔ)過(guò)程以XP_開(kāi)頭，用來(lái)調(diào)用操作系統(tǒng)提供的功能execmaster..xp_cmdshell'ping10.8.16.1'（3） 用戶自定義的存儲(chǔ)過(guò)程,這是我們所指的存儲(chǔ)過(guò)程常用格式模版：Createprocedureprocedue_name[?parameterdata_type][output][with]{recompile|encryption}assql_statement解釋：output:表示此參數(shù)是可傳回的with（recompile|encryption}recompile:表示每次執(zhí)行此存儲(chǔ)過(guò)程時(shí)都重新編譯一次[encryption:所創(chuàng)建的存儲(chǔ)過(guò)程的內(nèi)容會(huì)被加密。SQL存儲(chǔ)過(guò)程實(shí)例2007-11-0215:24實(shí)例1：只返回單一記錄集的存儲(chǔ)過(guò)程。表銀行存款表（bankMoney）的內(nèi)容如下IduserIDSexMoney001Zhangsan男30002Wangwu男50003Zhangsan男40要求1：查詢表bankMoney的內(nèi)容的存儲(chǔ)過(guò)程createproceduresp_query_bankMoneyasselect*frombankMoneygoexecsp_query_bankMoney注*在使用過(guò)程中只需要把中的SQL語(yǔ)句替換為存儲(chǔ)過(guò)程名，就可以了很方便吧！實(shí)例2(向存儲(chǔ)過(guò)程中傳遞參數(shù))：加入一筆記錄到表bankMoney，并查詢此表中userID=Zhangsan的所有存款的總金額。Createprocinsert_bank@param1char(10),@param2varchar(20),@param3varchar(20),@param4int,@param5intoutputwithencryption 加密asinsertbankMoney(id,userID,sex,Money)Values(@param1,@param2,@param3,@param4)select@param5=sum(Money)frombankMoneywhereuserID='Zhangsan'go在SQLServer查詢分析器中執(zhí)行該存儲(chǔ)過(guò)程的方法是：declare@total_priceintexecinsert_bank'004','Zhangsan'，'男'，100,@total_priceoutputprint'總余額為'+convert(varchar,@total_price)go在這里再啰嗦一下存儲(chǔ)過(guò)程的3種傳回值(方便正在看這個(gè)例子的朋友不用再去查看語(yǔ)法內(nèi)容)：以Return傳回整數(shù)以output格式傳回參數(shù)Recordset傳回值的區(qū)別：output和return都可在批次程式中用變量接收，而recordset則傳回到執(zhí)行批次的客戶端中。實(shí)例3：使用帶有復(fù)雜SELECT語(yǔ)句的簡(jiǎn)單過(guò)程下面的存儲(chǔ)過(guò)程從四個(gè)表的聯(lián)接中返回所有作者(提供了姓名)、出版的書(shū)籍以及出版社。該存儲(chǔ)過(guò)程不使用任何參數(shù)。USEpubsIFEXISTS(SELECTnameFROMsysobjectsWHEREname='au_info_all'ANDtype='P')DROPPROCEDUREau_info_allGOCREATEPROCEDUREau_info_allASSELECTau_lname,au_fname,title,pub_nameFROMauthorsaINNERJOINtitleauthortaONa.au_id=ta.au_idINNERJOINtitlestONt.title_id=ta.title_idINNERJOINpublisherspONt.pub_id=p.pub_idGOau_info_all存儲(chǔ)過(guò)程可以通過(guò)以下方法執(zhí)行：EXECUTEau_info_all—OrEXECau_info_all如果該過(guò)程是批處理中的第一條語(yǔ)句，則可使用：au_info_all實(shí)例4：使用帶有參數(shù)的簡(jiǎn)單過(guò)程CREATEPROCEDUREau_info@lastnamevarchar(40),@firstnamevarchar(20)ASSELECTau_lname,au_fname,title,pub_nameFROMauthorsaINNERJOINtitleauthortaONa.au_id=ta.au_idINNERJOINtitlestONt.title_id=ta.title_idINNERJOINpublisherspONt.pub_id=p.pub_idWHEREau_fname=@firstnameANDau_lname=@lastnameGOau_info存儲(chǔ)過(guò)程可以通過(guò)以下方法執(zhí)行：EXECUTEau_info'Dull','Ann'—OrEXECUTEau_info@lastname='Dull',@firstname='Ann'—OrEXECUTEau_info@firstname='Ann',@lastname='Dull'—OrEXECau_info'Dull','Ann'—OrEXECau_info@lastname='Dull',@firstname='Ann'—OrEXECau_info@firstname='Ann',@lastname='Dull'如果該過(guò)程是批處理中的第一條語(yǔ)句，則可使用：au_info'Dull','Ann'—Orau_info@lastname='Dull',@firstname='Ann'—Orau_info@firstname='Ann',@lastname='Dull'實(shí)例5：使用帶有通配符參數(shù)的簡(jiǎn)單過(guò)程CREATEPROCEDUREau_info2@lastnamevarchar(30)='D%',@firstnamevarchar(18)='%'ASSELECTau_lname,au_fname,title,pub_nameFROMauthorsaINNERJOINtitleauthortaONa.au_id=ta.au_idINNERJOINtitlestONt.title_id=ta.title_idINNERJOINpublisherspONt.pub_id=p.pub_idWHEREau_fnameLIKE@firstnameANDau_lnameLIKE@lastnameGOau_info2存儲(chǔ)過(guò)程可以用多種組合執(zhí)行。下面只列出了部分組合:EXECUTE