存储过程、触发器和用户定义函数

1、2022-3-21第第8章章 存储过程、触发器和用户定义函数存储过程、触发器和用户定义函数软件工程系软件工程系 刘金岭刘金岭2022-3-228.1 流程控制语句流程控制语句 BEGIN . .END. .END IF. . ELSE IF. . ELSE CASE CASE 语句语句 循环语句循环语句2022-3-23流程控制语句流程控制语句(1)vBEGIN END语句语句BEGIN END语句能够将多个语句能够将多个T-SQL语句组合成一个语句块，并语句组合成一个语句块，并将它们视为一个单元处理。其语句格式如下：将它们视为一个单元处理。其语句格式如下：BEGIN, n, nEND在在BE

2、GINEND语句中可以嵌套另外的语句中可以嵌套另外的BEGINEND语句来定义另语句来定义另一语句块。一语句块。T-SQL语言提供了一些可以用于改变语句执行顺序的命语言提供了一些可以用于改变语句执行顺序的命令，称为流程控制语句。流程控制语句允许用户更好地组织令，称为流程控制语句。流程控制语句允许用户更好地组织存储过程中的语句，可以方便地实现程序的功能。存储过程中的语句，可以方便地实现程序的功能。 2022-3-24vIF ELSE语句语句IF ELSE 条件表达式条件表达式例例8.1 在教学管理数据库中，如果在教学管理数据库中，如果“C4”号课程的平均成绩高于号课程的平均成绩高于80分，分，则

3、显示则显示“C4号课程的平均成绩还不错号课程的平均成绩还不错”，否则显示，否则显示“C4号课程的平均成号课程的平均成绩一般绩一般”。IF (SELECT AVG(GRADE) FROM SC WHERE CNO=C4)80 PRINT C4号课程的平均成绩还不错号课程的平均成绩还不错ELSE PRINT C4号课程的平均成绩一般号课程的平均成绩一般流程控制语句流程控制语句(2)2022-3-25vCASE语句语句(1) 简单简单CASE语句语句: CASE WHEN THEN WHEN THEN ELSE END流程控制语句流程控制语句(3)例例8.2 在教学管理数据库中，查询在教学管理数据库

4、中，查询S表中学生所在系的中文名称。如表中学生所在系的中文名称。如“李芸李芸”的系部是的系部是“信息系信息系”。SELECT SNAME AS 姓名姓名, CASE SDEPT WHEN CS THEN 计算机科学系计算机科学系 WHEN IS THEN 信息系信息系 WHEN MC THEN 机械系机械系 FROM S WHEN MA THEN 数学系数学系 WHERE SNAME=李芸李芸 END AS 系部系部2022-3-26(2) 搜索搜索CASE语句语句 CASE WHEN THEN WHEN THEN ELSE END例例8.3在教学管理数据库中，显示学生在教学管理数据库中，显示

5、学生“C1”课程的课程的“成绩等级成绩等级”。 SELECT SNAME AS 姓名姓名, CASE WHEN GRADE=90 THEN 优秀优秀 WHEN GRADE=80 THEN 良好良好 WHEN GRADE=70 THEN 中等中等 WHEN GRADE=60 THEN 及格及格 WHEN GRADE60 THEN 不及格不及格 END AS 成绩等级成绩等级 FROM S JOIN SC ON S.SNO=SC.SNO AND CNO=C1流程控制语句流程控制语句(4)2022-3-27v循环语句循环语句WHILE BEGIN BREAK -断路断路 CONTINUE -短路短路

6、END流程控制语句流程控制语句(5)2022-3-28例例8.4 在教学管理数据库中，利用循环的在教学管理数据库中，利用循环的PRINT语句输出语句输出S表中女同学的信表中女同学的信息。息。DECLARE info VARCHAR(200)DECLARE curs CURSORSET curs=CURSOR SCROLL DYNAMICFORSELECT 学号是：学号是：+SNO+;姓名是：姓名是：+SNAME+;性别是：性别是：+SEX+;年年龄是：龄是：+ convert(varchar(3),AGE)+系部是：系部是：+SDEPTFROM SWHERE SEX=FOPEN cursFET

7、CH NEXT FROM curs INTO infoWHILE(fetch_status=0) -游标读取下一条数据成功。游标读取下一条数据成功。 BEGIN PRINT info FETCH NEXT FROM curs INTO info END流程控制语句流程控制语句(6)2022-3-29nRETURN语句语句使用使用RETURN语句，可以从查询或过程中无条件地退出，而不去执语句，可以从查询或过程中无条件地退出，而不去执行位于行位于RETURN之后的语句。语句格式为：之后的语句。语句格式为： RETURN 其中，其中，为一个整型数值，是为一个整型数值，是RETURN语句要返回的值。语

8、句要返回的值。该语句的含义是：向执行调用的过程或应用程序返回一个整数值。该语句的含义是：向执行调用的过程或应用程序返回一个整数值。注意：当用于存储过程时，不能返回空值。如果试图返回空值，将注意：当用于存储过程时，不能返回空值。如果试图返回空值，将生成警告信息，并返回生成警告信息，并返回0值。值。流程控制语句流程控制语句(7)2022-3-2108.2 存储过程存储过程 主要内容主要内容n 存储过程概述存储过程概述 n 创建存储过程创建存储过程 n 调用存储过程调用存储过程 n 管理存储过程管理存储过程 流程控制语句流程控制语句(2)2022-3-211存储过程的分类存储过程的分类(1)SQL

