sqlserver数据库原理及应用第八章-存储过程和触发器ppt课件.ppt
火灾袭来时要迅速疏散逃生，不可蜂拥而出或留恋财物，要当机立断，披上浸湿的衣服或裹上湿毛毯、湿被褥勇敢地冲出去第第8章：章：存储过程和触发器8.18.1存储过程的概念存储过程的概念 存储过程是存储过程是SQLSQL语句和可选控制流语句和可选控制流语句的预编译集合，它以一个名字存语句的预编译集合，它以一个名字存储并作为一个单元处理。储并作为一个单元处理。火灾袭来时要迅速疏散逃生，不可蜂拥而出或留恋财物，要当机立断，披上浸湿的衣服或裹上湿毛毯、湿被褥勇敢地冲出去8.28.2存储过程的分类存储过程的分类 在在SQL ServerSQL Server中存储过程分为两类，即中存储过程分为两类，即系统系统提供的提供的存储过程存储过程和和用户自定义的存储过用户自定义的存储过程程。第第8章：章：存储过程和触发器火灾袭来时要迅速疏散逃生，不可蜂拥而出或留恋财物，要当机立断，披上浸湿的衣服或裹上湿毛毯、湿被褥勇敢地冲出去1)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.3.使用存储过程的优点使用存储过程的优点1)1)实现模块化编程实现模块化编程2)2)使用存储过程可以加快程序的运行速度使用存储过程可以加快程序的运行速度一个存储过程可以被多个用户共享和重用。一个存储过程可以被多个用户共享和重用。存储过程在创建时即在服务器上进行编译，存储过程在创建时即在服务器上进行编译，所以执行起来比单个所以执行起来比单个sqlsql语句快。语句快。第第8章：章：存储过程和触发器火灾袭来时要迅速疏散逃生，不可蜂拥而出或留恋财物，要当机立断，披上浸湿的衣服或裹上湿毛毯、湿被褥勇敢地冲出去3)3)使用存储过程可以减少网络流量使用存储过程可以减少网络流量存储过程存储在数据库内，由应用程序通过存储过程存储在数据库内，由应用程序通过一个调用语句就可以执行它，不需要将大量一个调用语句就可以执行它，不需要将大量T-SQLT-SQL语句传送到服务器端。语句传送到服务器端。4)4)使用存储过程可以提高数据库的安全性使用存储过程可以提高数据库的安全性用户可以调用存储过程，实现对表中数据的用户可以调用存储过程，实现对表中数据的有限操作，但可以不赋予其直接修改数据表有限操作，但可以不赋予其直接修改数据表的权限，这样就提高了表中数据的安全性。的权限，这样就提高了表中数据的安全性。第第8章：章：存储过程和触发器火灾袭来时要迅速疏散逃生，不可蜂拥而出或留恋财物，要当机立断，披上浸湿的衣服或裹上湿毛毯、湿被褥勇敢地冲出去4.4.存储过程创建存储过程创建1)1)使用企业管理器创建存储过程使用企业管理器创建存储过程2)2)使用使用T-SQLT-SQL创建存储过程创建存储过程第第8章：章：存储过程和触发器火灾袭来时要迅速疏散逃生，不可蜂拥而出或留恋财物，要当机立断，披上浸湿的衣服或裹上湿毛毯、湿被褥勇敢地冲出去3)创建存储过程应注意的事项：创建存储过程应注意的事项：存储过程是数据库对象，其名称必须遵守标识符规则。存储过程是数据库对象，其名称必须遵守标识符规则。不能将不能将CREATE PROCEDURECREATE PROCEDURE语句与其他语句与其他SQLSQL语句组合语句组合到单个批处理中。到单个批处理中。只能在当前数据库中创建存储过程。创建存储过程的只能在当前数据库中创建存储过程。创建存储过程的权限默认属于数据库所有者，该所有者可将此权限授权限默认属于数据库所有者，该所有者可将此权限授予其他用户。予其他用户。第第8章：章：存储过程和触发器火灾袭来时要迅速疏散逃生，不可蜂拥而出或留恋财物，要当机立断，披上浸湿的衣服或裹上湿毛毯、湿被褥勇敢地冲出去5.5.执行存储过程执行存储过程1)1)可以使用可以使用EXECUTEEXECUTE命令执行存储过程命令执行存储过程USE USE schoolEXEC p1EXEC p12)2)或直接写存储过程的名称或直接写存储过程的名称(如果存储过程是批处理的如果存储过程是批处理的第一条语句第一条语句)：USE USE schoolGO GO-批处理以批处理以GOGO结束结束P1P1GOGO第第8章：章：存储过程和触发器火灾袭来时要迅速疏散逃生，不可蜂拥而出或留恋财物，要当机立断，披上浸湿的衣服或裹上湿毛毯、湿被褥勇敢地冲出去6.6.修改存储过程修改存储过程1)1)使用企业管理器修改存储过程使用企业管理器修改存储过程(1)(1)重命名重命名(2)(2)修改定义修改定义第第8章：章：存储过程和触发器火灾袭来时要迅速疏散逃生，不可蜂拥而出或留恋财物，要当机立断，披上浸湿的衣服或裹上湿毛毯、湿被褥勇敢地冲出去2)2)使用使用T-SQLT-SQL语句修改存储过程语句修改存储过程(1)重命名重命名 sp_rename 原名称原名称,新名称新名称,object(2)修改定义修改定义ALTER PROCEDURE authorAS第第8章：章：存储过程和触发器火灾袭来时要迅速疏散逃生，不可蜂拥而出或留恋财物，要当机立断，披上浸湿的衣服或裹上湿毛毯、湿被褥勇敢地冲出去7.7.删除存储过程删除存储过程1)1)使用企业管理器修改存储过程使用企业管理器修改存储过程2)2)使用使用T-SQLT-SQL语句修改存储过程语句修改存储过程DROP PROCPROCEDUTE sproc_name第第8章：章：存储过程和触发器火灾袭来时要迅速疏散逃生，不可蜂拥而出或留恋财物，要当机立断，披上浸湿的衣服或裹上湿毛毯、湿被褥勇敢地冲出去8.8.存储过程参数化存储过程参数化(重点、难点重点、难点)存储过程为我们提供了执行某种过程的能力，但是，存储过程为我们提供了执行某种过程的能力，但是，如果它不能接受让其进行操作的某种数据，那么在大如果它不能接受让其进行操作的某种数据，那么在大多数环境下来就没有用处。例如，建立一个删除表中多数环境下来就没有用处。例如，建立一个删除表中数据的存储过程，要知道删除满足什么条件的记录。数据的存储过程，要知道删除满足什么条件的记录。同样，有时候我们也想让存储过程输出一些信息，例同样，有时候我们也想让存储过程输出一些信息，例如，我们建立一个更新表中数据的存储过程，一般情如，我们建立一个更新表中数据的存储过程，一般情况需要知道到底更新了多少条记录，等等。要想实现况需要知道到底更新了多少条记录，等等。要想实现上面的功能，就需要建立带有参数的存储过程。上面的功能，就需要建立带有参数的存储过程。第第8章：章：存储过程和触发器火灾袭来时要迅速疏散逃生，不可蜂拥而出或留恋财物，要当机立断，披上浸湿的衣服或裹上湿毛毯、湿被褥勇敢地冲出去1)1)创建带有输入参数的存储过程创建带有输入参数的存储过程例题1：创建一个向表创建一个向表studentstudent中输中输入数据的存储过程。入数据的存储过程。第第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个参数，并且个参数，并且因为没有给这些参数提供默认值，所以为了成功运行该存储因为没有给这些参数提供默认值，所以为了成功运行该存储过程，必须提供这些参数值。过程，必须提供这些参数值。如果执行如下语句：如果执行如下语句：exec spinsert 112,李四李四,男男,1985-1-2 其中少了一个参数，尽管在基本表中该字段允许为空，其中少了一个参数，尽管在基本表中该字段允许为空，但是此但是此proc也不能被成功执行。也不能被成功执行。第第8章：章：存储过程和触发器火灾袭来时要迅速疏散逃生，不可蜂拥而出或留恋财物，要当机立断，披上浸湿的衣服或裹上湿毛毯、湿被褥勇敢地冲出去例题2：2)2)创建带有输入参数的存储过程，同时给参创建带有输入参数的存储过程，同时给参数提供默认值。数提供默认值。第第8章：章：存储过程和触发器火灾袭来时要迅速疏散逃生，不可蜂拥而出或留恋财物，要当机立断，披上浸湿的衣服或裹上湿毛毯、湿被褥勇敢地冲出去Use schoolGOCREATE PROC spinsertsno char(5),sname char(8),ssex char(2),sbirthday datetime,class char(5)=95031-(或者或者class char(5)=null)AS INSERT INTO student(sno,sname,ssex,sbirthday,class)VALUES(sno,sname,ssex,sbirthday,class)火灾袭来时要迅速疏散逃生，不可蜂拥而出或留恋财物，要当机立断，披上浸湿的衣服或裹上湿毛毯、湿被褥勇敢地冲出去exec spinsert 112,李四李四,男男,1985-1-2执行存储过程执行存储过程:此时，该此时，该procproc执行成功执行成功!3)3)创建带有输出参数的存储过程创建带有输出参数的存储过程第第8章：章：存储过程和触发器火灾袭来时要迅速疏散逃生，不可蜂拥而出或留恋财物，要当机立断，披上浸湿的衣服或裹上湿毛毯、湿被褥勇敢地冲出去Use schoolGOCREATE PROC averagest_no int,st_name char(8)output,st_avg float outputASSelect st_name=student.sname,st_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 st_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.9.存储过程的返回值存储过程的返回值例题例题1：1)创建存储过程创建存储过程 create proc spreturn as declare a1 char(30)set a1=下面是存储过程的返回值：下面是存储过程的返回值：print a1第第8章：章：存储过程和触发器火灾袭来时要迅速疏散逃生，不可蜂拥而出或留恋财物，要当机立断，披上浸湿的衣服或裹上湿毛毯、湿被褥勇敢地冲出去 declare b int exec b=spreturn print b可以看到运行结果是：下面是存储过程的返回值：0存储过程在执行后都会返回一个整型值。如果执行成存储过程在执行后都会返回一个整型值。如果执行成功，返回功，返回0 0；否则返回；否则返回-1-1到到-99-99之间的数值。之间的数值。0是存储过程的返回值。第第8章：章：存储过程和触发器2)运行存储过程运行存储过程火灾袭来时要迅速疏散逃生，不可蜂拥而出或留恋财物，要当机立断，披上浸湿的衣服或裹上湿毛毯、湿被褥勇敢地冲出去例如：执行前面创建的存储过程例如：执行前面创建的存储过程 spinsertdeclare b intexec b=spinsert 112,李四,男,1985-1-2,95031print b执行两次上面的语句，第二次就不能成功执行了。所以执行两次上面的语句，第二次就不能成功执行了。所以返回值是个非返回值是个非0值。值。第第8章：章：存储过程和触发器火灾袭来时要迅速疏散逃生，不可蜂拥而出或留恋财物，要当机立断，披上浸湿的衣服或裹上湿毛毯、湿被褥勇敢地冲出去格式：格式：return 注意：注意：返回值必须是整数。返回值必须是整数。Return语句的最大特点是：语句的最大特点是：一旦执行了一旦执行了return语语句，那么系统就无条件的从过程中退出。也就是句，那么系统就无条件的从过程中退出。也就是说，无论在过程的哪个位置执行了说，无论在过程的哪个位置执行了return语句，语句，就再也不会执行该过程中的语句了。就再也不会执行该过程中的语句了。例如在第六章讲到的例题：例如在第六章讲到的例题：第第8章：章：存储过程和触发器火灾袭来时要迅速疏散逃生，不可蜂拥而出或留恋财物，要当机立断，披上浸湿的衣服或裹上湿毛毯、湿被褥勇敢地冲出去declare a1 intset a1=1print a1return set a1=2print a1return我们知道过程运行结果是我们知道过程运行结果是1 1，这就说明了没有执行第一个，这就说明了没有执行第一个returnreturn后面的语句。如果把第一个后面的语句。如果把第一个returnreturn删除再运行过程，删除再运行过程，会得到会得到1 1和和2 2。证明这次过程中的语句都被执行了。证明这次过程中的语句都被执行了。第第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。这是因为系统本身有返回值，如果过程成功执行，返这是因为系统本身有返回值，如果过程成功执行，返回回0。因此，例题。因此，例题1默认的返回了默认的返回了0。但是，我们也可。但是，我们也可以让过程返回我们希望得到的值，例如例题以让过程返回我们希望得到的值，例如例题2。一般。一般情况下，我们都给情况下，我们都给return指定返回值。指定返回值。第第8章：章：存储过程和触发器火灾袭来时要迅速疏散逃生，不可蜂拥而出或留恋财物，要当机立断，披上浸湿的衣服或裹上湿毛毯、湿被褥勇敢地冲出去说明：说明：返回值返回值(return(return实现实现)与输出参与输出参数不是一回事。但有的情况，两者可数不是一回事。但有的情况，两者可以完成相同的功能。以完成相同的功能。例如：例如：创建一个创建一个proc，功能是：，功能是：求求1-N1-N的和。的和。要求：要求：使用输出参数和返回值两种方式来实现。使用输出参数和返回值两种方式来实现。第第8章：章：存储过程和触发器火灾袭来时要迅速疏散逃生，不可蜂拥而出或留恋财物，要当机立断，披上浸湿的衣服或裹上湿毛毯、湿被褥勇敢地冲出去执行：执行：declare a intdeclare a int exec exec p1 5,a outputp1 5,a output print a print a create proc p1n int,sum int outputasdeclare i intset sum=0set i=1while(i=n)beginset sum=sum+iset i=i+1end create proc p1create proc p1n intn intasasdeclare sum int,i intdeclare sum int,i intset sum=0set sum=0set i=1set i=1while(i=n)while(i100 or 100 or degree0)0)print print 成绩值超出范围成绩值超出范围 else else insert into score insert into score select*from inserted select*from inserted end end第第8章：章：触发器火灾袭来时要迅速疏散逃生，不可蜂拥而出或留恋财物，要当机立断，披上浸湿的衣服或裹上湿毛毯、湿被褥勇敢地冲出去 执行如下操作：执行如下操作：insert into scoreinsert into score values(114,3-105,-values(114,3-105,-1)1)再执行如下操作：再执行如下操作：insert into score insert into score values(114,3-values(114,3-105,55)105,55)第第8章：章：触发器火灾袭来时要迅速疏散逃生，不可蜂拥而出或留恋财物，要当机立断，披上浸湿的衣服或裹上湿毛毯、湿被褥勇敢地冲出去2.2.使用使用updateupdate触发器触发器 使用使用UPDATEUPDATE触发器主要应用在两方面：触发器主要应用在两方面：1)1)在更新记录时，要求符合一定的规则；在更新记录时，要求符合一定的规则；2)2)实现级连更新。实现级连更新。例例1：禁止修改：禁止修改student表中学号为表中学号为101的学生的学生的姓名。的姓名。第第8章：章：触发器火灾袭来时要迅速疏散逃生，不可蜂拥而出或留恋财物，要当机立断，披上浸湿的衣服或裹上湿毛毯、湿被褥勇敢地冲出去create trigger c4create trigger c4on studenton studentinstead of updateinstead of updateasasbeginbeginif exists(select sno from deleted where sno=101)if exists(select sno from deleted where sno=101)print print 不能修改该生的姓名不能修改该生的姓名elseelseupdate update studentset set sname=(select=(select sname from inserted)from inserted)where where sno=(select=(select sno from deleted)from deleted)-此题也可以用此题也可以用where sno=(select sno from inserted)end end 火灾袭来时要迅速疏散逃生，不可蜂拥而出或留恋财物，要当机立断，披上浸湿的衣服或裹上湿毛毯、湿被褥勇敢地冲出去例例2：更新：更新student表中的表中的sno时，级连更新时，级连更新score表中的相关记录的表中的相关记录的sno。第第8章：章：触发器火灾袭来时要迅速疏散逃生，不可蜂拥而出或留恋财物，要当机立断，披上浸湿的衣服或裹上湿毛毯、湿被褥勇敢地冲出去create trigger c7on studentfor updateasbeginupdate scoreset sno=(select sno from inserted)where sno in(select sno from deleted)end注意：注意：如果在表上定义了级连更新，则不能创建如果在表上定义了级连更新，则不能创建UPDATE触发器。触发器。第第8章：章：触发器火灾袭来时要迅速疏散逃生，不可蜂拥而出或留恋财物，要当机立断，披上浸湿的衣服或裹上湿毛毯、湿被褥勇敢地冲出去3.3.使用使用deletedelete触发器触发器 使用使用DELETEDELETE触发器主要应用在两方面触发器主要应用在两方面：1)1)在删除记录时，要求符合一定的规则；在删除记录时，要求符合一定的规则；2)2)实现级连删除。实现级连删除。例例1：禁止删除禁止删除student表中姓名为表中姓名为张张丽丽的记录。的记录。第第8章：章：触发器火灾袭来时要迅速疏散逃生，不可蜂拥而出或留恋财物，要当机立断，披上浸湿的衣服或裹上湿毛毯、湿被褥勇敢地冲出去create trigger tri9on studentinstead 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章：章：触发器
