数据库存储过程

10存储过程通过本节的学习，应该掌握以下内容：存储过程的作用;熟练创建、修改、删除存储过程;在实际应用开发时能够灵活运用存储过程以提高开发效率。10存储过程1.存储过程的概念2.创建存储过程3.执行存储过程4.存储过程的管理与维护1.存储过程的概念SQLServer应用操作中,存储过程和触发器扮演相当重要的角色,不仅能提高应用效率,确保一致性,更能提高系统执行速度。同时，使用触发器来完成业务规则，达到简化程序设计的目的。1.存储过程的概念存储过程包括系统存储过程和用户存储过程。系统存储过程：Executesp_help表名：查看表的结构。Executesp_helpindex表名：查看表上的索引信息。Executesp_helptext视图名：查看视图的定义信息。1.存储过程的概念存储过程按返回的数据类型，可分为两类：一类类似于SELECT语句，用于查询数据，查询到的数据以结果集的形式给出；另一类存储过程是通过输出参数返回信息，或不返回信息只执行一个动作。存储过程可以嵌套，即一个存储过程的内部可以调用另一个存储过程。1.存储过程的概念（1）基本概念存储过程是一组编译在单个执行计划中的Transact-SQL语句，它将一些固定的操作集中起来交给SQLServer数据库服务器完成，以实现某个任务。存储过程就是预先编译和优化并存储于数据库中的过程。1.存储过程的概念（2）存储过程的优点1）通过本地存储、代码预编译和缓存技术实现高性能的数据操作。2）通过通用编程结构和过程重用实现编程框架。3）通过隔离和加密的方法提高了数据库的安全性。1.存储过程的概念使用存储过程的好处1）允许模块化的程序设计。2）更快的执行速度。3）有效降低网络流量。4）较好地安全机制。1.存储过程的概念（3）存储过程的类型1）系统存储过程系统存储过程（SystemStoredProcedures）主要存储在master数据库中，以sp_开头，用来进行系统的各项设定，获取信息，从而为系统管理员管理SQLServer提供支持。1.存储过程的概念（3）存储过程的类型1）系统存储过程系统存储过程我们以前接触过：Executesp_help表名：查看表的结构。Executesp_helpindex表名：查看表上的索引信息。Executesp_helptext视图名：查看视图的定义信息。SQLServer提供了很多系统存储过程，他们用于系统管理、用户登录管理、权限设置、数据库对象管理、数据复制等操作。1.存储过程的概念（3）存储过程的类型2）用户自定义的存储过程用户定义的存储过程可分为本地存储过程（LocalStoredProcedures）、远程存储过程(RemoteStoredProcedures)、临时存储过程(TemporaryStoredProcedures)、扩展存储过程(ExtendedStoredProcedures)等。在这里我们只讨论本地存储过程。1.存储过程的概念（3）存储过程的类型2）用户自定义的存储过程由用户在当前工作的数据库中创建的存储过程，称作本地存储过程。事实上一般所说的存储过程指的就是本地存储过程。2.创建存储过程创建用户自定义存储过程有两种方法：可以使用T-SQL语言的CREATEPROCEDURE语句创建存储过程，也可以使用对象资源管理器向导创建存储过程。2.创建存储过程创建和使用存储过程都必须遵循如下的规则：1）创建存储过程的权限默认为属于数据库所有者，该所有者可以把次权限授予其他用户。2）存储过程是数据库对象，其名称必须遵守标识符规则。名称标识符的长度最大为128位，且数据库中必须惟一。3）只能在当前数据库中创建存储过程。4）每个存储过程最多可以使用1024个参数。5）存储过程最大支持32层嵌套。2.创建存储过程（1）创建不带参数的存储过程创建存储过程的基本语法如下：CREATEPROCEDURE存储过程名[WITHENCRYPTION]—对存储过程加密[WITHRECOMPILE]—对存储过程重新编译ASSQL语句2.创建存储过程（1）创建不带参数的存储过程执行存储过程的基本语法如下：EXEC[UTE]存储过程名如果该存储过程是批处理中第一条语句，则EXEC可以省略。简单存储过程类似于将一组SQL语句起个名字，然后就可以在需要时反复调用。复杂一些的则要有输入和输出参数。2.创建存储过程【实例】在学生成绩管理数据库中，创建一个名为“grade_pro”的存储过程，用于查询不及格学生的成绩信息（包括学号，姓名，课程名称，成绩）。USE学生成绩管理系统GOcreateproceduregrade_proasselect学生表.学号,姓名,课程名,分数from学生表,课程表,成绩表where学生表.学号=成绩表.学号and课程表.课程号=成绩表.课程号and分数60go2.创建存储过程【实例】针对教师表Teacher，创建一个名为teacher_pro1的存储过程，用于查询所有男教师的信息。USE学生成绩管理系统GOcreateprocedureteacher_proasselect*from教师表where性别='男'go执行存储过程在存储过程建立好后，该存储过程作为数据库对象已经存在，其名称和文件分别存放在sysobjects和syscomments系统表中。可以使用T-SQL的EXECUTE语句来执行存储过程。如果该存储过程是批处理中第一条语句，则EXEC关键字可以省略。执行存储过程的基本语法如下：EXEC[UTE]存储过程名2.创建存储过程（2）创建带输入参数的存储过程一个存储过程可以带一个或多个输入参数，输入参数是指由调用程序向存储过程传递的参数，它们在创建存储过程语句中被定义，在执行存储过程中给出相应的参数值。2.创建存储过程（2）创建带输入参数的存储过程创建带输入参数的存储过程的语法格式如下：CREATEPROCEDURE存储过程名@参数名数据类型[=默认值][,...n][WITHENCRYPTION][WITHRECOMPILE]ASSQL语句2.创建存储过程【例10-2】从学生表中返回指定学号的学生的信息。createprocedureu_getstudent@numbervarchar(40)asselect*from学生表where学号=@number执行存储过程（2）执行带参数的存储过程在执行存储过程的语句中，有两种方式来传递参数值，分别是使用参数名传递参数值和按参数位置传递参数值。使用参数名传递参数值，是通过语句“@参数名=参数值”给参数传递值。当存储过程含有多个输入参数时，参数值可以按任意顺序制定，对于允许空值和具有默认值的输入参数可以不给出参数的传递值。执行存储过程（2）执行带参数的存储过程1）使用参数名传递参数值执行使用参数名传递参数值的存储过程的语法格式如下。EXECUTE存储过程名[@参数名=参数值][,...n]执行存储过程【实例】执行【实例10-2】创建的存储过程u_getstudentexecuteu_getstudent@number='1002'执行存储过程（2）执行带参数的存储过程2）按位置传送参数值在执行存储过程的语句中，不参照被传递的参数而直接给出参数的传递值。当存储过程含有多个输入参数时，传递值的顺序必须与存储过程中定义的输入参数的顺序相一致。按位置传递参数值的存储过程的语法格式如下。EXECUTE存储过程名[参数值1,参数值2,...n]执行存储过程【实例】执行【实例10-2】创建的存储过程u_getstudentexecuteu_getstudent'1002'执行存储过程可以看到，按参数位置传递参数值比按参数名传递参数值简洁,比较适合参数值较少的情况;而按参数名传递参数使程序可读性增强。特别是参数数量较多时，建议使用按参数名称传递参数的方法，这样的程序可读性、可维护性都要好一些。2.创建存储过程【例10-3】创建带有通配符参数的存储过程。下面的存储过程只从学生表中返回指定一些学生(提供名字和姓氏)的信息。该存储过程对传递的参数进行模式匹配，如果没有提供参数，则返回所有学生的信息。createprocedureu_getstudentbyname@namevarchar(40)='%'asselect*from学生表where姓名like@namego执行存储过程执行存储过程，可以按多种组合执行。executeu_getstudentbynameexecuteu_getstudentbyname'张%'executeu_getstudentbyname'张刘'2.创建存储过程（3）创建带输出参数的存储过程如果我们需要从存储过程中返回一个或多个值，可以通过在创建存储过程的语句中定义输出参数来实现，为了使用输出参数，需要在CREATEPROCEDURE语句中指定OUTPUT关键字。2.创建存储过程（3）创建带输出参数的存储过程创建带输出参数的存储过程的语法格式如下：CREATEPROCEDURE存储过程名@参数名数据类型[VARYING][=默认值]OUTPUT[,...n][WITHENCRYPTION][WITHRECOMPILE]ASSQL语句2.创建存储过程【例10-4】创建一个存储过程U_getstudentaveagebydept，获得某个院系的学生的平均年龄。createprocedureu_getstudentaveagebydept@deptvarchar(40),@aveageintoutasselect@aveage=avg(year(getdate())-year(出生日期))from学生表where院系名称=@deptgo执行存储过程存储过程的执行：declare@aveageintexecuteu_getstudentaveagebydept'计算机学院',@aveageoutprint@aveage2.创建存储过程【实例】创建一个存储过程ssc_pro3，获得选取某门课程的选课人数。USE学生成绩管理系统GOcreateprocedures_pro3@snamevarchar(30),@countintoutasselect@count=count(*)from学生表,课程表,成绩表where学生表.学号=成绩表.学号and课程表.课程号=成绩表.课程号and课程名=@snamego3.执行存储过程在存储过程建立好后，该存储过程作为数据库对象已经存在，其名称和文件分别存放在sysobjects和syscomments系统表中。可以使用T-SQL的EXECUTE语句来执行存储过程。如果该存储过程是批处理中第一条语句，则EXEC关键字可以省略。执行存储过程的基本语法如下：EXEC[UTE]存储过程名3.执行存储过程（1）执行不带参数的存储过程【实例8-37】执行【实例8-32】中创建的名为“grade_pro”的存储过程，用于查询不及格学生的成绩信息（包括学号，姓名，课程名称，成绩）。Execgrade_pro【实例8-38】执行【实例8-33】创建的名为teacher_pro1的存储过程，用于查询所有男教师的信息。Execteacher_pro13.执行存储过程（2）执行带参数的存储过程在执行存储过程的语句中，有两种方式来传递参数值，分别是使用参数名传递参数值和按参数位置传递参数值。使用参数名传递参数值，是通过语句“@参数名=参数值”给参数传递值。当存储过程含有多个输入参数时，参数值可以按任意顺序制定，对于允许空值和具有默认值的输入参数可以不给出参数的传递值。3.执行存储过程（2）执行带参数的存储过程1）使用参数名传递参数值执行使用参数名传递参数值的存储过程的语法格式如下。EXECUTE存储过程名[@参数名=参数值][,...n]3.执行存储过程【实例8-39】执行【实例8-34】创建的存储过程ssc_pro1，使用输入参数（课程名称），用于查询某门课程的选修情况，包括学号，姓名，课程名称和成绩。EXECssc_pro1@scnam