9、Server 支持的存储过程可分为支持的存储过程可分为5类：类：系统存储过程、本地系统存储过程、本地存储过程、临时存储过程、远程存储过程和扩展存储过程存储过程、临时存储过程、远程存储过程和扩展存储过程。（1）系统存储过程）系统存储过程。系统存储过程是由系统提供的存储过程，。系统存储过程是由系统提供的存储过程，可以作为命令执行各种操作。系统存储过程定义在系统数据库可以作为命令执行各种操作。系统存储过程定义在系统数据库master中，其前缀是中，其前缀是sp_，它们为检索系统表的信息提供了方便，它们为检索系统表的信息提供了方便快捷的方法。快捷的方法。（2）用户存储过程）用户存储过程。本地存储过程是

10、指在用户数据库中创建。本地存储过程是指在用户数据库中创建的存储过程，这种存储过程完成用户指定的数据库操作，其名称的存储过程，这种存储过程完成用户指定的数据库操作，其名称不能以不能以sp_为前缀。为前缀。SQL Server 2008中，本地存储过程可以使中，本地存储过程可以使用用T-SQL语言编写。语言编写。2022-3-212存储过程的分类存储过程的分类(2) 存储过程存储过程：存储过程保存：存储过程保存T-SQL语句集合，可以接受和返回语句集合，可以接受和返回用户提供的参数。存储过程中可以包含根据客户端应用程序提供用户提供的参数。存储过程中可以包含根据客户端应用程序提供的信息，在一个或多个

11、表中插入新行所需的语句。的信息，在一个或多个表中插入新行所需的语句。 CLR存储过程存储过程：CLR存储过程是对存储过程是对Microsoft .NET Framework公共语言运行时（公共语言运行时（CLR）方法的引用，可以接受和返）方法的引用，可以接受和返回用户提供的参数。它们在回用户提供的参数。它们在“.NET Framework 程序集程序集”中是作中是作为类的公共静态方法实现的。为类的公共静态方法实现的。 CLR常用简写词语，常用简写词语，CLR是公共语言运行时，是公共语言运行时，Common Language Runtime)和和Java虚拟机一样也是一个运行时环境，虚拟机一样也

12、是一个运行时环境， 它负责资源管理（内存分配和垃圾收集），并保证应用和底层它负责资源管理（内存分配和垃圾收集），并保证应用和底层 操作系统之间必要的分离。操作系统之间必要的分离。2022-3-213存储过程的分类存储过程的分类(3)（3）临时存储过程。）临时存储过程。临时存储过程属于用户存储过程。如果临时存储过程属于用户存储过程。如果用户存储过程的名称前面有一个用户存储过程的名称前面有一个“#”，该存储过程就称为局部临，该存储过程就称为局部临时存储过程，这种存储过程只能在一个用户会话中使用。时存储过程，这种存储过程只能在一个用户会话中使用。如果用户存储过程的名称前有两个如果用户存储过程的名称前

13、有两个“#”，该过程就是全局临，该过程就是全局临时存储过程，这种存储过程可以在所有用户会话中使用。时存储过程，这种存储过程可以在所有用户会话中使用。（4）远程存储过程。）远程存储过程。远程存储过程指从远程服务器上调用的远程存储过程指从远程服务器上调用的存储过程。存储过程。（5）扩展存储过程。）扩展存储过程。在在SQL Server环境之外执行的动态链接环境之外执行的动态链接库称为扩展存储过程，其前缀是库称为扩展存储过程，其前缀是sp_。2022-3-214存储过程概述存储过程概述(1) 存储过程是存储过程是T-SQL语句和流程控制语句的预编译集合，以一个名称存储并作语句和流程控制语句的预编译集

14、合，以一个名称存储并作为一个单元处理。存储过程存储在数据库内，可由应用程序通过一个调用执行，为一个单元处理。存储过程存储在数据库内，可由应用程序通过一个调用执行，而且允许用户声明变量、有条件执行以及其它强大的编程功能。而且允许用户声明变量、有条件执行以及其它强大的编程功能。 v使用存储过程的优势和不足使用存储过程的优势和不足 提高了处理复杂任务的能力。提高了处理复杂任务的能力。主要用于数据库中执行操作的编程语句，通过主要用于数据库中执行操作的编程语句，通过接受输入参数并以输出参数的格式向调用过程或批处理返回多个值。接受输入参数并以输出参数的格式向调用过程或批处理返回多个值。 增强了代码的复用率

15、和共享性。增强了代码的复用率和共享性。存储过程只需编译一次，以后即可多次执行，存储过程只需编译一次，以后即可多次执行，因此使用存储过程可以提高应用程序的性能。因此使用存储过程可以提高应用程序的性能。 减少网络中的数据流量。减少网络中的数据流量。譬如一个需要数百行譬如一个需要数百行SQL代码的操作用一条执行语代码的操作用一条执行语句完成，不需要在网络中发送数百行代码，从而大大减轻了网络负荷。句完成，不需要在网络中发送数百行代码，从而大大减轻了网络负荷。 可作为安全机制使用。可作为安全机制使用。数据库用户可以通过得到权限来执行存储过程，而不数据库用户可以通过得到权限来执行存储过程，而不必给予用户直

