触发器与存储过程PPT课件

1、2021/3/9信息系统系信息系统系数据库系统概论数据库系统概论An Introduction to Database System触发器与存储过程触发器与存储过程2021/3/9Transact-SQL程序设计程序设计n数据类型n系统提供的数据类型n用户自定义的数据类型n变量与常量n运算符与表达式n算术运算符与表达式n位运算符与表达式n比较运算符与表达式n逻辑运算符与表达式n连接运算符与表达式2021/3/9Transact-SQL程序设计程序设计n函数n常用函数：字符串函数 ，数学函数，日期函数n用户定义函数n程序控制流语句n语句块BEGIN.ENDn条件执行语句IFELSEnWHILE循

2、环语句2021/3/9n变量用于临时存放数据，变量中的数据随着程序的运行而变化，变量有名字与数据类型两个属性。n变量的命名使用常规标识符，即以字母、下划线(_)、at符号()、数字符号(#)开头，后续字母、数字、at符号、美元符号($)、下划线的字符序列。不允许嵌入空格或其他特殊字符。1变量变量9.1.2 9.1.2 常量与变量常量与变量2021/3/9n全局变量由系统定义并维护，通过在名称前面加“”符号n局部变量的首字母为单个“”。全局变量和局部变量全局变量和局部变量9.1.2 9.1.2 常量与变量常量与变量2021/3/9(1)局部变量局部变量n局部变量使用DECLARE语句定义DECL

3、ARE local_variable data_type ,.nn变量名最大长度为30个字符。一条DECLARE语句可以定义多个变量，各变量之间使用逗号隔开。n例如DECLARE name varchar(30),type int9.1.2 9.1.2 常量与变量常量与变量2021/3/9局部变量的赋值局部变量的赋值 用SELECT为局部变量赋值SELECT variable_name=expression , nFROM WHERE n例如nDECLARE int_var intnSELECT int_var =12 /*给int_var赋值*/nSELECT int_var /*将int_

4、var的值输出到屏幕上*/9.1.2 9.1.2 常量与变量常量与变量2021/3/9在一条语句中可以同时对几个变量进行赋值在一条语句中可以同时对几个变量进行赋值n例如nDECLARE LastName char(8),Firstname char(8),BirthDate datetimenSELECT LastName=Smith,Firstname=David,BirthDate=1985-2-20nSELECT LastName,Firstname,BirthDaten局部变量没有被赋值前，其值是NULL，若要在程序中引用它，必须先赋值。 9.1.2 9.1.2 常量与变量常量与变量2

5、021/3/9例1 使用SELECT语句从S表中检索出学生编号为“95001”的行，再将学生的名字赋给变量stuname。DECLARE stuname varchar(40) SELECT stuname=snameFROM SWHERE Sno=950019.1.2 9.1.2 常量与变量常量与变量2021/3/9 用用SET给局部变量赋值给局部变量赋值nSET语句格式为：SET local_variable=expressionn使用SET初始化变量的方法与SELECT语句相同，但一个SET语句只能为一个变量赋值。例2 计算S表的记录数并赋值给局部变量rows。DECLARE rows

6、intSET rows=(SELECT COUNT(*) FROM S)SELECT rows9.1.2 9.1.2 常量与变量常量与变量2021/3/9(2)全局变量全局变量n全局变量通常被服务器用来跟踪服务器范围和特定会话期间的信息，不能显式地被赋值或声明。n全局变量不能由用户定义，也不能被应用程序用来在处理器之间交叉传递信息。 9.1.2 9.1.2 常量与变量常量与变量2021/3/92 函函 数数2.1 常用函数2.2 用户定义函数2021/3/99.2 9.2 函函 数数2.1 常用函数常用函数n函数是组编译好的Transact-SQL语句，它们可以带一个或一组数值做参数，也可不带

7、参数，它返回一个数值、数值集合，或执行一些操作。n函数能够重复执行一些操作，从而避免不断重写代码。nSQL Server 2000支持两种函数类型：(1) 内置函数:是一组预定义的函数，是Transact-SQL语言的一部分，按Transact-SQL参考中定义的方式运行且不能修改。(2) 用户定义函数:由用户定义的Transact-SQL函数。它将频繁执行的功能语句块封装到一个命名实体中，该实体可以由Transact-SQL语句调用。2021/3/99.2.1 9.2.1 常用函数常用函数日期函数日期函数n日期函数用来操作datetime和smalldatetime类型的数据，执行算术运算。

8、与其他函数一样，可以在SELECT语句和WHERE子句以及表达式中使用日期函数。 2021/3/99.2.1 9.2.1 常用函数常用函数SQL Server的日期函数的日期函数函数名称及格式描 述Getdate()返回当前系统的日期和时间返回当前系统的日期和时间Datename(datepart, date_expr)以字符串形式返回以字符串形式返回date_exprdate_expr中的指定部分，如果合适的话还将其中的指定部分，如果合适的话还将其转换为名称转换为名称( (如如June)June)Datepart(datepart, date_expr)以整数形式返回以整数形式返回date_

9、exprdate_expr中的中的datepartdatepart指定部分指定部分Datediff(datepart,date_expr1,date_expr2)以以datepartdatepart指定的方式，返回指定的方式，返回date_expr2date_expr2与与date_expr1date_expr1之差之差Dateadd(datepart, number, date_expr)返回以返回以datepartdatepart指定方式表示的指定方式表示的date_exprdate_expr加上加上numbernumber以后的日期以后的日期Day(date_expr)返回返回date_

10、exprdate_expr中的日期值中的日期值Month(date_expr)返回返回date_exprdate_expr中的月份值中的月份值Year(date_expr)返回返回date_exprdate_expr中的年份值中的年份值2021/3/99.2.1 9.2.1 常用函数常用函数日期部分日期部分日期部分日期部分写写 法法取值范围取值范围Yearyy17539999Quarterqq14Monthmm112Dayofyeardy1366Daydd131Weekwk154Weekdaydw17(MonSun)Hourhh023Minutemi059Secondss059Millisec

11、ondms09992021/3/99.2.1 9.2.1 常用函数常用函数例：DECLARE int_var intSELECT int_var =datediff(dd,2009-10-11,2009-11-10) SELECT int_varn为了从datediff中得到一个正值，应注意把较早的日期放在前面 2021/3/99.2.1 9.2.1 常用函数常用函数例：DECLARE char_var varchar(10)SELECT char_var =datename(dd,2009-10-11)SELECT char_var 2021/3/91 SQL Server 2000触发器触

12、发器1.1 SQL Server 2000触发器概述1.2 Deleted表和Inserted表 1.3 定义触发器1.4 删除触发器定义1.5 查看触发器 2021/3/91.1 Server Server 20001.1 Server Server 2000触发器概述触发器概述n触发器是一种特殊的存储过程。n不需要由用户调用执行，而是当用户对表中的数据进行UPDATE、INSERT或DELETE操作时系统自动触发执行的。n触发器通常用于保证业务规则和数据完整性，其主要优点是用户可以用编程的方法来实现复杂的处理逻辑和商业规则，增强了数据完整性约束的功能。 2021/3/91.1 Server

13、 Server 20001.1 Server Server 2000触发器概述触发器概述n使用触发器可以实施更为复杂的数据完整性约束。n在同一数据库中的简单参照完整性常通过PRIMARY KEY约束和FOREIGN KEY约束来实现。但参照完整性约束不能参照其他数据库中的对象； 而可跨数据库的参照完整性约束只能通过触发器来实现。nCHECK约束只能引用当前列（列级CHECK约束）或当前表（表级CHECK约束）中的列值，而触发器则可引用其他表中的列值。2021/3/91.1 Server Server 20001.1 Server Server 2000触发器概述触发器概述nSQL Server

14、 2000提供以下两种触发方式：n后触发(AFTER)：在触发操作（INSERT、 UPDATE或DELETE）执行完成，并处理过所有约束后激活触发器，这种方式称为后触发。n替代触发(INSTEAD OF )：当触发操作发生时，不是执行触发的SQL语句，而转去执行替代触发器。在表和视图中，每个INSERT、 UPDATE或DELETE最多可以定义一个INSTEAD OF 触发器。 2021/3/91 SQL Server 2000触发器触发器1.1 SQL Server 2000触发器概述1.2 Deleted表和Inserted表 1.3 定义触发器1.4 删除触发器定义1.5 查看触发器

15、2021/3/91.2 Deleted1.2 Deleted表和表和InsertedInserted表表n在触发器执行的时候，系统会产生两个临时表：Inserted 表和Deleted 表。n它们的结构和触发器所在的表的结构相同，SQL Server 2000 自动创建和管理这些表。n可以使用这两个临时的驻留内存的表测试某些数据修改的效果及设置触发器操作的条件，然而不能直接对表中的数据进行更改。 2021/3/91.2 Deleted1.2 Deleted表和表和InsertedInserted表表nDeleted 表n用于存储 DELETE 和 UPDATE 语句所影响的行的副本。在执行 D

16、ELETE 或 UPDATE 语句时，行从触发器表中删除，并传输到 deleted 表中。Deleted 表和触发器表通常没有相同的行。nInserted 表n用于存储 INSERT 和 UPDATE 语句所影响的行的副本。在一个插入或更新事务处理中，新建行被同时添加到 Inserted 表和触发器表中。Inserted 表中的行是触发器表中新行的副本。2021/3/91.2 Deleted1.2 Deleted表和表和InsertedInserted表表在对具有触发器的表（触发器表）进行操作时，其操作过程：n执行INSERT操作，插入到触发器表中的新行被自动的插入到Inserted 表中。n

17、执行DELETE操作，从触发器表中删除的行被自动的插入到Deleted 表中。n执行UPDATE操作，先从触发器表中删除旧行，然后再插入新行。其中被删除的旧行被插入到Deleted 表中，插入的新行被插入到Inserted 表中。 2021/3/91 SQL Server 2000触发器触发器1.1 SQL Server 2000触发器概述1.2 Deleted表和Inserted表 1.3 定义触发器1.4 删除触发器定义1.5 查看触发器 2021/3/91.3 1.3 定义触发器定义触发器n语法格式：CREATE TRIGGER 触发器名称ON 表名 FOR | AFTER | INST

18、EAD OF INSERT , DELETE , UPDATE AS SQL 语句 . n 2021/3/91.3 1.3 定义触发器定义触发器例1：在S表创建UPDATE触发器Create trigger t1_son sfor update as print the table s was updated2021/3/9例2 创建触发器，实现如下约束： 当删除S表中学生信息时，级联删除相应的选课信息。2021/3/91.3 1.3 定义触发器定义触发器例3定义一个触发器，实现教师工资表sal_Teach中的以下约束： “教授的实发工资不得低于4000元，如果低于4000元，自动改为4000

19、元”。方法一：create trigger t1 on sal_teachafter 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=eno2021/3/91.3 1.3 定义触发器定义触发器方法二：create trigger t2 on sal_teachfor insert,updateas update sal

20、_teach set sal=4000 from sal_teach,inserted where inserted.eno= sal_teach.eno and sal_teach.Job=教授 and sal_teach.sal= age 2021/3/9执行执行n执行1：不提供任何参数值。EXEC P_Studentn执行2：提供全部参数值。EXEC P_Student 信息系, 女, 19n执行3：只提供第二个参数的值。EXEC P_Student sex = 女n执行4：只提供第一个和第三个参数的值。EXEC P_Student sex=女 , age = 192021/3/9创建带有输出参数的存储过程创建带有输出参数的存储过程 n计算两个数的和。CREATE PROCEDURE sumvar1 int, var2 int, var3 int outputAs Set var3 = var1 * var2 n执行此存储过程：Declare res intExecute Proc1 5,7,res outputPrint res 2021/3/9创建带输入参数和一个输出参数的存储过程创建带输入参数和一个输出参数的存储过程 n统计指定课程的平均成绩，并将统计的结果用输出参数返回。CREATE PROCEDURE AvgGrade cn char(20), a