《SQL Server 数据库案例教程》课件第11章存储过程

课程内容回顾事务的概念事务的特性:原子性、一致性、隔离性和持久性(ACID)事务可以分为如下类型：显式事务隐式事务自动提交事务索引的好处和分类聚集索引和非聚集索引。视图的好处本章目标了解存储过程的优点掌握常用的系统存储过程掌握如何创建存储过程掌握如何调用存储过程存储过程介绍存储和执行T-SQL程序的方法有两种:一种是在本地存储程序，然后创建应用程序来将命令发送到SQLServer并对结果进行处理。另一种方法是将程序存储为SQLServer中的存储过程，然后创建应用程序来执行存储过程并对结果进行处理。存储过程用户定义的存储过程:数据库开发人员或管理员编写的用来运行经常执行的管理任务，或者应用复杂的业务规则。包含数据操纵或数据检索语句

系统存储过程:SQLServer提供了一些预编译的存储过程，用以管理SQLServer和显示有关数据库和用户的信息

存储过程中的语句存储过程----------------单个SELECT语句SELECT语句块可以包含SELECT语句与逻辑控制语句存储过程中的语句SQLServer中的存储过程与其他语言中的过程或函数类似，它们的共同特征是:它们都接收输入参数，并向调用过程或语句返回值。它们都包含在数据库中执行操作或调用其他存储过程的编程语句。它们都向调用过程返回状态值，指示执行过程是否成功存储过程的优点存储过程的优点:允许模块化程序设计只需创建过程一次并将其存储在数据库中，以后即可在程序中调用该过程任意次允许更快执行存储过程将比Transact-SQL批代码的执行要快减少网络流量存储过程存储在后端数据库中不需要通过网络传输可作为安全机制使用即使对于没有直接执行存储过程中语句权限的用户，也可授予他执行该存储过程的权限常用的系统存储过程SQLServer提供系统存储过程，它们是一组预编译的T-SQL语句所有系统存储过程的名称都以“_sp”开头。系统存储过程位于master数据库中系统存储过程

说明sp_databases列出服务器上的所有数据库sp_helpdb报告有关指定数据库或所有数据库的信息sp_renamedb更改数据库的名称sp_tables返回当前环境下可查询的对象的列表sp_columns返回某个表列的信息sp_help查看某个表的所有信息sp_helpconstraint查看某个表的约束sp_helpindex查看某个表的索引sp_stored_procedures列出当前环境中的所有存储过程sp_helptext显示默认值、未加密的存储过程、用户定义的存储过程、触发器或视图的实际文本常用的系统存储过程的使用常用的扩展存储过程：xp_cmdshell它可以完成DOS命令下的一些操作，诸如创建文件夹、列出文件等语法:示例:xp_cmdshell扩展存储过程的使用EXECxp_cmdshellDOS命令[NO_OUTPUT]USEmasterGO--创建文件夹H:\prodexecxp_cmdshell'mkdirH:\prod',NO_OUTPUTexecxp_cmdshell'dirH:\prod\'--查看文件

常用的系统存储过程的使用EXECsp_databases--不带参数EXECsp_helpdb

--可带参数,也可不带,所有数据库中可用USEempDB--指定数据库empDBGOEXECsp_helpemp--带参数,参数为表emp示例:其他系统存储过程的使用一些系统存储过程必须在特定的数据库中使用,大多数在所有数据库中可用创建存储过程创建存储过程SSMS:可视化的方式T-SQL:代码(重点介绍)

使用CREATEPROCEDURE语句创建存储过程。所有的存储过程都创建在当前数据库中语法:CREATEPROC[EDURE]存储过程名

[{@参数1数据类型}[=默认值][OUTPUT],.......,{@参数n数据类型}[=默认值][OUTPUT]]ASSQL语句其中,参数部分为可选