16、接访问数据库对象的权限。这样，对于数据表，用户只能通过存储必给予用户直接访问数据库对象的权限。这样，对于数据表，用户只能通过存储过程来访问，并进行有限的操作，从而保证了表中数据的安全。过程来访问，并进行有限的操作，从而保证了表中数据的安全。2022-3-215存储过程概述存储过程概述(2)使用存储过程也有不足之处，主要表现在：使用存储过程也有不足之处，主要表现在：n如果需要对输入存储过程的参数进行更改，或者要更改由其返回如果需要对输入存储过程的参数进行更改，或者要更改由其返回的数据，则需要更新程序集中的代码以添加参数、更新调用等，一的数据，则需要更新程序集中的代码以添加参数、更新调用等，一般比

17、较繁琐。般比较繁琐。n可移植性差。由于存储过程将应用程序绑定到可移植性差。由于存储过程将应用程序绑定到SQL Server，因，因此使用存储过程封装业务逻辑将限制应用程序的可移植性。此使用存储过程封装业务逻辑将限制应用程序的可移植性。n很多存储过程不支持面向对象的设计，无法采用面向对象的方式很多存储过程不支持面向对象的设计，无法采用面向对象的方式将业务逻辑进行封装，从而无法形成通用的可支持复用的业务逻辑将业务逻辑进行封装，从而无法形成通用的可支持复用的业务逻辑框架。框架。n代码可读性差，因此一般比较难维护。代码可读性差，因此一般比较难维护。2022-3-216存储过程概述存储过程概述(3)v常

18、见的存储过程常见的存储过程 (1) 系统存储过程系统存储过程系统存储过程是由系统存储过程是由SQL Server 系统提供的存储过程，可以作为命令执行各种系统提供的存储过程，可以作为命令执行各种操作。操作。 存储过程存储过程功能功能sp_addlogin 创建一个新的创建一个新的login帐户。帐户。sp_addrole 在当前数据库中增加一个角色。在当前数据库中增加一个角色。sp_cursorclose 关闭和释放游标。关闭和释放游标。sp_dbremove 删除数据库和该数据库相关的文件。删除数据库和该数据库相关的文件。sp_droplogin 删除一个登录帐户。删除一个登录帐户。sp_h

19、elpindex 返回有关表的索引信息。返回有关表的索引信息。sp_helprolemember 返回当前数据库中角色成员的信息。返回当前数据库中角色成员的信息。sp_helptrigger 显示触发器类型。显示触发器类型。sp_lock 返回有关锁的信息。返回有关锁的信息。sp_primarykeys 返回主键列的信息。返回主键列的信息。sp_statistics 返回表中的所有索引列表。返回表中的所有索引列表。2022-3-217存储过程概述存储过程概述(4)例例1 查看数据库文件查看数据库文件use JXGLgo sp_helpfile go例例2 查看数据库对象的相关信息查看数据库对象

20、的相关信息exec sp_databases; -查看数据库查看数据库 exec sp_tables; -查看表查看表 exec sp_columns student; -查看列查看列2022-3-218存储过程概述存储过程概述(5)例例3 在对象表在对象表sys.objects中查询数据库中查询数据库JXGL的所有存储过程。的所有存储过程。 USE JXGL select * from sys.objects where type = P;或：或：例例4 查询数据库查询数据库JXGL的所有存储过程。的所有存储过程。 USE JXGL select * from sys.objects whe

21、re type_desc like %pro% and name like sp%;2022-3-219存储过程概述存储过程概述(6)(2) 扩展存储过程扩展存储过程扩展存储过程以在扩展存储过程以在SQL Server 环境外执行的动态链接库环境外执行的动态链接库(Dynamic-Link Libraries，DLL)来实现。来实现。 扩展存储过程扩展存储过程功功 能能xp_availablemedia查看系统上可用的磁盘驱动器的空间信息。查看系统上可用的磁盘驱动器的空间信息。xp_dirtree查看某个目录下所有子目录的结构。查看某个目录下所有子目录的结构。xp_enumdsn查看系统上设定

22、好的查看系统上设定好的ODBC数据源。数据源。xp_enumgroups查看系统上的组信息。查看系统上的组信息。xp_fixeddrives列出服务器上固定驱动器以及可用空间。列出服务器上固定驱动器以及可用空间。例例8.6 查看查看d:mssql目录结构。目录结构。 EXEC xp_dirtree d:mssql例例8.5 在教学管理数据库中，显示表在教学管理数据库中，显示表S的相关性信息。的相关性信息。 EXEC sp_depends objname = S 2022-3-220创建存储过程创建存储过程 (1)v利用利用SSMS图形方式图形方式(1) 在在“对象资源管理器对象资源管理器”中，

