Sql Server 存儲過程實例講解

 　　一、遇到的問題

　　我們在之前學習的課程寫了不少的批處理語句，這些批處理語句存在兩個問題：

　　1.沒法像函數那樣傳參數運行(自定義化)

　　2.沒法像函數那樣可以反復地調用(功能化模塊化)

　　說到這裡，我們可以猜到，數據庫中應該可以建立函數形式的數據庫對象來解決這樣的問題。但是介紹這種數據庫對象之前，我們再來看幾個需要解決的問題：

　　我們已經學會把一條select語句封裝在視圖中，但是它只能用來查詢，如果我們希望進行其它操作，比如增刪改記錄、建刪庫表等，是不能用視圖來完成的。

　　另一方面，如果把一個帶有更改操作的批處理整個存儲成數據庫對象，還可以把處理數據的程序移動到離數據盡可能近的地方，而不是總把這些操作寫在客戶端程序(如Java、C#)中。通過將處理數據的程序從客戶應用程序移動到服務器，可以降低網絡流量，並提高性能和數據的完整性。

　　二、存儲過程的概念

　　解決上面的問題，我們可以使用一種叫做“存儲過程”的數據庫對象。

　　存儲過程(Stored Procedure) 把我們經常用到的一串復雜sql語句保存成一個數據庫對象，並給它起一個名字。每次使用存儲過程只需要使用如下的形式即可：

　　exec proc 存儲過程名

　　存儲過程還可以帶參數運行：

　　exec proc 存儲過程名 參數值[, 參數值...]

　　? 存儲過程並不神秘，它就是批處理。之前提到視圖是保存在服務器上的命名select語句，與之類似，存儲過程是保存在服務器上的命名批處理，系統將預先對它進行編譯。

　　? 存儲過程可以包含幾乎所有的T-SQL語句，如數據存取語句、流程控制語句、錯誤處理語句等，使用起來彈性很大。

　　? 數據庫中也存在著系統函數和用戶定義函數 這兩種對象，用戶定義函數的功能和存儲過程很像，但是有一定的區別。

　　【存儲過程的分類】

　　- 系統存儲過程 system stored procedure 前綴sp_ 例如sp_help sp、helpdb

　　- 擴展存儲過程 extended stored procedure 前綴xp_ 例如xp_cmdshell

　　- 用戶自定義存儲過程 user-defined stored procedure 也就是我們自己創建的

　　三、用戶自定義存儲過程的創建、修改、刪除

　　【創建存儲過程】

　　create proc 存儲過程名 --無參數的存儲過程

　　as

　　批處理語句

　　go

　　【修改存儲過程】

　　alter proc 存儲過程名 --無參數的存儲過程

　　as

　　批處理語句

　　go

　　可以看到，修改存儲過程的語法和創建的語法只差一個單詞，把create換成alt即可。

　　【刪除存儲過程】

　　drop proc 存儲過程名

　　我們還可使用management studio來管理存儲過程，展開菜單樹中的“可編程性”，在“存儲過程”的子節點中可以進行各種操作。這裡要說一下創建：當點擊“新建存儲過程”之後，會出現一個基於模板的創建語句。這時點擊菜單中的“查詢→指定模板參數的值”，即可彈出對話框來對模板進行設置，從而建立我們想要的存儲過程。另外，點擊菜單中的“視圖→模板資源管理器”，可以看到SQL SERVER為我們提供的各種SQL語句模板。

　　【一個簡單的例子】

　　--插入一個以時間為用戶名的用戶

　　create proc insUser

　　as

　　begin tran

　　declare @username varchar(20)

　　set @username=convert(varchar(8),getdate(),112)

　　+replace(convert(varchar(10),getdate(),8),':','')

　　if not exists(select * from yonghu where yonghuming=@username)

　　insert into yonghu values

　　(@username,'111111','@163.com','新用戶')

　　commit tran --也可以寫commit，但是建議不要去掉tran

　　go

　　然後使用exec執行這個存儲過程：

　　exec insUser

　　選中exec這一行，然後按F5快速地反復執行，你會發現在同一秒內只能插入一個用戶。

　　這個存儲過程一旦建立就不能再次執行這段代碼了，可以把create改成alter來修改。

　　注意存儲過程的代碼中不能go語句，因為go是用來提交批的，一旦遇到go系統會認為這個存儲過程的代碼已經書寫完畢，會提交create或者alter的批處理。如果希望在存儲過程中執行另一個批處理，請把該批處理寫成另一個存儲過程並調用。

　　四、用戶自定義存儲過程的參數傳遞和返回值

　　【傳遞參數】

　　create|alter proc 存儲過程名

　　@參數名參數類型[,

　　@參數名 參數類型...]

　　as

　　批處理語句

　　go

　　還記得我們前面做過的一個案例嗎?

　　declare @tablename nvarchar(10),@id varchar(10),@idvalue int

　　declare @sql varchar(100)

　　set @tablename='yiren'

　　set @id='yirenid'

　　set @idvalue=10

　　set @sql='select * from '+@tablename+' where '+@id+' = '+cast(@idvalue as varchar)

　　print @sql

　　exec(@sql)

　　現在我們把它寫成存儲過程。這樣我們每次都可以從一個指定表中提取我們想要的記錄了

　　create proc queryItem

　　@tablename nvarchar(10),

　　@id varchar(20),

　　@idvalue int --參數外面還可以套上圓括號，看起來更加清晰

　　as

　　declare @sql varchar(100)

　　set @sql='select * from '+@tablename+'

　　where '+@id+' = '+cast(@idvalue as varchar)

　　exec(@sql)

　　go

　　調用的方法：

　　exec queryItem 'yiren','yirenid',@idvalue=10

　　queryItem 'jingjiren','jingjirenid',1

　　存儲過程不使用exec也可以調用，但是不推薦這麼做。存儲過程參數的名字可以在調用時寫出來，但是這是完全沒必要的，所以@idvalue=10直接寫成10就可以了。

　　exec加不加括號效果不一樣。加括號是執行sql語句，不加括號是執行存儲過程。

　　【返回值】

　　1.以retrun返回，始終是整數值

　　return只能返回整數，即使不顯式寫出“return 整數值”這樣的語句，存儲過程也會自動返回一個數值0表示成功。我們可以在發生錯誤時返回非0值，表示有錯誤發生。不要試圖使用return返回一個在存儲過程中處理的結果，比如姓名、生日之類的內容，因為它是整數，功能極為有限。我們只用它返回存儲過程執行的狀態就足夠了。請看例子：

　　create proc returnProc

　　as

　　begin tran

　　declare @error int

　　insert into yiren (xingming) values ('王美麗')

　　set @error=@@error

　　insert into yiren (yirenid) values (1)

　　set @error=@error+@@error

　　if @error>0

　　rollback tran

　　else

　　commit tran

　　return @error

　　go

　　調用的方法：

　　declare @error int

　　--set @error=exec returnProc --這樣寫是錯誤的……

　　exec @error=returnProc

　　select '返回值'=@error

　　如果returnProc有個參數@xingming希望傳入'王美麗'，可以這樣調用：

　　exec @error=returnProc '王美麗'

　　--或者：

　　exec @error=returnProc @xingming='王美麗'

　　2.以output參數返回數據

　　output可以用來返回任何類型的數據，嚴格來說，它並不是一個“返回值”，而是一個能夠被存儲過程調用代碼處看到的“外部變量”。這樣說的原因看下面的例子就明白了：

　　--通過id查詢藝人的姓名和年齡

　　create proc queryProfile

　　@id int,

　　@xingming varchar(50) output, --必須有output

　　@nianling int output

　　as

　　select @xingming=xingming,@nianling=nianling

　　from yiren where yirenid=@id

　　go

　　調用的方法：

　　declare @xingming varchar(50),@nianling int

　　exec queryProfile 1,@xingming output,@nianling output --必須有output

　　print '1號藝人的姓名是'+@xingming

　　+'，年齡是'+cast(@nianling as varchar)+'歲'

　　在調用處，我們先定義了兩個變量，然後我們以output的方式把兩個變量傳遞給了存儲過程。於是存儲過程就可以看到這兩個來自外部的變量了。那麼存儲過程中對這兩個變量的一切修改都可以立刻體現到調用處的代碼中，因為它修改的實際上就是調用處的兩個變量。

　　3.select語句的結果集

　　如果在存儲過程中執行了select語句並顯示結果集(並不是使用select語句給變量賦值)，那麼這個結果集也可以看做是一種返回值(不能被批處理語句用，但是作為結果集可以被C#等編程語言使用)。

　　create proc selectProc

　　as

　　select * from yiren

　　go

　　exec selectProc

　　這種存儲過程可以用來實現“帶有參數的視圖” ，在上面【傳遞參數】 中舉出的例子就是。

　　【存儲過程的常