存储过程的特点
Sybase的存储过程是集中存储在SQL Server中的预先定义且已经编译好的事务存储过程由SQL语句和流程控制语句组成它的功能包括:接受参数;调用另一过程;返回一个状态值给调用过程或批处理指示调用成功或失败;返回若干个参数值给调用过程或批处理为调用者提供动态结果;在远程SQL Server中运行等
存储过程的性能特点如下:
·存储过程是预编译过的这就意味着它与普通的SQL语句或批处理的SQL语句不同当首次运行一个存储过程时SQL Server的查询处理器对其进行分析在排除了语法错误之后形成存储在系统中的可执行方案由于查询处理的大部分工作已经完成所以存储过程执行速度很快
·存储过程和待处理的数据都放在同一台运行SQL Server的计算机上使用存储过程查询当地的数据效率自然很高
·存储过程一般多由Client端通过存储过程的名字进行调用即跨网传送的只是存储过程的名字及少量的参数(如果有的话)而不是构成存储过程的许多SQL语句因此可以减少网络传输量加快系统响应速度
·存储过程还有着如同C语言子函数那样的被调用和返回值的方便特性
所以存储过程大大增强了SQL语言的功能效率和灵活性掌握和应用好存储过程对进一步发挥Sybase数据库系统的强大功能有着重要的意义
存储过程的语法规则
建立存储过程的语法规则为:
CREATEPROCedure[owner]procedurename[;number]
[[(]@parameter_namedatatype[=default][OUTput]
[@parameter_namedatatype[=default][OUTput]][)]]
[WITHRECOMPILE]
ASSQL_statements
使用存储过程的语法规则为:
[EXECute][@returnstatus=]
[[[server]database]owner]procedurename[;number]
[[@parameter_name=]value|[@parameter_name=]@varialbe[OUTput]
[[@parameter_name=]value|[@parameter_name=]@variable[OUTput]]]
[WITHRECOMPILE]
[page]下面简要介绍这两个命令的常用选项以及建立和使用存储过程的要点关于选项的更为详细的说明请参考有关手册
·[[[server]database]owner]procedure_name:存储过程的名字
·@parameter_name datatype[=default][OUTput]:形式参数(形参)的名称类型df ault是赋予的缺省值(可选)OUTput指定本参数为输出参数(可选)形参是存储过程中的自变量可以有多个名字必须以@打头最长个字符
·SQL_statements:定义存储过程功能的SQL语句
·@return_status:接受存储过程返回状态值的变量
·[@parameter_name=]value:实际参数(实参)@parameter_name为实参的名称(可选)如果某个实参以@parameter_name=value提供那么随后的实参也都要采用这一形式提供
·[@parameter_name=]@varialbe[OUTput]:将变量@varialbe中的值作为实参传递给形参@parameter_name(可选)如果变量@varialbe是用来接受返回的参数值则选项OUTput不可缺少
存储过程的建立和使用我们将通过几个例子进行介绍
假设有一个用下述语句生成的技能工资表RSLSGZJiNeng:
create table RS_LS_GZ_JiNeng /*技能工资表*/
(GeRen_id char() /*个人代码 */
RiQi smalldatetime /*执行日期 */
YuanYin_id char() null /*变动原因代码 */
JinE smallmoney) /*技能工资金额 */
该表存储着某单位员工多年来技能工资的历史档案
例如果要查询全体员工的技能工资变动历史则可先建立一个存储过程pRsGzJiNegAll:
createprocedurep_RsGz_JiNeng_Allas
select*
fromRS_LS_GZ_JiNeng
orderbyGeRenidRiQi
然后用批处理语句调用存储过程p_RsGz_JiNeng_All进行查询:
execute p_RsGz_JiNeng_All
本例只显示查询到的数据无输入输出参量是最简单的一个存储过程
例如果要查询某人技能工资的变动历史可建立另一个存储过程p_RsGz_JiNeng:
createprocedurep_RsGz_JiNeng@c_GeRenIdchar()
as
select*fromRS_LS_GZ_JiNeng
whereGeRen_id=@c_GeRenId
orderbyRiQi
之后用批处理语句调用存储过程p_Rs_Gz_JiNeng进行查询:
declare @GeRenId char()
select @GeRenId= /*设要查询员工的个人代码为 */
execute p_RsGz_JeNeng @c_GeRenId=@GeRenId
存储过程p_RsGz_JiNeng中定义了一个形参@c_GeRenId是字符型变量在调用该过程的批处理中既可以用具体的值也可以用变量作为实参用变量作实参(如本例)时必须用del are语句加以说明值得注意的是在批处理的调用过程语句中@c_GeRenId=@GeRenId中的@ c_GeRenId是存储过程p_RsGz_JiNeng中的形参名不是批处理中的变量所以不能将它列入d eclare语句的变量单中
例如果要计算当月工资就必须从工资历史中查出员工距离当前最近的一次技能工资变动的结果:
createprocedurep_RsGz_JiNeng_Slt
(@c_GeRenIdchar()@sm_JinEsmallmoneyoutput)
as
select@sm_JinE=JinE
fromRS_LS_GZ_JiNeng
whereRiQi=(selectmax(RiQi)
fromRS_LS_GZ_JiNeng
where GeRenid=@cGeRenId)/*找出历史记录中距离当前最近的日期*/
调用存储过程p_RsGz_JiNeng_Slt进行查询:
declare@GeRenIdchar()@JinEsmallmoney
select@GeRenid=/*设要查询员工的个人代码为*/
select@JinE=
executep_RsGz_JiNeng_slt@c_GeRenId=@GeRenId@sm_JinE=@JinEoutput
这里变量 @JinE用来存储过程形参@sm_JinE传回的金额在调用过程语句中@sm_JiE = @JinE output中的output不可省略否则变量@JinE将得不到形参传回的数值而始终为零(等于初值)
例查到了个人代码为员工的技能工资就显示其历史纪录查不到则显示一条出错信息
createprocedurep_RsGz_JiNeng_Rtn
@c_GeRenIdchar()
as
declare@ErrCodesmallint
select@ErrCode=
ifexists(select*fromRSLSGZJiNeng
whereGeRenid=@cGeRenId)
begin
select*
fromRS_LS_GZ_JiNeng
whrerGeRen_id=@c_GeRenId
orderbyRiQi
return@ErrCodE
end
eslE
begin
select@ErrCode=
return@ErrCodE
end
调用存储过程p_RsGz_JiNeng_Rtn:
declare@GeRenIdchar()@RtnCodesmallint
select@GeRenId=
select@RtnCode=
execute@RtnCode=p_RsGz_JiNeng_Rtn@c_GeRenId=@GeRenId
if@RtnCode=
printNothisone!
存储过程p_RsGz_JiNeng_Rtn向调用者返回一个存储在变量@ErrCode里的值这个值被称为状态值它向调用者反映存储过程执行的成败状态在本例中如果查不到指定员工技能工资的任何记录时就认为查无此人返回出错状态值否则返回成功状态值
调用过程的批处理语句使用变量@RtnCode存储返回的状态值一旦检出存储过程p_RsG_ JiNeng_Rtn返回了错误标志(@RtnCode=)就显示一条信息No this one![page]
小结
上述四个例子简要介绍了存储过程常用的几种形式从中我们已经可以领略到它的编程特色以及使用上的灵活性和方便性
虽然上述例子在调用存储过程时都是用SQL的批处理语句实现的但并不意味着这是唯一的方法例如在存储过程中调用存储过程(即所谓过程嵌套)的现象就很常见另外在其它Sybase数据库开发系统 (如PowerBuilder)的 script语句中调用Sybase的存储过程也非常普遍