23、展开要创建存储过程的数据库。中，展开要创建存储过程的数据库。(2) 展开展开“数据库数据库”、存储过程所属的数据库以及、存储过程所属的数据库以及“可编程性可编程性”。(3) 右键单击右键单击“存储过程存储过程”，在弹出的快捷菜单中选择，在弹出的快捷菜单中选择“新建存储过新建存储过程程”菜单项，出现菜单项，出现“新建存储过程新建存储过程”对话框。对话框。参数需要修改三个元素：参数需要修改三个元素：参数的名称、参数的数据类型以及参数的默参数的名称、参数的数据类型以及参数的默认值。参数按以下格式包括在尖括号认值。参数按以下格式包括在尖括号 () 中：中：。parameter_name：模板中模板中参

24、数的名称，此字段是只读的。参数的名称，此字段是只读的。data_type：模板中参数的数据类型，此字段是只读的。若要更改数模板中参数的数据类型，此字段是只读的。若要更改数据类型，请更改模板中的参数。据类型，请更改模板中的参数。default_value：为所选参数的指定值，默认值。为所选参数的指定值，默认值。 2022-3-221创建存储过程创建存储过程 (1)(4) 在主菜单在主菜单“查询查询”上，单击子菜单上，单击子菜单“指定模板参数的值指定模板参数的值”。如图。如图所示。所示。(5) 在在“指定模板参数的值指定模板参数的值”对话框中，对话框中，“值值”列包含参数的建议值。列包含参数的建议

25、值。接受这些值或将其替换为新值，再单击接受这些值或将其替换为新值，再单击“确定确定”。(6) 在查询编辑器中，使用过程语句替换在查询编辑器中，使用过程语句替换SELECT语句。语句。(7) 若要测试语法，请在若要测试语法，请在“查询查询”菜单上，单击菜单上，单击“分析分析”。(8) 若要创建存储过程，请在若要创建存储过程，请在“查询查询”菜单上，单击菜单上，单击“执行执行”。(9) 若要保存脚本，请在若要保存脚本，请在“文件文件”菜单上，单击菜单上，单击“保存保存”。 2022-3-222创建存储过程创建存储过程 (1)例例8.8 在教学管理数据库中，利用在教学管理数据库中，利用“新建存储过程

26、新建存储过程”面板，创建学号面板，创建学号和课程号参数的成绩查询存储过程和课程号参数的成绩查询存储过程SC_GRADE。 CREATE PROCEDURE SC_GRADE - Add the parameters for the stored procedure herepar_SNO CHAR(9),par_CNO CHAR(4)AS BEGIN- SET NOCOUNT ON added to prevent extra result sets from interfering with SELECT statements. SET NOCOUNT ON; SELECT GRADE FR

27、OM SC WHERE SNO=par_SNO AND CNO=par_CNO END2022-3-223创建存储过程创建存储过程 (2)n使用使用T-SQL语句语句CREATE PROCEDURE|PROC;n , n OUTPUT, n FOR REPLICATION AS|：过程中的参数。过程中的参数。 ：指定作为输出参数支持的结果集。：指定作为输出参数支持的结果集。FOR REPLICATION：使用该选项创建的存储过程可用作存储过程使用该选项创建的存储过程可用作存储过程的筛选器，且只能在复制过程中执行。的筛选器，且只能在复制过程中执行。 2022-3-224创建存储过程创建存储过程

28、(3)例例8.9 利用教学管理数据库的三个基本表，创建一个存储过程利用教学管理数据库的三个基本表，创建一个存储过程PS_GRADE，输出指定学生的姓名及课程名称、成绩信息。，输出指定学生的姓名及课程名称、成绩信息。USE JXGLGOCREATE PROCEDURE PS_GRADE S_NAME CHAR(8)AS SELECT SNAME,CNAME,GRADE FROM S JOIN SC ON S.SNO=SC.SNO AND SNAME=S_NAME JOIN C ON SC.CNO=C.CNO GOS_NAME作为输入参数，为存储过程传送指定学生的姓名。作为输入参数，为存储过程传送

29、指定学生的姓名。2022-3-225创建存储过程创建存储过程 (4)例例8.10 利用教学管理数据库的三个基本表，创建一个存储过程利用教学管理数据库的三个基本表，创建一个存储过程PV_GRADE，输入一个学生姓名，输出该学生所有选修课程的平均成绩。，输入一个学生姓名，输出该学生所有选修课程的平均成绩。USE JXGLGOCREATE PROCEDURE PV_GRADE S_NAME CHAR(8)=NULL,S_AVG REAL OUTPUTAS SELECT S_AVG=AVG(GRADE) FROM S JOIN SC ON S.SNO=SC.SNO AND SNAME=S_NAMEGO

30、本例中，本例中，自定义输入参数自定义输入参数S_NAME的同时，为输入参数指定默认值，的同时，为输入参数指定默认值，即在调用程序不提供学生姓名时，默认是所有学生的平均成绩。即在调用程序不提供学生姓名时，默认是所有学生的平均成绩。2022-3-226调用存储过程调用存储过程 (1)在需要执行存储过程时，可以使用在需要执行存储过程时，可以使用T-SQL 语句语句EXECUTE（可以简写（可以简写为为EXEC）。）。 EXEC|EXECUTE=,n=|OUTPUT|DEFAULT, nWITH RECOMPILE：是一个可选的整型变量，保存存储过程的返回状态。是一个可选的整型变量，保存存储过程的返回

