軟件開發學習之—實例解析SQL存儲過程
來源：荊州網站建設
時間：2017-05-19
存儲過程(Stored Procedure),是一組為了完成特定功能的SQL 語句，類似一門程序設計語言，也包括了數據類型、流程控制、輸入和輸出和它自己的函數庫。存儲過程可以說是一個記錄集，它是由一些T-SQL語句組成的代碼塊，這些T-SQL語句代碼像一個方法一樣實現一些功能（對單表或多表的增刪改查），然后再給這個代碼塊取一個名字，在用到這個功能的時候調用他就行了。不過SQL存儲過程對于一些初學者來說還是比較抽象難理解的，因此本文將由淺至深地剖析SQL存儲過程，幫助你學習它。
存儲過程的優點
1.存儲過程只在創造時進行編譯，以后每次執行存儲過程都不需再重新編譯，而一般SQL語句每執行一次就編譯一次,所以使用存儲過程可提高數據庫執行速度，效率要比T-SQL語句高。
2.當對數據庫進行復雜操作時，可將此復雜操作用存儲過程封裝起來與數據庫提供的事務處理結合一起使用。
3、一個存儲過程在程序在網絡中交互時可以替代大堆的T-SQL語句，所以也能降低網絡的通信量，提高通信速率。
4.存儲過程可以重復使用,可減少數據庫開發人員的工作量。
5.安全性高,可設定只有某些用戶才具有對指定存儲過程的使用權
存儲過程基本語法
--------------創建存儲過程-----------------
CREATE PROC [ EDURE ] procedure_name [ ;number ]
[{ @parameter data_type }
[ VARYING ] [ = default ] [ OUTPUT ]
][ ,...n ]
[ WITH
{RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION } ]
[ FOR REPLICATION ]
AS sql_statement [ ...n ]
--------------調用存儲過程-----------------
EXECUTE Procedure_name '' --存儲過程如果有參數，后面加參數格式為：@參數名=value，也可直接為參數值value
--------------刪除存儲過程-----------------
drop procedure procedure_name --在存儲過程中能調用另外一個存儲過程，而不能刪除另外一個存儲過程
創建存儲過程的參數
1.procedure_name ：存儲過程的名稱，在前面加#為局部臨時存儲過程，加##為全局臨時存儲過程。
2.; number：是可選的整數，用來對同名的過程分組，以便用一條 DROP PROCEDURE 語句即可將同組的過程一起除去。例如，名為 orders 的應用程序使用的過程可以命名為 orderproc;1、orderproc;2 等。DROP PROCEDURE orderproc 語句將除去整個組。如果名稱中包含定界標識符，則數字不應包含在標識符中，只應在 procedure_name 前后使用適當的定界符。
3.@parameter：存儲過程的參數。可以有一個或多個。用戶必須在執行過程時提供每個所聲明參數的值（除非定義了該參數的默認值）。存儲過程最多可以有 2.100 個參數。
使用 @ 符號作為第一個字符來指定參數名稱。參數名稱必須符合標識符的規則。每個過程的參數僅用于該過程本身；相同的參數名稱可以用在其它過程中。默認情況下，參數只能代替常量，而不能用于代替表名、列名或其它數據庫對象的名稱。有關更多信息，請參見 EXECUTE。
4.data_type：參數的數據類型。所有數據類型（包括text、ntext 和 image）均可以用作存儲過程的參數。不過，cursor 數據類型只能用于 OUTPUT 參數。如果指定的數據類型為 cursor，也必須同時指定 VARYING 和 OUTPUT 關鍵字。有關 SQL Server 提供的數據類型及其語法的更多信息，請參見數據類型。
說明對于可以是 cursor 數據類型的輸出參數，沒有最大數目的限制。
5.VARYING：指定作為輸出參數支持的結果集（由存儲過程動態構造，內容可以變化）。僅適用于游標參數。
6.default：參數的默認值。如果定義了默認值，不必指定該參數的值即可執行過程。默認值必須是常量或 NULL。如果過程將對該參數使用 LIKE 關鍵字，那么默認值中可以包含通配符（%、_、[] 和 [^]）。
7.OUTPUT：表明參數是返回參數。該選項的值可以返回給EXEC[UTE]。使用 OUTPUT 參數可將信息返回給調用過程。Text、ntext 和 image 參數可用作 OUTPUT 參數。使用 OUTPUT 關鍵字的輸出參數可以是游標占位符。
8.RECOMPILE: 表明 SQL Server 不會緩存該過程的計劃，該過程將在運行時重新編譯。在使用非典型值或臨時值而不希望覆蓋緩存在內存中的執行計劃時，請使用 RECOMPILE 選項。
9.ENCRYPTION: 表示 SQL Server加密 syscomments 表中包含 CREATE PROCEDURE 語句文本的條目。使用 ENCRYPTION 可防止將過程作為 SQL Server 復制的一部分發布。說明在升級過程中，SQL Server 利用存儲在 syscomments 中的加密注釋來重新創建加密過程。
10.FOR REPLICATION：指定不能在訂閱服務器上執行為復制創建的存儲過程。.使用 FOR REPLICATION 選項創建的存儲過程可用作存儲過程篩選，且只能在復制過程中執行。本選項不能和 WITH RECOMPILE 選項一起使用。
11.AS:指定過程要執行的操作。
12.sql_statement:過程中要包含的任意數目和類型的 Transact-SQL 語句。但有一些限制。
實例操作學習
下面通過表Student來具體了解一下存儲過程，因為是要了解存儲過程的簡單用法，所以例子很簡單。
無參數存儲過程：
選出Student表中的所有信息
create proc StuProc
as //此處 as 不可以省略不寫
begin //begin 和 end 是一對，不可以只寫其中一個，但可以都不寫
select S#,Sname,Sage,Ssex from student
end
go
有參數存儲過程：
全局變量
全局變量也稱為外部變量，是在函數的外部定義的，它的作用域為從變量定義處開始，到本程序文件的末尾。
選出指定姓名的學生信息:
create proc StuProc
@sname varchar(100)
as
begin
select S#,Sname,Sage,Ssex from studentwhere sname=@sname
end
go
exec StuProc '趙雷' //執行語句
上面是在外部給變量賦值，也可以在內部直接給變量設置默認值
create proc StuProc
@sname varchar(100)='趙雷'
as
begin
select S#,Sname,Sage,Ssex from studentwhere sname=@sname
end
go
exec StuProc
也可以把變量的內容輸出，使用output
create proc StuProc
@sname varchar(100),
@IsRight int output //傳出參數
as
if exists (select S#,Sname,Sage,Ssex fromstudent where sname=@sname)
set @IsRight =1
else
set @IsRight=0
go
declare @IsRight int
exec StuProc '趙雷' ,@IsRight output
select @IsRight
以上是全局變量，下面來了解局部變量
局部變量：
局部變量也稱為內部變量。局部變量是在函數內作定義說明的。其作用域僅限于函數內部，離開該函數后再使用這種變量是非法的。
局部變量的定義：
必須先用Declare命令定以后才可以使用，declare{@變量名數據類型}
局部變量的賦值方法：
set{@變量名=表達式}或者select{@變量名=表達式}
局部變量的顯示：
create proc StuProc
as
declare @sname varchar(100)
set @sname='趙雷'
select S#,Sname,Sage,Ssex from studentwhere sname=@sname
go
exec StuProc
那如果是要把局部變量的數據顯示出來怎么辦呢？
create proc StuProc
as
declare @sname varchar(100)
set @sname=(select Sname from student whereS#=01)
select @sname
go
exec StuProc
更詳細的實例操作學習
比如，在SQL Server查詢編輯器窗口中用CREATE PROCEDURE語句創建存儲過程PROC_InsertEmployee，用于實現向員工信息表（tb_Employee）中添加信息，同時生成自動編號。其SQL語句如下：
IF EXISTS (SELECT name
FROM sysobjects
WHERE name ='Proc_InsertEmployee'
AND type = 'P')
DROP PROCEDURE Proc_InsertEmployee
GO
CREATE PROCEDURE Proc_InsertEmployee
@PName nvarchar(50),
@PSex nvarchar(4),
@PAge int,
@PWage money
AS
begin
declare @PID nvarchar(50)
select @PID=Max(員工編號) from tb_Employee
if(@PID is null)
set @PID='P1001'
else
set @PID='P'+cast(cast(substring(@PID,2,4) as int)+1 as nvarchar(50))
begin
insert into tb_Employee values(@PID,@PName,@PSex,@PAge,@PWage)
end
end
go
存儲過程的修改：
創建完存儲過程之后，如果需要重新修改存儲過程的功能及參數，可以在SQL Server 2005中通過以下兩種方法進行修改：一種是用Microsoft SQL Server Mangement修改存儲過程；另外一種是用T-SQL語句修改存儲過程。
使用Microsoft SQL Server Mangement修改存儲過程，步驟如下：
（1）在SQL Server Management Studio的“對象資源管理器”中，選擇要修改存儲過程所在的數據庫（如：db_18），然后在該數據庫下，選擇“可編程性”。
（2）打開“存儲過程”文件夾，右鍵單擊要修改的存儲過程（如：PROC_SEINFO），在彈出的快捷菜單中選擇“修改”命令，將會出現查詢編輯器窗口。用戶可以在此窗口中編輯T-SQL代碼，完成編輯后，單擊工具欄中的“執行（X）”按鈕，執行修改代碼。用戶可以在查詢編輯器下方的Message窗口中看到執行結果信息。
使用Transact-SQL修改存儲過程：
使用ALTER PROCEDURE語句修改存儲過程，它不會影響存儲過程的權限設定，也不會更改存儲過程的名稱。
語法：
ALTER PROC [ EDURE ] procedure_name [ ;number ]
[{ @parameter data_type }
[ VARYING ] [ = default ] [ OUTPUT ]
][ ,...n ]
[ WITH
{RECOMPILE | ENCRYPTION
| RECOMPILE , ENCRYPTION }
]
[ FOR REPLICATION ]
AS
sql_statement [ ...n ]
參數說明：
procedure_name：是要更改的存儲過程的名稱。
交叉鏈接：關于ALTER PROCEDURE語句的其他參數與CREATE PROCEDURE語句相同，可參見上面的“創建存儲過程的參數”。
例如，修改存儲過程PROC_SEINFO，用于查詢年齡大于35的員工信息。SQL語句如下：
ALTER PROCEDURE [dbo].[PROC_SEINFO]
AS
BEGIN
SELECT * FROM tb_Employee where 員工年齡>35
END
存儲過程的刪除：
使用Microsoft SQL Server Mangement刪除存儲過程，步驟如下：
（1）在SQL Server Management Studio的“對象資源管理器”中，選擇要刪除存儲過程所在的數據庫（如：db_student），然后在該數據庫下選擇“可編程性”。
（2）打開“存儲過程”文件夾，右鍵單擊要刪除的存儲過程（如：PROC_SEINFO），在彈出的快捷菜單中選擇“刪除”命令。
（3）單擊“確定”按鈕，即可刪除所選定的存儲過程。
注意：刪除數據表后，并不會刪除相關聯的存儲過程，只是其存儲過程無法執行。
使用T-SQL刪除存儲過程：
DROP PROCEDURE語句用于從當前數據庫中刪除一個或多個存儲過程或過程組。
語法：
DROP PROCEDURE { procedure } [ ,...n ]
參數說明：
Procedure：是要刪除的存儲過程或存儲過程組的名稱。過程名稱必須符合標識符規則。可以選擇是否指定過程所有者名稱，但不能指定服務器名稱和數據庫名稱。
n：是表示可以指定多個過程的占位符。
例如刪除PROC_SEINFO存儲過程的SQL語句如下。
DROP PROCEDURE PROC_SEINFO
例如，刪除多個存儲過程proc10、proc20和proc30。
DROP PROCEDURE proc10, proc20, proc30
例如，刪除存儲過程組procs（其中包含存儲過程proc1、proc2、proc3）。
DROP PROCEDURE procs
注意：SQL語句DROP不能刪除存儲過程組中的單個存儲過程。
應用存儲過程驗證用戶登錄身份：
目前，驗證用戶登錄身份的方法有多種，而通過調用存儲過程來實現用戶身份驗證是目前最好的解決方案之一。因為存儲過程在創建時即在服務器上進行編譯，所以執行起來比單個SQL語句要快得多。
本例是通過調用存儲過程來驗證用戶登錄的用戶名和密碼是否正確。運行本實例，在“用戶名”和“密碼”文本框中輸入相應的用戶名和密碼，單擊“登錄”按鈕即可。
程序開發步驟：
（1）新建一個網站，將其命名為"index"，默認主頁名為Default.aspx。
（2）Default.aspx頁面涉及到的控件如表1所示。
（3）主要程序代碼如下。
打開SQL Server Management Studio，并連接到SQL Server2005中的數據庫。單擊工具欄中“”按鈕，新建查詢編輯器。在該查詢編輯器中，創建驗證登錄用戶身份的存儲過程PROC_EXISTS，具體的SQL語句如下：
CREATE PROC PROC_EXISTS
(
@UserName NVARCHAR(20),
@PassWord NVARCHAR(20),
@ReturnValue int OUTPUT
)
AS
IF EXISTS(select * from tb_member whereuserName=@UserName AND passWord=@PassWord)
set @ReturnValue= 100
ELSE
set @ReturnValue= -100
GO
在"登錄"按鈕的Click事件下，執行驗證登錄用戶身份的存儲過程，如果輸入的用戶名和密碼正確，則彈出對話框提示用戶登錄成功，代碼如下：
protected void btnLogin_Click(objectsender, EventArgs e)
{
//連接數據庫
myConn = newSqlConnection(ConfigurationManager.AppSettings["ConnectionString"].ToString());
myCmd = new SqlCommand("PROC_EXISTS", myConn); //調用存儲過程，判斷用戶是否存在
myCmd.CommandType = CommandType.StoredProcedure;
//為存儲過程的參數賦值
SqlParameter userName=new SqlParameter("@UserName",SqlDbType.NVarChar, 20);
userName.Value=this.txtName.Text.Trim();
myCmd.Parameters.Add(userName);
SqlParameter passWord=new SqlParameter("@PassWord",SqlDbType.NVarChar, 20);
passWord.Value = this.txtPassword.Text.Trim();
myCmd.Parameters.Add(passWord);
//指出該參數是存儲過程的OUTPUT參數
SqlParameter ReturnValue = newSqlParameter("@ReturnValue",SqlDbType.Int ,4);
ReturnValue.Direction = ParameterDirection.Output;
myCmd.Parameters.Add(ReturnValue);
try
{
myConn.Open();
myCmd.ExecuteNonQuery();
if (int.Parse(ReturnValue.Value.ToString()) == 100)
{
Response.Write("<script>alert('您是合法用戶，登錄成功！')</script>");
return;
}
else
{
Response.Write("<script>alert('您輸入的用戶名和密碼不正確，請重新輸入！')</script>");
return;
}
}
catch(Exception ex)
{
Response.Write(ex.Message.ToString());
}
finally
{
myConn.Close();
myConn.Dispose();
myCmd.Dispose();
}}
以上由湖北國菱荊州軟件開發、荊州網站建設工程師整理發布。喜歡程序開發的伙伴們歡迎一起交流探討。Email:business@gl-ns.com，電話：0716-6666211.
鄂公網安備 42100202000212號