存储过程_函数和触发器_数据库

1、存储过程，触发器和函数存储过程，触发器和函数存储过程，触发器和函数存储过程，触发器和函数v T-SQL编程基础编程基础v 存储过程存储过程v 触发器触发器v 自定义函数自定义函数v 小结小结FT-SQL语言基础语言基础主要数据类型：主要数据类型： smallint，int，numeric，decimal，char， varchar，nchar，nvarchar ，datetime运算符及优先级运算符及优先级常用函数常用函数变量：变量： 局部变量（内存变量，字段变量）局部变量（内存变量，字段变量） 全局变量（系统变量）全局变量（系统变量）内容回顾内容回顾v全局变量由系统定义并维护，通过在名称前面

2、加“”符号v局部变量的首字母为单个“”。全局变量和局部变量全局变量和局部变量(1)局部变量局部变量v局部变量使用DECLARE语句定义DECLARE local_variable data_type ,.nv变量名最大长度为30个字符。一条DECLARE语句可以定义多个变量，各变量之间使用逗号隔开。v例如DECLARE name varchar(30),type int局部变量的赋值局部变量的赋值 用SELECT为局部变量赋值SELECT variable_name=expression , nFROM WHERE v例如 DECLARE int_var int SELECT int_var

3、=12 /*给int_var赋值*/ SELECT int_var /*将int_var的值输出到屏幕上*/例1 使用SELECT语句从S表中检索出学生编号为“95001”的行，再将学生的名字赋给变量stuname。DECLARE stuname varchar(40) SELECT stuname=snameFROM SWHERE Sno=95001局部变量没有被赋值前，其值是NULL，若要在程序中引用它，必须先赋值。 用用SET给局部变量赋值给局部变量赋值vSET语句格式为：SET local_variable=expressionv使用SET初始化变量的方法与SELECT语句相同，但一个

4、SET语句只能为一个变量赋值。例2 计算S表的记录数并赋值给局部变量rows。DECLARE rows intSET rows=(SELECT COUNT(*) FROM S)SELECT rows(2)全局变量全局变量v全局变量通常被服务器用来跟踪服务器范围和特定会话期间的信息，不能显式地被赋值或声明。v全局变量不能由用户定义，也不能被应用程序用来在处理器之间交叉传递信息。 9.1.2 9.1.2 常量与变量常量与变量在在SQL ServerSQL Server中，可以使用两种类型的注释：中，可以使用两种类型的注释： 以以“ - - - - ”开头开头 ANSIANSI标准的注释符，用于单行

5、注释。标准的注释符，用于单行注释。 / /* * 注释内容注释内容 * */ / 用于单行或多行注释。用于单行或多行注释。T-SQL编程基础编程基础 注 释 输出语句输出语句 PRINTPRINTl作用：是在屏幕上显示指定信息。作用：是在屏幕上显示指定信息。l语法：语法：PRINT PRINT 常量常量 | | 变量变量| | 表达式表达式| | 函数函数 SELECTSELECTl作用：以列表结构形式在屏幕上显示指定信息。作用：以列表结构形式在屏幕上显示指定信息。l语法：语法：SELECT SELECT 常量常量 | | 变量变量| | 表达式表达式 | | 函数函数 AS AS 别名别名,

6、 n, n示例示例: :PRINT PRINT 服务器的名称：服务器的名称： + SERVERNAME+ SERVERNAMESELECT SERVERNAME AS SELECT SERVERNAME AS 服务器名称服务器名称T-SQL编程基础编程基础l批批是包含一个或多个是包含一个或多个 Transact-SQL Transact-SQL 语句的集合，从语句的集合，从应用程序一次性地发送到服务器执行。应用程序一次性地发送到服务器执行。SQL Server SQL Server 将将批处理语句编译成一个可执行单元，此单元称为执行批处理语句编译成一个可执行单元，此单元称为执行计划。计划。l一

7、般是将一些逻辑相关的业务操作语句放置在同一批一般是将一些逻辑相关的业务操作语句放置在同一批中，这完全由代码编写者决定。中，这完全由代码编写者决定。lGOGO是批处理结束标志。是批处理结束标志。 批处理批处理T-SQL编程基础编程基础lCREATECREATE 语句不能在批处理中与其他语句组合使用。语句不能在批处理中与其他语句组合使用。l不能在同一个批处理中更改表，然后引用新列。不能在同一个批处理中更改表，然后引用新列。l不能在同一个批中删除一个数据库对象又重建它。不能在同一个批中删除一个数据库对象又重建它。l不能在同一个批中将规则或默认绑定给列又使用它们。不能在同一个批中将规则或默认绑定给列又