31、状态。：要调用的存储过程名称。要调用的存储过程名称。 OUTPUT：指定存储过程必须返回一个参数。指定存储过程必须返回一个参数。 2022-3-227调用存储过程调用存储过程 (2)DEFAULT：根据过程的定义，提供参数的默认值。根据过程的定义，提供参数的默认值。WITH RECOMPILE：强制在执行存储过程时对其进行编译，并将其强制在执行存储过程时对其进行编译，并将其存储起来，以后执行时不再编译。存储起来，以后执行时不再编译。 例例8.11 调用定义存储过程调用定义存储过程PS_GRADE。 DECLARE NAME CHAR(9) SET NAME=马常友马常友 EXEC PS_GRA

32、DE NAME 例例8.12 调用存储过程调用存储过程PV_GRADE。 DECLARE S_AVG REAL EXEC PV_GRADE 姜云姜云,S_AVG OUTPUT PRINT 姜云平均成绩为：姜云平均成绩为：+STR(S_AVG)2022-3-228 管理存储过程管理存储过程 (1)n查看存储过程信息查看存储过程信息可以执行系统存储过程可以执行系统存储过程sp_helptext，来查看创建的存储过程的内容；，来查看创建的存储过程的内容；也可以执行系统存储过程也可以执行系统存储过程sp_help，来查看存储过程的名称、拥有者、，来查看存储过程的名称、拥有者、类型和创建时间，以及存储过

33、程中所使用的参数信息等。类型和创建时间，以及存储过程中所使用的参数信息等。 sp_helptext sp_help 例例8.13 查看存储过程查看存储过程PV_GRADE的相关内容信息。的相关内容信息。 EXEC sp_helptext PV_GRADE2022-3-229例例8.14 查看存储过程查看存储过程PV_GRADE的名称、参数等相关内容信息。的名称、参数等相关内容信息。 EXEC sp_help PV_GRADEv修改存储过程修改存储过程 (1) 利用利用SSMS图形方式图形方式 在在“对象资源管理器对象资源管理器”中，展开要修改存储过程的数据库。中，展开要修改存储过程的数据库。

34、依次展开依次展开“数据库数据库”、存储过程所属的数据库以及、存储过程所属的数据库以及“可编程性可编程性”。 展开展开“存储过程存储过程”，右击要修改的存储过程，在弹出的快捷菜单，右击要修改的存储过程，在弹出的快捷菜单中选择中选择“修改修改”菜单项即可。菜单项即可。 管理存储过程管理存储过程 (2)2022-3-230 (2) 使用使用T-SQL语句语句ALTER PROCEDURE | PROC;n , n OUTPUT , n FOR REPLICATION AS | 例例8.15 将存储过程修改为一个输入参数（学生姓名）和两个输出参将存储过程修改为一个输入参数（学生姓名）和两个输出参数（总

35、成绩和平均成绩）。数（总成绩和平均成绩）。ALTER PROCEDURE PS_GRADE S_NAME CHAR(8), S_AVG REAL OUTPUT,S_SUM INT OUTPUTAS SELECT S_AVG=AVG(GRADE),S_SUM=SUM(GRADE) FROM S JOIN SC ON S.SNO=SC.SNO AND SNAME=S_NAME 管理存储过程管理存储过程 (2)2022-3-231v删除存储过程删除存储过程(1) 利用利用SSMS图形方式图形方式删除存储过程的步骤如下：删除存储过程的步骤如下： 在在“对象资源管理器对象资源管理器”中，展开要删除存储过

36、程的数据库。中，展开要删除存储过程的数据库。 依次展开依次展开“数据库数据库”、存储过程所属的数据库以及、存储过程所属的数据库以及“可编程性可编程性”。 展开展开“存储过程存储过程”，右击要删除的存储过程，在弹出的快捷菜单，右击要删除的存储过程，在弹出的快捷菜单中选择中选择“删除删除”菜单项，出现菜单项，出现“删除对象删除对象”对话框，单击对话框，单击“确定确定”按钮按钮即可。即可。 (2) 使用使用T-SQL语句语句 DROP PROCEDURE, n 例例8.17 删除存储过程删除存储过程SC_GRADEUSE JXGLGODROP PROCEDURE SC_GRADEGO 管理存储过程管

37、理存储过程 (3)2022-3-232常用存储过程类型总结常用存储过程类型总结(1)例例1 只返回单一记录集的存储过程。只返回单一记录集的存储过程。create procedure student_info as select * from S go exec student_info2022-3-233例例2 向存储过程中传递参数。向存储过程中传递参数。加入一条记录到表加入一条记录到表SC，并查询此表中该学生的总成绩。，并查询此表中该学生的总成绩。USE JXGLGOCreate proc insert_SC param_SNO char(8),param_CNO char(4),param

38、_grade smallintasinsert SC(SNO,CNO,GRADE)Values(param_SNO,param_CNO,param_grade)select sum(GRADE) from SC where SNO= param_SNO GOexec insert_SC S10,C3,78常用存储过程类型总结常用存储过程类型总结(2)2022-3-234例例3 使用带有复杂使用带有复杂 SELECT 语句的简单过程。语句的简单过程。从三个表的联接中返回给定姓名的学生所学课程成绩。从三个表的联接中返回给定姓名的学生所学课程成绩。该存储过程不使用任何参数。该存储过程不使用任何参数。

