第8章 存储过程

第8章存储过程学习目标1、什么是存储过程2、了解存储过程的优点和分类3、掌握创建、执行存储过程的方法4、掌握存储过程的修改和删除存储过程存储过程存放在服务器端数据库中，是经编译过的能完成特定功能的T-SQL语句的集合，是作为一个单元来处理的。在存储过程中可以对任何数据及对象进行修改，包括新建或删除表、修改数据库设置等。存储过程在第一次执行时进行语法检查和编译，执行后它的执行计划就驻留在高速缓存中，用于后续调用。存储过程可以接收和输出参数、返回执行存储过程的状态值，还可以嵌套调用。用户可以像使用函数一样重复调用这些存储过程，实现它所定义的操作。项存入sysobjects

和syscomments

表编译过的执行计划

放入过程缓存编译优化创建执行

(初次或

重新编译)解析存储过程的初始处理存储过程的优点实现模块化编程，增强代码的重用性和共享性加快程序的运行速度，改善系统性能可以减少网络流量提高数据库的安全性存储过程的分类分类特点用户自定义存储过程Transact-SQLTransact-SQL存储过程是指保存的Transact-SQL语句集合，可以接受和返回用户提供的参数。CLRCLR存储过程是指对.NET框架公共语言运行时(CLR)方法的引用系统存储过程存储在master数据库中，可以在其他数据库中任意进行调用，由前缀“sp_”标识。扩展存储过程扩展存储过程以在SQLServer环境外执行的动态链接库（DLL）来实现。由前缀“xp_”标识。扩展存储过程直接在SQLServer的实例的地址空间中运行，可以使用SQLServer扩展存储过程API完成编程。8.2创建存储过程8.2.1使用SSMS创建存储过程8.2.2使用Transact-SQL创建存储过程create

proc[edure][所有者.]存储过程名[;整数][{@参数数据类型}[varying][=默认值][output][,...n][with{recompile|encryption|recompile,encryption|execute_as_clause}][forreplication]

as{sql语句[,...n]|externalnameassembly_name.class_name.method_name}8.3执行存储过程存储过程创建完后，要产生效果，必须要执行存储过程，可以使用execute语句来执行这个存储过程，也可以使用SSMS执行存储过程。8.3.1使用execute语句执行存储过程8.3.2使用SSMS执行存储过程8.3.1使用execute语句执行存储过程1．通过存储过程自身执行存储过程语法格式如下：[exec[ute]]

{[@整型变量=]存储过程名[;分组标识号]|@存储过程变量}[[@参数=]{参量值|@变量[output]|[default]}][,...n][withrecomplile]【例8.1】本例创建一个简单的无参数的存储过程：在Sales数据库中，创建存储过程proc_Employees，查询采购部的员工信息。usesalesgocreateprocproc_employeesasselect*fromemployeeswhere部门='采购部'go--执行存储过程execproc_employees【例8.2】创建一个带有输入参数的存储过程proc_goods，查询指定员工所进商品信息。usesalesgocreateprocproc_goods@员工编号char(6)='1001'asselect商品编号,商品名称,生产厂商,进货价,零售价,数量,进货时间

fromgoodswhere进货员工编号=@员工编号go--执行存储过程，查询1001号员工所进的商品的信息execproc_goods@员工编号=default--或execproc_goods@员工编号='1001'【例8.3】创建一个带有输入和输出参数的存储过程proc_GNO，查询指定厂商指定名称的商品所对应的商品编号。usesalesgocreateprocproc_gno@商品名称varchar(20),@生产厂商varchar(30),@商品编号intoutputasselect@商品编号=商品编号

fromgoodswhere商品名称=@商品名称and生产厂商=@生产厂商go--执行存储过程，查询惠普公司打印机商品编号declare@商品编号int

execproc_gno'打印机','惠普公司',@商品编号outputprint'该商品编号为：'+cast(@商品编号aschar(6))alterprocProc_sellQry@startTimedatetime,@endTimedatetime,@sumTotalfloatoutput--输出参数asselect@sumTotal=sum(sell.数量*零售价)--总销售额，对输出参数fromgoods,sellwheregoods.商品编号=sell.商品编号and售出时间<=@endTimeand售出时间>=@startTimedeclare@sumTotalfloatexecProc_sellQry'2005-01-01','2005-06-30',@sumTotaloutputprint@sumTotal【例8.4】创建带有参数和返回值的存储过程：在Sales数据库中创建存储过程ProcSum

ByGoods。查询指定厂商指定名称的商品在某年某月的总销售量。usesalesgocreateprocprocsumbygoods@goodnamevarchar(20),@corpvarchar(30),@yearint,@monthint,@sumintoutputas--声明和初始化一个局部变量，用于保存系统函数@@error的返回值declare@errorsave

intcreateprocproc_qryScore@s_numberchar(8),@c_numberchar(4),--输入参数————接收数据，作条件的一部分@scorenumeric(5,1)output,@creditintoutput--输出参数————得到结果，对输出参数作赋值，--执行时再显示输出参数的结果asselect@score=score,@credit=creditfromt_score,t_coursewheret_score.c_number=t_course.c_numberands_number=@s_numberandt_score.c_number=@c_numberset@errorsave=0--统计指定厂商指定名称的商品在指定年份月份总的销售量select@sum=sum(sell.数量)fromselljoingoodsonsell.商品编号=goods.商品编号where商品名称=@goodnameand生产厂商=@corpandyear(售出时间)=@yearandmonth(售出时间)=@monthif(@@error<>0)set@errorsave=@@errorreturn@errorsavegogo--执行存储过程，查询惠普公司2004年10月的打印机销售总量declare@retint,@sum

int

exec@ret=procsumbygoods'打印机','惠普公司',2004,10,@sumoutputprint'该存储过程执行结果如下：'print'返回值='+cast(@retaschar(1))print'总销售量='+cast(@sumaschar(4))2．执行字符串execute语句的主要用途是执行存储过程。此外，我们还可以将T-SQL语句放在字符串变量中，然后使用execute语句来执行该字符串，语法格式如下：exec[ute]({@字符串变量}|[n]'tsql字符串')[+...n])【例8.7】本例是用EXECUTE语句执行字符串的示例。usesalesgodeclare@sqlstrvarchar(40)set@sqlstr='select*fromemployeesorderby姓名'exec(@sqlstr)8.3.2使用SSMS执行存储过程在SSMS中执行存储过程proc_goods8.4修改和删除存