《SQL Server 2012数据库应用与开发教程（第三版） 教学课件 ppt 作者 卫 琳 主编 模块9 存储过程和触发器》由会员分享，可在线阅读，更多相关《SQL Server 2012数据库应用与开发教程（第三版） 教学课件 ppt 作者 卫 琳 主编 模块9 存储过程和触发器（66页珍藏版）》请在金锄头文库上搜索。
1、模块9 存储过程、触发器,本章学习目标： 了解存储过程、触发器的基本概念与特点 掌握存储过程的基本类型和相关操作 掌握触发器的类型与相关操作,任务9.1 存储过程,通过前面的学习，我们能够编写并运行T-SQL程序以完成各种不同的应用。保存T-SQL程序的方法有两种：一种是在本地保存程序的源文件，运行时先打开源文件再执行程序；另一种方法即将程序存储为存储过程，运行时调用存储过程执行。 因为存储过程是由一组T-SQL语句构成的，要使用存储过程，我们必需熟悉前面几章所讨论的基本的T-SQL语句，并且需要了解掌握一些关于函数、过程的概念。,9.1.1 存储过程的基本概念,存储过程是事先编好的、存储在数
2、据库中一组被编译了的T-SQL命令集合，这些命令用来完成对数据库的指定操作：存储过程可以接收用户的输入参数、向客户端返回表格或标量结果和消息、调用数据定义语言 (DDL) 和数据操作语言 (DML) 语句，然后返回输出参数。 通过定义可以看到，存储过程起到了我们在其他语言中所说的子程序的作用，我们可以将经常执行的管理任务或者复杂的业务规则，预先用T-SQL语句写好并保存为存储过程, 当需要数据库提供与该存储过程的功能相同的服务时，只需要使用EXECUTE命令，即可调用存储过程完成命令。,存储过程的优点：,1. 减少网络流量：存储过程在数据库服务器端执行，只向客户端返回执行结果。因此可以将在网络
3、中要发送的数百行代码，编写为一条存储过程，这样客户端只需要提交存储过程的名称和参数，即可实现相应功能，节省了网络流量，提高了执行的效率。此外，由于所有的操作都在服务器端完成，避免了在客户端和服务器端之间的多次往返。存储过程只需要将最终结果通过网络传输到客户端。 2. 提高系统性能：一般T-SQL语句每执行一次就需要编译一次，而存储过程只在创建时进行编译，被编译后存放在数据库服务器的过程高速缓存中，当使用时，服务器不必再重新分析和编译它们。因此，当对数据库进行复杂操作时（如对多个表进行UPDATE、INSERT或DELETE操作时），可将这些复杂操作用存储过程封装起来与数据库提供的事务处理结合一
4、起使用，节省了分析、解析和优化代码所需的CPU资源和时间。 3. 安全性高：使用存储过程可以完成所有数据库操作，并且可授予没有直接执行存储过程中语句的权限的用户，也可执行该存储过程的权限。另外可以防止用户直接访问表，强制用户使用存储过程执行特定的任务。 4. 可重用性：存储过程只需创建并存储在数据库中，以后即可任意在程序中调用该过程。存储过程可独立于程序源代码而单独修改，减少数据库开发人员的工作量。 5. 可自动完成需要预先执行的任务：存储过程可以在系统启动时自动执行，完成一些需要预先执行的任务，而不必在系统启动后再进行人工操作。,9.1.2 存储过程的类型,1. 系统存储过程 2. 扩展存储
5、过程 3.用户存储过程 用户存储过程在用户数据库中创建，通常与数据库对象进行交互，用于完成特定数据库操作任务，可以接受和返回用户提供的参数，名称不能以sp_为前缀。 在SQL Server 2008中，用户存储过程有两种类型：Transact-SQL存储过程和clr存储过程。 Transact-SQL存储过程保存T-SQL语句的集合，可以接受和返回用户提供的参数，也可以从数据库向客户端应用程序返回数据； CLR存储过程是指对Microsoft.NET Framework公共语言运行时方法的引用，可以接受和返回用户提供的参数。它们在.NET Framework程序集中是作为类的公共静态方法实现的
6、,use test go create procedure student_info as select student.sno,sname,cname,grade from student,sc,course where student.sno=sc.sno and o=o go,示例：创建一个存储过程，返回学生学号，姓名，课程名，成绩信息。,1.存储过程不使用任何参数,9.1.3 存储过程创建与执行,use test go create procedure student_info2 xm varchar(8),kcm varchar(50) as select student.sno,s
7、name,cname,grade from student,sc,course where student.sno=sc.sno and o=o and sname=xm and cname=kcm go,示例：创建一个存储过程，返回学生学号，姓名，课程名，成绩信息。接受与传递参数精确匹配的值。,2.存储过程使用带有输入参数的存储过程,1) 按位置传送参数值 exec student_info2 李明,高等数学 2)使用参数名传送 exec student_info2 xm=李明,kcm=高等数学 exec student_info2 kcm=高等数学,xm=李明,存储过程调用形式,示例：新建
8、一个存储过程，该存储过程定义了两个日期时间类型的输入参数和一个字符型输入参数，返回所有出生日期在两个输入日期之间，性别与输入的字符型参数相同的学生信息，其中字符型输入参数指定的默认值为“女”。,use test go create procedure stud_info3 startdate date,enddate date,sex char(2)=女 as if (startdate is null or enddate is null or sex is null) begin raiserror(null value are invalid,5,5) return end select
9、 * from student where sbirth between startdate and enddate and ssex=sex order by sbirth go use test exec stud_info3 1991-1-1,1992-12-31,3.使用带有通配符参数的存储过程,创建一个存储过程，返回学生学号，姓名，课程名，成绩。该存储过程使用了模糊匹配，如果没有提供参数，则使用预设的默认值。 if exists (select name from sysobjects where name=student_info3 and type=P) drop procedu
10、re student_info3 go create procedure student_info3 xm varchar(8)=刘% as select student.sno,sname,cname,grade from student join sc join course on o=o on student.sno=sc.sno where sname like xm go execute student_info3 王张%,4.使用带有output参数的存储过程,例：用于返回输入特定学生的单科成绩平均分。此存储过程有一个输入和输出参数。 use test go if exists (
11、select name from sysobjects where name=student_info4 and type=p) drop procedure student_info4 go create procedure student_info4 xm varchar(8),pjf tinyint output as select pjf=AVG(grade) from student join sc on student.sno=sc.sno where sname=xm go declare pjf tinyint exec student_info4 邓立新,pjf output
12、 select 邓立新,pjf go,Output变量必需在创建存储过程和使用该变量时都进行定义。,use test go create procedure stud_info startdate date,enddate date, recordcount int output as if (startdate is null or enddate is null) begin raiserror(null value are invalid,5,5) return end select * from student where sbirth between startdate and en
13、ddate order by sbirth select recordcount=ROWCOUNT Go,示例：新建一个存储过程，其功能是输入两个日期型数据，并使用输出参数返回这两个出生日期之间的所有学生人数。,4.使用带有output参数的存储过程,declare recordcount int exec stud_info 1991-1-1,1992-12-31 ,recordcount output select recordcount as 人数 go,其中，ROWCOUNT是SQL Server用来返回受上一语句影响的行数的系统变量，在这里用它来返回符合条件记录数。,create p
14、rocedure encrypt_this with encryption as select * from student Go exec sp_helptext encrypt_this,通过系统存储过程sp_helptext可显示规则、默认值、没有加密的存储过程，用户定义的函数、触发器或视图的文本。,示例：创建一个加密的存储过程,5.使用with encryption选项对用户隐藏存储过程,use master go create procedure sp_showtable table varchar(30)=xs% as select sysobjects.name as table
15、_name, sysindexes.name as index_name from sysindexes join sysobjects on sysindexes.id=sysobjects.id where sysobjects.name like table go use test exec sp_showtable xs% go,6.创建用户自己定义的存储过程 示例：创建一个过程，显示表名以xs开头的所有表及其索引。如果没有指定参数，此过程返回表名以kc开头的所有表及对应索引,9.1.4 .管理存储过程,查看存储过程 修改存储过程 删除存储过程 存储过程的重新编译,查看存储过程,示例：
16、 use test go exec sp_helptext stud_proc exec sp_depends stud_proc exec sp_help stud_proc,2.修改存储过程 修改存储过程通常是指编辑它的参数和Transact-SQL语句。下面我们分别说明如何使用对象资源管理器和Transact-SQL语句命令完成这项工作。 1). 使用对象资源管理器 单击“开始”按钮，选择“程序”“Microsoft SQL Server 2008”“SQL Server Management Studio”“对象资源管理器”。 分别展开“数据库”、欲修改存储过程所处的数据库、“可编程性”、“存储过程”、欲修改的存储过程。 右击欲修改存储过程，在弹出菜单中选择“修改”，此时立即在“SQL编辑器
