存储过程(精)PPT课件

,*,单击此处编辑母版标题样式,单击此处编辑母版文本样式,第11章 存储过程,返回本章首页,上一页,下一页,第11章 存储过程,本章教学概要,主要内容,11.1 存储过程的定义,11.2 存储过程的优点,11.3 存储过程的创建,11.4 重新编译存储过程,11.5 自动执行存储过程,11.6 查看、修改和删除存储过程,11.7 扩展存储过程,教学目标,理解存储过程的,概念和作用,掌握创建存储过程的方法,掌握执行存储过程的方法,掌握查看、修改和删除索引的方法,11.1 存储过程的定义,SQL Server的存储过程类似于编程语言中的过程。在使用Transact-SQL语言编程的过程中，我们可以将某些需要多次调用的实现某个特定任务的代码段编写成一个过程，将其保存在数据库中，并由SQL Server服务器通过过程名来调用它们，这些过程就叫做存储过程。,在SQL Server中存储过程分为三类：即,系统提供的存储过程,（sp_）,、,扩展存储过程,（xp_）,和,用户自定义的存储过程,。,11.2 存储过程的优点,存储过程是一种把重复的任务操作封装起来的一种方法，支持用户提供参数，可以返回、修改值，允许多个用户使用相同的代码，完成相同的数据操作。它提供了一种集中且一致的实现数据完整性逻辑的方法。存储过程用于实现频繁使用的查询、业务规则、被其它过程使用的公共例行程序。存储过程具有以下优点：,实现了,模块化编程,。,存储过程具有对数据库,立即访问,的功能。,使用存储过程可以,加快程序的运行速度,。,使用存储过程可以,减少网络流量,。,使用存储过程可以,提高数据库的安全性,。,11.3 存储过程的创建,在SQL Server中，可以使用两种方法创建存储过程 ：,使用T-SQL语句中的,CREATE PROCEDURE,命令创建存储过程。,利用SQL Server 企业管理器创建存储过程。,创建存储过程时，需要确定存储过程的三个组成部分：,所有的输入参数以及传给调用者的输出参数。,被执行的针对数据库的操作语句，包括调用其它存储过程的语句。,返回给调用者的状态值，以指明调用是成功还是失败。,1.,使用,T-SQL,语句创建存储过程,创建一个存储过程的语法如下：,CREATE PROC EDURE OWNER. procedure_name,(parameter data_type VARYING =default OUTPUT) , . . . n ,WITH RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION ,AS,sql_statement .n,CREATE PROCEDURE创建存储过程的参数的意义如下：,procedure_name,：用于指定要创建的存储过程的名称。,parameter,：过程中的参数。在,CREATE PROCEDURE,语句中可以声明一个或多个参数。,data_,type,：用于指定参数的数据类型。,VARYING,：用于指定作为输出,OUTPUT,参数支持的结果集。,Default,：用于指定参数的默认值。,OUTPUT,：表明该参数是一个返回参数。,RECOMPILE,：表明,SQL Server,不会保存该存储过程的执行计划 。,ENCRYPTION,：表示,SQL Server,加密了,syscomments,表，该表的,text,字段是包含,CREATE PROCEDURE,语句的存储过程文本。,AS,：用于指定该存储过程要执行的操作。,sql_,statement,：是存储过程中要包含的任意数目和类型的,Transact-SQL,语句。,创建存储过程前，应该考虑下列几个事项：,不能将,CREATE PROCEDURE,语句与其它,SQL,语句组合到单个批处理中。,只能在当前数据库中创建存储过程。除了临时存储过程。临时存储过程总是创建在,tempdb 数据库中,一个存储过程的最大尺寸为,128M,。,【例11-1】,创建一存储过程，要求该存储过程返回学生姓名、所学课程和任课教师。,CREATE PROCEDURE,spStuCouTea_name,AS SELECT,a.student_name,b.course_name,c.teacher_name,FROM,student a,INNER JOIN,student_course d,ON,a.student_id = d.student_id,INNER JOIN,course b,ON,d.course_id = b.course_id,INNER JOIN,teacher_course_class e,ON,(e.class_id = a.class_id) and (e.course_id = d.course_id),INNER JOIN,teacher c,ON,c.teacher_id = e.teacher_id,执行以上脚本，便可创建存储过程spStuCouTea_name。如,要执行该存储过程，可在查询分析器中执行如下语句：,EXEC,spStuCouTea_name,2. 使用企业管理器创建存储过程,（）在,SQL Server,企业管理器中，选择指定的服务器和数据库，用右键单击要创建存储过程的数据库，在弹出的快捷菜单中选择“新建”选项，再选择下一级菜单中的“存储过程”选项，或者用右键单击存储过程图标，从弹出的快捷菜单中选择“新建存储过程”选项，均会出现创建存储过程对话框。,（）在文本框中可以输入创建存储过程的T_SQL语句，单击“检查语法”，则可以检查语法是否正确；单击“确定”按钮，即可保存该存储过程。如果要设置权限，单击“权限”按钮。,3. 创建带输入参数的存储过程,输入参数是指由调用程序向存储过程传递的参数。它们在创建存储,过程语句中被定义，其参数值在执行该存储过程时由调用该存储过,程的语句给出。具体语法如下:,parameter_name dataype=default,【例11-2】,建立一个存储过程，选择某一个指定学生的学生姓名、所学课程及任课教师姓名。,CREATE PROCEDURE,spStuCouTea_withParam,studentname varchar(8),/*,studentname varchar(8)=%,*/,AS SELECT,a.student_name,b.course_name,c.teacher_name,FROM,student a,INNER JOIN,student_course d,ON,a.student_id = d.student_id,INNER JOIN,course b,ON,d.course_id = b.course_id,INNER JOIN,teacher_course_class e,ON,(e.class_id = a.class_id) and (e.course_id = d.course_id),INNER JOIN,teacher c,ON,c.teacher_id = e.teacher_id,WHERE,student_name = studentname,【例11-3】,为JWGL数据库建立一个存储过程，通过执行存储过程将学生信息添加到student表。,CREATE PROCEDURE,spAddStudent,id char(8)= NULL,name nvarchar(8)= NULL,sex char(2)= NULL,birthday smalldatetime= NULL,class char(6)= NULL,InDate smalldatetime= NULL,home nvarchar(40)= NULL,AS,IF,id IS NULL,OR,name IS NULL,OR,sex IS NULL,OR,birthday IS NULL,OR,class IS NULL,OR,InDate IS NULL,BEGIN,PRINT,请重新输入该学生信息！,PRINT,你必须提供学生的学号、姓名、性别、出生日期、班级号及入学日期。,PRINT,（家庭地址可以为空）,RETURN,END,DECLARE,bitSex bit,IF,sex=男,SET,bitSex = 1,ELSE,SET,bitSex = 0,BEGIN TRANSACTION,INSERT,student (student_id,student_name,sex,birth,class_id,entrance_date,home_addr),VALUES,(id, name, bitSex, birthday, class, InDate, home),IF,error 0,BEGIN,ROLLBACK TRAN,RETURN,END,COMMIT TRANSACTION,PRINT,学生+name+的信息成功,添加到表student中。,4. 创建带输出参数的存储过程,通过在创建存储过程的语句中定义输出参数，可以创建带输出参数的存储过程。执行该存储过程，可以返回一个或多个值。具体语法如下：,parameter_name dataype=default OUTPUT,【例11-4】,创建一个实现加法计算并将运算结果作为输出参数的存储过程。,CREATE PROCEDURE,spAdd,Value1 INT,Value2 INT,ResultValue,INT,OUTPUT,AS,SELECT,ResultValue = Value1 + Value2,创建了上面的存储过程之后，下面我们来看看怎样使用它。为了使用,spAdd,，接受其输出参数的返回值，调用它的程序中也必须定义一个变量，并使用,OUTPUT,关键字指定它为调用输出参数。,【例11-5】,执行spAdd存储过程，输入参数由value1和value2提供，输出参数valueTotal。,DECLARE,value1,INT,DECLARE,value2,INT,DECLARE,valueTotal,INT,SET,value1 = 125,SET,value2 = 3,SET,valueTotal = 34,EXEC,spAdd value1, value2, valueTotal,OUTPUT,PRINT,CONVERT,(,CHAR(5), value1) + 与 +,CONVERT,(CHAR(5), value2) +,的和等于: +,CONVERT,(,CHAR(5), valueTotal),11.4 重新编译存储过程,重新编译存储过程有以下三种方法：, 在创建存储过程时,，使用CREATE PROCEDURE中的RECOMPILE重编,译选项。具体语法为：CREATE PROCEDURE. ,WITH RECOMPILE,【例11-6】,为JWGL数据库创建一个带重编译选项的存储过程，用于查询某学生的成绩信息。,CREATE PROC,spStudentCourse,studentid char(8),WITH RECOMPILE,AS,SELECT,*,FROM,student_course,student_id = studentid, 执行存储过程时重编译,。在EXECUTE语句中使用WITH RECOMPILE选项，让SQL Server在执行一个存储过程时，重新编译该存储过程。其语法如下：,EXECUTE procedure_name parameter WITH RECOMPILE,【例11-7】,带重新编译选项，执行存储过程spAdd。,DECLARE,valueTotal,INT,EXEC,spAdd 4,9, valueTotal,OUTPUT,WITH RECOMPILE,PRINT CONVERT,(CHAR(5), valueTotal),执行存储过程时重编译，可以在执行存储过程期间创建新的查询计划，新的执行计划存放在高速缓存中。, 使用sp_recompile系统存储过程,，指定表的存储过程进行重编译。语法如下：,sp_recompile,table_name,【例11-8】,EXEC sp_recompile student 将强制student表的所有存储过程,和触发器在下一次运行时被重新编译。,11.5 自动执行存储过程,存储过程创建成功后，保存在数据库中。在SQL Server中可以使用,EXECUTE,命令来直接执行存储过程。,使用系统存储过程sp_procoption可以将现有存储过程设置为自动执行过程或停止自动执行，也可以查看SQL Server启动时执行的所有过程的列表。其语法是：,sp_procoption, ProcName = procedure, OptionName = option, OptionValue = value,其中：,ProcName = procedure：要为其设置或查看选项的过程名。无默认值。,OptionName = option：要设置的选项的名称。option的唯一值是startup，该值设置存储过程的自动执行状态。, OptionValue = value：表示选项是设置为开（true或on）还是关（false或off）。无默认值。,11.6 查看、修改和删除存储过程,1. 查看存储过程,存储过程被创建之后，它的名字就存储在系统表,sysobjects,中，它的源代码存放在系统表,syscomments,中。可以使用使用企业管理器或系统存储过程来查看用户创建的存储过程。,用T-SQL语句查看存储过程, 查看存储过程的定义,系统存储过程sp_helptext可查看,未加密,的存储过程的定义脚本，也可用于查看规则、默认值、用户定义函数、触发器或视图的定义脚本。使用其语法如下：,sp_helptext,objname=name, 查看有关存储过程的信息,使用系统存储过程sp_help可查看有关存储过程的信息。具体语法形式如下：,sp_help,proc_name,使用企业管理器查看存储过程,2.,修改存储过程,使用T-SQL语句修改存储过程,Transact-SQL中提供了ALTER PROCEDURE语句来更改已经创建的存储过程，它不会更改权限，也不影响相关的存储过程或触发器。它的语法如下所示：,ALTER PROC, EDURE ,procedure_name, parameter data_type ,VARYING, = default ,OUTPUT, ,.n ,WITH,RECOMPILE | ENCRYPTION,| RECOMPILE , ENCRYPTION, ,FOR REPLICATION,AS,sql_statement .n ,使用企业管理器修改存储过程,3. 删除存储过程,使用SQL语句删除存储过程,从当前数据库中删除一个或多个存储过程的T-SQL语句是 DROP PROCEDURE。具体语法如下：,DROP PROCEDURE,procedure ,n,【例11-9】,要将spAdd存储过程删除，则可以执行,DROP PROC,spAdd 语句。,使用企业管理器删除存储过程,补充内容：,重命名存储过程,修改存储过程的名称可以使用系统存储过程,sp_rename,，其语法形式如下：,sp_rename,原存储过程名称，新存储过程名称,另外，通过企业管理器也可以修改存储过程的名称。,11.7 扩展存储过程,1. 什么是扩展存储过程,扩展存储过程使你得以使用编程语言创建自己的外部例程。对用户来说，扩展存储过程与普通存储过程一样，执行方法也相同。可将参数传递给扩展存储过程，扩展存储过程可返回结果，也可返回状态。扩展存储过程可用于扩展MS SQL Server 2005的功能。,扩展存储过程与通常的存储过程其实有很大差别。存储过程是一系列预编译的Transact-SQL语句，而,扩展存储过程是对动态链接库函数的调用,。SQL Server一般以“,xp,”为前缀标识扩展存储过程名，但不是必须的。,2 . 注册扩展存储过程,使用T-SQL语句注册扩展存储过程,SQL Server 2005的系统管理员通过系统存储过程sp_addextendedproc来添加一个扩展存储过程到系统的master数据库中。这个操作称为注册扩展存储过程。,sp_addextendedproc functname= procedure, dllname= dll,【例11-10】,假设已经编写好了一个名为xp_hello.dll的动态链接库，其中有一个函数叫xp_hello，我们使用以下的语句来把xp_hello添加到SQL Server中。,USE,master,EXEC sp_addextendedproc,xp_hello，xp_hello.dll,使用企业管理器注册扩展存储过程,3. 如何使用扩展存储过程,4. 删除扩展存储过程,使用T-SQL语句删除扩展存储过程,其语法如下：,sp_dropextendedproc function_name= procedure,【例11-11】,执行存储过程,sp_dropextendedproc,，将扩展存储过程,xp_hello,从,master,库中删除。,USE,master,EXEC,sp_dropextendedproc xp_hello,使用企业管理器删除扩展存储过程,本章教学小结,本章阐明了存储过程的应用，通过本章的学习,应能充分地理解存储过程的作用和在数据库系,统中的地位，并且能够熟练地创建、修改、查,看、删除存储过程及解决存储过程中错误的处,理。,