SQLSERVER数据库原理及应用 _存储过程和触发器.ppt

《SQLSERVER数据库原理及应用 _存储过程和触发器.ppt》由会员分享，可在线阅读，更多相关《SQLSERVER数据库原理及应用 _存储过程和触发器.ppt（78页珍藏版）》请在三一办公上搜索。

1、第8章：存储过程和触发器,8.1存储过程的概念,存储过程是SQL语句和可选控制流语句的预编译集合，它以一个名字存储并作为一个单元处理。,8.2存储过程的分类,在SQL Server中存储过程分为两类，即系统提供的存储过程和用户自定义的存储过程。,第8章：存储过程和触发器,1)系统存储过程：由系统自动创建，主要存储在master数据库中，一般以sp_为前缀。系统存储过程完成的功能主要是从系统表中获取信息。可以在其它数据库中调用系统存储过程。当创建一个新的数据库时，一些系统存储过程会在新数据库中被自动创建。,第8章：存储过程和触发器,2)用户自定义存储过程：由用户创建并能完成某一特定功能的存储过程

2、。,第8章：存储过程和触发器,CREATE PROC P1ASSelect sname,cname,degreefrom student,score,courseWhere student.sno=score.sno and o=o,例如：,第8章：存储过程和触发器,3.使用存储过程的优点,1)实现模块化编程,2)使用存储过程可以加快程序的运行速度,一个存储过程可以被多个用户共享和重用。,存储过程在创建时即在服务器上进行编译，所以执行起来比单个sql语句快。,第8章：存储过程和触发器,3)使用存储过程可以减少网络流量,存储过程存储在数据库内，由应用程序通过一个调用语句就可以执行它，不需要将大量

3、T-SQL语句传送到服务器端。,4)使用存储过程可以提高数据库的安全性,用户可以调用存储过程，实现对表中数据的有限操作，但可以不赋予其直接修改数据表的权限，这样就提高了表中数据的安全性。,第8章：存储过程和触发器,4.存储过程创建,1)使用企业管理器创建存储过程,2)使用T-SQL创建存储过程,第8章：存储过程和触发器,3)创建存储过程应注意的事项：,存储过程是数据库对象，其名称必须遵守标识符规则。,不能将CREATE PROCEDURE语句与其他SQL语句组合到单个批处理中。,只能在当前数据库中创建存储过程。创建存储过程的权限默认属于数据库所有者，该所有者可将此权限授予其他用户。,第8章：存

4、储过程和触发器,5.执行存储过程,1)可以使用EXECUTE命令执行存储过程USE schoolEXEC p1,2)或直接写存储过程的名称(如果存储过程是批处理的第一条语句)：USE schoolGO-批处理以GO结束P1GO,第8章：存储过程和触发器,6.修改存储过程,1)使用企业管理器修改存储过程(1)重命名(2)修改定义,第8章：存储过程和触发器,2)使用T-SQL语句修改存储过程(1)重命名 sp_rename 原名称,新名称,object(2)修改定义ALTER PROCEDURE authorAS,第8章：存储过程和触发器,7.删除存储过程,1)使用企业管理器修改存储过程2)使用T

5、-SQL语句修改存储过程DROP PROCPROCEDUTE sproc_name,第8章：存储过程和触发器,8.存储过程参数化(重点、难点),存储过程为我们提供了执行某种过程的能力，但是，如果它不能接受让其进行操作的某种数据，那么在大多数环境下来就没有用处。例如，建立一个删除表中数据的存储过程，要知道删除满足什么条件的记录。同样，有时候我们也想让存储过程输出一些信息，例如，我们建立一个更新表中数据的存储过程，一般情况需要知道到底更新了多少条记录，等等。要想实现上面的功能，就需要建立带有参数的存储过程。,第8章：存储过程和触发器,1)创建带有输入参数的存储过程,例题1：创建一个向表studen

6、t中输入数据的存储过程。,第8章：存储过程和触发器,Use schoolGOCREATE PROC spinsertsno char(5),sname char(8),ssex char(2),sbirthday datetime,class char(5)AS INSERT INTO student(sno,sname,ssex,sbirthday,class)VALUES(sno,sname,ssex,sbirthday,class),执行存储过程:exec spinsert 111,张三,男,1980-1-1,95031 上面例题创建的存储过程spinsert一共需要5个参数，并且因为没

