sqlserver数据库原理及应用存储过程和触发器

《sqlserver数据库原理及应用存储过程和触发器》由会员分享，可在线阅读，更多相关《sqlserver数据库原理及应用存储过程和触发器（78页珍藏版）》请在装配图网上搜索。

1、第第8章：章：存储过程和触发器存储过程的概念存储过程的概念 存储过程是存储过程是SQLSQL语句和可选控制流语句和可选控制流语句的预编译集合，它以一个名字存语句的预编译集合，它以一个名字存储并作为一个单元处理。储并作为一个单元处理。存储过程的分类存储过程的分类 在在SQL ServerSQL Server中存储过程分为两类，即中存储过程分为两类，即系统系统提供的提供的存储过程存储过程和和用户自定义的存储过用户自定义的存储过程程。第第8章：章：存储过程和触发器1)1)系统存储过程：由系统自动创立，主要存系统存储过程：由系统自动创立，主要存储在储在mastermaster数据库中，一般以数据库中，

2、一般以sp_sp_为前缀。系为前缀。系统存储过程完成的功能主要是从系统表中获统存储过程完成的功能主要是从系统表中获取信息。可以在其它数据库中调用系统存储取信息。可以在其它数据库中调用系统存储过程。当创立一个新的数据库时，一些系统过程。当创立一个新的数据库时，一些系统存储过程会在新数据库中被自动创立。存储过程会在新数据库中被自动创立。第第8章：章：存储过程和触发器2)2)用户自定义存储过程：由用户创立并能完用户自定义存储过程：由用户创立并能完成某一特定功能的存储过程。成某一特定功能的存储过程。第第8章：章：存储过程和触发器CREATE PROC P1ASSelect sname,cname,de

3、greefrom student,score,courseWhere=and=例如：例如：第第8章：章：存储过程和触发器3.3.使用存储过程的优点使用存储过程的优点1)1)实现模块化编程实现模块化编程2)2)使用存储过程可以加快程序的运行速度使用存储过程可以加快程序的运行速度一个存储过程可以被多个用户共享和重用。一个存储过程可以被多个用户共享和重用。存储过程在创立时即在效劳器上进行编译，存储过程在创立时即在效劳器上进行编译，所以执行起来比单个所以执行起来比单个sqlsql语句快。语句快。第第8章：章：存储过程和触发器3)3)使用存储过程可以减少网络流量使用存储过程可以减少网络流量存储过程存储在

4、数据库内，由应用程序通过存储过程存储在数据库内，由应用程序通过一个调用语句就可以执行它，不需要将大量一个调用语句就可以执行它，不需要将大量T-SQLT-SQL语句传送到效劳器端。语句传送到效劳器端。4)4)使用存储过程可以提高数据库的平安性使用存储过程可以提高数据库的平安性用户可以调用存储过程，实现对表中数据的用户可以调用存储过程，实现对表中数据的有限操作，但可以不赋予其直接修改数据表有限操作，但可以不赋予其直接修改数据表的权限，这样就提高了表中数据的平安性。的权限，这样就提高了表中数据的平安性。第第8章：章：存储过程和触发器4.4.存储过程创立存储过程创立1)1)使用企业管理器创立存储过程使

5、用企业管理器创立存储过程2)2)使用使用T-SQLT-SQL创立存储过程创立存储过程第第8章：章：存储过程和触发器3)创立存储过程应注意的事项：创立存储过程应注意的事项：存储过程是数据库对象，其名称必须遵守标识符规那存储过程是数据库对象，其名称必须遵守标识符规那么。么。不能将不能将CREATE PROCEDURECREATE PROCEDURE语句与其他语句与其他SQLSQL语句组合语句组合到单个批处理中。到单个批处理中。只能在当前数据库中创立存储过程。创立存储过程的只能在当前数据库中创立存储过程。创立存储过程的权限默认属于数据库所有者，该所有者可将此权限授权限默认属于数据库所有者，该所有者可

6、将此权限授予其他用户。予其他用户。第第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)(

7、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、难点)存储过程为我们提供了执行某种过程的能力，但是，存储过程为我们提供了执行某种过程的能力，但是，如果它不能接受让其进行操作的某种数据，那么在大如果它不能接受让其进行操作的某种数据，那么在大多数环境下来就没有用处。例如，建立一个删除表中多数环境下来就没有用处。例如，建立一个删除表中数据的存储过程，要知道删除满足什么条件的记录。数据的存储过程，要知道删除满足什么条件的记录。同样，有时候我们也想让存储过程输出一些信息，例同样，有时候我们也想让存储过程输出一些信息，例如，我们建立一个更新表中数据的存储过程，一般情如，我们建立一个更新表中数据的存储过程，一般情况需要知道到底更新了多少条记录，等等。要想

9、实现况需要知道到底更新了多少条记录，等等。要想实现上面的功能，就需要建立带有参数的存储过程。上面的功能，就需要建立带有参数的存储过程。第第8章：章：存储过程和触发器1)1)创立带有输入参数的存储过程创立带有输入参数的存储过程例题1：创立一个向表student中输入数据的存储过程。第第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,sbir

