SQL Server 預存程序詳解

◆優點：
　　執行速度更快。預存程序只在創造時進行編譯，而一般SQL語句每執行一次就編譯一次，所以使用預存程序執行速度更快。
　　預存程序用於處理複雜的操作時，程式的可讀性更強、網路的負擔更小。
　　使用預存程序封裝事務效能更佳。
　　能有效放注入，安全性更好。
　　可維護性高，在一些商務規則發生變化時，有時只需調整預存程序即可，而不用改動和重編輯程式。
　　更好的代碼重用。

　　◆ 缺點：
　　預存程序將給伺服器帶來額外的壓力。
　　 預存程序多多時維護比較困難。
　　移植性差，在升級到不同的資料庫時比較困難。
　　調試麻煩，SQL語言的處理功能簡單。

　　總之複雜的操作或需要事務操作的SQL建議使用預存程序，而參數多且操作簡單SQL語句不建議使用預存程序。

預存程序定義

　　預存程序是一組 Transact-SQL 陳述式，它們只需編譯一次，以後即可多次執行。因為 Transact-SQL 陳述式不需要重新編譯，所以執行預存程序可以提高效能。
　　觸發器是一種特殊的預存程序，不由使用者直接調用。建立觸發器時，將其定義為在對特定表或列進行特定類型的資料修改時激發。

預存程序的設計規則

　　CREATE PROCEDURE 定義自身可以包括任意數量和類型的 SQL 陳述式，但以下語句除外。

　　不能在預存程序的任何位置使用這些語句。
　　CREATE AGGREGATE、 CREATE RULE、CREATE DEFAULT、 CREATE SCHEMA、CREATE 或 ALTER FUNCTION、CREATE 或 ALTER TRIGGER、CREATE 或 ALTER PROCEDURE、CREATE   或 ALTER VIEW、SET PARSEONLY、SET SHOWPLAN_ALL、SET SHOWPLAN_TEXT、 SET SHOWPLAN_XML、USE database_name

　　其他資料庫物件均可在預存程序中建立。可以引用在同一預存程序中建立的對象，只要引用時已經建立了該對象即可。
　　可以在預存程序內引用暫存資料表。
　　如果在預存程序內建立本地暫存資料表，則暫存資料表僅為該預存程序而存在；退出該預存程序後，暫存資料表將消失。
　　如果執行的預存程序將調用另一個預存程序，則被調用的預存程序可以訪問由第一個預存程序建立的所有對象，包括暫存資料表在內。
　　如果執行對遠程 Microsoft SQL Server 2005 執行個體變更的遠端預存程序，則不能復原這些更改。遠端預存程序不參與交易處理。
　　預存程序中的參數的最大數目為 2100。
　　預存程序中的局部變數的最大數目僅受可用記憶體的限制。
　　根據可用記憶體的不同，預存程序最大可達 128 MB

實現預存程序