创建不带参数的存储过程问题：查看部门的平均工资以及各部门工资不超过2000的员工，并根据工资数额显示加薪信息USEempDBGO/*--创建存储过程---*/CREATEPROCEDUREproc_empASDECLARE@salAvgfloat,@salSumfloatSELECT@salAvg=AVG(sal),@salSum=sum(sal)FROMempprint'员工平均工资：'+convert(varchar(10),@salAvg)print'员工总工资：'+convert(varchar(10),@salSum)IF(@salAvg>2000)print'员工不需要加薪'ELSEprint'员工需要加薪'print'--------------------------------------------'print'部门需要加薪的员工:'SELECTdname,empno,ename,salFROMdeptINNERJOINempONdept.deptno=emp.deptnoWHEREsal<2000GO调用存储过程语法:

调用示例存储过程执行结果如下:EXEC过程名[参数]EXECproc_emp创建带参数的存储过程CREATEPROC[EDURE]存储过程名

[{@参数1数据类型}[=默认值][OUTPUT],.......,{@参数n数据类型}[=默认值][OUTPUT]]ASSQL语句

回顾创建存储过程的语法存储过程中的参数可分为2种:输入参数:可以在调用时向存储过程传递参数,此参数可用来在存储过程中传入值输出参数:如果希望返回值，则可以使用输出参数，输出参数后有“OUTPUT”标记，执行存储过程后，将把返回值存放在输出参数中，可供其他T-SQL语句读取访问CREATEPROCEDURE语句中声明一个或多个变量作为参数@参数数据类型[=默认值]创建带输入参数的存储过程示例-1emp表中，当部门不同时，需要加薪的员工和部门的平均工资会有所不同问题：给定部门号，求该部门的平均工资

USEempDBGO/*--创建存储过程--*/CREATEPROCEDUREpro_emp@dnointASdeclare@salavgfloatSELECT@salavg=AVG(sal)FROMempWHEREdeptno=@dnoprint'部门平均工资为:'+convert(varchar(10),@salavg)GO/*--调用存储过程--*/--给定部门号10，求出该部门的平均工资EXECpro_emp10演示创建带输入参数的存储过程创建带输入参数的存储过程示例-2

问题：对于不同部门，求出从事销售工作的人数

USEempDBGO/*--创建参数带默认值的存储过程--*/CREATEPROCEDUREproc_job@dnoint=20,@jbvarchar(20)='SALESMAN'ASdeclare@rsintprint'部门:'+convert(varchar(5),@dno)+'工作:'+@jbprint'------------------------------------------'SELECT@rs=COUNT(*)FROMempWHEREdeptno=@dnoandjob=@jbprint'人数：'+convert(varchar(10),@rs)GO/*--调用存储过程--*/EXECproc_job--都采用默认值，得到10部门，从事销售工作的人数EXECproc_job30--部门号为30，工种采用默认值EXECproc_job20,‘CLERK’--都不采用默认值，部门号位0，工种为’CLERK’--错误的调用方式：EXECproc_job,’CLERK’

–部门号为默认值--正确的调用方式：EXECproc_job@jb=’CLERK’

–部门号为默认值创建带输出参数的存储过程如果希望调用存储过程后，返回一个或多个值，就需要使用输出(OUTPUT)参数了。为了使用输出参数，必须在CREATEPRODECURE语句和EXECUTE语句中指定OUTPUT关键字。在执行存储过程时，如果忽略OUTPUT关键字，存储过程仍然会执行但不返回值

CREATEPROC[EDURE]存储过程名

[{@参数1数据类型}[=默认值][OUTPUT],.......,{@参数n数据类型}[=默认值][OUTPUT]]ASSQL语句

表示输出参数创建带输出参数的存储过程示例-1USEempDBGOCREATEPROCEDUREpro_job@dnoint=20,@jbvarchar(20)='SALESMAN',--默认参数放后@rsintoutput--OUTPUT关键字，否则视为输入参数ASprint'部门:'+convert(varchar(5),@dno)+'工作:'+@jbprint'---------------------------'SELECT@rs=COUNT(*)FROMempWHEREdeptno=@dnoandjob=@jbprint'人数：'+convert(varchar(10),@rs)GO/*--调用存储过程--*/declare@noint--定义变量，用于存放调用存储过程时返回的结果execpro_job30,'CLERK',@nooutput--调用时要带有OUTPUT关键字print'求得的人数是：'+convert(varchar(10),@no)问题：对于不同部门，求出从事销售工作的人数

