SQL Server 7.0 入門(八)---預存程序中返回結果

來源:互聯網
上載者:User
server|預存程序 預存程序中返回結果
    從預存程序中返回結果有三種方式:
1、 返回結果集
這是用戶端應用程式返回結果的最通用的方法。結果集是通過使用SELECT語句選擇資料產生的。結果集可以從永久表、暫存資料表或局部變數中產生。將結果返回到另一個預存程序不是一種有效方法。預存程序不能訪問另一個預存程序建立的結果集。
例如從永久表中返回結果集:
USE pubs
GO
CREATE PROCEDURE ap_CreateResultFromPermtable
AS
SELECT au_iname FROM authors
GO
例如從局部變數中建立結果集:
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、 設定OUTPUT參數的值
輸出參數經常用來從預存程序中檢索出結果。如果某個參數在傳輸到預存程序中時被定義成OUTPUT,則對該參數的任何修改在退出儲存之後仍然有效。
例如:
USE pubs
GO
CREATE PROCEDURE ap_SetOutputVar @count integer OUTPUT
AS
SELECT @count = count(*) FROM authors
GO
從輸出參數中檢索出值:
USE pubs
GO
CREATE PROCEDURE ap_GetOutputVar
AS
DECLARE @num integer
EXECUTE ap_SetOutputVar @num OUTPUT
PRINT “the count is”+convert(char,@num)
GO
· 將遊標使用成OUTPUT參數。遊標可以使用OUTPUT(輸出)參數,但不能使用成輸入參數。也就是說,遊標可以作為結果返回,但卻不能傳輸到過程中去。當遊標被用作參數時,需要限定其為OUTPUT和VARYING。VARYING關鍵字指出該結果集要用來支援輸出參數。這樣就提供了將結果集返回到調用過程的能力。
例如:
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參數返回狀態
這是一種從預存程序返回錯誤碼的方法。預存程序總是返回一個狀態值,使用者也可以使用RETURN語句返回自己的狀態。
例如:
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
例如檢索出返回的狀態:
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

在預存程序中進行錯誤處理
    如同其它程式一樣,在預存程序中進行錯誤處理是非常重要的。系統變更@@error在執行每一個Transact SQL語句之後都會得到一個值。對於成功的執行,@@error的值為0,如果出現錯誤,則@@error中將包含錯誤資訊。@@error系統變數對預存程序的錯誤處理是非常重要的。
    注意:為了防止錯誤,@@error所能設定的值在sysmessages表的“error”中反映了出來。
在預存程序中的錯誤有兩種類型:
1、  資料庫相關的錯誤
這些錯誤是由資料庫的不一致性引起的,系統使用非0的@@error值表示特定的資料庫問題。在Transact SQL執行之後,可以通過@@error獲得所出現的錯誤。如果發現@@error不為0,則必須採取必要的行動,大多數情況下,儲存將不再繼續進行處理而返回。下面的樣本展示了典型的擷取資料庫錯誤的方法。該過程將錯誤碼放置到輸出變數中,這樣,調用程式就能夠訪問到。
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、  商務邏輯錯誤
這些錯誤是由於違反了商務規則而引起的。要擷取這些錯誤,首先需要定義商務規則,基於這些規則,需要在預存程序中增加必要的錯誤偵測代碼。人們經常使用RAISERROR語句通報這些錯誤。RAISERROR提供了返回使用者定義錯誤及將@@error變數設定成使用者定義錯誤號碼的能力。錯誤訊息可以被動態地建立,或者基於錯誤號碼從“sysmessages”表中檢索到。一旦出現了錯誤,錯誤就會以一種伺服器錯誤訊息的方式返回到客戶機。下面是RAISERROR命令的文法:
RAISERROR (msg_id | msg_str, severity, state
[, argument ][,…n]])
[WITH options]
Msg_id指明使用者定義訊息的id,該訊息儲存在“sysmessages”系統資料表中。
Msg_str用於動態建立訊息的訊息字串。這與C語言中的“printf”非常相似。
Severity定義使用者賦值的錯誤訊息嚴重程度。
State是從1到127的任意整數值,它表示錯誤的調用狀態資訊。負數的state值將預設為1。
OPTIONS指明錯誤的定製選項。OPTIONS的有效值如下:
1) LOG。
將錯誤記錄到伺服器錯誤日誌和NT事件記錄中。該選項需要訊息帶有從19到25的嚴重程度。而只有系統管理員才能發出這種訊息。
2) NOWAIT。
將訊息立即發送到用戶端伺服器。
3) SETERROR。
不管其嚴重層級如何,將@@error的值設定為msg_id或5000。

遠端程序呼叫
    SQL Server提供了調用駐留在不同伺服器上的預存程序的能力。調用這樣的預存程序稱謂遠端預存程序調用。為了使得調用能從一個SQL Server轉移到另一個伺服器,兩個伺服器應該相互定義成對方的有效遠程伺服器。
    設定遠程伺服器的配置:
    · 擴充某個伺服器的組。
    · 右擊該伺服器並點擊“Properties”。
    · 設定選項“Allow other SQL Servers to connect remotely to this SQL server via RPC”。
    · 設定“Query time out”選項的值,該值指定從一個查詢處理返回所能等待的秒數。預設值為0,表示允許無限的等待時間。
    · 設定完成配置選項之後,點擊“OK”。
    · 重新啟動伺服器之後,修改將會生效。
    · 在另一台遠程伺服器上重複相同的步驟。
    調用遠端預存程序需要指明伺服器的名稱,後帶資料庫的名稱和擁有者的名稱。下面是在不同的伺服器(Server2)上調用一個預存程序的樣本。
Exec server2.pubs.dbo.myproc

豆豆的後話:
    這裡只是粗淺的介紹了SQL Server常用的知識,對象也是基於SQL Server資料庫編寫應用程式的編程人員,而非資料庫管理者。但對於應用程式編程者,瞭解資料庫的管理也是非常有用的。建議在以後的時間自行去瞭解資料庫的管理,這對於最佳化程式也是相當有用的。


相關文章

Beyond APAC's No.1 Cloud

19.6% IaaS Market Share in Asia Pacific - Gartner IT Service report, 2018

Learn more >

Apsara Conference 2019

The Rise of Data Intelligence, September 25th - 27th, Hangzhou, China

Learn more >

Alibaba Cloud Free Trial

Learn and experience the power of Alibaba Cloud with a free trial worth $300-1200 USD

Learn more >

聯繫我們

該頁面正文內容均來源於網絡整理,並不代表阿里雲官方的觀點,該頁面所提到的產品和服務也與阿里云無關,如果該頁面內容對您造成了困擾,歡迎寫郵件給我們,收到郵件我們將在5個工作日內處理。

如果您發現本社區中有涉嫌抄襲的內容,歡迎發送郵件至: info-contact@alibabacloud.com 進行舉報並提供相關證據,工作人員會在 5 個工作天內聯絡您,一經查實,本站將立刻刪除涉嫌侵權內容。