SQL Server 2005数据库实践教程第4章 管理触发器与存储过程

1、1SQL Server 2005数据库实践教程数据库实践教程开发与设计篇开发与设计篇主编：钱 哨第4章 .管理存储过程和触发器2本章你将学习到：o 了解存储过程以及触发器的概念o 了解触发器的功能和类型o 掌握如何使用企业管理器创建和管理存储过程和触发器o 掌握如何使用T-SQL语句创建和管理存储过程和触发器3第一节、管理存储过程本节你将学习和了解到：1、讲述存储过程如何创建并收集相关信息2、系统如何对存储过程进行处理3、如何对存储过程传入和传出数据4 存储过程（存储过程（Stored Procedure） 是存放在SQL SEVER中的预先编译好了的SQL程序。由于存储过程是预先编译好了的，

2、因此他们具有各种类型查询的最好性能。1、存储过程是以一个名称存储在数据库中，可以作为一个独立的数据对象，也可以作为一个单元在数据库中被用户调用。2、存储过程可以接收和输出数据、参数以及返回执行存储过程的状态值，还可以嵌套使用。3、存储过程提供了标准的SQL语言所没有的高级特性，其传递参数和执行逻辑表达式的功能，有助于应用程序设计者处理复杂的数据任务。4、存储过程是工作在服务器上的，从而有效的减少C/S频繁访问的数据流量，减少数据操作所需要的网络带宽和数据流量5、存储过程使得开发者不必在客户端开发大量的程序代码，同时在数据库的安全性上面得到提高。一、存储过程的基本概念52、存储过程的运行特点o存

3、储过程是存放在SQL SEVER中的特别快的存储对象，当首次运行存储过程的时候，它将按照以下的方式进行：（1）该过程被分解成为部件对象。（2）检查数据库中对象（表、视图）是否存在，这被称为是分解。（3）分解成功后，该过程名称被存放在sysbject表中，创建存储过程的代码被放在syscomments表中（4）编译，并且编译过程中将创建如何运行查询蓝本。该蓝本被称作是查询计划或查询树，查询树存放在sysprocedures表中。（5）存储过程首次执行时候读出查询计划并完全编译成为过程计划。今后的数据操作中将按照这样的计划执行，从而节约了每次执行存储过程的语法检查、分解和编译查询树的执行时间。（6

4、）存储过程的最大好处是：一旦执行了存储过程，过程计划将存储到cache中，这样在下次运行该存储过程的时候，将直接从cache中读取并运行，大大提高了查询的速度究竟是什么让存储过程这么了不起呢？6存储过程的好处（2）1、存储过程封装事务，一旦封装完毕，该封装可以用于多个应用，从而有一致的数据接口。这样带来的好处就是：如果改变过程的功能，只需要在一个地方修改，而不必要对每一个应用进行修改。2、通过存储过程可以传入参数并返回参数3、执行速度快，工作效率高；规范化程序设计。4、提高系统的安全性能。7创建存储过程的规则o 引用的对象必须在创建存储过程前就存在。o 不能在单个存储过程中再创建同名的存储过程

5、o 存储过程最多有255个参数o 存储过程不能够执行下列的语句： create procedure,rule,viewo 存储过程创建的文本不可以超过64kb 8二、使用管理平台创建存储过程1、创建存储过程9三、使用T-SQL管理存储过程1、创建存储过程（语法如下）：CREATE PROCEDURE procedure_name;number/指定的存储过程名称/number用来对同名过程进行分组，以便用一条drop procedure就可以将同组的过程一并除去/parameter data_type/指定存储过程的参数名称以及类型/ VARYING=defaultOUTPUT/指定输出参数支

6、持的结果集（仅适合于带游标的参数）/默认数值/用来指定参数是可以返回的，可以将该信息返回给调用的过程/,nWITH RECOMPILE|ENCRYPTION|RECOMPILE, ENCRYPTIONFOR REPLICATION/指定每执行一次都要重新编译/SQL需要加密syscomments表中的内容/该存储过程只能够在数据数据复制的时候使用，本选项不能够和with recompile联合使用/AS sql_statementn/该项包含T-SQL/10存储过程编程例子例例1.在在school数据库中创建一个存储过程，用于返回工资收入高于教师平均收入的教师情况，并数据库中创建一个存储过程，

