SQL Server預存程序基本文法

轉自http://c21.cnblogs.com/archive/2006/05/08/393779.html

預存程序的概念
       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
GO
通過下述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

下面的例子使用了預設參數
USE Northwind
GO
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)
GO
exec insert_Products_1 1,1
Select * from Products where SupplierID=1 and CategoryID=1
GO

下面的例子使用了返回參數
USE Northwind
GO
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}]
[FOR REPLICATION]
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'   
       執行結果返回系統目錄下的檔案內容文本資訊。

最後給大家舉一個例子：

QUOTE:/**
1、        在Northwind資料庫中，建立一個帶查詢參數的預存程序，
要求在輸入一個定購金額總額＠total時，查詢超出該值的所
有產品的相關資訊，包括產品名稱和供應商名稱、單位元量、
單價、以及該產品的定購金額總額，並通過一個輸出參數返回
滿足查詢條件的產品數
**/

IF exists (select * from SysObjects where name='more_than_total' and type='p')
   drop procedure more_than_total
go
CREATE PROCEDURE More_Than_Total
        @total money = 0
AS
Declare @amount smallint
BEGIN
        select distinct
           P.productName,
           S.contactName,
           P.UnitPrice
           
    from Products P inner join [order Details] O
         on p.productID=o.productID inner join suppliers s
         on p.supplierID=s.SupplierID
    where O.productID in
    (select productID
     from   [order Details]
     group by productId
     having sum(quantity*unitprice)>@total
    )
END
GO