SQL Server預存程序簡介

標籤：執行   exec   char   網路流   關鍵字   bsp   declare   lte   資料   

預存程序是由一個或多個T-SQL語句組成的語句組，它可以接受輸入參數和輸出參數且能向調用程式返回多個值；可以調用其他過程；可以向調用程式返回結果狀態值以標記執行成功或失敗；

使用預存程序的好處

1. 減少伺服器/用戶端網路流量：過程多條命令進行單個批處理執行，只一次網路傳輸；
2. 更強的安全性：防止SQL注入，也可以進行加密處理；
3. 代碼重複使用：可封裝重複的資料庫操作；
4. 更易維護：更新預存程序指令碼可不更新調用的服務端程式；
5. 更好的效能：首次執行時會產生執行計畫，供後續重複使用；

建立預存程序

預存程序建立文法如下：

USE Database;GOCREATE PROCEDURE getStudents    @num int,    @name  varchar(32)AS    DECLARE @condi varchar(32);    SET　@condi = @name + ‘%‘;    select top (@num) * from student        where name like @condi;GO

上述預存程序有兩個參數，表示查詢以name以@name開頭的前@num條記錄，如傳入@num=10, @name=‘李‘，則會返回前10條學生姓名以‘李’開頭的學生記錄。

執行預存程序

如果預存程序不帶參數： exec proc_name;

如果預存程序帶參數，則按方法簽名傳入參數即可，如上述例子： exec getStudents 10, ‘李‘;

刪除預存程序

刪除預存程序跟刪除資料庫、表一樣，使用DROP關鍵字。 如： DROP PROCEDURE getStudents;

修改預存程序

修改預存程序既可以使用alter關鍵字，也可現將原預存程序刪除再建立。 如：

（1）使用Alter：

ALTER PROCEDURE getStudentsAS     select * from student;GO

（2）先刪除再建立：

IF OBJECT_ID(‘getStudents‘, ‘P‘) IS NOT NULL    DROP PROCEDURE getStudents;GOCREATE PROCEDURE getStudentsAS    select * from student;GO

以上兩種方式效果一樣。

從預存程序中返回資料

預存程序中使用OUTPUT關鍵字來返回資料。看例子：

IF OBJECT_ID(‘getStudentName‘, ‘P‘) IS　NOT NULL    DROP PROCEDURE getStudentName;GOCREATE PROCEDURE getStudentName    @name nvarchar(32) OUTPUTAS    select @name=name from student where name=‘ray‘;RETURNGO

改過程將student的name返回給@name變數，調用如下：

DECLARE @RES nvarchar(32);EXEC getStudentName @[email protected] OUTPUT;PRINT @res;

有時，預存程序OUTPUT參數經常與資料庫遊標（cursor）結合使用，有關用法請參考.

參考資料

• MSDN

SQL Server預存程序簡介