39、USE JXGL GO CREATE PROCEDURE sname_gradeAS SELECT S.SNO,SNAME,CNAME,GRADE FROM S JOIN SC ON S.SNO=SC.SNO AND SNAME=姜云姜云 JOIN C ON SC.CNO=C.CNOGO常用存储过程类型总结常用存储过程类型总结(3)2022-3-235例例4 使用带有参数的简单过程。使用带有参数的简单过程。从三个表的联接中返回输入姓名的学生所学课程成绩。从三个表的联接中返回输入姓名的学生所学课程成绩。该存储过程不使用任何参数。该存储过程不使用任何参数。USE JXGL GO CREATE PR

40、OCEDURE sname_grade sname_input char(8)AS SELECT S.SNO,SNAME,CNAME,GRADE FROM S JOIN SC ON S.SNO=SC.SNO AND SNAME=aname_input JOIN C ON SC.CNO=C.CNOGO常用存储过程类型总结常用存储过程类型总结(4)2022-3-236例例5 使用带有通配符参数的简单过程。使用带有通配符参数的简单过程。USE JXGLGO CREATE PROCEDURE sno_grade -sname_input=S% sno_input char(8)=%ASsELECT S

41、.SNO,SNAME,CNAME,GRADE FROM S JOIN SC ON S.SNO=SC.SNO AND S.SNO=sno_input JOIN C ON SC.CNO=C.CNOGOexec sno_grade S1常用存储过程类型总结常用存储过程类型总结(5)2022-3-237本次课小结本次课小结 主要内容主要内容nSQL Server 2008 流程控制语句流程控制语句n存储过程的创建存储过程的创建n存储过程的调用存储过程的调用n存储过程的管理存储过程的管理 作业作业 EX8 1，2，52022-3-238 上次课主要内容上次课主要内容nSQL Server 2008 流程

42、控制语句流程控制语句nBEGUN.ENDnIF.ELSEnCASEnWHILEn存储过程的创建存储过程的创建n存储过程的调用存储过程的调用n存储过程的管理存储过程的管理 2022-3-2398.3 触发器触发器主要内容主要内容n 触发器概述触发器概述 n 创建触发器创建触发器n 管理触发器管理触发器 2022-3-240当对某一个表的一定的操作，触发某种条件，从而执行的一段程序，当对某一个表的一定的操作，触发某种条件，从而执行的一段程序，称为触发器。而对表进行插入、更新、删除的时候会自动执行的特殊存称为触发器。而对表进行插入、更新、删除的时候会自动执行的特殊存储过程。储过程。 n触发器的常用功

43、能触发器的常用功能(1) 完成比约束更复杂的数据约束。完成比约束更复杂的数据约束。 (2) 触发器可以检查触发器可以检查T-SQL所做的操作是否被允许。所做的操作是否被允许。例如：在产品库例如：在产品库存表里，如果要删除一条产品记录，在删除记录时，触发器可以检查该存表里，如果要删除一条产品记录，在删除记录时，触发器可以检查该产品库存数量是否为零，如果为零则取消该删除操作。产品库存数量是否为零，如果为零则取消该删除操作。(3) 当一个当一个T-SQL语句对数据表进行操作的时候，语句对数据表进行操作的时候，触发器可以根据该触发器可以根据该T-SQL语句的操作情况来对另一个数据表进行操作。语句的操作

44、情况来对另一个数据表进行操作。例如：一个订单取例如：一个订单取消的时候，那么触发器可以自动修改产品库存表，在订购量的字段上减消的时候，那么触发器可以自动修改产品库存表，在订购量的字段上减去被取消订单的订购数量。去被取消订单的订购数量。(4) 触发器也可以调用一个或多个存储过程。触发器也可以调用一个或多个存储过程。触发器概述触发器概述 (1)2022-3-241触发器概述触发器概述 (2) (5) 在在T-SQL语句执行完之后，语句执行完之后，触发器可以自动调用触发器可以自动调用“数据库邮数据库邮件件”来发送邮件。来发送邮件。例如，当一个订单交费之后，可以让物流人员发例如，当一个订单交费之后，可

45、以让物流人员发送送Email，通知他尽快发货。，通知他尽快发货。 (6) 返回自定义的错误信息。返回自定义的错误信息。例如插入一条重复记录时，可以返例如插入一条重复记录时，可以返回一个具体的友好的错误信息给前台应用程序。回一个具体的友好的错误信息给前台应用程序。 (7) 触发器可以修改原来要操作的触发器可以修改原来要操作的T-SQL语句。语句。例如原来的例如原来的T-SQL语句是要删除数据表里的记录，但该数据表里的记录是重要记语句是要删除数据表里的记录，但该数据表里的记录是重要记录，是不允许删除的，那么触发器可以不执行该语句。录，是不允许删除的，那么触发器可以不执行该语句。 (8) 防止数据表

