存儲過程實例

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