8、使用它们。l如果如果 EXECUTEEXECUTE 语句是批处理中的第一句，则不需要语句是批处理中的第一句，则不需要 EXECUTE EXECUTE 关键字。关键字。（1） 批处理使用应遵守的规则批处理使用应遵守的规则T-SQL编程基础编程基础 批处理批处理l编译错误使执行计划无法编译，会导致批处理中的任何语编译错误使执行计划无法编译，会导致批处理中的任何语句都无法执行。句都无法执行。l运行时错误产生以下两种影响之一：运行时错误产生以下两种影响之一：u停止执行批处理中当前语句和它之后的语句。停止执行批处理中当前语句和它之后的语句。u停止执行当前语句，继续执行其它所有语句。停止执行当前语句，继续

9、执行其它所有语句。 l在遇到运行时错误之前执行的语句不受影响。在遇到运行时错误之前执行的语句不受影响。（2）批处理运行时错误的影响）批处理运行时错误的影响T-SQL编程基础编程基础 批处理批处理l语法：语法： BEGINBEGIN 执行的执行的SQLSQL语句语句 ENDENDl说明：定义一组语句，通常在说明：定义一组语句，通常在IFIF、ELSEELSE或或WHILEWHILE后面。后面。 （1 1）复合语句（）复合语句（BEGINENDBEGINEND）（2 2） 判断语句（判断语句（IFELSEIFELSE）语法：语法：IFIF 条件表达式条件表达式 SQLSQL语句语句1 1 ELSE

10、ELSE SQL SQL语句语句2 2 说明：当条件为真时，执行一条或一组语句。说明：当条件为真时，执行一条或一组语句。 T-SQL编程基础编程基础流程控制语句流程控制语句示例：求英语课程的平均分数。如果在示例：求英语课程的平均分数。如果在8080以上，则显示以上，则显示 “ “成绩达成绩达到预期目标到预期目标”，并查询高于平均分的学号和分数；否则，显示还，并查询高于平均分的学号和分数；否则，显示还需继续努力需继续努力”，并查询低于平均分的学号和分数。，并查询低于平均分的学号和分数。 USEUSE 教学成绩管理数据库教学成绩管理数据库 GOGO SET NOCOUNT ON SET NOCOU

11、NT ON - - - - 不显示统计信息不显示统计信息 DECLARE DECLARE 平均分平均分 FLOATFLOAT SELECTSELECT 平均分平均分=AVG(=AVG(分数分数) ) FROMFROM 教学成绩表教学成绩表 WHEREWHERE 课程编号课程编号= = ( ( SELECTSELECT 编号编号 FROM FROM 课程信息表课程信息表 WHEREWHERE 名称名称=英语英语) ) PRINTPRINT 英语课程平均分数英语课程平均分数 + STR( + STR(平均分平均分,5,1),5,1)IFIF ( (平均分平均分 80) 80) BEGINBEGIN

12、 PRINTPRINT 成绩达到预期目标成绩达到预期目标! ! SELECTSELECT 学号学号, ,分数分数 FROMFROM 教学成绩表教学成绩表 WHEREWHERE 分数分数平均分平均分 ANDAND 课程编号课程编号= = ( ( SELECTSELECT 编号编号 FROM FROM 课程信息表课程信息表 WHEREWHERE 名称名称=英语英语) ) ENDEND ELSEELSE BEGINBEGIN PRINTPRINT 还需继续努力！低于平均分的学生：还需继续努力！低于平均分的学生： SELECTSELECT 学号学号, ,分数分数 FROMFROM 教学成绩表教学成绩表

13、 WHEREWHERE 分数分数=90 =90 ANDAND 分数分数=100 =80 =80 ANDAND 分数分数90 =70 =70 AND AND 分数分数80 =60 =60 ANDAND 分数分数70 70 THENTHEN 及格及格 ELSEELSE 不及格不及格 ENDEND SELECTSELECT * * FROMFROM #CJB #CJB GOGO示例示例2 2：根据分数计算出成绩等级。：根据分数计算出成绩等级。 USEUSE 教学成绩管理数据库教学成绩管理数据库 GOGO SET NOCOUNT ONSET NOCOUNT ON SELECTSELECT 学号学号,

14、,分数分数, ,成绩成绩 INTOINTO #CJB #CJB FROMFROM 教学成绩表教学成绩表 UPDATEUPDATE #CJB #CJB SETSET 成绩成绩= = CASE CASE FLOORFLOOR( (分数分数/10)/10) WHENWHEN 10 10 THENTHEN 优秀优秀 WHENWHEN 9 9 THENTHEN 优秀优秀 WHEN WHEN 8 8 THENTHEN 良好良好 WHENWHEN 7 7 THENTHEN 中等中等 WHENWHEN 6 6 THENTHEN 及格及格 ELSEELSE 不及格不及格 ENDEND SELECTSELECT

15、* * FROMFROM #CJB #CJB GOGOl语法：语法： WHILE WHILE 条件表达式条件表达式 SQLSQL语句语句 | | SQLSQL语句组语句组l说明：说明： 设置重复执行设置重复执行 SQL SQL 语句或语句组的条件，只要指定语句或语句组的条件，只要指定的条件为真，就重复执行语句。在循环内部可使用如下的条件为真，就重复执行语句。在循环内部可使用如下控制语句：控制语句： uBREAK BREAK : :跳出循环之后执行跳出循环之后执行uCONTINUE CONTINUE : :转到循环开始之处执行转到循环开始之处执行（4 4）循环语句（）循环语句（WHILEWHIL

