存儲(chǔ)過程的特點(diǎn)
Sybase的存儲(chǔ)過程是集中存儲(chǔ)在SQL Server中的預(yù)先定義且已經(jīng)編譯好的事務(wù)。存儲(chǔ)過程由SQL語(yǔ)句和流程控制語(yǔ)句組成。它的功能包括:接受參數(shù);調(diào)用另一過程;返回一個(gè)狀態(tài)值給調(diào)用過程或批處理,指示調(diào)用成功或失敗;返回若干個(gè)參數(shù)值給調(diào)用過程或批處理,為調(diào)用者提供動(dòng)態(tài)結(jié)果;在遠(yuǎn)程SQL Server中運(yùn)行等。
存儲(chǔ)過程的性能特點(diǎn)如下:
·存儲(chǔ)過程是預(yù)編譯過的,這就意味著它與普通的SQL語(yǔ)句或批處理的SQL語(yǔ)句不同,當(dāng)首次運(yùn)行一個(gè)存儲(chǔ)過程時(shí),SQL Server的查詢處理器對(duì)其進(jìn)行分析,在排除了語(yǔ)法錯(cuò)誤之后形成存儲(chǔ)在系統(tǒng)中的可執(zhí)行方案。由于查詢處理的大部分工作已經(jīng)完成,所以存儲(chǔ)過程執(zhí)行速度很快。
·存儲(chǔ)過程和待處理的數(shù)據(jù)都放在同一臺(tái)運(yùn)行SQL Server的計(jì)算機(jī)上,使用存儲(chǔ)過程查詢當(dāng)?shù)氐臄?shù)據(jù),效率自然很高。
·存儲(chǔ)過程一般多由Client端通過存儲(chǔ)過程的名字進(jìn)行調(diào)用,即跨網(wǎng)傳送的只是存儲(chǔ)過程的名字及少量的參數(shù)(如果有的話),而不是構(gòu)成存儲(chǔ)過程的許多SQL語(yǔ)句,因此可以減少網(wǎng)絡(luò)傳輸量,加快系統(tǒng)響應(yīng)速度。
·存儲(chǔ)過程還有著如同C語(yǔ)言子函數(shù)那樣的被調(diào)用和返回值的方便特性。
所以,存儲(chǔ)過程大大增強(qiáng)了SQL語(yǔ)言的功能、效率和靈活性。掌握和應(yīng)用好存儲(chǔ)過程,對(duì)進(jìn)一步發(fā)揮Sybase數(shù)據(jù)庫(kù)系統(tǒng)的強(qiáng)大功能有著重要的意義。
存儲(chǔ)過程的語(yǔ)法規(guī)則
建立存儲(chǔ)過程的語(yǔ)法規(guī)則為:
CREATE PROCedure[owner.]procedurename[;number]
[[(]@parameter_name datatype[=default][OUTput]
[,@parameter_name datatype[=default][OUTput]]...[)]]
[WITH RECOMPILE]
AS SQL_statements
使用存儲(chǔ)過程的語(yǔ)法規(guī)則為:
[EXECute][@return-status=]
[[[server.]database.]owner.]procedurename[;number]
[[@parameter_name=]value|[@parameter_name=]@varialbe[OUTput]
[,[@parameter_name=]value|[@parameter_name=]@variable[OUTput]...]]
[WITH RECOMPILE]
下面簡(jiǎn)要介紹這兩個(gè)命令的常用選項(xiàng)以及建立和使用存儲(chǔ)過程的要點(diǎn),關(guān)于選項(xiàng)的更為詳細(xì)的說明請(qǐng)參考有關(guān)手冊(cè)。
·[[[server.]database.]owner.]procedure_name:存儲(chǔ)過程的名字。
·@parameter_name datatype[=default][OUTput]:形式參數(shù)(形參)的名稱、類型。df ault是賦予的缺省值(可選),OUTput指定本參數(shù)為輸出參數(shù)(可選)。形參是存儲(chǔ)過程中的自變量,可以有多個(gè),名字必須以@打頭,最長(zhǎng)30個(gè)字符。
·SQL_statements:定義存儲(chǔ)過程功能的SQL語(yǔ)句。
·@return_status:接受存儲(chǔ)過程返回狀態(tài)值的變量。
·[@parameter_name=]value:實(shí)際參數(shù)(實(shí)參),@parameter_name為實(shí)參的名稱(可選)。如果某個(gè)實(shí)參以@parameter_name=value提供,那么隨后的實(shí)參也都要采用這一形式提供。
·[@parameter_name=]@varialbe[OUTput]:將變量@varialbe中的值作為實(shí)參傳遞給形參@parameter_name(可選),如果變量@varialbe是用來接受返回的參數(shù)值,則選項(xiàng)OUTput不可缺少。
存儲(chǔ)過程的建立和使用,我們將通過幾個(gè)例子進(jìn)行介紹。
假設(shè)有一個(gè)用下述語(yǔ)句生成的技能工資表RS-LS-GZ-JiNeng:
create table RS_LS_GZ_JiNeng /*技能工資表*/
(GeRen_id char(4), /*個(gè)人代碼 */
RiQi smalldatetime, /*執(zhí)行日期 */
YuanYin_id char(1) null, /*變動(dòng)原因代碼 */
JinE smallmoney) /*技能工資金額 */
該表存儲(chǔ)著某單位員工多年來技能工資的歷史檔案。
例1.如果要查詢?nèi)w員工的技能工資變動(dòng)歷史,則可先建立一個(gè)存儲(chǔ)過程p-RsGz-JiNeg-All:
create procedure p_RsGz_JiNeng_All as
select *
from RS_LS_GZ_JiNeng
order by GeRenid,RiQi
然后用批處理語(yǔ)句調(diào)用存儲(chǔ)過程p_RsGz_JiNeng_All進(jìn)行查詢:
execute p_RsGz_JiNeng_All
本例只顯示查詢到的數(shù)據(jù),無輸入、輸出參量,是最簡(jiǎn)單的一個(gè)存儲(chǔ)過程。
例2.如果要查詢某人技能工資的變動(dòng)歷史,可建立另一個(gè)存儲(chǔ)過程p_RsGz_JiNeng:
create procedure p_RsGz_JiNeng @c_GeRenId char(4)
as
select *from RS_LS_GZ_JiNeng
where GeRen_id=@c_GeRenId
order by RiQi
之后用批處理語(yǔ)句調(diào)用存儲(chǔ)過程p_Rs_Gz_JiNeng進(jìn)行查詢:
declare @GeRenId char(4)
select @GeRenId="0135" /*設(shè)要查詢員工的個(gè)人代碼為"0135" */
execute p_RsGz_JeNeng @c_GeRenId=@GeRenId
存儲(chǔ)過程p_RsGz_JiNeng中定義了一個(gè)形參@c_GeRenId,是字符型變量。在調(diào)用該過程的批處理中,既可以用具體的值也可以用變量作為實(shí)參。用變量作實(shí)參(如本例)時(shí),必須用del are語(yǔ)句加以說明。值得注意的是,在批處理的調(diào)用過程語(yǔ)句中,@c_GeRenId=@GeRenId中的@ c_GeRenId是存儲(chǔ)過程p_RsGz_JiNeng中的形參名,不是批處理中的變量,所以不能將它列入d eclare語(yǔ)句的變量單中。
例3.如果要計(jì)算當(dāng)月工資,就必須從工資歷史中查出員工距離當(dāng)前最近的一次技能工資變動(dòng)的結(jié)果:
create procedure p_RsGz_JiNeng_Slt
(@c_GeRenId char(4),@sm_JinE smallmoney output)
as
select @sm_JinE=JinE
from RS_LS_GZ_JiNeng
where RiQi=(select max(RiQi)
from RS_LS_GZ_JiNeng
where GeRenid=@c-GeRenId)/*找出歷史記錄中距離當(dāng)前最近的日期*/
調(diào)用存儲(chǔ)過程p_RsGz_JiNeng_Slt進(jìn)行查詢:
declare @GeRenId char(4),@JinE smallmoney
select @GeRenid="0135"/*設(shè)要查詢員工的個(gè)人代碼為"0135"*/
select @JinE=0
execute p_RsGz_JiNeng_slt @c_GeRenId=@GeRenId,@sm_JinE=@ JinE output
這里,變量 @JinE用來存儲(chǔ)過程形參@sm_JinE傳回的金額。在調(diào)用過程語(yǔ)句中,@sm_JiE = @JinE output中的output不可省略。否則,變量@JinE將得不到形參傳回的數(shù)值而始終為零(等于初值)。
例4.查到了個(gè)人代碼為"0135"員工的技能工資就顯示其歷史紀(jì)錄,查不到則顯示一條出錯(cuò)信息。
create procedure p_RsGz_JiNeng_Rtn
@c_GeRenId char(4)
as
declare @ErrCode smallint
select @ErrCode=0
if exists(select* from RS-LS-GZ-JiNeng
where GeRenid=@c-GeRenId)
begin
select *
from RS_LS_GZ_JiNeng
whrer GeRen_id=@c_GeRenId
order by RiQi
return @ErrCode
end
esle
begin
select @ErrCode=1
return @ErrCode
end
調(diào)用存儲(chǔ)過程p_RsGz_JiNeng_Rtn:
declare @GeRenId char(4),@RtnCode smallint
select @GeRenId="0135"
select @RtnCode=0
execute @RtnCode=p_RsGz_JiNeng_Rtn @c_GeRenId=@GeRenId
if @RtnCode=1
print"No this one!"
存儲(chǔ)過程p_RsGz_JiNeng_Rtn向調(diào)用者返回一個(gè)存儲(chǔ)在變量@ErrCode里的值,這個(gè)值被稱為狀態(tài)值,它向調(diào)用者反映存儲(chǔ)過程執(zhí)行的成敗狀態(tài)。在本例中,如果查不到指定員工技能工資的任何記錄時(shí),就認(rèn)為"查無此人",返回出錯(cuò)狀態(tài)值1。否則,返回成功狀態(tài)值0。
調(diào)用過程的批處理語(yǔ)句使用變量@RtnCode存儲(chǔ)返回的狀態(tài)值,一旦檢出存儲(chǔ)過程p_RsG_ JiNeng_Rtn返回了錯(cuò)誤標(biāo)志(@RtnCode=1),就顯示一條信息"No this one!"。
小結(jié)
上述四個(gè)例子簡(jiǎn)要介紹了存儲(chǔ)過程常用的幾種形式,從中我們已經(jīng)可以領(lǐng)略到它的編程特色以及使用上的靈活性和方便性。
雖然上述例子在調(diào)用存儲(chǔ)過程時(shí)都是用SQL的批處理語(yǔ)句實(shí)現(xiàn)的,但并不意味著這是唯一的方法。例如在存儲(chǔ)過程中調(diào)用存儲(chǔ)過程(即所謂過程嵌套)的現(xiàn)象就很常見。另外,在其它Sybase數(shù)據(jù)庫(kù)開發(fā)系統(tǒng) (如PowerBuilder)的 script語(yǔ)句中調(diào)用Sybase的存儲(chǔ)過程也非常普遍。