存儲過程實例

存儲過程的優(yōu)點2007-11-0215:21存儲過程只在創(chuàng)造時進行編譯即可，以后每次執(zhí)行存儲過程都不需再重新編譯，而我們通常使用的SQL語句每執(zhí)行一次就編譯一次，所以使用存儲過程可提高數(shù)據(jù)庫執(zhí)行速度。經(jīng)常會遇到復雜的業(yè)務邏輯和對數(shù)據(jù)庫的操作，這個時候就會用SP來封裝數(shù)據(jù)庫操作。當對數(shù)據(jù)庫進行復雜操作時（如對多個表進行Update,Insert,Query,Delete時），可將此復雜操作用存儲過程封裝起來與數(shù)據(jù)庫提供的事務處理結合一起使用?？梢詷O大的提高數(shù)據(jù)庫的使用效率，減少程序的執(zhí)行時間，這一點在較大數(shù)據(jù)量的數(shù)據(jù)庫的操作中是非常重要的。在代碼上看，SQL語句和程序代碼語句的分離，可以提高程序代碼的可讀性。存儲過程可以設置參數(shù)，可以根據(jù)傳入?yún)?shù)的不同重復使用同一個存儲過程，從而高效的提高代碼的優(yōu)化率和可讀性。安全性高,可設定只有某此用戶才具有對指定存儲過程的使用權存儲過程的種類：（1） 系統(tǒng)存儲過程：以sp_開頭，用來進行系統(tǒng)的各項設定.取得信息.相關管理工作，如sp_help就是取得指定對象的相關信息。（2） 擴展存儲過程以XP_開頭，用來調用操作系統(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