16、E）T-SQL编程基础编程基础 流程控制语句流程控制语句USEUSE 教学成绩管理数据库教学成绩管理数据库GOGOSET NOCOUNT ONSET NOCOUNT ONSELECTSELECT 学号学号, ,分数分数 INTOINTO #CJB #CJB FROMFROM 教学成绩表教学成绩表 WHEREWHERE 课程编号课程编号=900002=900002DECLAREDECLARE n int n intWHILEWHILE(1=1) (1=1) BEGINBEGIN SELECTSELECT n=COUNT( n=COUNT(* *) ) FROMFROM #CJB #CJB WHE

17、REWHERE 分数分数60 AND 55 55 IFIF (n0) (n0) UPDATEUPDATE #CJB #CJB SETSET 分数分数= =分数分数+5 +5 WHEREWHERE 分数分数9595 ELSEELSE BREAKBREAK ENDENDSELECTSELECT * * FROMFROM #CJB #CJB ORDER BYORDER BY 分数分数GOGO 编写编写存储过程存储过程、自定义函数自定义函数和和触发器触发器是是SQL Server程序设计的灵魂，应用好它程序设计的灵魂，应用好它们，将使数据库的管理和应用更加方便和们，将使数据库的管理和应用更加方便和灵活

18、。灵活。存储过程，触发器和函数存储过程，触发器和函数v T-SQL编程基础编程基础v 存储过程存储过程v 触发器触发器v 自定义函数自定义函数v 小小 结结在数据库实际应用中，存在有带变量数据在数据库实际应用中，存在有带变量数据处理需求，如：处理需求，如：某班学生信息表某班学生信息表、某老师带过某老师带过的学生的学生、某班某门课不及格学生某班某门课不及格学生等。如何更好等。如何更好实现这样的应用功能？实现这样的应用功能？可采用可采用SQL Server中应用最广泛、最灵活的中应用最广泛、最灵活的存储过程技术存储过程技术来实现。来实现。存储过程存储过程 存储过程存储过程是存储在是存储在SQL S

19、erverSQL Server服务器中的一组服务器中的一组预编译预编译的的T-SQLT-SQL语句语句 ，用于完成某项任务。，用于完成某项任务。 存储过程是一个独立的存储过程是一个独立的数据库对象数据库对象，可被应用，可被应用程序调用，以减少重复编写代码。程序调用，以减少重复编写代码。SQL ServerSQL Server系统系统为用户提供一组实用的为用户提供一组实用的系统存储过程系统存储过程，同时支持用，同时支持用户户自定义的存储过程自定义的存储过程。 存储过程存储过程存储过程存储过程 由系统提供，用以管理和显示有关数据库和用户的由系统提供，用以管理和显示有关数据库和用户的信息。存储在信息

20、。存储在mastermaster数据库中并数据库中并以以sp_sp_为前缀为前缀，在任何，在任何数据库中都可以调用。数据库中都可以调用。 存放在存放在用户数据库用户数据库中以中以dt_dt_为前缀的存储过程也是为前缀的存储过程也是一种系统存储过程。一种系统存储过程。 这类存储过程一般在用户创建数这类存储过程一般在用户创建数据库时由系统自动生成，主要用于处理用户数据库中系据库时由系统自动生成，主要用于处理用户数据库中系统自动生成的一些系统表，起到实时维护的作用统自动生成的一些系统表，起到实时维护的作用 系统存储过程系统存储过程存储过程存储过程v 以sp_为前缀的系统存储过程存储过程存储过程v 以

21、dt_为前缀的系统存储过程存储过程存储过程存储过程的概念 用户自定义存储过程用户自定义存储过程由数据库开发人员或管理员编写的完成特定任务的存由数据库开发人员或管理员编写的完成特定任务的存储过程，存储在创建时的数据库中。储过程，存储在创建时的数据库中。我们将重点学习自定义存储过程的创建和使用方法。我们将重点学习自定义存储过程的创建和使用方法。存储过程-单个 SELECT 语句SELECT 语句块可以包含可以包含SELECT语句与逻辑控制语句存储过程中的语句存储过程中的语句 存储过程存储过程 创建存储过程（创建存储过程（SSMSSSMS）存储过程存储过程创建存储过程的模板，只需创建存储过程的模板，

22、只需修改相应的参数就可创建存修改相应的参数就可创建存储过程储过程存储过程存储过程 创建存储过程（创建存储过程（SSMSSSMS） 创建存储过程（创建存储过程（T-SQLT-SQL语句）语句）CREATE PROCDURECREATE PROCDURE 存储过程名存储过程名 参数参数 参数的数据类型参数的数据类型 = =默认值默认值 OUTPUTOUTPUT ,.n ,.nASAS 任意数量的任意数量的T-SQLT-SQL 语句语句 FCREATE PROCEDURE CREATE PROCEDURE 语句中可以声明一个或多个变量作为参数语句中可以声明一个或多个变量作为参数 参数分两种：参数分两