演示创建带输出参数的存储过程创建带输出参数的存储过程示例-2USEempDBGOCREATEPROCEDUREMathProd@m1smallint，

@m2smallint，

@resultsmallintOUTPUTASSET@result=@m1*@m2GO/*调用存储过程，传递两个实参5和6，将求得结果输出到变量@answer*/DECLARE@answersmallintEXECMathProd5,6,@answerOUTPUTSELECT'Theresultis:',@answer问题:创建一个名为MathProd的存储过程，用以计算出两个数字的乘积，然后声明一个变量以打印字符串“Theresultis：演示创建带输出参数的存储过程修改和删除存储过程修改存储过程可以通过SSMS或T-SQL语句修改存储过程。使用ALTERPROCEDURE语句来修改现有的存储过程，在使用ALTERPROCEDURE进行修改时，SQLServer会覆盖存储过程以前的定义删除存储过程DROPPROCEDURE语句用来从当前的数据库删除用户定义的存储过程语法:示例:删除存储过程MathProdDROPPROCEDURE存储过程名USEempDBGODROPPROCEDUREMathProdGO错误信息处理为了提高存储过程的效率，存储过程应该包含与用户进行交互的状态（成功或失败）的错误信息，在错误发生时，尽可能给客户提供足够多的信息

在错误处理中可以检查以下内容：SQLServer错误自定义的错误信息。@@error

这个系统函数包含最近一次执行的Transact-SQL语句的错误编号。当语句执行时，对错误编号进行清除并重新设置。RAISERROR使用RAISERROR语句能返回用户定义的错误信息并设置一个系统标志来记录已经发生的错误。在使用RAISERROR语句时必须指定错误严重级别和信息状态。错误信息处理示例

问题:在empDB数据库中创建名为AddRec的存储过程，该存储过程使用@@error系统函数来确定在每个INSERT语句执行时是否发生错误。如果发生错误，事务将回滚USEempDBGOCREATEPROCEDUREAddRec@deptnoint=NULL,@dnamevarchar(20)=NULL,@locvarchar(20)=NULLASBEGINTRANSACTION--事务开始

INSERTINTODEPT(deptno,dname,loc)VALUES(@deptno,@dname,@loc)IF@@error<>0--判断T-SQL语句是否有错，如果有错@@error不为0BEGINROLLBACKTRAN--事务回滚

RETURN--返回

ENDCOMMITTRANSACTION--事务结束/*--执行存储过程--*/EXECAddRec50,'采购部','天河南一路'--执行正确，事务提交EXECAddRec'aa','自动化部','天河南一路'--执行错误，事务回滚RAISERROR语句如果存储过程在执行过程中可能会出现错误，则需要在存储过程中加入错误检查语句。在存储过程中，可以使用PRINT语句显示用户定义的错误信息。但是，这些信息是临时的，且只能显示给用户，使用RAISERROR语句能返回用户定义的错误信息并设置一个系统标志来记录已经发生的错误。在使用RAISERROR语句时必须指定错误严重级别和信息状态语法:其中:msg_id：在sysmessages系统表中指定的用户定义错误信息。msg_str：用户定义的特定信息，最长255个字符severity：与特定信息相关联，表示用户定义的严重性级别。用户可使用的级别为0-18级。19-25级是为sysadmin固定角色的成员预留的，并且需要指定WITHLOG选项。20-25级错误被认为是致命错误。state：表示错误的状态，是1-127的值option：指示是否将错误记录到服务器错误日志中。RAISERROR（{msg_id|msg_str}{,severity,state}）

[WITHoption[，。。。n]]RAISERROR语句示例接前面示例代码如下:CREATEPROCEDUREpro_emp@dnointASdeclare@salavgfloatSELECT@salavg=AVG(sal)FROMempWHEREdeptno=@dnoprint'部门平均工资为:'+convert(varchar(10),@salavg)GO当用户调用存储过程时，传入的部门号为负数时，将弹出错误警告，终止存储过程的执行RAISERROR语句示例USEempDBGO/*--创建存储过程--*/CREATEPROCEDUREpro_emp@dnointASIF(@dno<0)BEGINRAISERROR(‘部门号错误,请指定一个正整数,程序退出’,16,1)