7、用于返回工资收入高于教师平均收入的教师情况，并按照他们的工资降序排列按照他们的工资降序排列use school-如果存在同名的存储过程，先删除之if exists(select name from sysobjects where name=higher_sal)drop procedure higher_salgo-新建存储过程create procedure higher_salasselect *from teacherwhere salary (select avg(salary) from teacher)order by salary descgo-执行该存储过程exec high

8、er_salgo112、执行存储过程例子TSQL用EXECUTE来执行存储过程，语法如下：EXECUTEreturn_status= procedure_name ;number/return_status是整型局部变量，用于保存存储过程的返回值/指定保存的存储过程名称/指定该存储过程与其他同名的存储过程同组时候的标号/parameter=value|variableOUTPUT|DEFAULT,N/parameter是在创建的时候定义的过程参数。调用者向存储过程所传递的参数值由value参数或variable变量提供/WITH RECOMPILE/指定实行存储过程的时候重新编译执行计划/12

9、2、执行存储过程例子注意：注意：在执行存储过程中，如果使用WITH RECOMPILE选项可以带来的好处只要体现在下面两点之中：n 在create procedure中使用with recompile后，执行计划将不被存放到CACHE中，每次执行的时候都要重新编译整个过程，这与标准的查询方式非常类似该种方式对于执行效率低的参数非常有用，通过每次的重新编译，过程可以针对新的参数进行优化执行。n 在Exec procedure 中使用with recompile则可以将执行的过程一次性的打入到cache中，以供后面程序中exec proc 的快速调用。13关于存储过程输入参数附值的问题为了给存储过

10、程的输入参数附值，有两种方式可以选择：第一种：根据参数名称给输入参数附值。parameter_name=value第二种：根据参数定义时候的顺序附值parameter value1， value2，.14输入参数附值举例use schoolif exists(select name from sysobjects where name=stu_info)drop proc stu_infogocreate proc stu_info stu_name varchar(20), stu_grade floatasselect student.sno,sname,cname,degreefrom

