第11章 存储过程

第11章存储过程第11章存储过程主要内容：存储过程的定义及其特点创建存储过程的方法如何执行存储过程查看、修改、删除存储过程11.1存储过程的定义SQLServer提供了一种方法，它可以将一些固定的操作集中起来由SQLServer数据库服务器来完成，以实现某个任务，这种方法就是存储过程。存储过程就是一系列编译好的、能实现特定数据操作功能的SQL代码集，它与特定的数据库相关联，存储在SQLServer服务器上。在SQLServer中存储过程分为三类：即系统提供的存储过程、扩展存储过程和用户自定义的存储过程。11.2存储过程的优点存储过程提供了处理复杂任务的能力增强代码的重用性和共享性减少网络数据流量加快系统运行速度加强系统安全性11.3存储过程的创建在SQLServer中，可以使用三种方法创建存储过程：1.使用创建存储过程向导创建存储过程。2.利用SQLServer企业管理器创建存储过程。3.使用Transact-SQL语句中的CREATEPROCEDURE命令创建存储过程。创建存储过程时，需要确定存储过程的三个组成部分：1.所有的输入参数以及传给调用者的输出参数。2.被执行的针对数据库的操作语句，包括调用其它存储过程的语句。3.返回给调用者的状态值，以指明调用是成功还是失败。1.使用创建存储过程向导创建存储过程在企业管理器中，选择工具菜单中的向导选项，选择“创建存储过程向导”（如图11-1所示），则出现欢迎使用创建存储过程向导对话框，如图11-2所示。根据图11-2--图11-7提示可完成创建存储过程。图11-1选择存储过程向导图11-2欢迎使用创建存储过程向导对话框图11-3选择数据库对话框图11-4选择数据库对象对话框图11-5完成创建存储过程向导对话框图11-6编辑存储过程属性对话框图11-7编辑存储过程SQL对话框2.使用SQLServer企业管理器创建1)在SQLServer企业管理器中，选择指定的服务器和数据库，用右键单击要创建存储过程的数据库，在弹出的快捷菜单中选择“新建”选项，再选择下一级菜单中的“存储过程…”选项，如图11-8所示；或者用右键单击存储过程图标，从弹出的快捷菜单中选择“新建存储过程…”选项，如图11-9所示。均会出现创建存储过程对话框，如图11-10所示。2)在文本框中可以输入创建存储过程的T_SQL语句，单击“检查语法”，则可以检查语法是否正确；单击“确定”按钮，即可保存该存储过程。如果要设置权限，单击“权限…”按钮，如图11-11所示。图11-8选择新建存储过程对话框（1）图11-9选择新建存储过程对话框（2）图11-10新建存储过程对话框图11-11设置权限对话框3.使用Transact-SQL语句中的CREATEPROCEDURE命令创建存储过程创建存储过程前，应该考虑下列几个事项：1)不能将CREATEPROCEDURE语句与其它SQL语句组合到单个批处理中。2)创建存储过程的权限默认属于数据库所有者，该所有者可将此权限授予其他用户。3)存储过程是数据库对象，其名称必须遵守标识符规则。4)只能在当前数据库中创建存储过程。5)一个存储过程的最大尺寸为128M。创建步骤1)编写sql语句。2)测试sql语句是否正确，并能实现功能要求。3)若得到的结果数据符合预期要求，则按照存储过程的语法，创建该存储过程。4)执行该存储过程，验证其正确性。使用CREATEPROCEDURE创建存储过程的语法形式如下：CREATEPROC[EDURE]procedure_name[;number][{@parameterkkdata_type}[VARYING][=default][OUTPUT]][,...n][WITH{RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION}][FORREPLICATION]ASsql_statement[...n]用CREATEPROCEDURE创建存储过程的语法参数的意义如下：procedure_name：用于指定要创建的存储过程的名称。number：该参数是可选的整数，它用来对同名的存储过程分组，以便用一条DROPPROCEDURE语句即可将同组的过程一起除去。@parameter：过程中的参数。在CREATEPROCEDURE语句中可以声明一个或多个参数。data_type：用于指定参数的数据类型。VARYING：用于指定作为输出OUTPUT参数支持的结果集。Default：用于指定参数的默认值。OUTPUT：表明该参数是一个返回参数。RECOMPILE：表明SQLServer不会保存该存储过程的执行计划。ENCRYPTION：表示SQLServer加密了syscomments表，该表的text字段是包含CREATEPROCEDURE语句的存储过程文本。FORREPLICATION：用于指定不能在订阅服务器上执行为复制创建的存储过程。AS：用于指定该存储过程要执行的操作。sql_statement：是存储过程中要包含的任意数目和类型的Transact-SQL语句。存储过程创建示例[例11-1]创建一存储过程，要求该存储过程返回学生姓名、所学课程和任课老师。CreateprocedurespStuCouTea_nameAsSelecta.student_name,b.course_name,c.teacher_nameFromstudenta,courseb,teacherc,student_coursed,teacher_course_classeWherea.student_id=d.student_idandd.course_id=b.course_idande.course_id=d.course_idande.class_id=a.class_idandc.teacher_id=e.teacher_id该存储过程如果要执行，可在查询分析器中执行如下语句：execspstucoutea_name4)创建存储过程的注意事项：a)每个存储过程应该完成一项单独的工作。b)为防止别的用户看到自己编写的存储过程的脚本，创建存储过程可以使用参数withencryption.c)一般存储过程都是在服务器上创建和测试，在客户机上使用时，还应该进行测试。创建带输入参数的存储过程输入参数是指由调用程序向存储过程传递的参数。他们在创建存储过程语句中被定义，其参数指在执行该存储过程时由调用该存储过程的语句给出。[例11-2]继续上一个例子。建立一个存储过程，选择某一个指定学生的学生姓名、所学课程及任课老师姓名。Createprocedurespstucoutea_withparam@studentnamevarchar(8)=‘%’AsSelecta.student_name,b.course_name,c.teacher_nameFromstudenta,courseb,teacherc,student_coursed,teacher_course_classeWherea.student_id=d.student_idandd.course_id=b.course_idande.course_id=d.course_idande.class_id=a.class_idandc.teacher_id=e.teacher_idandstudent_name=@studentname[例11-3]为jwgl数据库建立一个存储过程，通过执行存储过程将学生信息添加到student表。Createprocedurespaddstudent@Idchar(8)=null,@namenvarchar(8)=null,@sexchar(2)=null,@birthdaysmalldatetime=null,@classchar(6)=null,@indatesmalldatetime=null,@homenvarchar(40)=nullAsIf@idisnullor@nameisnullor@sexisnullor@birthdayisnullor@classisnullor@indateisnullBeginprint‘请重新输入该学生信息！’print‘你必须提供学生的学号、姓名、性别、出生日期、班级号及入学日期。’print‘（家庭住址可以为空）’returnEndDeclare@bitsexbitIf@sex=‘男’set@bitsex=1Elseset@bitsex=0Begintransactioninsertstudent(student_id,student_name,sex,birth,class_id,entrance_date,home_addr)Values(@id,@name,@bitsex,@birthday,@class,@indate,@home)If@@error0BeginRollbacktranReturnEndCommittransactionPrint‘学生’+@name+‘的信息成功添加到表student中。’执行语句:Spaddstudent‘g9940211’,’赵凯,‘男’,‘1985-01-09’,‘g99402’,’1999-01-01’,’南京中山北路10号‘可以将该学生的信息插入到student表中。创建带输出参数的存储过程通过在创建存储过程的语句中定义输出参数，可以创建带输出参数的存储过程。执行该存储过程，可以返回一个或多个值。[例11-4]创建一个实现加法计算并将运算结果作为输出参数的存储过程。createprocspadd@value1int,@value2int,@resultvalueintoutputAsSelect@resultvalue=@value1+@value2go[例11-5]执行spadd存储过程，输入参数由value1和value2提供，输出参数valuetotal.Declare@value1intDeclare@value2intDeclare@valuetotalintSet@value1=125Set@value2=3Set@valuetotal=34Execspadd@value1,@value2,@valuetotaloutputPrintconvert(char(5),@value1)+’与‘+convert(char(5),@value2)+’的和等于：‘+convert(char(5),@valuetotal)Go结果显示：125与3的和等于：128重新编译存储过程在某些情况下，可能需要改变数据库的逻辑结构，或者为表新增索引。为了使该存储过程能够根据数据库的改变重新优化，或从新的索引中受益，这就要求sqlserver在执行存储过程时对它重新编译，因为除非重新启动sqlserver，否则，存储过程访问数据表的原始查询不会自动优化。以下是重新编译存储过程的三种方法。1、在创建存储过程时，使用createprocedure中的recompile重编译选项。createprocedure…[withrecompile][例11-6]为jwgl数据库创建一个带重编译选项的存储过程，用于查询某学生的成绩信息。Createprocspstudentcourse@studentidchar(8)withrecompileAsSelect*fromstudent_courseWherestudent_id=@studentidgo2、执行存储过程时编译。在execute语句中使用withrecompile选项，让SQLServer在执行一个存储过程时，重新编译该存储过程。Executeprocddure_name[parameter][withrecompile][例11-7]带重新编译选项，执行存储过程spadd.Declare@valuetotalintExecspadd4,9,@valuetotaloutputwi