23、种：u输入参数输入参数: :用来向存储过程中传入值，执行存储过程时必须用来向存储过程中传入值，执行存储过程时必须提供参数的值，除非定义了参数的默认值。提供参数的值，除非定义了参数的默认值。 u输出参数输出参数: :由由“ OUTPUT OUTPUT ”标记，存放存储过程执行后的返回标记，存放存储过程执行后的返回值并提供给调用者使用。值并提供给调用者使用。存储过程存储过程 创建不带参数的存储过程创建不带参数的存储过程示例：示例：查询计算机系学生的考试成绩，列出学查询计算机系学生的考试成绩，列出学生的姓名、课程名和成绩生的姓名、课程名和成绩。CREATE PROCEDURE student_gra

24、de1AS SELECT Sname, Cname,Grade FROM s,sc,c WHERE s.sno = sc.sno and o = o and Sdept = 计算机系计算机系 存储过程存储过程存储过程存储过程存储过程的执行（存储过程的执行（SSMSSSMS） EXECEXEC UTEUTE 存储过程名存储过程名 参数参数1 1，参数，参数n n 如果存储过程是如果存储过程是批处理中的第一批处理中的第一条语句，该关键条语句，该关键字可省略。字可省略。存储过程的执行（存储过程的执行（T-SQL)T-SQL)存储过程存储过程查询某个指定系学生的考试情况，列出学生的姓查询某个指定系学生

25、的考试情况，列出学生的姓名、所在系、课程名和考试成绩。名、所在系、课程名和考试成绩。CREATE PROCEDURE student_grade2 dept char(20)AS SELECT Sname,Sdept,Cname,Grade FROM s,sc,c WHERE s.sno = sc.sno and o = o and Sdept = dept 创建带创建带输入参数输入参数的存储过程的存储过程存储过程存储过程示例：示例：查询某个学生某门课程的考试成绩，列出学生查询某个学生某门课程的考试成绩，列出学生的姓名、课程名和成绩。的姓名、课程名和成绩。CREATE PROCEDURE st

26、udent_grade2 s_name char(10), c_name char(20) AS SELECT Sname, Cname, Grade FROM s,sc,c WHERE s.sno = sc.sno and o = o and sname = s_name and cname = c_name 创建带多个输入参数的存储过程创建带多个输入参数的存储过程 存储过程存储过程参数的传递方式参数的传递方式 v按参数位置传递值 EXEC student_grade2 刘晨, 数学 v按参数名传递值 EXEC Student_grade2 student_name = 刘晨, course

27、_name=数学 示例：示例：查询某个学生某门课程的考试成绩，若没有查询某个学生某门课程的考试成绩，若没有指定课程，则默认课程为指定课程，则默认课程为“数据库数据库”。CREATE PROCEDURE student_grade2 s_name char(10), c_name char(20)=数据库数据库AS SELECT Sname, Cname, Grade FROM s,sc,c WHERE s.sno = sc.sno and o = o and sname = s_name and cname = c_name 创建带多个输入参数并有默认值的存储过程创建带多个输入参数并有默认值的

28、存储过程 存储过程存储过程调用参数有默认值的存储过程调用参数有默认值的存储过程EXEC student_grade2 李勇v 等价于执行：EXEC student_grade2 李勇, 数据库 创建带输入参数和一个输出参数的存储过程创建带输入参数和一个输出参数的存储过程 v 统计指定课程的平均成绩，并将统计的结果用输出参数返回。CREATE PROCEDURE AvgGrade cn char(20), avg_grade int outputAS SELECT avg_grade = AVG(Grade) FROM SC,C WHERE C.Cno = SC.Cno and Cname =

29、cn 执行存储过程v declare avg_grade int v exec AvgGrade 数据库,avg_grade outputv select avg_grade 平均成绩创建带输入参数和多个输出参数的存储过程创建带输入参数和多个输出参数的存储过程 v 统计指定课程的平均成绩和选课人数，将统计的结果用输出参数返回。CREATE PROCEDURE Avg_Count cn char(20), avg_grade int output, total int outputAS SELECT avg_grade = AVG(Grade), total = COUNT(*) FROM SC

30、 ,C WHERE C.Cno = SC.Cno and Cname = cn 执行存储过程：v declare avg_grade int, total intv exec Avg_Count 数学,avg_grade output,total outputv select avg_grade 平均成绩,total 选课人数存储过程的执行F 要使用输出参数，必须在要使用输出参数，必须在CREATE PRODECURECREATE PRODECURE 语语句和句和EXECUTEEXECUTE语句中指定语句中指定OUTPUTOUTPUT关键字。在执行存关键字。在执行存储过程时，如果忽略储过程时，

