熱點推薦:
您现在的位置: 電腦知識網 >> 編程 >> SQL Server >> 正文

SQL Server存儲過程編程經驗技巧

2013-11-12 23:41:24  來源: SQL Server 

  MS SQL Server目前正日益成為WindowNT操作系統上面最為重要的一種數據庫管理系統隨著
  MS SQL Server2000的推出微軟的這種數據庫服務系統真正的實現了在WindowsNT/200
  0系列操作系統一統天下的局面在微軟的操作系統上面沒有任何一種數據庫系統能與之相抗衡包括數據庫領域的領
  頭羊甲骨文公司的看家數據庫系統Oracle在內不可否認MS SQL Server最大的缺陷就是只能運行
  在微軟自己的操作系統上面這一點是MS SQL Server的致命傷口但在另一方面卻也成了最好的促進劑
  促使MS SQL Server在自己僅有的土地上面將自己的功能發揮到了極至最大限度的利用了Windo
  wsNT系列操作系統的各種潛能!作為MS SQL Server數據庫系統中很重要的一個概念就是存儲過程
  理的使用存儲過程可以有效的提高程序的性能並且將商業邏輯封裝在數據庫系統中的存儲過程中可以大大提高整個
  軟件系統的可維護性當你的商業邏輯發生了改變的時候不再需要修改並編譯客戶端應用程序以及重新分發他們到為數
  眾多的用戶手中你只需要修改位於服務器端的實現相應商業邏輯的存儲過程即可合理的編寫自己需要的存儲過程
  以最大限度的利用MS SQL Server的各種資源下面我們來共同看看各種編寫MS SQL Server
  存儲過程和使用存儲過程的技巧經驗!
  
    下面我們討論的前提都是您有過一定的MS SQL Server存儲過程的編寫經驗下面的各種技巧如果沒有
  特別注明則同時適用於MS SQL Server7.0和MS SQL Server2000
  
    1使用OUTPUT類型參數的存儲過程的技巧
  
  一般的存儲過程都是直接返回一段記錄集給調用者但是有的時候我們只需要一些存儲過程返回的一些參數的值這時候
  可以指定存儲過程的OUTPUT參數比如
  
    Create procedure GetName 
  
    @uid nvarchar(1)
  
    @usernam nvarchar(10)='' output
  
    AS
  
    set @username='hongchao'
  
    GO
  
    在上面的存儲過程當中我們傳遞進的參數是@uid而參數@username則在調有的時候不需要傳遞進去
  這樣存儲過程會返回給我們參數@username的值是hongchao上面的都比較的容易需要注意的
  是當在SQL2000中的時候如果你的存儲過程只有一個參數並且這個參數是OUTPUT類型的你必須在調
  用這個存儲過程的時候給這個參數一個初始的值否則會出現調用錯誤的情況!
  
    2在存儲過程中的書寫注意事項
  
    這一點在MS SQL Server7.0和MS SQL Server2000種有些地方是不一樣的也不
  知道是不是微軟的遺漏那就是有些系統的關鍵字在不同的版本之間有所不同比如關鍵字level同樣的一句話
  
  select * from users where level=1
  
  在MS SQL Server7當中的存儲過程當中運行沒有絲毫的問題但是到了MS SQL Server20
  00當中則會出現運行的錯誤原因就是在於在MS SQL Server2000中level被當作了關鍵字
  (奇怪的是SQL7當中也同樣是關鍵字卻沒問題)所以在SQL2000當中上面的語句應當改為
  
  select * from users where [level]=1
  
  從上面的例子中我們可以看到在你編寫存儲過程的時候最好在有可能和系統關鍵字的地方使用將他包
  圍起來以避免在移植過程中出現的運行錯誤問題
  
    3在存儲過程中使用系統存儲過程SP_Executesql的注意事項
  
    我們在編寫自己的存儲過程的時候往往在很多的情況下會使用到系統的存儲過程SP_Execute但是需
  要的注意的是如果你在這個存儲過程的參數(一般是一段SQL語句)當中進行了臨時Table的操作那末對於調
  用者來說這個臨時Table是不可見的也就是說你無法通過臨時Table來在調用者和被調用者之間傳遞值
  決的方法是使用全局臨時Table也就是##開頭的Table
  
    4在存儲過程中使用臨時Table和游標的注意事項
  
    如果我們的商業邏輯比較復雜在存儲過程當中就需要一些媒介作為中轉台這時候臨時表舅發揮了作用但是請
  務必記得在使用完之後即使刪除使用到的臨時Table
  
    而在存儲過程當中想要依次遍歷一個記錄集的唯一方法就是使用系統游標同樣要注意的是在使用完成之後及時關
  閉和銷毀游標對象釋放他用到的資源並且不在萬不得已的情況下不要隨意使用游標因為他會占用較多的系統資源
  尤其是對於大並發量的情況下很容易使得系統資源耗盡而崩潰
  
    使用臨時Table和游標各有利弊在使用的過程中要適當的利用即可!
  
    5在存儲過程中調用外部的ActiveX DLL程序
  
    有些特殊的情況下我們可能會需要調用外部的ActiveX DLL程序這個時候就需要使用到系統的存儲過
  程sp_OACreate以及其他的相關系統存儲過程都是以sp_OA開頭的存儲過程可以自由的在自己的存儲
  過程當中調用ActiveX DLL的各種方法和屬性比如下面的例子
  
    DECLARE @object int
  
    DECLARE @hr int
  
    DECLARE @property varchar(255)
  
    DECLARE @return varchar(255)
  
    DECLARE @src varchar(255) @desc varchar(255)
  
    -- 建立一個對象(SQLDMO.SQLServer).
  
    EXEC @hr = sp_OACreate 'SQLDMO.SQLServer' @object 
  OUT
  
    IF @hr <> 0
  
    BEGIN
  
       EXEC sp_OAGetErrorInfo @object @src OUT @desc 
  OUT 
  
       SELECT hr=convert(varbinary(4)@hr) Source=@sr
  c Description=@desc
  
        RETURN
  
    END
  
    -- 設置對象的屬性.
  
    EXEC @hr = sp_OASetProperty @object 'HostName' 'G
  izmo'
  
    IF @hr <> 0
  
    BEGIN
  
       EXEC sp_OAGetErrorInfo @object @src OUT @desc 
  OUT 
  
       SELECT hr=convert(varbinary(4)@hr) Source=@sr
  c Description=@desc
  
        RETURN
  
    END
  
    -- 通過OUTPUT參數獲取對象的屬性值.
  
    EXEC @hr = sp_OAGetProperty @object 'HostName' @p
  roperty OUT
  
    IF @hr <> 0
  
    BEGIN
  
       EXEC sp_OAGetErrorInfo @object @src OUT @desc 
  OUT 
  
       SELECT hr=convert(varbinary(4)@hr) Source=@sr
  c Description=@desc
  
        RETURN
  
    END
  
    PRINT @property
  
    -- 調用對象的方法
  
    EXEC @hr = sp_OAMethod @object 'Connect' NULL 'm
  y_server' 'my_login' 'my_password'
  
    IF @hr <> 0
  
    BEGIN
  
       EXEC sp_OAGetErrorInfo @object @src OUT @desc 
  OUT 
  
       SELECT hr=convert(varb
From:http://tw.wingwit.com/Article/program/SQLServer/201311/11235.html
    Copyright © 2005-2013 電腦知識網 Computer Knowledge   All rights reserved.