636      阿裏雲  技術社區[雲棲]

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

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

【存儲過程的常用功能】

        從存儲過程參數和返回值的用法我們可以看出，存儲過程通常用來處理一些對數據庫的更新操作、或者是按照特定的需要從數據庫中查詢信息，並以變量的形式（而不是結果集的形式）返回給調用處、或者是以結果集的形式返回，但並不能被調用處的語句所使用。我們可以使用return的數值來監控存儲過程執行得是否順利。

五、存儲過程的嵌套調用

        在一個存儲過程中還可以執行另一個存儲過程，即嵌套調用。可以多次嵌套，但最多32層。可以用@@NESTLEVEL來查看嵌套當前層數：

create proc proc_1 --獲取藝人id
@yid int output
as
    select top 1 @yid=yirenid from yiren
       where nicheng='芙蓉姐姐'
    print '存儲過程1在第'+cast(@@NESTLEVEL as varchar)+'層'
go

create proc proc_1_1 --獲取粉絲id
@fid int output
as
    declare @yid int
    exec proc_1 @yid output
    select @fid=yonghuid from fensi
       where yirenid=@yid
       order by yonghuid desc
    print '存儲過程1_1在第'+cast(@@NESTLEVEL as varchar)+'層'
go

create proc proc_1_1_1 --獲得芙蓉姐姐的粉絲
as
    declare @fid int
    exec proc_1_1 @fid output
    select * from yonghu
       where yonghuid=@fid
    print '存儲過程1_1_1在第'+cast(@@NESTLEVEL as varchar)+'層'
go

exec proc_1_1_1
go

六、係統存儲過程

        sp_databases             列出服務器上的所有數據庫（無參數）

        sp_server_info            列出服務器信息（可以有參數）

        sp_stored_procedures              列出當前環境中的所有存儲過程（可以有參數）

        sp_tables              返回當前環境下可查詢的對象的列表（無參數）

        sp_configure              顯示或更改當前服務器的全局配置設置

        sp_help         顯示有關數據庫對象的信息（可以有參數）如sp_help yiren

        sp_helpdb          顯示有關數據庫的信息（可以有參數）如sp_helpdb SuperStar

        sp_helptext       顯示規則、默認值、未加密的存儲過程、用戶定義函數、觸發器或視圖的文本（有參數）如sp_helptext proc_1

七、存儲過程的注意事項

【存儲過程的優點】

Ø  隻在創建時編譯，執行速度快效率高

Ø  減少網絡傳輸流量

Ø  提高安全性

Ø  模塊式編程，可以重複使用

Ø  統一每次的操作流程

【偷偷說一句】

        前麵不管學什麼都要提一下缺點，但是存儲過程這裏卻沒提。這並不意味著存儲過程沒有任何缺點，畢竟任何東西都不能濫用，但是可以看出來，存儲過程的確是T-SQL編程的核心內容，是最重要的部分。而它本身又是如此地容易掌握，相信你現在的心情不錯吧？

【在存儲過程中使用事務】

        存儲過程中是可以使用事務的，這毫無疑問。在存儲過程中使用事務不必用goto語句，在rollback或者commit語句之後直接return即可終止存儲過程的執行。前麵我們也提到過：在普通批處理中實際上也是可以使用return語句的。

八、使用存儲過程實現分頁查詢

<strong>create proc queryPage
    @tablename nvarchar(50), --用於傳入表名
    @idname nvarchar(50), --用於傳入字段名
    @pagesize int, --用於傳入每頁記錄數
    @currentpage int, --用於傳入希望查看的頁麵編號
    @totalpages int output --用於傳出頁麵總數
as
--聲明保存查詢語句的局部變量：
declare @sql as nvarchar(1000)
--聲明保存記錄總數的局部變量：
declare @rowcount as int
--獲得記錄總數：
set @sql='select @rc=count(*) from '+@tablename
--不要直接執行select @rowcount=count(*) from @tablename
--將參數傳入語句：
exec sp_executesql @sql,N'@rc int output',@rc=@rowcount output
--將根據每頁的行數得到的總頁數保存到輸出參數中：
set @totalpages = ceiling(cast(@rowcount as float)/cast(@pagesize as float))
if @currentpage >1
begin
    if @currentpage>@totalpages
    begin
       set @currentpage = @totalpages --則顯示最後一頁
    end
    set @sql = 'select top '+cast(@pagesize as varchar)
    +' * from '+@tablename+' where '+@idname+' not in (select top '
    +cast(@pagesize*(@currentpage-1) as varchar)
    +' '+@idname+' from '+@tablename+' order by '+@idname+')
order by '+@idname
end
else --隻選第一頁就不必使用子查詢了，提高性能
begin
    set @sql = 'select top '+cast(@pagesize as varchar)
    +' * from '+@tablename+' order by '+@idname
end
exec(@sql) --執行查詢語句
go</strong>

Ø  sp_executesql這個係統存儲過程是另一種執行sql語句的方法。它比exec(@sql)的功能高級一些。在這裏我們為它傳入3個參數，分別是等待處理的@sql（必須是nvarchar類型的）、使用字符串為@sql語句定義新的變量名、為字符串中定義的新變量賦值（@rc是字符串中定義的新變量，把它賦為@rowcount，又因為希望通過@rc為@rowcount返回值，所以指定為output）。

Ø  ceiling函數得到大於某小數的最小整數，如ceiling(3.5)會得到4。這裏把@rowcount和@pagesize相除，得到的數字是個小數。小數部分無法組成一個完整的分頁但是不代表沒有記錄，所以使用ceiling函數，讓@totalpages的值能夠正確賦值。

Ø  當傳入的currentpage並不在正確的分頁編號範圍內時，我們的代碼做出了處理，給@currentpage賦了一個正確範圍內的數值。

調用上麵的存儲過程：

declare @totalpages int
exec queryPage 'yiren','yirenid',5,13,@totalpages output
print '一共'+cast(@totalpages as varchar)+'行'

最後更新：2017-04-04 07:36:07

  上一篇： word中插入題注(表1、圖1)

  下一篇： 更新數據庫中某一列的值，讓其在原數的基礎上加N