31、如果忽略OUTPUTOUTPUT关键字，存储过程仍然关键字，存储过程仍然会执行但不返回值会执行但不返回值创建删除数据的存储过程创建删除数据的存储过程 v删除考试成绩不及格学生的修课记录。CREATE PROCEDURE p_DeleteSCASDELETE FROM sc WHERE grade 60 创建修改数据的存储过程创建修改数据的存储过程 v将指定课程的学分增加2分。CREATE PROCEDURE p_UpdateCredit cn varchar(20)AS UPDATE course SET credit=credit+2 WHERE cname = cn 存储过程存储过程存储过

32、程的修改（存储过程的修改（SSMS)SSMS)ALTER PROCDUREALTER PROCDURE 存储过程名存储过程名 参数参数 参数的数据类型参数的数据类型 = =默认值默认值 OUTPUT ,.n OUTPUT ,.nAS AS 任意数量的任意数量的T-SQLT-SQL 语句语句 存储过程存储过程存储过程的修改（存储过程的修改（T-SQL)T-SQL)存储过程存储过程存储过程的删除（存储过程的删除（SSMS)SSMS)示例示例: :删除删除 补课表补课表 存储过程。存储过程。 USE USE 教学成绩管理数据库教学成绩管理数据库 GO GO DROP PROC DROP PROC 补

33、考表补考表DROP DROP PROCPROC EDUREEDURE 存储过程存储过程名名 ,. ,. 存储过程存储过程存储过程的修改（存储过程的修改（T-SQL)T-SQL)一旦创建并存储后可被任意一旦创建并存储后可被任意多次调用多次调用，支持程序，支持程序模块模块化化的设计。的设计。 第一次被调用后就驻留内存，再次调用时不再需要编第一次被调用后就驻留内存，再次调用时不再需要编译，译，执行速率较高执行速率较高 。存储过程存储过程存储在后端数据库存储在后端数据库中不需要通过网络传输，中不需要通过网络传输，可可减少网络流量减少网络流量 。即使对于没有直接执行存储过程中语句权限的用户，即使对于没有

34、直接执行存储过程中语句权限的用户，也可授予他执行该存储过程的权限，可作为也可授予他执行该存储过程的权限，可作为安全机制安全机制使用使用 。 存储过程的优点存储过程的优点: :存储过程存储过程增加复杂性增加复杂性。当业务逻辑比较复杂时不建议使用存储。当业务逻辑比较复杂时不建议使用存储过程，这样会增加复杂性。过程，这样会增加复杂性。可移植性低可移植性低。（不同的数据库系统）。（不同的数据库系统） 存储过程的缺点存储过程的缺点: :存储过程存储过程存储过程，触发器和函数存储过程，触发器和函数v T-SQL编程基础编程基础v 存储过程存储过程v 触发器触发器v 自定义函数自定义函数v 小结小结 触发器

35、触发器是特殊类型的存储过程，主要由操作事是特殊类型的存储过程，主要由操作事件件( (INSERTINSERT、UPDATEUPDATE、DELETEDELETE) ) 触发而被自动执行。触发而被自动执行。 触发器可以实现比约束更复杂的数据完整性触发器可以实现比约束更复杂的数据完整性，经常用于经常用于加强加强数据的数据的完整性约束完整性约束和和业务规则业务规则。 触发器本身是一个特殊的触发器本身是一个特殊的事务单位事务单位。 触发器触发器触发器触发器u 与表相关联与表相关联：必须定义在表上。：必须定义在表上。 u 自动触发自动触发：由：由INSERTINSERT、DELETEDELETE、UPD

36、ATEUPDATE操作触发操作触发u 不能直接调用，也不能传递或接受参数不能直接调用，也不能传递或接受参数u 是事务的一部分是事务的一部分：触发器和触发语句作为可在触：触发器和触发语句作为可在触 发器内回滚的单个事务。发器内回滚的单个事务。 触发器的特点触发器的特点触发器触发器u 按触发时刻分按触发时刻分- AFTERAFTER触发器：执行触发操作和处理完约束后激发，触发器：执行触发操作和处理完约束后激发， 只能定义于表上，同一个表上可定义多个。只能定义于表上，同一个表上可定义多个。- INSTEADINSTEAD触发器：由触发器的程序代替触发语句的执行，在处触发器：由触发器的程序代替触发语句

37、的执行，在处 理约束之前激发，可在表或视图上创建，同理约束之前激发，可在表或视图上创建，同 一个表或视图只可定义一个。一个表或视图只可定义一个。u 按触发语句分按触发语句分- INSERTINSERT触发器、触发器、 UPDATEUPDATE触发器、触发器、 DELETEDELETE触发器触发器 触发器的类型触发器的类型触发器触发器 触发器触发器被被触发时触发时，系统自动在内存中创建系统自动在内存中创建deleteddeleted表表或或insertedinserted表表。只读，不允许修改只读，不允许修改；触发器执行完成后，触发器执行完成后，自动删除自动删除。 insertedinserte