CREATE { PROC | PROCEDURE } [schema_name.] procedure_name [ ; number ]
             [ { @parameter [ type_schema_name. ] data_type } [ VARYING ] [ = default ] [ [ OUT [ PUT ] ] --名稱、類型、預設值、方向
             [ ,...n ]
         [ WITH <procedure_option> [ ,...n ]
         [ FOR REPLICATION ]
         AS
           { <sql_statement> [;][ ...n ] | <method_specifier> } --SQL語句
         [;]
         <procedure_option> ::=
             [ ENCRYPTION ]
             [ RECOMPILE ] --運行時編譯
             [ EXECUTE_AS_Clause ]
         <sql_statement> ::= { [ BEGIN ] statements [ END ] }
         <method_specifier> ::= EXTERNAL NAME assembly_name.class_name.method_name
執行預存程序

　　使用 Transact-SQL EXECUTE 語句。如果預存程序是批處理中的第一條語句，那麼不使用 EXECUTE 關鍵字也可以執行預存程序
　　使用 sp_procoption 讓SQLSERVER 自動執行預存程序
           sp_procoption [ @ProcName = ] 'procedure' , [ @OptionName = ] 'option'    , [ @OptionValue = ] 'value' --過程的名稱、option 的唯一值為 startup、設定為開啟（true 或 on）還是關閉（false 或 off）。

用TSQL語句編寫預存程序

一、變數和參數
           DECLARE 語句通過以下操作初始化 Transact-SQL 變數：
           指定名稱。名稱的第一個字元必須為一個 @。
           指定系統提供的或使用者定義的資料類型和長度。對於數值變數還指定精度和小數位元。對於 XML 類型的變數，可以指定一個可選的架構集合。
           將值設定為 NULL。
           如：DECLARE @MyCounter int
           第一次聲明變數時，其值設定為 NULL。若要為變數賦值，請使用 SET 語句。這是為變數賦值的首選方法。也可以通過 SELECT 語句的挑選清單中當前所引用值為變數賦值。
           參數用於在預存程序和函數以及調用預存程序或函數的應用程式或工具之間交換資料：
           輸入參數允許調用方將資料值傳遞到預存程序或函數。
           輸出參數允許預存程序將資料值或遊標變數傳遞迴調用方。使用者定義函數不能指定輸出參數。
           每個預存程序向調用方返回一個整數傳回碼。如果預存程序沒有顯式設定傳回碼的值，則傳回碼為 0。

二、流程式控制制語句

           1、BEGIN 和 END 語句
               BEGIN 和 END 語句用於將多個 Transact-SQL 陳述式組合為一個邏輯塊。在控制流程語句必須執行包含兩條或多條 Transact-SQL 陳述式的語句塊的任何地方，都可以使用 BEGIN 和 END 語句。
如：
IF (@@ERROR <> 0)
BEGIN
      SET @ErrorSaveVariable = @@ERROR
      PRINT 'Error encountered, ' +
     CAST(@ErrorSaveVariable AS VARCHAR(10))
END
           2、GOTO 語句
               GOTO 語句使 Transact-SQL 批處理的執行跳至標籤。不執行 GOTO 語句和標籤之間的語句。
       IF(1=1)
    GOTO calculate_salary
    print 'go on' --條件成立則跳過此句。
       calculate_salary:
     print 'go to'
           3、IF...ELSE 語句
               IF 語句用於條件的測試。得到的控制流程取決於是否指定了可選的 ELSE 語句：
    if(1=1)
     print 1
    else if(2=2)
     print 2
    else if(3=3)
     print 3
    else
     print 0
           4、RETURN 語句
                 RETURN 語句無條件終止查詢、預存程序或批處理。預存程序或批處理中 RETURN 語句後面的語句都不執行。當在預存程序中使用 RETURN 語句時，此語句可以指定返回給調用應用程式、批處理或過程的整數值。如果 RETURN 未指定值，則預存程序返回 0
           5、WAITFOR 語句
                 WAITFOR 語句掛起批處理、預存程序或事務的執行，直到發生以下情況：
     已超過指定的時間間隔。
     到達一天中指定的時間。
     指定的 RECEIVE 語句至少修改一行或並將其返回到 Service Broker 隊列。
                 WAITFOR 語句由下列子句之一指定：
             DELAY 關鍵字後為 time_to_pass，是指完成 WAITFOR 語句之前等待的時間。完成 WAITFOR 語句之前等待的時間最多為 24 小時。
     如：
      WAITFOR DELAY '00:00:02'
      SELECT EmployeeID FROM    Employee;
             TIME 關鍵字後為 time_to_execute，指定 WAITFOR 陳述式完成所用的時間。
      GO
      BEGIN
          WAITFOR TIME '22:00';
          DBCC CHECKALLOC;
      END;
      GO
             RECEIVE 語句子句，從 Service Broker 隊列檢索一條或多條訊息。使用 RECEIVE 語句指定 WAITFOR 時，如果當前未顯示任何訊息，該語句將等待訊息到達隊列。
             TIMEOUT 關鍵字後為 timeout，指定 Service Broker 等待訊息到達隊列的時間長度（毫秒）。可以在 RECEIVE 語句或 GET CONVERSATION GROUP 語句中指定 TIMEOUT。
           6、WHILE...BREAK 或 CONTINUE 語句
                 只要指定的條件為 True 時，WHILE 語句就會重複語句或語句塊。REAK 或 CONTINUE語句通常和WHILE一起使用。BREAK 語句退出最內層的 WHILE 迴圈，CONTINUE 語句則重新開始 WHILE 迴圈。
go
declare @Num int
declare @ID int
declare @i int
set @i=1
while(exists(select * from T where Num<5    )) --擷取數量小於5的記錄
begin
   select @Num=Num,@ID=ID from T where Num<5 order by ID desc
   print Str(@i)+ '編號：'+Str(@ID)+ ' 值'+str(@Num)
   update T set Num=Num*2 where ID=@ID
   set @i=@i+1
   if(@i>3)
     break --退出迴圈
  
end
           7、CASE 語句
            CASE 函數用於計算多個條件並為每個條件返回單個值。CASE 函數通常的用途是將代碼或縮寫替換為可讀性更強的值
--用法一：
select ID,
    Grade=Case Num
when 1 then '不及格'
when 2 then '不及格'
when 3 then '不及格'
when 4 then '良好'
else '優秀'
end
from T
---用法二：
select ID,
    Grade=Case  
when    Num<3 then '不及格'
when    Num=3 then '及格'
when    Num=4 then '良好'
when    Num>4 then '優秀'
end
from T

三、運行時產生語句
           Transact-SQL 支援使用下列兩種方法於運行時在 TTransact-SQL 指令碼、預存程序和觸發器中產生 SQL 陳述式：
使用 sp_executesql 系統預存程序執行 Unicode 字串。sp_executesql 支援與 RAISERROR 陳述式類似的參數替換。
           使用 EXECUTE 語句執行字串。EXECUTE 語句不支援已執行字串中的參數替換。
四、處理資料庫引擎錯誤
          在 Transact-SQL 中有兩種方式可以擷取錯誤資訊：
          1、在 TRY...CATCH 構造的 CATCH 塊的範圍內，您可以使用以下系統函數：
            ERROR_LINE()，返回出現錯誤的行號。
            ERROR_MESSAGE()，返回將返回給應用程式的訊息文本。該文本包括為所有可替換參數提供的值，如長度、對象名或時間。
            ERROR_NUMBER() 返回錯誤號碼。
            ERROR_PROCEDURE()，返回出現錯誤的預存程序或觸發器的名稱。如果在預存程序或觸發器中未出現錯誤，該函數返回 NULL。
            ERROR_SEVERITY() 返回嚴重性。
            ERROR_STATE()，返回狀態。
           2、在執行任何 Transact-SQL 陳述式之後，您可以立即使用 @@ERROR 函數測試錯誤並檢索錯誤號碼。
           RAISERROR
             RAISERROR 用於將與 SQL Server Database Engine 產生的系統錯誤或警告訊息使用相同格式的訊息返回到應用程式中。
           3、PRINT
             PRINT 語句用於將訊息返回到應用程式。PRINT 採用字元或 Unicode 字串運算式作為參數，並將字串作為訊息返回到應用程式。

本文來自CSDN部落格，轉載請標明出處：http://blog.csdn.net/nutian/archive/2007/10/30/1856313.aspx