46、结构更改或数据表被删除。防止数据表结构更改或数据表被删除。为了保护已经建好的为了保护已经建好的数据表，触发器可以在接收到数据表，触发器可以在接收到DROP和和ALTER开头的开头的T-SQL语句时，语句时，不进行对数据表的操作。不进行对数据表的操作。2022-3-242v触发器的分类触发器的分类(l) DML触发器触发器DML触发器是当数据库服务器中发生数据操作语言事件时执行的存储触发器是当数据库服务器中发生数据操作语言事件时执行的存储过程。过程。DML触发器又分为两类：触发器又分为两类：AFTER触发器：只有执行某一操作触发器：只有执行某一操作INSERT、UPDATE、DELETE之之后触

47、发器才被触发，后触发器才被触发，INSTEAD OF触发器：不执行其定义的操作（触发器：不执行其定义的操作（INSERT、UPDATE、DELETE）而仅是执行触发器本身。对同一操作只能定义一个）而仅是执行触发器本身。对同一操作只能定义一个INSTEAD OF触发器。触发器。(2) DDL触发器触发器DDL触发器是在响应数据定义语言事件时执行的存储过程。一般用于触发器是在响应数据定义语言事件时执行的存储过程。一般用于执行数据库中管理任务，如审核和规范数据库操作、防止数据库表结构执行数据库中管理任务，如审核和规范数据库操作、防止数据库表结构被修改等。被修改等。触发器概述触发器概述 (1)2022

48、-3-243n利用利用SSMS图形方式图形方式(1) 在在“对象资源管理器对象资源管理器”中，展开要创建中，展开要创建DML触发器的数据库和其触发器的数据库和其中的表或视图。中的表或视图。(2) 右键单击右键单击“触发器触发器”选项，在弹出的快捷菜单中选择选项，在弹出的快捷菜单中选择“新建触发新建触发器器”菜单项。出现菜单项。出现“新建触发器新建触发器”对话框，如图所示，在其中编辑有关对话框，如图所示，在其中编辑有关的的T-SQL命令即可。命令即可。(3) 命令编辑完后，进行语法检查，然后单击命令编辑完后，进行语法检查，然后单击“确定确定”按钮，至此一按钮，至此一个个DML触发器创建成功。触发

49、器创建成功。 创建触发器创建触发器 (1)2022-3-244v使用使用T-SQL语句语句CREATE TRIGGER ON |FOR|AFTER|INSTEAD OFINSERT,UPDATE,DELETEAS | FOR|AFTER|INSTEAD OF：指定触发器触发的时机。指定触发器触发的时机。FOR|AFTER指定在相应操作（指定在相应操作（INSERT、UPDATE、DELETE）成）成功执行后才触发。功执行后才触发。 INSTEAD OF指定执行指定执行DML触发器用于触发器用于“代替代替”引发触发器执行的引发触发器执行的INSERT、UPDATE或或DELETE语句。语句。 创

50、建触发器创建触发器 (2)2022-3-245例例 创建创建JXGL数据库作用域的数据库作用域的DDL触发器，当删除一个表时，提触发器，当删除一个表时，提示禁止该操作，然后回滚删除表的操作。示禁止该操作，然后回滚删除表的操作。USE JXGLGOCREATE TRIGGER safetyON DATABASEAFTER DROP_TABLEASPRINT 不能删除该表不能删除该表ROLLBACK TRANSACTION尝试删除表尝试删除表syb：DROP TABLE syb执行结果如图所示：执行结果如图所示：创建触发器创建触发器 (3)2022-3-246例例8.20 在教学管理数据库中，用在

51、教学管理数据库中，用T-SQL语句为语句为S表创建一个表创建一个DELETE类型的触发器类型的触发器DEL_COUNT，删除数据时，显示删除学生的个数。，删除数据时，显示删除学生的个数。USE JXGLGOCREATE TRIGGER DEL_COUNTON SFOR DELETEAS DECLARE COUNT VARCHAR(50) SELECT COUNT=STR(ROWCOUNT)+个学生被删除个学生被删除 SELECT COUNTRETURN GO创建触发器创建触发器 (4)2022-3-247例例8.22 在教学管理数据库中，创建在教学管理数据库中，创建DDL触发器触发器JXGL_

52、LIMITED，防，防止数据库止数据库JXGL中任一表被删除或修改。中任一表被删除或修改。USE JXGLGOCREATE TRIGGER JXGL_LIMITEDON DATABASEFOR DROP_TABLE,ALTER_TABLEAS PRINT 不允许对数据库不允许对数据库JXGL的表进行修改或删除的表进行修改或删除GO创建触发器创建触发器 (5)2022-3-248u触发器中使用的特殊表触发器中使用的特殊表执行触发器时，系统创建了两个特殊的临时表执行触发器时，系统创建了两个特殊的临时表inserted表和表和deleted表，下面介绍一下这两个表的内容。表，下面介绍一下这两个表的内

53、容。inserted表表：当向表中插入数据时，：当向表中插入数据时，INSERT触发器触发执行，新触发器触发执行，新的记录插入到触发器表和的记录插入到触发器表和inserted表中。表中。deleted表表：用于保存已从表中删除的记录，当触发一个：用于保存已从表中删除的记录，当触发一个DELETE触发器时，被删除的记录存放到触发器时，被删除的记录存放到deleted表中。表中。修改一条记录等于插入一条新记录，同时删除旧记录。当对定修改一条记录等于插入一条新记录，同时删除旧记录。当对定义了义了UPDATE触发器的表记录修改时，表中原记录移到触发器的表记录修改时，表中原记录移到deleted表中，