38、d表表 和和 deleted deleted 表表触发器触发器修改操作修改操作inserted表表deleted表表增加增加(INSERT)(INSERT)记录记录存放新增的记录存放新增的记录-删除删除(DELETE)(DELETE)记录记录-存放被删除的记录存放被删除的记录修改修改(UPDATE)(UPDATE)记录记录存放更新后的记录存放更新后的记录存放更新前的记录存放更新前的记录DeletedDeleted表和表和InsertedInserted表表在对具有触发器的表（触发器表）进行操作时，其操作过程：v 执行INSERT操作，插入到触发器表中的新行被自动的插入到Inserted 表中。

39、v 执行DELETE操作，从触发器表中删除的行被自动的插入到Deleted 表中。v 执行UPDATE操作，先从触发器表中删除旧行，然后再插入新行。其中被删除的旧行被插入到Deleted 表中，插入的新行被插入到Inserted 表中。 触发器触发器 触发器的创建（触发器的创建（SSMSSSMS）创建触发器的模板，只需修改创建触发器的模板，只需修改相应的参数就可创建触发器相应的参数就可创建触发器CREATE TRIGGERCREATE TRIGGER 触发器名触发器名ONON 表名或视图名表名或视图名 FORFOR | | AFTERAFTER | | INSTEAD OFINSTEAD OF

40、 INSERTINSERT , , UPDATEUPDATE , , DELETEDELETEAS AS IF UPDATEIF UPDATE( (列名列名1) 1) ANDAND| |OROR UPDATEUPDATE( (列名列名2)2) .n .n SQL SQL语句语句触发器触发器 触发器的创建（触发器的创建（T-SQLT-SQL）F 主要参数说明主要参数说明u AFTERAFTER: :执行指定操作语句和处理完约束后激发触发器。执行指定操作语句和处理完约束后激发触发器。u FORFOR: :如果仅指定如果仅指定 FOR FOR 关键字，则关键字，则 AFTER AFTER 是默认设置

41、。是默认设置。u INSTEAD OFINSTEAD OF: :指定语句激发触发器并被代替执行。指定语句激发触发器并被代替执行。u DELETEDELETE、INSERTINSERT、UPDATEUPDATE：指定激活触发器的数据修改：指定激活触发器的数据修改语句，必须至少指定一个选项，若指定的选项多于一个，语句，必须至少指定一个选项，若指定的选项多于一个，需用逗号分隔这些选项。需用逗号分隔这些选项。u ASAS：指定触发器要执行的操作。：指定触发器要执行的操作。u IF UPDATEIF UPDATE( (列名列名1) 1) ANDAND| |OROR UPDATEUPDATE( (列名列名

42、2)2): :指定何指定何列数据修改时激发触发器。不能用于列数据修改时激发触发器。不能用于DELETEDELETE语句。语句。触发器触发器F 创建触发器时需要指定如下内容创建触发器时需要指定如下内容: :u 触发器名称：触发器名。触发器名称：触发器名。u 何处触发：表名或视图名。何处触发：表名或视图名。u 何时激发：何时激发：FOR|AFTER |INSTEAD OF FOR|AFTER |INSTEAD OF 。u 何种数据修改语句触发：何种数据修改语句触发：INSERT|UPDATE|DELETE INSERT|UPDATE|DELETE 。u 何列数据修改时触发何列数据修改时触发: IF

