SQL存儲過程的基本概念及文法

存儲過程的概念

       SQL Server提供了一種方法，它可以将一些固定的操作集中起來由SQL Server資料庫伺服器來完成，以實作某個任務，這種方法就是存儲過程。

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

       在SQL Server中存儲過程分為兩類：即系統提供的存儲過程和使用者自定義的存儲過程。

       可以出于任何使用SQL語句的目的來使用存儲過程，它具有以下優點：

       可以在單個存儲過程中執行一系列SQL語句。

       可以從自己的存儲過程内引用其他存儲過程，這可以簡化一系列複雜語句。

       存儲過程在建立時即在伺服器上進行編譯，是以執行起來比單個SQL語句快，而且減少網絡通信的負擔。

       安全性更高。

建立存儲過程 

       在SQL Server中，可以使用三種方法建立存儲過程 ：

         ①使用建立存儲過程向導建立存儲過程。

         ②利用SQL Server 企業管理器建立存儲過程。

         ③使用Transact-SQL語句中的CREATE PROCEDURE指令建立存儲過程。

下面介紹使用Transact-SQL語句中的CREATE PROCEDURE指令建立存儲過程

    建立存儲過程前，應該考慮下列幾個事項： 

     ①不能将 CREATE PROCEDURE 語句與其它 SQL 語句組合到單個批進行中。

     ②存儲過程可以嵌套使用，嵌套的最大深度不能超過32層。

     ③建立存儲過程的權限預設屬于資料庫所有者，該所有者可将此權限授予其他使用者。

     ④存儲過程是資料庫對象，其名稱必須遵守辨別符規則。

     ⑤隻能在目前資料庫中建立存儲過程。

     ⑥ 一個存儲過程的最大尺寸為128M。

使用CREATE PROCEDURE建立存儲過程的文法形式如下：

QUOTE:

CREATE PROC[EDURE]procedure_name[;number][;number]

[{@parameter data_type}[VARYING][=default][OUTPUT]][,...n] 

WITH   

{RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION}]

[FOR REPLICATION] 

AS sql_statement [ ...n ] 

用CREATE PROCEDURE建立存儲過程的文法參數的意義如下： 

procedure_name：用于指定要建立的存儲過程的名稱。 

number：該參數是可選的整數，它用來對同名的存儲過程分組，以便用一條 DROP PROCEDURE 語句即可将同組的過程一起除去。 

@parameter：過程中的參數。在 CREATE PROCEDURE 語句中可以聲明一個或多個參數。 

data_type：用于指定參數的資料類型。 

VARYING：用于指定作為輸出OUTPUT參數支援的結果集。 

Default：用于指定參數的預設值。 

OUTPUT：表明該參數是一個傳回參數。 

例如：下面建立一個 簡單的存儲過程productinfo，用于檢索産品資訊。

USE Northwind

if exists(select name from sysobjects

          where name='productinfo' and type = 'p')

   drop procedure productinfo

GO

create  procedure productinfo

as

select * from products

通過下述sql語句執行該存儲過程：execute productinfo 

即可檢索到産品資訊。

執行存儲過程 

直接執行存儲過程可以使用EXECUTE指令來執行，其文法形式如下：

[[EXEC[UTE]]

   {       [@return_status=]

          {procedure_name[;number]|@procedure_name_var}            [[@parameter=]{value|@variable[OUTPUT]|[DEFAULT]}      

      [,...n] 

[ WITH RECOMPILE ] 

使用 EXECUTE 指令傳遞單個參數，它執行 showind 存儲過程，以 titles 為參數值。showind 存儲過程需要參數 (@tabname)，它是一個表的名稱。其程式清單如下：

    EXEC showind titles

當然，在執行過程中變量可以顯式命名：

    EXEC showind @tabname = titles

如果這是 isql 腳本或批進行中第一個語句，則 EXEC 語句可以省略：

    showind titles或者showind @tabname = titles

下面的例子使用了預設參數

CREATE PROCEDURE insert_Products_1

        ( @SupplierID_2         int,

         @CategoryID_3         int,

                 @ProductName_1 nvarchar(40)='無')

AS INSERT INTO Products

         (ProductName,SupplierID,CategoryID) 

VALUES 

        (@ProductName_1,@SupplierID_2,@CategoryID_3)

exec insert_Products_1 1,1

Select * from Products where SupplierID=1 and CategoryID=1

下面的例子使用了傳回參數

CREATE PROCEDURE query_products

(      @SupplierID_1 int,

        @ProductName_2 nvarchar(40) output)

AS 

select @ProductName_2 = ProductName   from products

where SupplierID = @SupplierID_1

執行該存儲過程來查詢SupplierID為1的産品名：

declare @product nvarchar(40)

exec query_products 1,@product output

select '産品名'= @product

go

檢視存儲過程 

   存儲過程被建立之後，它的名字就存儲在系統表sysobjects中，它的源代碼存放在系統表syscomments中。可以使用使用企業管理器或系統存儲過程來檢視使用者建立的存儲過程。

使用企業管理器檢視使用者建立的存儲過程 

   在企業管理器中，打開指定的伺服器和資料庫項，選擇要建立存儲過程的資料庫，單擊存儲過程檔案夾，此時在右邊的頁框中顯示該資料庫的所有存儲過程。用右鍵單擊要檢視的存儲過程，從彈出的快捷菜單中選擇屬性選項，此時便可以看到存儲過程的源代碼。 

使用系統存儲過程來檢視使用者建立的存儲過程 

可供使用的系統存儲過程及其文法形式如下：

sp_help：用于顯示存儲過程的參數及其資料類型

   sp_help [[@objname=] name]

參數name為要檢視的存儲過程的名稱。 

   sp_helptext：用于顯示存儲過程的源代碼 

   sp_helptext [[@objname=] name]

參數name為要檢視的存儲過程的名稱。

   sp_depends：用于顯示和存儲過程相關的資料庫對象

   sp_depends [@objname=]’object’

參數object為要檢視依賴關系的存儲過程的名稱。

   sp_stored_procedures：用于傳回目前資料庫中的存儲過程清單

修改存儲過程

    存儲過程可以根據使用者的要求或者基表定義的改變而改變。使用ALTER PROCEDURE語句可以更改先前通過執行 CREATE PROCEDURE 語句建立的過程，但不會更改權限，也不影響相關的存儲過程或觸發器。其文法形式如下：

   ALTERPROC[EDURE]procedure_name[;number]

[{@parameterdata_type}

[VARYING][=default][OUTPUT]][,...n] [WITH

   {RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION}]

AS   

sql_statement [ ...n ]

重命名和删除存儲過程

1. 重命名存儲過程

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

      sp_rename  原存儲過程名稱，新存儲過程名稱

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

删除存儲過程

   删除存儲過程可以使用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'   

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

最後給大家舉一個例子：

/**

1、        在Northwind資料庫中，建立一個帶查詢參數的存儲過程，

要求在輸入一個定購金額總額＠total時，查詢超出該值的所

有産品的相關資訊，包括産品名稱和供應商名稱、機關數量、

單價、以及該産品的定購金額總額，并通過一個輸出參數傳回

滿足查詢條件的産品數

**/