11、student,score,coursewhere student.sno=score.sno and o=o and sname=stu_name and degreestu_gradego-第一种方式exec stu_info stu_name=李军,stu_grade=70-第二种方式exec stu_info 李军,7015关于存储过程的返回值调用存储过程语句中，必须有可以接收返回值的变量。调用存储过程语句中，必须有可以接收返回值的变量。例如：例如：use schoolif exists(select name from sysobjects where name=stu_object

12、1)drop proc stu_object1gocreate proc stu_object1class varchar(10),avg_degree float outputasselect avg_degree=avg(degree)from student,scorewhere student.sno=score.sno and class=classgodeclare avgdegree floatexecute stu_object1 95031,avgdegree outputprint 95031班同学的平均成绩是：+rtrim(cast(avgdegree as float)

13、-rtrim（）截断所有尾随空格后返回一个字符串（）截断所有尾随空格后返回一个字符串。-cast（）数值型转换成为字符型（）数值型转换成为字符型16-输入一个学生的学号,可以知道他的平均成绩create procedure student_avg sno varchar(12),stu_avg int outputasselect stu_avg=avg(degree)from scorewhere sno=snogroup by snodeclare stu_avg int ,char char(30)set char=该同学的平均成绩是:exec student_avg 103,stu_a

14、vg outputprint rtrim(char)+ltrim(cast(stu_avg as char(10)存储过程编程例子17存储过程编程例子-在数据库在数据库school中创建存储过程中创建存储过程avg_degree，用于查询每门课程的平均成绩。，用于查询每门课程的平均成绩。-该存储过程包含一个输入参数、一个输出参数和一个返回值该存储过程包含一个输入参数、一个输出参数和一个返回值use school-如果存在过程名称为av_degree，则删除之if exists(select name from sysobjects where name=av_degree)drop proce

15、dure av_degreego-开始创建存储过程av_degree-输入参数avg_cno用于传递课程号码-输入参数avg_deg用于传递平均成绩create procedure av_degreeavg_cno varchar(10),avg_deg float OUTPUTas-声明和初始化一个变量，用于保存返回值declare errorsave intset errorsave=018存储过程编程例子select avg_cno=cno,avg_deg=avg(degree)from scoregroup by cnoif (error0) set errorsave=errorre

16、turn errorsavego-声明变量，用于保存返回值和输出参数声明变量，用于保存返回值和输出参数declare returnvalue char(10),avg floatexec returnvalue=av_degree 101,avg outputprint 执行结果如下：print 您所查询的返回值是+cast(returnvalue as char(2)print 101号课程的平均成绩是：+cast(avg as char(10)19存储过程也可以在insert语句中使用例子use schoolif exists(select name from sysobjects whe

17、re name=avg_view)drop view avg_viewgocreate view avg_viewasselect sno,avg(degree) as avg_degreefrom scoregroup by snogoif exists(select name from sysobjects where name=avg_stu)drop view avg_stugocreate view avg_stu(sno,sname,class,avg_degree)asselect student.sno,student.sname,student.class,avg_view.

18、avg_degreefrom avg_view,studentwhere avg_view.sno=student.snogo20if exists(select name from sysobjects where name=avg_prof)drop procedure avg_profgocreate procedure avg_profas(select * from avg_stu where avg_degree=80)goif exists(select name from sysobjects where name=avg_table)drop table avg_tableg

19、ocreate table avg_table(sno char(10),sname char(10),class char(10),avg_degree float)insert into avg_table(sno,sname,class,avg_degree)exec avg_profgoselect * from avg_table存储过程也可以在存储过程也可以在insert语句中使用例子语句中使用例子213、查看存储过程（1）查看存储过程的文本EXEC sp_helptext prof_name（2）查看存储过程的相关性EXEC sp_depends prof_name（3）查看存储

20、过程的所有者、类型、创建日期和参数EXEC sp_help prof_name224、修改存储过程使用ALTER procedure T_SQL语句修改存储过程，语法如下：ALTER procedure procedure_name ;number /指定的存储过程名称/number用来对同名过程进行分组，以便用一条drop procedure就可以将同组的过程一并除去/ parameter data_type /指定存储过程的参数名称以及类型/ VARYING=defaultOUTPUT /指定输出参数支持的结果集（仅适合于带游标的参数）/默认数值/用来指定参数是可以返回的，可以将该信息返回

21、给调用的过程/WITH RECOMPILE|ENCRYPTION| RECOMPILE,ENCRYPTIONFOR REPLICATION/指定每执行一次都要重新编译/SQL需要加密syscomments表中的内容/该存储过程只能够在数据数据复制的时候使用，本选项不能够和with recompile联合使用/AS sql_statementn/该项包含T-SQL/23修改存储过程注意事项1. 只有存储过程的创建者、db_owner成员才可以修改存储过程。2. 在CREATE procedure 语句中使用的选项也必须在alter procedure语句中使用3. 一般希望对数据库中现存的存储过

22、程进行修改，又不希望改动存储过程设置的相关权限的时候，多应考虑使用alter procedure语句24修改存储过程例子alter procedure avg_profas(select * from avg_stu where avg_degree=70)GOexec avg_prof255、删除存储过程Drop procedure procedure_name266、创建一组存储过程我们再看一下存储过程的语法结构(number)：ALTER procedure procedure_name ;number“Number”选项是通过分号和数字，创建一组存储过程。一组存储过程通常是为了同一个同

23、一个应用程序使用的。这样，维护工作将变得非常简单，因为一个特定应用程序使用的所有过程出自同一个组。27创建一组存储过程的例子创建一组存储过程的例子use samplegocreate proc group_part;1asselect * from 员工数据表 where 所属部门=办公室gocreate proc group_part;2asselect * from 员工数据表 where 所属部门=项目部gocreate proc group_part;3asselect * from 员工数据表 where 所属部门=录入部go28创建一组存储过程的例子创建一组存储过程的例子下面就可以

24、分别执行他们了：exec group_part;3exec group_part;2exec group_part;1学习提示：1、如果一组存储过程创建了，则你不能够仅仅单独删除单个的存储过程，而必须删除整个的存储过程组并且重新创建一个新的存储过程组。2、在创建存储过程的时候，首先系统会检查SQL语句，这样可以避免出现意外。3、在删除存储过程组的时候，只要删除存储过程的名字，所有组的部件就会被全部删除了。如：drop procedure dbo.group_part297、在存储过程中使用参数-首先，请看这样的一个例子：use schoolgo-创建视图，求各班在每一门课程的平均成绩是多少？i

25、f exists(select name from sysobjects where name=claview_avg)drop view claview_avggocreate view claview_avgas(select class,o,avg(degree) as class_avgfrom student,scorewhere student.sno=score.snogroup by class,cno)Go-未完，见下页307、在存储过程中使用参数/*开始定义变量class，以保存班级的班号*/if exists(select name from sysobjects whe

26、re name=clafun_avg)drop function clafun_avggo-创建函数，各班每门课程的平均成绩情况Create function clafun_avg(class char(10)Returns order_table table /*定义返回数据表*/(班号 char(10),课程号 char(10),课程名称 char(10),平均成绩 float)AS-下一页中，开始定义查询317、在存储过程中使用参数Begininsert order_table select claview_avg.class,o,ame,class_avg from claview_a

27、vg,course where claview_o=o and claview_avg.class=class ReturnEndGo-未完，下页继续327、在存储过程中使用参数-下面我们学习在存储过程中使用参数if exists(select name from sysobjects where name=scores_class)drop proc scores_classgo-创建存储过程create proc scores_classclass1 float,class2 float,class_avg float output-此处必须定义一个输出的变量值asselect class

28、_avg=(class1+class2)/2go337、在存储过程中使用参数-下面学习的是如何利用参数来调用这个存储过程declare class1 float,class2 float,class_avg floatset class1=(select avg(平均成绩) from clafun_avg(95031)set class2=(select avg(平均成绩) from clafun_avg(95033)exec scores_class class1,class2,class_avg output-以上内容是必须同存储过程对应的参数传接进去，特别是输出的参数定义print 各班

29、的平均成绩应该是：+str(class_avg)+分go348、存储过程中也可以使用、存储过程中也可以使用with recompile选项选项1）在create procedure中使用with recompile后，执行计划将不被存放到CACHE中，每次执行的时候都要重新编译整个过程，这与标准的查询方式非常类似该种方式对于执行效率低的参数非常有用，通过每次的重新编译，过程可以针对新的参数进行优化执行。2）在Exec procedure 中使用with recompile则可以将执行的过程一次性的打入到cache中，以供后面程序中exec proc 的快速调用35第二节、管理触发器触发器是一种

30、特殊类型的存储过程，当使用update,insert或者delete命令时，触发器将自动执行；这样做的目的是维护数据的完整性。本节中，你将学习到：1、了解触发器的概念2、了解触发器的功能和类型3、掌握如何使用企业管理器和TSQL语句创建和管理触发器。36一、触发器的作用一般触发器将完成以下的一些功能：1、级联修改数据库中的相关表。2、执行比完整性约束更为复杂的约束操作。3、拒绝或回滚违反引用完整性的操作。4、修改前后数据之间的差别37二、触发器类型和触发操作触发器有五种类型：Update,insert,delete,instead of,after类型触发器1、在表上进行更新操作时候将激发up

31、date触发器。2、在表上进行插入操作时候将激发insert触发器。3、在表上进行删除操作时候将激发delete触发器。4、不插入、更新或删除动作时候，将激发instead of 触发器。5、after触发器则在一个触发动作发生并完成之后，并提供一种机制以便控制多个触发器的执行顺序。38三、使用T-SQL管理触发器1、创建触发器Create trigger trigger_nameOn table/viewfor after/instead of insert/update/deleteAsSql_statement39创建触发器示例use schoolgocreate trigger wel

32、com_stu on studentafter insertasprint 欢迎你，新同学！gocreate trigger byby_stu on studentafter deleteasprint 再见，我的同学！go40创建触发器示例下面你插入一条数据看看：insert into student(sno,sname,sex,birthday,class)values(99120,张星星,男,1997-02-21,9912)然后，你再删除一条数据看看：delete from studentwhere sname=张星星41四、确保数据的完整性注意：关于inserted表和deleted表

33、触发器中使用了两种特殊的表inserted表和deleted表Inserted表：用于存储insert和update语句所影响的副本Deleted表：用于存储deleted和update语句所影响的副本,在执行deleted时候，行被删除的时候会将信息传输到deleted表。 实验案例：首先我们知道，如果一个学生的信息已经从student中被删除了，那么这个学生的成绩信息就不应该再在score表中存在了。作为关系性数据库，应该从这层逻辑层面上解决：请在student表中建立存储过程：CREATE trigger score_stu on student for deleteasdelete score from deleted where deleted.sno=score.sno42四、确保数据的完整性下面试试删除：use schooldelete from student where sno=108例子2：问题：如果插入score表中没有stud