SQl Server預存程序基礎

 

一、預存程序的概念

　　　　預存程序是SQL語句和可選控制流程語句的先行編譯集合，儲存在資料庫中，可由應用程式通過一個調用執行，而且允許使用者聲明變數、有條件執行以及其他強大的編程功能。

在SQL Server中預存程序分為兩類：即系統提供的預存程序和使用者自訂的預存程序。

       　　使用預存程序有幾下幾個優點：

       　　1.可以在單個預存程序中執行一系列SQL語句。

       　　2.可以從自己的預存程序內引用其他預存程序，這可以簡化一系列複雜語句。

   　　　 3.預存程序在建立時即在伺服器上進行編譯，所以執行起來比單個SQL語句快，而且減少網路通訊的負擔。

       　　4.安全性更高，使用參數，天然的避免Sql注入。

二、建立預存程序

　　　　我們在使用Sql語句來建立預存程序前，應該考慮下列幾個事項：

　　　　　　1、不能將 CREATE PROCEDURE 語句與其它 SQL 陳述式組合到單個批處理中。

　　　　　　2、預存程序可以嵌套使用，嵌套的最大深度不能超過32層。

　　　　　　3、建立預存程序的許可權預設屬於資料庫擁有者，該所有者可將此許可權授予其他使用者。

　　　　　　4、預存程序是資料庫物件，其名稱必須遵守標識符規則。

　　　　　　5、只能在當前資料庫中建立預存程序。

　　　　　　6、一個預存程序的最大尺寸為128M。

　　　　建立預存程序的文法如下：

代碼

　　　　　　CREATE   PROCEDURE   預存程序名

         　　（

                   參數1  參數類型=參數值            參數方向

                   參數2  參數類型=參數值            參數方向

　　　　　　  )

　　　　　　AS

                  　　Begin

                            預存程序體

                            RETURN

                 　　 End

 

　　　　下面我們使用樣本來展示如何建立一個預存程序：      　　

代碼

USE NorthwindGO  /*預存程序被建立之後，它的名字就儲存在系統資料表sysobjects中，  它的原始碼存放在系統資料表syscomments中,所以在建立預存程序之前，  先判斷一下系統中是否已經建立過該預存程序了，如果建立過了，那麼先刪除該預存程序。*/ If  Exists(select * from sysobjects where name=’ myProcedure’ and type=’p’)         　　Drop  procedure  myProcedure;GO Create  Proc   myProcedure( @SupplierID_2         int,  @CategoryID_3         int,  @ProductName_1        nvarchar(40)='無',     --該參數預設值為’無’                     @ProductName_2        nvarchar(40)   output)AS          　　Begin　　　　　　Insert   into  Products 　　(ProductName,SupplierID,CategoryID) 　　　　　　　　　　　　　　　values   　　(@ProductName_1,@SupplierID_2,@CategoryID_3);            select @ProductName_2 = ProductName   from products           　　　　　　　　　　　　　　　　　　where SupplierID = @SupplierID_1 ；                  　　endGO

 

　　注意：　1. 語句體中，使用SET來給參數賦值。

           　　2. 預存程序的所有變數的參數都需要用“@”作為開始符；

           　　3. 在預存程序中能聲明變數，能進行迴圈操作，如下：

代碼

Create    procedure   BatchAddClass

As

Begin

      Declare    @count    int

      Set   @count=10

      While   @count>0

           Begin

                Insert into  tbClass(ClassName) Values(@count)

                Set   @count=@count-1

           End

      Return

End

 　　上述語句實現了批量插入10條記錄到tbClass表中；

 

三、執行預存程序

　　直接執行預存程序可以使用EXECUTE命令來執行，下面使用樣本來執行預存程序：

 

declare   @product   nvarchar(40)

exec  myProcedure   1, 1001,  @product output

select '產品名'= @product

go

 

　　使用系統預存程序來查看使用者建立的預存程序,可供使用的系統預存程序及其文法形式如下：

　　　　sp_help：用於顯示預存程序的參數及其資料類型

              　sp_help [name]

　　　　　　參數name為要查看的預存程序的名稱。

     　　sp_helptext：用於顯示預存程序的原始碼

                 sp_helptext [name]

　　　　　　參數name為要查看的預存程序的名稱。

     　　sp_depends：用於顯示和預存程序相關的資料庫物件

                 sp_depends [name]

　　　　　　參數name為要查看依賴關係的預存程序的名稱。

     　　sp_stored_procedures：用於返回當前資料庫中的預存程序列表

 

四、修改預存程序

　　預存程序可以根據使用者的要求或者基表定義的改變而改變。使用ALTER PROCEDURE語句可以更改先前通過執行 CREATE PROCEDURE 語句建立的過程，但不會更改許可權，也不影響相關的預存程序或觸發器。修改預存程序的方法和建立預存程序的方法一樣，只是將建立預存程序的Create  Procedure改為Alter Procedure而已。

 

五、重新命名和刪除預存程序

　　1. 重新命名預存程序

                修改預存程序的名稱可以使用系統預存程序sp_rename，其文法形式如下：

                    　　sp_rename  原預存程序名稱，新預存程序名稱

           　　另外，通過企業管理器也可以修改預存程序的名稱。

 

　　2.刪除預存程序

　　　　　　刪除預存程序可以使用DROP命令，DROP命令可以將一個或者多個預存程序或者預存程序組從當前資料庫中刪除，其文法形式如下：

         　　　　drop    procedure {procedure} [,…n]

　　　　　　當然，利用企業管理器也可以很方便地刪除預存程序。

 

六、預存程序的重新編譯

 　　在我們使用了一次預存程序後，可能會因為某些原因，必須向表中新增加資料列或者為表新添加索引，從而改變了資料庫的邏輯結構。這時，需要對預存程序進行重新編譯，SQL Server提供三種重新編譯預存程序的方法 ：

    1、在建立預存程序時設定重新編譯

       文法格式：

CREATE  PROCEDURE   procedure_name    

WITH   RECOMPILE    AS   sql_statement

     2、在執行預存程序時設定重編譯

       文法格式：

EXECUTE  procedure_name  WITH  RECOMPILE

 

    3、通過使用系統預存程序設定重編譯

        文法格式

 EXEC  sp_recompile  OBJECT

 

七、系統預存程序與擴充預存程序

　　1.系統預存程序

　　　　系統預存程序儲存在master資料庫中，並以sp_為首碼，主要用來從系統資料表中擷取資訊，為系統管理員管理SQL Server提供協助，為使用者查看資料庫物件提供方便。比如用來查看數　　據庫對象資訊的系統預存程序sp_help、顯示預存程序和其它對象的文本的預存程序sp_helptext等。

 

　　2.擴充預存程序：

　　　　擴充預存程序以xp_為首碼，它是關聯式資料庫引擎的開放式資料服務層的一部分，其可以使使用者在動態連結程式庫(DLL)檔案所包含的函數中實現邏輯，從而擴充了Transact-SQL的功能，並且可以象調用Transact-SQL過程那樣從Transact-SQL語句調用這些函數。

例:  利用擴充預存程序xp_cmdshell為一個作業系統外殼執行指定命令串，並作為文本返回任何輸出。

               use master

               exec   xp_cmdshell   'dir *.exe'   

 

    執行結果返回系統目錄下的檔案內容文本資訊。