預存程序入門教程

什麼是預存程序呢？

定義：

      將常用的或很複雜的工作，預先用SQL語句寫好並用一個指定的名稱儲存起來, 那麼以後要叫資料庫提供與已定義好的預存程序的功能相同的服務時,只需調用execute,即可自動完成命令。

講到這裡,可能有人要問：這麼說預存程序就是一堆SQL語句而已啊？

                                       Microsoft公司為什麼還要添加這個技術呢?

那麼預存程序與一般的SQL語句有什麼區別呢?

預存程序的優點：

                       1.預存程序只在創造時進行編譯，以後每次執行預存程序都不需再重新編譯，而一般SQL語句每執行一次就編譯一次,所以使用預存程序可提高資料庫執行速度。

                        2.當對資料庫進行複雜操作時(如對多個表進行Update,Insert,Query,Delete時），可將此複雜操作用預存程序封裝起來與資料庫提供的交易處理結合一起使用。

                       3.預存程序可以重複使用,可減少資料庫開發人員的工作量

                       4.安全性高,可設定只有某此使用者才具有對指定預存程序的使用權

預存程序的種類：

    1.系統預存程序：以sp_開頭,用來進行系統的各項設定.取得資訊.相關管理工作,

                               如 sp_help就是取得指定對象的相關資訊

   2.擴充預存程序   以XP_開頭,用來叫用作業系統提供的功能

                              exec master..xp_cmdshell 'ping 10.8.16.1'

   3.使用者自訂的預存程序,這是我們所指的預存程序

   常用格式

   Create procedure procedue_name

   [@parameter data_type][output]

   [with]{recompile|encryption}

   as

        sql_statement

解釋: 

output：表示此參數是可傳回的

with {recompile|encryption}

recompile:表示每次執行此預存程序時都重新編譯一次

encryption:所建立的預存程序的內容會被加密

 

例：

CREATE PROC upGetUserName
@intUserId        INT,
@ostrUserName NVARCHAR(20) OUTPUT                -- 要輸出的參數
AS
BEGIN
        -- 將uName的值賦給 @ostrUserName 變數，即要輸出的參數
        SELECT @ostrUserName=uName FROM uUser WHERE uId=@intUserId
END 

 

其中 CREATE PROC 語句（完整語句為CREATE PROCEDURE）的意思就是告訴SQL SERVER，現在需要建立一個預存程序，upGetUserName 就是預存程序名稱，@intUserId 和 @ostrUserName 分別是該預存程序的兩個參數，注意，在SQL SERVER中，所有使用者定義的變數都以“@”開頭，OUTPUT關鍵字表示這個參數是用來輸出的，AS之後就是預存程序內容了。只要將以上代碼在“查詢分析器”裡執行一次，SQL SERVER就會在當前資料庫中建立一個名為“upGetUserName”的預存程序。你可以開啟“企業管理器”，選擇當前操作的資料庫，然後在左邊的樹型列表中選擇“預存程序”，此時就可以在右邊的列表中看到你剛剛建立的預存程序了（如果沒有，重新整理一下即可）。

用sqlserver產生:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE sp_test
 @id int,
 @name varchar(20) output
AS
BEGIN
 -- SET NOCOUNT ON added to prevent extra result sets from
 -- interfering with SELECT statements.
 SET NOCOUNT ON;

    -- Insert statements for procedure here
 SELECT @name=name from test where id=@id
END
GO

 

用sqlserver調用：

USE [test]
GO

DECLARE @return_value int,
  @name varchar(20)

EXEC @return_value = [dbo].[sp_test]
  @id = 1,
  @name = @name OUTPUT

SELECT @name as N'@name'

SELECT 'Return Value' = @return_value

GO