10、thday,class)VALUES(sno,sname,ssex,sbirthday,class)执行存储过程执行存储过程:exec spinsert 111,exec spinsert 111,张三张三,男男,1980-,1980-1-1,950311-1,95031 上面例题创立的存储过程上面例题创立的存储过程spinsertspinsert一共需要一共需要5 5个参数，并个参数，并且因为没有给这些参数提供默认值，所以为了成功运行该存且因为没有给这些参数提供默认值，所以为了成功运行该存储过程，必须提供这些参数值。储过程，必须提供这些参数值。如果执行如下语句：如果执行如下语句：exec s

11、pinsert 112,exec spinsert 112,李四李四,男男,1985-1-2,1985-1-2 其中少了一个参数，尽管在根本表中该字段允许为其中少了一个参数，尽管在根本表中该字段允许为空，但是此空，但是此procproc也不能被成功执行。也不能被成功执行。第第8章：章：存储过程和触发器例题2：2)2)创立带有输入参数的存储过程，同时给参创立带有输入参数的存储过程，同时给参数提供默认值。数提供默认值。第第8章：章：存储过程和触发器Use schoolGOCREATE PROC spinsertsno char(5),sname char(8),ssex char(2),sbirt

12、hday 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

13、_no int,st_name char(8)output,st_avg float outputASSelect st_name=student.sname,st_avg=)From student,score where=Group by Having=st_noDeclare st_name char(8)Declare st_avg floatExec average 108,st_name output,st_avg outputSelect 姓名姓名=st_name,平均分平均分=st_avg Go 执行存储过程执行存储过程:第第8章：章：存储过程和触发器Declare st_na

14、me 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)

15、创立存储过程创立存储过程 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)运行存储过程运行

16、存储过程例如：执行前面创立的存储过程例如：执行前面创立的存储过程 spinsertdeclare b intexec b=spinsert 112,李四,男,1985-1-2,95031print b执行两次上面的语句，第二次就不能成功执行了。所以执行两次上面的语句，第二次就不能成功执行了。所以返回值是个非返回值是个非0值。值。第第8章：章：存储过程和触发器格式：格式：return 注意：注意：返回值必须是整数。返回值必须是整数。Return语句的最大特点是：语句的最大特点是：一旦执行了一旦执行了return语语句，那么系统就无条件的从过程中退出。也就是句，那么系统就无条件的从过程中退出。也就

17、是说，无论在过程的哪个位置执行了说，无论在过程的哪个位置执行了return语句，语句，就再也不会执行该过程中的语句了。就再也不会执行该过程中的语句了。例如在第六章讲到的例题：例如在第六章讲到的例题：第第8章：章：存储过程和触发器declare a1 intset a1=1print a1return set a1=2print a1return我们知道过程运行结果是我们知道过程运行结果是1 1，这就说明了没有执行第一个，这就说明了没有执行第一个returnreturn后面的语句。如果把第一个后面的语句。如果把第一个returnreturn删除再运行过程，删除再运行过程，会得到会得到1 1和和2

18、 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

19、 b 通过例题可以看到，可以给通过例题可以看到，可以给return语句指定返回语句指定返回值。但例题值。但例题1没有指定返回值，结果仍然有返回值没有指定返回值，结果仍然有返回值0。这是因为系统本身有返回值，如果过程成功执行，返这是因为系统本身有返回值，如果过程成功执行，返回回0。因此，例题。因此，例题1默认的返回了默认的返回了0。但是，我们也可。但是，我们也可以让过程返回我们希望得到的值，例如例题以让过程返回我们希望得到的值，例如例题2。一般。一般情况下，我们都给情况下，我们都给return指定返回值。指定返回值。第第8章：章：存储过程和触发器说明：说明：返回值返回值(return(retur

20、n实现实现)与输出参与输出参数不是一回事。但有的情况，两者可数不是一回事。但有的情况，两者可以完成相同的功能。以完成相同的功能。例如：创立一个例如：创立一个proc，功能是：求，功能是：求1-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 s

21、um=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 成绩值超出范围成绩值超出范围 elseelse insert into score insert into score select select*from

22、inserted from inserted end end第第8章：章：触发器 执行如下操作：执行如下操作：insert into scoreinsert into score values(114,3-105,-1)values(114,3-105,-1)再执行如下操作：再执行如下操作：insert into scoreinsert into score values(114,3-values(114,3-105,55)105,55)第第8章：章：触发器2.2.使用使用updateupdate触发器触发器 使用使用UPDATEUPDATE触发器主要应用在两方面：触发器主要应用在两方面：1)

23、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 whe

24、re 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表中的相关

25、记录的表中的相关记录的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)在删除记录时

26、，要求符合一定的规那么；在删除记录时，要求符合一定的规那么；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章：章：触发器