第10章-存储过程

存储过程10.1练习题10及参考答案1．什么是存储过程？存储过程分为哪几类？使用存储过程有什么好处？答：存储过程是一系列预先编辑好的、能实现特定数据操作功能的SQL代码集，它与特定的数据库相关联，存储在SQLServer服务器上。用户可以像使用函数一样重复调用这些存储过程，实现它所定义的操作。存储过程分为3类：系统提供的存储过程、用户定义的存储过程和扩展存储过程。使用存储过程有如下好处如下：（1）存储过程提供了处理复杂任务的能力：存储过程提供了许多标准SQL语言所没有的高级特性，通过传递参数和执行逻辑表达式，能够处理复杂任务。（2）增强代码的重用性和共享性：每一个存储过程可以在系统中重复地调用，可以被多个有访问权限的用户访问。存储过程可以增强代码的重用性和共享性，加快应用系统的开发速度，提高开发的质量和效率。（3）减少网络数据流量：存储过程是存放在服务器中并在服务器上运行的，应用系统调用存储过程时只有触发执行存储过程的命令和执行结束返回的结果在网络中传输。所以，使用存储过程可以减少网络中的数据流量。（4）加快系统运行速度：第一次执行后的存储过程会驻留在内存中，以后可以直接运行，从而加快应用系统的处理速度。（5）加强系统安全性：SQLServer可以不授予用户某些表、视图的访问权限，但授予用户执行存储过程的权限，通过存储过程来对这些表或视图进行访问操作。从而保证表中数据的安全性。2．修改存储过程有哪几种方法？假设有一个存储过程需要修改但又不希望影响现有的权限，应使用哪个语句来进行修改？答：修改存储过程有两种方法：一种方法是把旧的存储过程删除，然后再重新建立该存储过程。另一种方法是用单个的步骤更改该存储过程。使用前一种方法修改存储过程，所有与该存储过程相关联的权限都将丢失。而使用后一种方法可以更改过程或参数定义，但为该存储过程定义的权限将保留。所以要修改一个存储过程但又不希望影响现有的权限第章1100可使用后一种方法。使用的语句为ALTERPROCEDURE。10.2上机实验题9及操作过程在上机实验题8的factory数据库上，使用T-SQL语句完成如下各题：（1）创建一个为worker表添加职工记录的存储过程Addworker。（2）创建一个存储过程Delworker删除worker表中指定职工号的记录。（3）显示存储过程Delworker。（4）删除存储过程Addworker和Delworker。操作过程（1）建立存储过程的程序如下：USEfactoryGOCREATEPROCEDUREAddworker@noint=NULL,@namechar(10)=NULL,@sexchar(2)=NULL,@birthdaydatetime=NULL,@nachar(2)=NULL,@wtimedatetime=NULL,@depnoint=NULLASIF@noISNULLOR@nameISNULLOR@sexISNULLOR@birthdayISNULLOR@depnoISNULLBEGINPRINT'请重新输入该职工信息！'PRINT'你必须提供职工号、姓名、性别、出生日期、部门号'RETURNENDBEGINTRANSACTIONINSERTINTOworkerVALUES(@no,@name,@sex,@birthday,@na,@wtime,@depno)IF@@error0BEGINROLLBACKTRANRETURNENDCOMMITTRANSACTIONPRINT'职工'+@name+'的信息成功添加到表worker中'执行下列语句，可验证存储过程的正确性：USEfactoryGOAddworker20,'陈立','女','55/03/08','否','75/10/10',4GOSELECT职工号,姓名,性别,党员否FROMworkerGO执行结果如下：职工陈立的信息成功添加到表worker中职工号姓名性别党员否-------------------------1孙华男是10陈涛男否11刘欣男否12李涵男是13王小燕女否14李艺女否15魏君女否2孙天奇女否20陈立女否3陈明男否4李华男否5余慧男是6欧阳少兵男否7程西女是8张旗男否9刘夫文男是（2）建立存储过程的程序如下：USEfactoryGOCREATEPROCEDUREDelworker@noint=NULLASIF@noISNULLBEGINPRINT'必须输入职工号！'RETURNENDBEGINTRANSACTIONDELETEFROMworkerWHERE职工号=@noIF@@error0BEGINROLLBACKTRANRETURNENDCOMMITTRANSACTIONPRINT'成功删除职工号为'+CAST(@noASCHAR(2))+'的职工记录'执行下列语句，可验证存储过程的正确性：USEfactoryGODelworker20GOSELECT职工号,姓名,性别,党员否FROMworkerGO执行结果如下：成功删除职工号为的职工记录职工号姓名性别党员否-------------------------1孙华男是10陈涛男否11刘欣男否12李涵男是13王小燕女否14李艺女否15魏君女否2孙天奇女否3陈明男否4李华男否5余慧男是6欧阳少兵男否7程西女是8张旗男否9刘夫文男是（3）对应的程序如下：USEfactoryGOEXECsp_helptextDelworkerGO执行结果如下：Text--------------------------------------------------------------------CREATEPROCEDUREDelworker@noint=NULLASIF@noISNULLBEGINPRINT'必须输入职工号！'RETURNENDBEGINTRANSACTIONDELETEFROMworkerWHERE职工号=@noIF@@error0BEGINROLLBACKTRANRETURNENDCOMMITTRANSACTIONPRINT'成功删除职工号为'+CAST(@noASCHAR(2))+'的职工记录'（4）对应的程序如下：USEfactoryGOIFEXISTS(SELECTnameFROMsysobjectsWHEREname='Addworker'ANDtype='P')DROPPROCEDUREAddworkerGOIFEXISTS(SELECTnameFROMsysobjectsWHEREname='Delworker'ANDtype='P')DROPPROCEDUREDelworkerGO执行结果如下：命令已成功完成。