數(shù)據(jù)庫存儲(chǔ)過程詳解

/* 存儲(chǔ)過程可以看作是在數(shù)據(jù)庫中的存儲(chǔ)t-sql腳本  為什么使用存儲(chǔ)過程 1、增加性能???本地存儲(chǔ)發(fā)送的內(nèi)容少、調(diào)用快、預(yù)編譯、高速緩存 ???????一般語句的執(zhí)行：檢查權(quán)限、檢查語法，建立執(zhí)行計(jì)劃處理語句的要求 ???????存儲(chǔ)過程：創(chuàng)建時(shí)已經(jīng)檢查了語法；第一次執(zhí)行的時(shí)候執(zhí)行計(jì)劃被創(chuàng)建，被編譯； ??????????????再次執(zhí)行時(shí)不需要重檢查語法、不需要重編譯、根據(jù)已經(jīng)緩存的計(jì)劃來決定是否需要重創(chuàng)建執(zhí)行計(jì)劃 2、增強(qiáng)安全???加密、分離（權(quán)限設(shè)置，用戶只需要有執(zhí)行存儲(chǔ)過程的權(quán)限，不需要有訪問存儲(chǔ)過程所使用的對(duì)象的權(quán)限） ??? 3、在transact-sql中使用非數(shù)據(jù)庫技術(shù)??dll 4、編程模式——使用外部編程語言調(diào)用 ???1）input ???2）output ???3）feedback?狀態(tài)代碼或描述性的文本 ???4）模塊化、可重用、可調(diào)用其他存儲(chǔ)過程 ???5）隱藏程序邏輯，便于編程 ???6）可以調(diào)用動(dòng)態(tài)連接庫（外接的程序） 基本原則：越簡(jiǎn)單越好?單一任務(wù) */  /* 分類 1、系統(tǒng)存儲(chǔ)過程? ???存在于master數(shù)據(jù)庫，一般以sp_開頭 ???提供對(duì)系統(tǒng)表格數(shù)據(jù)調(diào)用、數(shù)據(jù)庫管理功能、安全管理功能的支持 ??--表格授權(quán) ??use?pubs ??go ??execute?sp_table_privileges?stores ??--顯示kylinadministrator的所有進(jìn)程 ??execute?sp_who?@loginame='W2K3SERVERAdministrator' ??--報(bào)告有關(guān)孤立的?microsoft?windows?nt?用戶和組的信息，這些用戶和組已不在?windows?nt?環(huán)境中，但仍在?microsoft?sql?server系統(tǒng)表中擁有項(xiàng)。 ??execute?sp_validatelogins 2、本地存儲(chǔ)過程???用戶創(chuàng)建的解決特定問題的 3、臨時(shí)存儲(chǔ)過程???存儲(chǔ)于tempdb ????????????????????創(chuàng)建、調(diào)用時(shí)的數(shù)據(jù)庫????使用范圍???????????生存周期? ???#local?????????????????????不限數(shù)據(jù)庫????????創(chuàng)建時(shí)的連接有效????從創(chuàng)建時(shí)開始，當(dāng)創(chuàng)建的連接中斷時(shí)消失 ???##global???????????????????不限數(shù)據(jù)庫????????所有連接????????????從創(chuàng)建時(shí)開始，當(dāng)創(chuàng)建的連接中斷時(shí)消失 ???直接創(chuàng)建在tempdb的存儲(chǔ)過程??tempdb????????????所有連接????????????從創(chuàng)建時(shí)開始，當(dāng)數(shù)據(jù)庫服務(wù)器服務(wù)停止時(shí)消失 ???create?proc?#local ???as ???select?'#local' ???go ???exec?#local ???go ???create?proc?##global ???as ???select?'##global' ???go ???exec?##global ???go ???use?tempdb ????go ????create?procedure?directtemp ????as ????select?*?from?[pubs].[dbo].[authors] ????go ???use?northwind ???go ???exec?tempdb.dbo.directtemp  4、擴(kuò)展存儲(chǔ)過程??c++?xp ???xp_sendmail既是系統(tǒng)存儲(chǔ)過程，也是擴(kuò)展存儲(chǔ)過程 ???使用objectproperty來判斷是否是擴(kuò)展存儲(chǔ)過程 ????use?master ????--擴(kuò)展存儲(chǔ)過程 ????select?objectproperty(object_id('sp_prepare'),?'isextendedproc') ????--非擴(kuò)展存儲(chǔ)過程 ????select?objectproperty(object_id('xp_logininfo'),?'isextendedproc') 5、遠(yuǎn)程存儲(chǔ)過程 ???目前版本中只是為了向后兼容，已被分布式查詢替代 */   /* 存儲(chǔ)過程在數(shù)據(jù)庫中如何存儲(chǔ) 名字?sysobjects 文本?syscomments? */  /* 練習(xí)1：通過查詢分析器中的對(duì)象查看器查看存儲(chǔ)過程 */  /* 練習(xí)2：查看存儲(chǔ)過程的內(nèi)容 ???????圖形 ???????語句 */ select?*?from?sysobjects select?*?from?syscomments? go select?*?from?syscomments? where?id?=?object_id('custorderhist') go select?name,text from?sysobjects?inner?join?syscomments? on?sysobjects.id?=?syscomments.id where?sysobjects.name?=?'custorderhist' go sp_helptext?sp_helptext go use?northwind go exec?sp_help?custorderhist exec?sp_helptext?custorderhist exec?sp_depends?custorderhist exec?sp_stored_procedures?'custorderhist'?   /* 系統(tǒng)存儲(chǔ)過程 以使用為主 */  /* 本地存儲(chǔ)過程的創(chuàng)建、修改、刪除 1、t-sql語句 create?procedure alter?procedure drop?procedure  create?procedure?存儲(chǔ)過程名字 as 存儲(chǔ)過程文本 go?  alter?procedure?存儲(chǔ)過程名字 as 存儲(chǔ)過程文本 go? ? drop?procedure?存儲(chǔ)過程名字 2、企業(yè)管理器??右鍵 ???????????????向?qū)?*/  /* 簡(jiǎn)單? */ --?--?--?select?top?1?*?from?products --?--?--?select?top?1?*?from?orders --?--?--?select?top?1?*?from?[order?details] /*1、和視圖比較*/ alter??proc?sp_qry_salesdetails as select?a.productid?as?商品編號(hào),a.productname?as?商品名稱,b.unitprice?as?數(shù)量,b.quantity?as?價(jià)格, b.unitprice*b.quantity?as?金額,c.requireddate?as?銷售時(shí)間 from?[order?details]?as?b?join?products?as?a on?b.productid=a.productid join?orders?as?c on?b.orderid=c.orderid go print?'測(cè)試'  execute?sp_qry_salesdetails  --遞歸算法 --視圖??存儲(chǔ)過程??函數(shù) alter?view?v_qry_salesdetails as select?a.productid?as?商品編號(hào),a.productname?as?商品名稱,b.unitprice?as?數(shù)量,b.quantity?as?價(jià)格, b.unitprice*b.quantity?as?金額,c.requireddate?as?銷售時(shí)間 from?[order?details]?as?b?join?products?as?a on?b.productid=a.productid join?orders?as?c on?b.orderid=c.orderid print?'測(cè)試'  select?*?from?v_qry_salesdetails? /* 默認(rèn)情況下第一次執(zhí)行時(shí)的執(zhí)行計(jì)劃被保存，以后執(zhí)行時(shí)都是用這個(gè)執(zhí)行計(jì)劃，直到服務(wù)器重啟或存儲(chǔ)過程使用的表格變化時(shí) 當(dāng)存儲(chǔ)過程變化時(shí)，如：參數(shù)變化，需要重新編譯、制定新的執(zhí)行計(jì)劃 當(dāng)每次調(diào)用存儲(chǔ)過程時(shí)強(qiáng)制重新編譯的方法： 1、創(chuàng)建時(shí)指定?with?recompile? 2、sp_recompile? */ create?procedure?sp1 as? select?*?from?customers  exec?sp1  alter?procedure?sp1 as? select?*?from?customers  alter?procedure?sp1 with?recompile as? select?*?from?customers  sp_recompile?sp1  --加密存儲(chǔ)過程?with?encryption? select?objectproperty(object_id('sp_qry_salesdetails'),?'isencrypted')  /* 刪除存儲(chǔ)過程 drop?proc? */ use?northwind go create?proc?dbo.sp_dropproc as select?'northwind.dbo.sp_dropproc' go exec?northwind.dbo.sp_dropproc go use?master go create?proc?dbo.sp_dropproc as select?'master.dbo.sp_dropproc' go exec?master.dbo.sp_dropproc go use?northwind go drop?proc?sp_dropproc go exec?sp_dropproc exec?master.dbo.sp_dropproc  /* 提供輸入?yún)?shù)?input */ create?proc?qry_salesdetails?@y?int,@m?int?--varchar(10) as select?a.productid?as?商品編號(hào),a.productname?as?商品名稱,b.unitprice?as?數(shù)量,b.quantity?as?價(jià)格,b.unitprice*b.quantity?as?金額,c.requireddate?as?銷售時(shí)間 from?[order?details]?as?b?join?products?as?a on?b.productid=a.productid join?orders?as?c on?b.orderid=c.orderid --where?convert(varchar(2),month(c.requireddate))?=?@m where?year(c.requireddate)?=?@y?and?month(c.requireddate)?=?@m go? exec?qry_salesdetails?1996,9 exec?qry_salesdetails?9,1996 exec?qry_salesdetails?@m=9,@y=1996 exec?qry_salesdetails?@y=1996,@m=9 go /* northwind?數(shù)據(jù)庫 orders?order?details?表格?* 根據(jù)指定用戶ID顯示此用戶在1996-07-01到1997-07-01之間的訂貨記錄? 要求存儲(chǔ)過程文本加密? */   use?northwind go --創(chuàng)建存儲(chǔ)過程 --?drop?proc?qry_showorders? create?proc?qry_showorders?@custid?nchar(5) with?encryption???--加密 as if?@custid?is??null --?begin --???print?'提供了不正確的參數(shù)' --???return --?end select?*? from?orders?od?inner?join?[order?details]?oddt on?od.orderid?=?oddt.orderid where?shippeddate?&gt;='1996-07-01'?and?shippeddate?0 begin ???print?'有錯(cuò)誤' ???set?@e?=?@@error end ???return?@e go declare?@er?int exec?@er?=?testerror select?@er  /* ??@@rowcount */  select?@@rowcount select?*?from?customers select?@@rowcount  /* null?值 */ create?proc?testreturn?@a?int as? if?@a?is?null begin ???return(100) end else?if?@a=@start  go exec?qry_salesdetails?6,'1996-01-01','1997-01-01'       alter?proc?qry_salesdetails?@no?int?=?-1,@start?char(10),@end?char(10) as? declare?@sql?varchar(4000) set?@sql?=?'select?a.productid?as?商品編號(hào),a.productname?as?商品名稱, b.unitprice?as?數(shù)量,b.quantity?as?價(jià)格,b.unitprice*b.quantity?as?金額, c.requireddate?as?銷售時(shí)間? ????????from?[order?details]?as?b?join?products?as?a ????on?b.productid=a.productid ????join?orders?as?c ????on?b.orderid=c.orderid??where?1=1??' if?@no?is?not?null ?????set?@sql?=?@sql?+?'?and??a.productid?=?'+convert(varchar(10),@no) if?@start?is?not?null??and??@end?is?not?null ?????set?@sql?=?@sql????+?'?and?c.requireddate?&gt;=??'''+?@start+'''' ????????????????????????+?'?and?c.requireddate?<p>更多sql的相關(guān)技術(shù)文章，請(qǐng)?jiān)L問<a href="http://www.php.cn/sql/" target="_self" style="color: rgb(146, 208, 80); text-decoration: underline;"><span style="color: rgb(146, 208, 80);">sql教程</span></a><span style="color: rgb(146, 208, 80);"></span>欄目進(jìn)行學(xué)習(xí)！</p>