43、 UPDATE(: IF UPDATE(列名）列名）u 如何触发：如何触发：SQLSQL语句指定触发器触发时所作的操作。语句指定触发器触发时所作的操作。触发器触发器定义触发器定义触发器例1：在S表创建UPDATE触发器Create trigger t1_son sfor update as print the table s was updated例2 创建触发器，实现如下约束： 当删除S表中学生信息时，级联删除相应的选课信息。更具通用性的触发器更具通用性的触发器CREATE TRIGGER S_del_trigger ON student FOR DELETE AS DELETE FROM

44、sc WHERE sc.Sno in (select sno from deleted) CREATE TRIGGER S_del_trigger ON student FOR DELETE AS declare sno char(8)Select sno =deleted.sno from deleted DELETE FROM sc WHERE sc.Sno =sno定义触发器定义触发器例3定义一个触发器，实现教师工资表sal_Teach中的以下约束： “教授的实发工资不得低于4000元，如果低于4000元，自动改为4000元”。方法一：create trigger t1 on sal_t

45、eachafter insert,updateas declare sal float,job char(8),eno char(4)select eno=eno,sal=sal,job=job from inserted if sal4000 and job=教授 update sal_teach set sal=4000 where eno=eno1.3 1.3 定义触发器定义触发器方法二：create trigger t2 on sal_teachfor insert,updateas update sal_teach set sal=4000 from sal_teach,insert

46、ed where inserted.eno= sal_teach.eno and sal_teach.Job=教授 and sal_teach.sal4000删除触发器定义删除触发器定义v通过使用DROP TRIGGER语句，即可从当前数据库表中删除触发器。 vDROP TRIGGER语句的命令格式为： DROP TRIGGER 触发器名 查看触发器查看触发器v若要查看某一特定表上现有的触发器，使用存储过程：sp_helptrigger 数据表名v若要查看已经建好的触发器代码。使用存储过程：sp_helptext 触发器名， 修改触发器（修改触发器（SSMSSSMS）触发器触发器 修改触发器（

47、修改触发器（T-SQLT-SQL） ALTER TRIGGERALTER TRIGGER 触发器名触发器名ONON 表名或视图名表名或视图名 FORFOR | | AFTERAFTER | | INSTEAD OFINSTEAD OF INSERTINSERT , , UPDATEUPDATE , , DELETEDELETEASAS IF UPDATEIF UPDATE( (列名列名1) 1) ANDAND| |OROR UPDATEUPDATE( (列名列名2)2) .n .n SQLSQL语句语句触发器触发器 禁用或启用触发器（禁用或启用触发器（SSMSSSMS）触发器触发器ALTER

48、TABLEALTER TABLE 表名表名 ENABLEENABLE | | DISABLEDISABLE TRIGGERTRIGGER ALL ALL | | 触发器名触发器名,n n 禁用或启用触发器（禁用或启用触发器（T-SQLT-SQL）触发器触发器 删除触发器（删除触发器（SSMSSSMS）触发器触发器F 如果删除表，则表中所有的触发器将被自动删除。如果删除表，则表中所有的触发器将被自动删除。 DROP TRIGGERDROP TRIGGER 触发器名触发器名 删除触发器（删除触发器（T-SQLT-SQL）触发器触发器F 触发器使用限制触发器使用限制: :u CREATE TRIGG

49、ER CREATE TRIGGER 必须是必须是批处理中的第一条语句批处理中的第一条语句。 u 在同一个表中约束的执行优先于触发器，若在操作中触发器在同一个表中约束的执行优先于触发器，若在操作中触发器与约束发生冲突，触发器将不执行。与约束发生冲突，触发器将不执行。u 不能在临时表上建立触发器，但是在触发器定义中可以引用不能在临时表上建立触发器，但是在触发器定义中可以引用临时表。临时表。u 触发器中不允许以下触发器中不允许以下 T-SQL T-SQL 语句：语句：ALTER DATABASEALTER DATABASECREATE DATABASECREATE DATABASEDISK INIT

50、DISK INITDISK RESIZEDISK RESIZEDROP DATABASEDROP DATABASELOAD DATABASELOAD DATABASELOAD LOGLOAD LOGRECONFIGURERECONFIGURERESTORE DATABASERESTORE DATABASERESTORE LOGRESTORE LOG触发器触发器存储过程，触发器和函数存储过程，触发器和函数v T-SQL编程基础编程基础v 存储过程存储过程v 触发器触发器v 自定义函数自定义函数v 小结小结 函数函数是由一个或多个是由一个或多个 Transact-SQL Transact-SQL

51、语句组成的子语句组成的子程序，可用于封装代码以便重复使用。程序，可用于封装代码以便重复使用。 在在SQL ServerSQL Server中，除了系统内置的函数外，用户还中，除了系统内置的函数外，用户还可以可以自定义函数自定义函数，用以补充和扩展系统支持的内置函数。，用以补充和扩展系统支持的内置函数。 函数必须有返回值，根据函数返回值形式的不同，函数必须有返回值，根据函数返回值形式的不同，自定义函数分为：自定义函数分为：标量值函数标量值函数、内联表值函数内联表值函数、多语句多语句表值函数表值函数三种三种 关于自定义函数关于自定义函数自定义函数自定义函数 标量值函数标量值函数返回一个返回一个确定

52、类型的标量值确定类型的标量值，其返，其返回值类型为回值类型为除除TEXTTEXT、 NTEXTNTEXT、 IMAGEIMAGE、CURSORCURSOR、 TIMESTAMP TIMESTAMP 和和TABLE TABLE 类型外的其它数据类型。类型外的其它数据类型。 函数体语句定义在函数体语句定义在BEGIN-ENDBEGIN-END语句内，其中包语句内，其中包含了可以返回值的含了可以返回值的T-SQL T-SQL 命令。命令。（1 1）标量值函数）标量值函数自定义函数自定义函数 关于自定义函数关于自定义函数 内联表值函数内联表值函数的返回值是一个的返回值是一个表表。 内联表值函数内联表值

53、函数没有没有由由BEGIN-END BEGIN-END 语句括起来的函语句括起来的函数体，只有数体，只有一个一个 SELECT SELECT 语句语句。其返回的表位于。其返回的表位于RETURNRETURN 子句中的子句中的SELECTSELECT 语句从数据库中筛选出来。语句从数据库中筛选出来。 内联表值函数功能相当于一个内联表值函数功能相当于一个参数化的视图参数化的视图。 自定义函数自定义函数（1 1）内联表值函数）内联表值函数 关于自定义函数关于自定义函数 是标量型和内联表值型函数的结合体。它的返是标量型和内联表值型函数的结合体。它的返回值是一个回值是一个表表，但它和标量型函数一样有一个

54、用，但它和标量型函数一样有一个用BEGIN-ENDBEGIN-END 语句括起来的函数体，返回值的表数据语句括起来的函数体，返回值的表数据是由函数体中的语句插入的。是由函数体中的语句插入的。 它可以进行它可以进行多次查询多次查询，对数据进行，对数据进行多次筛选与多次筛选与合并合并，弥补了内联表值型函数的不足。，弥补了内联表值型函数的不足。 （1 1）多语句表值函数）多语句表值函数自定义函数自定义函数 关于自定义函数关于自定义函数 创建函数（创建函数（SSMSSSMS）自定义函数自定义函数创建函数的模创建函数的模板，只需修改板，只需修改相应的参数就相应的参数就可创建自定义可创建自定义函数函数格式

55、格式1 1： CREATE FUNCTIONCREATE FUNCTION 所有者所有者.自定义函数名自定义函数名(参数参数n) n) RETURNSRETURNS 返回参数的类型返回参数的类型 AS AS BEGINBEGIN 函数体函数体 RETURN RETURN 函数返回的标量值函数返回的标量值END END F 该语法格式用于创建标量值函数；该语法格式用于创建标量值函数；F RETURNRETURN子句返回单个数据值，如同使用系统内置的函数一样。子句返回单个数据值，如同使用系统内置的函数一样。 创建函数（创建函数（T-SQLT-SQL）自定义函数自定义函数示例：创建一个自定义函数示例

56、：创建一个自定义函数is_is_中文字符串中文字符串 ，判断自变量是否，判断自变量是否是纯中文字符串，返回字符串：是纯中文字符串，返回字符串：是是或或否否 。 CREATE FUNCTIONCREATE FUNCTION isis中文字符串中文字符串(字符串字符串 NCHARNCHAR(255)(255) RETURNSRETURNS NCHAR NCHAR(1) (1) ASAS BEGINBEGIN DECLARE DECLARE I I TINYINTTINYINT, J , J TINYINTTINYINT SET SET I= I=LENLEN(字符串字符串) ) SET SET J

57、=1 J=1 WHILE WHILE (J=I) (J=I) BEGINBEGIN IFIF ( (UNICODEUNICODE( (SUBSTRINGSUBSTRING(字符串字符串,J,1)256) ,J,1)256) RETURNRETURN 否否 SET SET J=J+1 J=J+1 ENDEND RETURNRETURN 是是 ENDEND自定义函数自定义函数格式格式2 2： CREATE FUNCTIONCREATE FUNCTION 所有者所有者.自定义函数名自定义函数名(参数参数n n RETURNS TABLE ASRETURNS TABLE AS RETURN RETUR

58、N ( SELECT ( SELECT 查询语句查询语句 ) )F 该语法格式用于创建表值函数；该语法格式用于创建表值函数；F 返回值是一个返回值是一个SelectSelect语句查询结果的表。语句查询结果的表。自定义函数自定义函数 创建函数（创建函数（T-SQLT-SQL）示例：创建一个自定义函数：示例：创建一个自定义函数： 某班某课不及格表某班某课不及格表 输入参数班级输入参数班级名称和课程名称，返回：表。名称和课程名称，返回：表。 CREATE FUNCTION CREATE FUNCTION 某班某课不及格表某班某课不及格表 (class VARCHAR(16),course VARC

59、HAR(50)(class VARCHAR(16),course VARCHAR(50) RETURNSRETURNS TABLETABLE ASAS RETURNRETURN ( ( SELECT SELECT 学号学号, ,姓名姓名, ,课程名称课程名称, ,分数分数, ,学年学期学年学期, ,考试类别考试类别 FROMFROM 教学成绩表视图教学成绩表视图 WHERE WHERE 班级班级=class AND =class AND 课程名称课程名称=course AND =course AND 分数分数60)60)自定义函数自定义函数格式格式3 3： CREATE FUNCTIONCRE

60、ATE FUNCTION 所有者所有者.自定义函数名自定义函数名(参数参数,n ,n RETURNSRETURNS 数据表名数据表名 TABLETABLE ( (列名列名 数据类型数据类型,n),n) ASAS BEGINBEGIN 有效的语句有效的语句 RETURNRETURN ENDENDF 该语法格式用于创建多语句表值函数；该语法格式用于创建多语句表值函数；F 返回值是一个表。返回值是一个表。自定义函数自定义函数 创建函数（创建函数（T-SQLT-SQL）F多语句表值函数的主体中有效的语句是：多语句表值函数的主体中有效的语句是： 赋值语句。赋值语句。 流程控制语句。流程控制语句。 DEC