7、有给这些参数提供默认值，所以为了成功运行该存储过程，必须提供这些参数值。如果执行如下语句：exec spinsert 112,李四,男,1985-1-2 其中少了一个参数，尽管在基本表中该字段允许为空，但是此proc也不能被成功执行。,第8章：存储过程和触发器,例题2：,2)创建带有输入参数的存储过程，同时给参数提供默认值。,第8章：存储过程和触发器,Use schoolGOCREATE PROC spinsertsno char(5),sname char(8),ssex char(2),sbirthday datetime,class char(5)=95031-(或者class char

8、(5)=null)AS INSERT INTO student(sno,sname,ssex,sbirthday,class)VALUES(sno,sname,ssex,sbirthday,class),exec spinsert 112,李四,男,1985-1-2,执行存储过程:,此时，该proc执行成功!,3)创建带有输出参数的存储过程,第8章：存储过程和触发器,Use schoolGOCREATE PROC averagest_no int,st_name char(8)output,st_avg float outputASSelect st_name=student.sname,st

9、_avg=avg(score.degree)From student,score where student.sno=score.snoGroup by student.sno,student.snameHaving student.sno=st_no,Declare st_name char(8)Declare st_avg floatExec average 108,st_name output,st_avg outputSelect 姓名=st_name,平均分=st_avg Go,执行存储过程:,第8章：存储过程和触发器,Declare st_name char(8)Declare s

10、t_avg floatExec average 108,st_name output,st_avg outputPrint st_name print st_avg Go,或者:,第8章：存储过程和触发器,Declare st_name char(8)Declare st_avg floatExec average 108,st_name output,st_avg outputPrint st_name+cast(st_avg as char(4)Go,或者:,第8章：存储过程和触发器,9.存储过程的返回值,例题1：1)创建存储过程 create proc spreturn as decla

11、re a1 char(30)set a1=下面是存储过程的返回值：print a1,第8章：存储过程和触发器,declare b int exec b=spreturn print b,可以看到运行结果是：下面是存储过程的返回值：0,存储过程在执行后都会返回一个整型值。如果执行成功，返回0；否则返回-1到-99之间的数值。,0是存储过程的返回值。,第8章：存储过程和触发器,2)运行存储过程,例如：执行前面创建的存储过程 spinsert,declare b intexec b=spinsert 112,李四,男,1985-1-2,95031print b,执行两次上面的语句，第二次就不能成功执

12、行了。所以返回值是个非0值。,第8章：存储过程和触发器,格式：return 注意：返回值必须是整数。,Return语句的最大特点是：一旦执行了return语句，那么系统就无条件的从过程中退出。也就是说，无论在过程的哪个位置执行了return语句，就再也不会执行该过程中的语句了。,例如在第六章讲到的例题：,第8章：存储过程和触发器,declare a1 intset a1=1print a1return set a1=2print a1return,我们知道过程运行结果是1，这就说明了没有执行第一个return后面的语句。如果把第一个return删除再运行过程，会得到1和2。证明这次过程中的语句

13、都被执行了。,第8章：存储过程和触发器,Return怎么用在存储过程中呢？,第8章：存储过程和触发器,修改例题1如下：,例题2：1)创建存储过程 create proc spreturn as declare a1 char(30)set a1=下面是存储过程的返回值：print a1 return 100-return,默认返回0,第8章：存储过程和触发器,2)运行存储过程 declare b int exec b=spreturn print b,通过例题可以看到，可以给return语句指定返回值。但例题1没有指定返回值，结果仍然有返回值0。这是因为系统本身有返回值，如果过程成功执行，返回

14、0。因此，例题1默认的返回了0。但是，我们也可以让过程返回我们希望得到的值，例如例题2。一般情况下，我们都给return指定返回值。,第8章：存储过程和触发器,说明：返回值(return实现)与输出参数不是一回事。但有的情况，两者可以完成相同的功能。,例如：创建一个proc，功能是：求1-N的和。要求：使用输出参数和返回值两种方式来实现。,第8章：存储过程和触发器,执行：declare a int exec p1 5,a output print a,create proc p1n int,sum int outputasdeclare i intset sum=0set i=1while(i

15、=n)beginset sum=sum+iset i=i+1end,create proc p1n intasdeclare sum int,i intset sum=0set i=1while(i=n)beginset sum=sum+iset i=i+1Endreturn sum,declare a intexec a=P1 5print a,小练习,第8章：存储过程和触发器,练习1:创建一个proc，功能是输入学号和课程号，能够显示相应的成绩?,select degreefrom scorewhere sno=101 and cno=2-124,用查询语句，显示学号为101，课程号为2-

16、124的学生的成绩。？,第8章：存储过程和触发器,第一步：,select degree from scorewhere sno=101 and cno=2-124,创建一个proc，功能是显示学号为101，课程号为2-124的学生的成绩。？,Create proc P1as,第8章：存储过程和触发器,第二步：,创建一个proc，功能是输入学号和课程号，能够显示相应的成绩?,第8章：存储过程和触发器,第三步：,create proc p1x1 int,x2 int,x int outputasselect x=degree from scorewhere sno=x1 and cno=x2,执行