54、表中，修改过的记录插入到修改过的记录插入到inserted表中。由于表中。由于inserted表和表和deleted表都是表都是临时表，它们在触发器执行时被创建，触发器执行完后就消失了，临时表，它们在触发器执行时被创建，触发器执行完后就消失了，所以只可以在触发器的语句中使用所以只可以在触发器的语句中使用SELECT语句查询这两个表。语句查询这两个表。触发器说明触发器说明 (1)2022-3-249u使用触发器的限制使用触发器的限制 CREATE TRIGGER 必须是批处理中的第一条语句，并且只能应必须是批处理中的第一条语句，并且只能应用到一个表中。用到一个表中。 触发器只能在当前的数据库中创

55、建，但触发器可以引用当前触发器只能在当前的数据库中创建，但触发器可以引用当前数据库的外部对象。数据库的外部对象。 如果指定触发器所有者名限定触发器，要以相同的方式限定如果指定触发器所有者名限定触发器，要以相同的方式限定表名。表名。 在同一在同一CREATE TRIGGER语句中，可以为多种操作（如语句中，可以为多种操作（如 INSERT 和和 UPDATE）定义相同的触发器操作。）定义相同的触发器操作。 如果一个表的外键在如果一个表的外键在 DELETE、UPDATE 操作上定义了级联，则操作上定义了级联，则不能在该表上定义不能在该表上定义 INSTEAD OF DELETE、INSTEAD

56、OF UPDATE 触发器。触发器。触发器说明触发器说明 (2)2022-3-250 对于含有对于含有DELETE或或UPDATE操作定义的外键表，不能使用操作定义的外键表，不能使用INSTEAD OF DELETE和和INSTEAD OF UPDATE触发器。触发器。 TRUNCATE TABLE语句虽然能够删除表中所有记录，但它不会语句虽然能够删除表中所有记录，但它不会触发触发DELETE触发器。触发器。 在触发器内可以指定任意的在触发器内可以指定任意的 SET 语句，所选择的语句，所选择的 SET 选项在触选项在触发器执行期间有效，并在触发器执行完后恢复到以前的设置。发器执行期间有效，并

57、在触发器执行完后恢复到以前的设置。 触发器中不允许包含以下触发器中不允许包含以下 T-SQL 语句：语句： CREATE DATABASE、ALTER DATABASE、LOAD DATABASE、RESTORE DATABASE、DROP DATABASE、LOAD LOG、RESTORE LOG、DISK INIT、DISK RESIZE和和RECONFIGURE。 DML触发器最大的用途是返回行级数据的完整性，而不是返回触发器最大的用途是返回行级数据的完整性，而不是返回结果。所以应当尽量避免返回任何结果集。结果。所以应当尽量避免返回任何结果集。触发器说明触发器说明 (3)2022-3-2

58、51v查看触发器信息查看触发器信息执行系统存储过程执行系统存储过程sp_helptext来查看创建的触发器的内容；来查看创建的触发器的内容；执行系统存储过程执行系统存储过程sp_help来查看触发器的名称、拥有者、类型和创来查看触发器的名称、拥有者、类型和创建时间，以及触发器中所使用的参数信息等。建时间，以及触发器中所使用的参数信息等。sp_helptext sp_help 例例8.24 在教学管理数据库中，利用在教学管理数据库中，利用sp_helptext查看触发器查看触发器DEL_COUNT内容。内容。 USE JXGL GO EXEC sp_helptext DEL_COUNT GO管理

59、触发器管理触发器 (1)2022-3-252n修改触发器修改触发器ALTER TRIGGER ON |FOR|AFTER|INSTEAD OFINSERT,UPDATE,DELETEAS | 例例8.25 修改触发器修改触发器S_I_U，使得对学生表，使得对学生表S进行添加或修改操作时，进行添加或修改操作时，自动给出错误提示信息，并撤销此次操作。自动给出错误提示信息，并撤销此次操作。ALTER TRIGGER S_I_UON SINSTEAD OFINSERT,UPDATEAS PRINT 你执行的添加或修改操作无效！你执行的添加或修改操作无效！ 管理触发器管理触发器 (2)2022-3-25

60、3v删除触发器删除触发器DROP TRIGGER 例例8.25 在教学管理数据库中，删除在教学管理数据库中，删除S表上的触发器表上的触发器DEL_COUNT。USE JXGLGODROP TRIGGER DEL_COUNTGO注意：删除触发器所在的表时，注意：删除触发器所在的表时，SQL Server将自动删除与该表相关将自动删除与该表相关的触发器。的触发器。管理触发器管理触发器 (3)2022-3-254n禁用与启用触发器禁用与启用触发器删除了触发器后，它就从当前数据库中消失了。禁用触发器不会删除删除了触发器后，它就从当前数据库中消失了。禁用触发器不会删除触发器，该触发器仍然作为对象存在于当