數據庫存儲過程詳解

/* 存儲過程可以看作是在數據庫中的存儲t-sql腳本  為什么使用存儲過程 1、增加性能???本地存儲發送的內容少、調用快、預編譯、高速緩存 ???????一般語句的執行：檢查權限、檢查語法，建立執行計劃處理語句的要求 ???????存儲過程：創建時已經檢查了語法；第一次執行的時候執行計劃被創建，被編譯； ??????????????再次執行時不需要重檢查語法、不需要重編譯、根據已經緩存的計劃來決定是否需要重創建執行計劃 2、增強安全???加密、分離（權限設置，用戶只需要有執行存儲過程的權限，不需要有訪問存儲過程所使用的對象的權限） ??? 3、在transact-sql中使用非數據庫技術??dll 4、編程模式——使用外部編程語言調用 ???1）input ???2）output ???3）feedback?狀態代碼或描述性的文本 ???4）模塊化、可重用、可調用其他存儲過程 ???5）隱藏程序邏輯，便于編程 ???6）可以調用動態連接庫（外接的程序） 基本原則：越簡單越好?單一任務 */  /* 分類 1、系統存儲過程? ???存在于master數據庫，一般以sp_開頭 ???提供對系統表格數據調用、數據庫管理功能、安全管理功能的支持 ??--表格授權 ??use?pubs ??go ??execute?sp_table_privileges?stores ??--顯示kylinadministrator的所有進程 ??execute?sp_who?@loginame='W2K3SERVERAdministrator' ??--報告有關孤立的?microsoft?windows?nt?用戶和組的信息，這些用戶和組已不在?windows?nt?環境中，但仍在?microsoft?sql?server系統表中擁有項。 ??execute?sp_validatelogins 2、本地存儲過程???用戶創建的解決特定問題的 3、臨時存儲過程???存儲于tempdb ????????????????????創建、調用時的數據庫????使用范圍???????????生存周期? ???#local?????????????????????不限數據庫????????創建時的連接有效????從創建時開始，當創建的連接中斷時消失 ???##global???????????????????不限數據庫????????所有連接????????????從創建時開始，當創建的連接中斷時消失 ???直接創建在tempdb的存儲過程??tempdb????????????所有連接????????????從創建時開始，當數據庫服務器服務停止時消失 ???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、擴展存儲過程??c++?xp ???xp_sendmail既是系統存儲過程，也是擴展存儲過程 ???使用objectproperty來判斷是否是擴展存儲過程 ????use?master ????--擴展存儲過程 ????select?objectproperty(object_id('sp_prepare'),?'isextendedproc') ????--非擴展存儲過程 ????select?objectproperty(object_id('xp_logininfo'),?'isextendedproc') 5、遠程存儲過程 ???目前版本中只是為了向后兼容，已被分布式查詢替代 */   /* 存儲過程在數據庫中如何存儲 名字?sysobjects 文本?syscomments? */  /* 練習1：通過查詢分析器中的對象查看器查看存儲過程 */  /* 練習2：查看存儲過程的內容 ???????圖形 ???????語句 */ 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'?   /* 系統存儲過程 以使用為主 */  /* 本地存儲過程的創建、修改、刪除 1、t-sql語句 create?procedure alter?procedure drop?procedure  create?procedure?存儲過程名字 as 存儲過程文本 go?  alter?procedure?存儲過程名字 as 存儲過程文本 go? ? drop?procedure?存儲過程名字 2、企業管理器??右鍵 ???????????????向導 */  /* 簡單? */ --?--?--?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?商品編號,a.productname?as?商品名稱,b.unitprice?as?數量,b.quantity?as?價格, b.unitprice*b.quantity?as?金額,c.requireddate?as?銷售時間 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?'測試'  execute?sp_qry_salesdetails  --遞歸算法 --視圖??存儲過程??函數 alter?view?v_qry_salesdetails as select?a.productid?as?商品編號,a.productname?as?商品名稱,b.unitprice?as?數量,b.quantity?as?價格, b.unitprice*b.quantity?as?金額,c.requireddate?as?銷售時間 from?[order?details]?as?b?join?products?as?a on?b.productid=a.productid join?orders?as?c on?b.orderid=c.orderid print?'測試'  select?*?from?v_qry_salesdetails? /* 默認情況下第一次執行時的執行計劃被保存，以后執行時都是用這個執行計劃，直到服務器重啟或存儲過程使用的表格變化時 當存儲過程變化時，如：參數變化，需要重新編譯、制定新的執行計劃 當每次調用存儲過程時強制重新編譯的方法： 1、創建時指定?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  --加密存儲過程?with?encryption? select?objectproperty(object_id('sp_qry_salesdetails'),?'isencrypted')  /* 刪除存儲過程 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  /* 提供輸入參數?input */ create?proc?qry_salesdetails?@y?int,@m?int?--varchar(10) as select?a.productid?as?商品編號,a.productname?as?商品名稱,b.unitprice?as?數量,b.quantity?as?價格,b.unitprice*b.quantity?as?金額,c.requireddate?as?銷售時間 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?數據庫 orders?order?details?表格?* 根據指定用戶ID顯示此用戶在1996-07-01到1997-07-01之間的訂貨記錄? 要求存儲過程文本加密? */   use?northwind go --創建存儲過程 --?drop?proc?qry_showorders? create?proc?qry_showorders?@custid?nchar(5) with?encryption???--加密 as if?@custid?is??null --?begin --???print?'提供了不正確的參數' --???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?'有錯誤' ???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?商品編號,a.productname?as?商品名稱, b.unitprice?as?數量,b.quantity?as?價格,b.unitprice*b.quantity?as?金額, c.requireddate?as?銷售時間? ????????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的相關技術文章，請訪問<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>欄目進行學習！</p>