17、proc：方式一declare x intexec p1 0301,0001,x outputprint x 或者声明其他名字的变量，例如：a,第8章：存储过程和触发器,或者是：方式二declare x intexec p1 x1=0301,x2=0001,x=x outputprint x,如果创建存储过程时，把参数的顺序改一下，即 create proc p1 x int output，x1 int,x2 int as.然后按方式一的语句执行，发现出现了问题。如果用方式二的语句执行，就可以了，执行语句如下：,第8章：存储过程和触发器,declare a intexec p1 x1=0301

18、,x2=0001,x=a outputprint a或者是（保证参数顺序一致）：declare x intexec p1 x output,0301,0001print x此时也能成功运行。,第8章：存储过程和触发器,结论：,1.运行proc时，如果输入的参数顺序正好与声明的参数顺序一致，那么，运行proc时，可以直接按顺序写出参数值，否则，就应该以赋值的形式给出参数值。2.输出结果的变量名不一定要和proc内部的参数名称相同。但输入参数必须相同。,第8章：存储过程和触发器,练习2：创建一个向表student中输入数据的存储过程，并且带有默认值。说明：基本表student中，ssex,clas

19、s允许为空。,第8章：存储过程和触发器,Use schoolGOCREATE PROC spinsertsno char(5),sname char(8),ssex char(2)=男,sbirthday datetime,class char(5)=95031AS INSERT INTO student(sno,sname,ssex,sbirthday,class)VALUES(sno,sname,ssex,sbirthday,class),exec spinsert 114,张三,男,1977-1-1此语句能正常执行，class默认为95031。如果想要让ssex字段为默认值男，该如何输入

20、？,exec spinsert 114,张四,1977-1-1,95032执行时，会提示有语法错误。,exec spinsert 114,张五,1977-1-1,95032或者exec spinsert 114,张六,null,1977-1-1,95032能成功执行，但与目的不相符(一个是空格，一个是null)。？,exec spinsert sno=114,sname=张七,sbirthday=1977-1-1,class=95032,结论：,1.运行proc时，如果想使用输入参数的默认值，除非此参数后面再没有输入参数，否则，就要以赋值的形式给出参数值。2.给输入参数赋值时，默认值、空格值(

21、即空白)、null是不同的概念。,第8章：存储过程和触发器,练习3：创建一个proc，功能是：根据输入的课程名称显示该选修课程的平均成绩。,create proc p5x char(10),x1 int outputasselect x1=avg(degree)from scoregroup by cno having cno in(select cno from course where cname=x),第8章：存储过程和触发器,执行：declare x1 intexec p5 操作系统,x1 outputprint x1,练习4：创建一个proc，功能是：显示每个学生的姓名、选修课程名称

22、和成绩。,第8章：存储过程和触发器,Create proc p1AsSelect sname,cname,degreeFrom student join score on student.sno=score.sno join course on So=o,执行proc：exec p1,第8章：存储过程和触发器,如果要求创建一个proc，功能是：显示指定学生的姓名、选修课程名称和成绩。？,Create proc p1name char(20)AsSelect sname,cname,degreeFrom student join score on student.sno=score.sno jo

23、in course on So=oWhere sname=name,第8章：存储过程和触发器,执行proc：exec p1 王丽,第8章：存储过程和触发器,小结：存储过程的特点,存储过程可以包含一条或多条T-SQL语句。存储过程可以接受输入参数并可以返回输出值。一个存储过程可以调用另一个存储过程。存储过程会返回执行情况的状态代码给调用它的程序。,第8章：存储过程和触发器,附加：,创建系统存储过程：创建系统存储过程的时候，与创建用户存储过程的不同之处：1)它们都是用sp_开头的；2)它们都驻留在master数据库中。,第8章：存储过程和触发器,第8章：存储过程和触发器,例：USE master

24、GO CREATE PROC sp_sum1 x1 int,x2 int as declare sum int set sum=x1+x2 print sum GO,第8章：存储过程和触发器,在任意一个数据库中都可以执行存储过程sp_sum1 exec sp_sum1 1,2,总结：存储过程是非常强大的数据库部件，系统存储过程对数据库维护和管理很有用，自定义存储过程对要其完成的任何任务均很有意义。相对视图和查询，其优势是它们经过了预编译，在首次执行后，其执行计划被存入驻留于RAM的过程cache中，另一个好处是即使用户对底层表没有权限，也可以赋给用户执行存储过程的权限。,第8章：存储过程和触发

