SQL Server 7.0 入門（八）
2024-08-31 00:48:49
供稿：網(wǎng)友
網(wǎng)站運(yùn)營seo文章大全提供全面的站長運(yùn)營經(jīng)驗(yàn)及seo技術(shù)!存儲過程中返回結(jié)果
從存儲過程中返回結(jié)果有三種方式：
1、 返回結(jié)果集
這是客戶端應(yīng)用程序返回結(jié)果的最通用的方法。結(jié)果集是通過使用select語句選擇數(shù)據(jù)產(chǎn)生的。結(jié)果集可以從永久表、臨時(shí)表或局部變量中產(chǎn)生。將結(jié)果返回到另一個(gè)存儲過程不是一種有效的方法。存儲過程不能訪問另一個(gè)存儲過程建立的結(jié)果集。
例如從永久表中返回結(jié)果集：
use pubs
go
create procedure ap_createresultfrompermtable
as
select au_iname from authors
go
例如從局部變量中創(chuàng)建結(jié)果集：
use pubs
go
create procedure ap_createresultfromvariable
as
declare @au_iname char(20)
select @au_iname = au_iname from authors
where au_id = ‘172-32-1176’
select @au_id
go
2、 設(shè)置output參數(shù)的值
輸出參數(shù)經(jīng)常用來從存儲過程中檢索出結(jié)果。如果某個(gè)參數(shù)在傳輸?shù)酱鎯^程中時(shí)被定義成output，則對該參數(shù)的任何修改在退出存儲之后仍然有效。
例如：
use pubs
go
create procedure ap_setoutputvar @count integer output
as
select @count = count(*) from authors
go
從輸出參數(shù)中檢索出值：
use pubs
go
create procedure ap_getoutputvar
as
declare @num integer
execute ap_setoutputvar @num output
print “the count is”+convert(char,@num)
go
· 將游標(biāo)使用成output參數(shù)。游標(biāo)可以使用output（輸出）參數(shù)，但不能使用成輸入?yún)?shù)。也就是說，游標(biāo)可以作為結(jié)果返回，但卻不能傳輸?shù)竭^程中去。當(dāng)游標(biāo)被用作參數(shù)時(shí)，需要限定其為output和varying。varying關(guān)鍵字指出該結(jié)果集要用來支持輸出參數(shù)。這樣就提供了將結(jié)果集返回到調(diào)用過程的能力。
例如：
use pubs
go
create procedure gettitlecount @count_cursor cursor varying output
as
set @count_cursor = cursor
for
select au_id,count(*)
from titleauthors
group by au_id
open @count_cursor
go
3、 通過return參數(shù)返回狀態(tài)
這是一種從存儲過程返回錯(cuò)誤碼的方法。存儲過程總是返回一個(gè)狀態(tài)值，用戶也可以使用return語句返回自己的狀態(tài)。
例如：
use pubs
go
create procedure ap_setreturnstatus
as
declare @count integer
select @count = count(*) from authors
if @count = 0
return(1)
else
return (0)
go
例如檢索出返回的狀態(tài)：
use pubs
go
create procedure ap_getreturnstatus
as
declare @status integer
execute @status = ap_setreturnstatus
if @status = 1
print “no rows found”
else
print “successful”
go
在存儲過程中進(jìn)行錯(cuò)誤處理
如同其它程序一樣，在存儲過程中進(jìn)行錯(cuò)誤處理是非常重要的。系統(tǒng)變更@@error在執(zhí)行每一個(gè)transact sql語句之后都會得到一個(gè)值。對于成功的執(zhí)行，@@error的值為0，如果出現(xiàn)錯(cuò)誤，則@@error中將包含錯(cuò)誤信息。@@error系統(tǒng)變量對存儲過程的錯(cuò)誤處理是非常重要的。
注意：為了防止錯(cuò)誤，@@error所能設(shè)置的值在sysmessages表的“error”中反映了出來。
在存儲過程中的錯(cuò)誤有兩種類型：
1、 數(shù)據(jù)庫相關(guān)的錯(cuò)誤
這些錯(cuò)誤是由數(shù)據(jù)庫的不一致性引起的，系統(tǒng)使用非0的@@error值表示特定的數(shù)據(jù)庫問題。在transact sql執(zhí)行之后，可以通過@@error獲得所出現(xiàn)的錯(cuò)誤。如果發(fā)現(xiàn)@@error不為0，則必須采取必要的行動，大多數(shù)情況下，存儲將不再繼續(xù)進(jìn)行處理而返回。下面的示例展示了典型的獲取數(shù)據(jù)庫錯(cuò)誤的方法。該過程將錯(cuò)誤代碼放置到輸出變量中，這樣，調(diào)用程序就能夠訪問到。
use pubs
go
create procedure ap_trapdatabaseerror @return_code integer output
as
update authors set au_iname = “jackson”
where au_iname = “smith”
if @@error <> 0
begin
select @return_code = @@error
return
end
else
@return_code = 0
go
2、 業(yè)務(wù)邏輯錯(cuò)誤
這些錯(cuò)誤是由于違反了業(yè)務(wù)規(guī)則而引起的。要獲取這些錯(cuò)誤，首先需要定義業(yè)務(wù)規(guī)則，基于這些規(guī)則，需要在存儲過程中增加必要的錯(cuò)誤檢測代碼。人們經(jīng)常使用raiserror語句通報(bào)這些錯(cuò)誤。raiserror提供了返回用戶定義錯(cuò)誤及將@@error變量設(shè)置成用戶定義錯(cuò)誤號的能力。錯(cuò)誤消息可以被動態(tài)地建立，或者基于錯(cuò)誤號從“sysmessages”表中檢索到。一旦出現(xiàn)了錯(cuò)誤，錯(cuò)誤就會以一種服務(wù)器錯(cuò)誤消息的方式返回到客戶機(jī)。下面是raiserror命令的語法：
raiserror (msg_id | msg_str, severity, state
[, argument ][,…n]])
[with options]
msg_id指明用戶定義消息的id，該消息存儲在“sysmessages”系統(tǒng)表中。
msg_str用于動態(tài)創(chuàng)建消息的消息字符串。這與c語言中的“printf”非常相似。
severity定義用戶賦值的錯(cuò)誤消息嚴(yán)重程度。
state是從1到127的任意整數(shù)值，它表示錯(cuò)誤的調(diào)用狀態(tài)信息。負(fù)數(shù)的state值將缺省為1。
options指明錯(cuò)誤的定制選項(xiàng)。options的有效值如下：
1） log。
將錯(cuò)誤記錄到服務(wù)器錯(cuò)誤日志和nt事件日志中。該選項(xiàng)需要消息帶有從19到25的嚴(yán)重程度。而只有系統(tǒng)管理員才能發(fā)出這種消息。
2） nowait。
將消息立即發(fā)送到客戶端服務(wù)器。
3） seterror。
不管其嚴(yán)重級別如何，將@@error的值設(shè)置為msg_id或5000。
遠(yuǎn)程過程調(diào)用
sql server提供了調(diào)用駐留在不同服務(wù)器上的存儲過程的能力。調(diào)用這樣的存儲過程稱謂遠(yuǎn)程存儲過程調(diào)用。為了使得調(diào)用能從一個(gè)sql server轉(zhuǎn)移到另一個(gè)服務(wù)器，兩個(gè)服務(wù)器應(yīng)該相互定義成對方的有效遠(yuǎn)程服務(wù)器。
設(shè)置遠(yuǎn)程服務(wù)器的配置：
· 擴(kuò)展某個(gè)服務(wù)器的組。
· 右擊該服務(wù)器并點(diǎn)擊“properties”。
· 設(shè)置選項(xiàng)“allow other sql servers to connect remotely to this sql server via rpc”。
· 設(shè)置“query time out”選項(xiàng)的值，該值指定從一個(gè)查詢處理返回所能等待的秒數(shù)。缺省值為0，表示允許無限的等待時(shí)間。
· 設(shè)置完成配置選項(xiàng)之后，點(diǎn)擊“ok”。
· 重新啟動服務(wù)器之后，修改將會生效。
· 在另一臺遠(yuǎn)程服務(wù)器上重復(fù)相同的步驟。
調(diào)用遠(yuǎn)程存儲過程需要指明服務(wù)器的名稱，后帶數(shù)據(jù)庫的名稱和擁有者的名稱。下面是在不同的服務(wù)器（server2）上調(diào)用一個(gè)存儲過程的示例。
exec server2.pubs.dbo.myproc
豆豆的后話：
這里只是粗淺的介紹了sql server常用的知識，對象也是基于sql server數(shù)據(jù)庫編寫應(yīng)用程序的編程人員，而非數(shù)據(jù)庫管理者。但對于應(yīng)用程序編程者，了解數(shù)據(jù)庫的管理也是非常有用的。建議在以后的時(shí)間自行去了解數(shù)據(jù)庫的管理，這對于優(yōu)化程序也是相當(dāng)有用的。