25、器,上机试验：,1.练习前面讲的四个练习题。,2.创建一个proc1，检查score中是否有记录，如果有，返回1，否则返回0，然后在另外的过程proc2中调用proc1，如果有记录，输出存在记录，否则输出没有记录。,第8章：存储过程和触发器,上机试验：,3.在school数据库中创建一个proc，名称为p1，功能是如果找到指定学生和选修课程的记录，则用return语句返回1，否则返回0。并且在另外的过程中调用p1，查找学生李军是否选修了数字电路这门课程，如果选了，就输出李军同学选修了数字电路这门课程，否则，输出无此记录。(既有输入参数又有返回值)。,第8章：存储过程和触发器,一、触发器的定义

26、触发器是响应特定事件的一种特殊的存储过程。存储过程主要通过名字直接调用，而触发器是通过事件（UPDATE、INSERT、DELETE）触发而执行。二、触发器的类型1.根据触发触发器行为的不同，将触发器分为以下四种类型：,第8章：触发器,1)INSERT触发器,2)DELETE触发器,3)UPDATE触发器,4)以上几种类型的混合匹配触发器,第8章：触发器,2.根据触发触发器时间的不同，将触发器分为以下两种类型：1)AFTER触发器 在某一语句执行之后激活触发器。,2)INSTEAD OF触发器 在某一语句执行之前激活触发器，不执行其所定义的语句，只执行触发器本身。同一操作只能定义一个INSTE

27、AD OF 触发器。,第8章：触发器,三、创建触发器1.使用T-SQL语句创建触发器,create trigger trigger_nameon tablename|view_namefor|after|instead of delete,insert,update as sql statements,第8章：触发器,例题：当课程表发生改变时，显示信息发生改变。create trigger tri1 on course for delete,update,insert as print 信息发生改变,当课程表中记录被删除时，显示有记录被删除。？,create trigger tri2 on c

28、ourse for delete as print 有记录被删除,第8章：触发器,2.使用企业管理器创建触发器(P),第8章：触发器,CREATE TRIGGER语句必须是批处理中的第一条语句，并且只能应用到一个表中。,说明：,触发器中不允许使用以下语句：ALTER DATABASE、CREATE DATABASE、DROP DATABASE等。,第8章：触发器,在触发器执行时，会产生两个临时表：inserted表和deleted表。,四、insertde表和delete表(P),第8章：触发器,执行insert操作，插入到触发器表中的新行被插入到inserted表中。执行delete操作，从

29、触发器表中删除的行被插入 到deleted表中。执行update操作，先从触发器表中删除旧行，再插入新行。其中被删除的旧行被插入到delete表中，插入的新行被插入到inserted表中。(p288例15.2),第8章：触发器,五、使用触发器,1.使用Insert触发器,例如：在score表中插入记录时，成绩不能超出范围(即应在0100)。,使用INSERT触发器主要实现在插入时，限制一定的条件。,第8章：触发器,create trigger tri3on scoreinstead of insertAsbegin if exists(select degree from inserted w

30、here degree100 or degree0)print 成绩值超出范围 else insert into score select*from inserted end,第8章：触发器,执行如下操作：insert into score values(114,3-105,-1)再执行如下操作：insert into score values(114,3-105,55),第8章：触发器,2.使用update触发器,使用UPDATE触发器主要应用在两方面：1)在更新记录时，要求符合一定的规则；2)实现级连更新。,例1：禁止修改student表中学号为101的学生的姓名。,第8章：触发器,cre

31、ate trigger c4on studentinstead of updateasbeginif exists(select sno from deleted where sno=101)print 不能修改该生的姓名elseupdate studentset sname=(select sname from inserted)where sno=(select sno from deleted)-此题也可以用where sno=(select sno from inserted)end,例2：更新student表中的sno时，级连更新score表中的相关记录的sno。,第8章：触发器,c

32、reate trigger c7on studentfor updateasbeginupdate scoreset sno=(select sno from inserted)where sno in(select sno from deleted)end,注意：如果在表上定义了级连更新，则不能创建UPDATE触发器。,第8章：触发器,3.使用delete触发器,使用DELETE触发器主要应用在两方面：1)在删除记录时，要求符合一定的规则；2)实现级连删除。,例1：禁止删除student表中姓名为张丽的记录。,第8章：触发器,create trigger tri9on studentinst

33、ead of deleteAsbeginif exists(select sname from deleted where sname=张丽)print 不能删除该生记录elsedelete from student where sname=(select sname from deleted)end,第8章：触发器,例2：删除student表中记录时，级连删除score表中的所有相关记录。,create trigger c6on studentfor deleteAsbegindelete scorewhere sno in(select sno from deleted)End,第8章：触发器,六、修改触发器(P291),七、删除触发器(P292),第8章：触发器,