触发器与存储过程精选文档.ppt

《触发器与存储过程精选文档.ppt》由会员分享，可在线阅读，更多相关《触发器与存储过程精选文档.ppt（59页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、触发器与存储过程触发器与存储过程本讲稿第一页，共五十九页Transact-SQL程序设计程序设计n数据类型n系统提供的数据类型n用户自定义的数据类型n变量与常量n运算符与表达式n算术运算符与表达式n位运算符与表达式n比较运算符与表达式n逻辑运算符与表达式n连接运算符与表达式本讲稿第二页，共五十九页Transact-SQL程序设计程序设计n函数n常用函数：字符串函数，数学函数，日期函数n用户定义函数n程序控制流语句n语句块BEGIN.ENDn条件执行语句IFELSEnWHILE循环语句本讲稿第三页，共五十九页n变量用于临时存放数据，变量中的数据随着程序的运行而变化，变量有名字与数据类型两个属性。

2、n变量的命名使用常规标识符，即以字母、下划线(_)、at符号()、数字符号(#)开头，后续字母、数字、at符号、美元符号($)、下划线的字符序列。不允许嵌入空格或其他特殊字符。1变量变量9.1.2 9.1.2 常量与变量常量与变量本讲稿第四页，共五十九页n全局变量由系统定义并维护，通过在名称前面加“”符号n局部变量的首字母为单个“”。全局变量和局部变量全局变量和局部变量9.1.2 9.1.2 常量与变量常量与变量本讲稿第五页，共五十九页(1)局部变量局部变量n局部变量使用DECLARE语句定义DECLARE local_variable data_type,.nn变量名最大长度为30个字符。一

3、条DECLARE语句可以定义多个变量，各变量之间使用逗号隔开。n例如DECLARE name varchar(30),type int9.1.2 9.1.2 常量与变量常量与变量本讲稿第六页，共五十九页局部变量的赋值局部变量的赋值 用SELECT为局部变量赋值SELECT variable_name=expression,nFROM WHERE n例如nDECLARE int_var intnSELECT int_var=12 /*给int_var赋值*/nSELECT int_var /*将int_var的值输出到屏幕上*/9.1.2 9.1.2 常量与变量常量与变量本讲稿第七页，共五十九页

4、在一条语句中可以同时对几个变量进行赋值在一条语句中可以同时对几个变量进行赋值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 常量与变量常量与变量本讲稿第八页，共五十九页例1 使用SELECT语句从S表中检索出学生编号为“95001”的

5、行，再将学生的名字赋给变量stuname。DECLARE stuname varchar(40)SELECT stuname=snameFROM SWHERE Sno=950019.1.2 9.1.2 常量与变量常量与变量本讲稿第九页，共五十九页 用用SET给局部变量赋值给局部变量赋值nSET语句格式为：SET local_variable=expressionn使用SET初始化变量的方法与SELECT语句相同，但一个SET语句只能为一个变量赋值。例2 计算S表的记录数并赋值给局部变量rows。DECLARE rows intSET rows=(SELECT COUNT(*)FROM S)SE

6、LECT rows9.1.2 9.1.2 常量与变量常量与变量本讲稿第十页，共五十九页(2)全局变量全局变量n全局变量通常被服务器用来跟踪服务器范围和特定会话期间的信息，不能显式地被赋值或声明。n全局变量不能由用户定义，也不能被应用程序用来在处理器之间交叉传递信息。9.1.2 9.1.2 常量与变量常量与变量本讲稿第十一页，共五十九页2 函函 数数2.1 常用函数2.2 用户定义函数本讲稿第十二页，共五十九页9.2 9.2 函函 数数2.1 常用函数常用函数n函数是组编译好的Transact-SQL语句，它们可以带一个或一组数值做参数，也可不带参数，它返回一个数值、数值集合，或执行一些操作。n

7、函数能够重复执行一些操作，从而避免不断重写代码。nSQL Server 2000支持两种函数类型：(1)内置函数:是一组预定义的函数，是Transact-SQL语言的一部分，按Transact-SQL参考中定义的方式运行且不能修改。(2)用户定义函数:由用户定义的Transact-SQL函数。它将频繁执行的功能语句块封装到一个命名实体中，该实体可以由Transact-SQL语句调用。本讲稿第十三页，共五十九页9.2.1 9.2.1 常用函数常用函数日期函数日期函数n日期函数用来操作datetime和smalldatetime类型的数据，执行算术运算。与其他函数一样，可以在SELECT语句和WH

8、ERE子句以及表达式中使用日期函数。本讲稿第十四页，共五十九页9.2.1 9.2.1 常用函数常用函数SQL Server的日期函数的日期函数函数名称及格式描 述Getdate()返回当前系统的日期和时间返回当前系统的日期和时间Datename(datepart,date_expr)以字符串形式返回以字符串形式返回date_exprdate_expr中的指定部分，如果合适的话还将其转中的指定部分，如果合适的话还将其转换为名称换为名称(如如June)June)Datepart(datepart,date_expr)以整数形式返回以整数形式返回date_exprdate_expr中的中的datep

9、artdatepart指定部分指定部分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_exprdate_expr中的日期值中的日期值M

10、onth(date_expr)返回返回date_exprdate_expr中的月份值中的月份值Year(date_expr)返回返回date_exprdate_expr中的年份值中的年份值本讲稿第十五页，共五十九页9.2.1 9.2.1 常用函数常用函数日期部分日期部分日期部分日期部分写写 法法取值范围取值范围Yearyy17539999Quarterqq14Monthmm112Dayofyeardy1366Daydd131Weekwk154Weekdaydw17(MonSun)Hourhh023Minutemi059Secondss059Millisecondms0999本讲稿第十六页，共五

11、十九页9.2.1 9.2.1 常用函数常用函数例：DECLARE int_var intSELECT int_var=datediff(dd,2009-10-11,2009-11-10)SELECT int_varn为了从datediff中得到一个正值，应注意把较早的日期放在前面 本讲稿第十七页，共五十九页9.2.1 9.2.1 常用函数常用函数例：DECLARE char_var varchar(10)SELECT char_var=datename(dd,2009-10-11)SELECT char_var 本讲稿第十八页，共五十九页An Introduction to Database

12、System1 SQL Server 2000触发器触发器1.1 SQL Server 2000触发器概述1.2 Deleted表和Inserted表 1.3 定义触发器1.4 删除触发器定义1.5 查看触发器 本讲稿第十九页，共五十九页An Introduction to Database System1.1 Server Server 20001.1 Server Server 2000触发器概述触发器概述n触发器是一种特殊的存储过程。n不需要由用户调用执行，而是当用户对表中的数据进行UPDATE、INSERT或DELETE操作时系统自动触发执行的。n触发器通常用于保证业务规则和数据完整性

13、，其主要优点是用户可以用编程的方法来实现复杂的处理逻辑和商业规则，增强了数据完整性约束的功能。本讲稿第二十页，共五十九页An Introduction to Database System1.1 Server Server 20001.1 Server Server 2000触发器概述触发器概述n使用触发器可以实施更为复杂的数据完整性约束。n在同一数据库中的简单参照完整性常通过PRIMARY KEY约束和FOREIGN KEY约束来实现。但参照完整性约束不能参照其他数据库中的对象；而可跨数据库的参照完整性约束只能通过触发器来实现。nCHECK约束只能引用当前列（列级CHECK约束）或当前表（表

14、级CHECK约束）中的列值，而触发器则可引用其他表中的列值。本讲稿第二十一页，共五十九页An Introduction to Database System1.1 Server Server 20001.1 Server Server 2000触发器概述触发器概述nSQL Server 2000提供以下两种触发方式：n后触发(AFTER)：在触发操作（INSERT、UPDATE或DELETE）执行完成，并处理过所有约束后激活触发器，这种方式称为后触发。n替代触发(INSTEAD OF)：当触发操作发生时，不是执行触发的SQL语句，而转去执行替代触发器。在表和视图中，每个INSERT、UPDAT

15、E或DELETE最多可以定义一个INSTEAD OF 触发器。本讲稿第二十二页，共五十九页An Introduction to Database System1 SQL Server 2000触发器触发器1.1 SQL Server 2000触发器概述1.2 Deleted表和Inserted表 1.3 定义触发器1.4 删除触发器定义1.5 查看触发器 本讲稿第二十三页，共五十九页An Introduction to Database System1.2 Deleted1.2 Deleted表和表和InsertedInserted表表n在触发器执行的时候，系统会产生两个临时表：Inserte

16、d 表和Deleted 表。n它们的结构和触发器所在的表的结构相同，SQL Server 2000 自动创建和管理这些表。n可以使用这两个临时的驻留内存的表测试某些数据修改的效果及设置触发器操作的条件，然而不能直接对表中的数据进行更改。本讲稿第二十四页，共五十九页An Introduction to Database System1.2 Deleted1.2 Deleted表和表和InsertedInserted表表nDeleted 表n用于存储 DELETE 和 UPDATE 语句所影响的行的副本。在执行 DELETE 或 UPDATE 语句时，行从触发器表中删除，并传输到 deleted

17、表中。Deleted 表和触发器表通常没有相同的行。nInserted 表n用于存储 INSERT 和 UPDATE 语句所影响的行的副本。在一个插入或更新事务处理中，新建行被同时添加到 Inserted 表和触发器表中。Inserted 表中的行是触发器表中新行的副本。本讲稿第二十五页，共五十九页An Introduction to Database System1.2 Deleted1.2 Deleted表和表和InsertedInserted表表在对具有触发器的表（触发器表）进行操作时，其操作过程：n执行INSERT操作，插入到触发器表中的新行被自动的插入到Inserted 表中。n执行

18、DELETE操作，从触发器表中删除的行被自动的插入到Deleted 表中。n执行UPDATE操作，先从触发器表中删除旧行，然后再插入新行。其中被删除的旧行被插入到Deleted 表中，插入的新行被插入到Inserted 表中。本讲稿第二十六页，共五十九页An Introduction to Database System1 SQL Server 2000触发器触发器1.1 SQL Server 2000触发器概述1.2 Deleted表和Inserted表 1.3 定义触发器1.4 删除触发器定义1.5 查看触发器 本讲稿第二十七页，共五十九页An Introduction to Databa

19、se System1.3 1.3 定义触发器定义触发器n语法格式：CREATE TRIGGER 触发器名称ON 表名 FOR|AFTER|INSTEAD OF INSERT ,DELETE ,UPDATE AS SQL 语句 .n 本讲稿第二十八页，共五十九页An Introduction to Database System1.3 1.3 定义触发器定义触发器例1：在S表创建UPDATE触发器Create trigger t1_son sfor update as print the table s was updated本讲稿第二十九页，共五十九页例2 创建触发器，实现如下约束：当删除S表

20、中学生信息时，级联删除相应的选课信息。本讲稿第三十页，共五十九页An Introduction to Database System1.3 1.3 定义触发器定义触发器例3定义一个触发器，实现教师工资表sal_Teach中的以下约束：“教授的实发工资不得低于4000元，如果低于4000元，自动改为4000元”。方法一：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

21、 sal4000 and job=教授 update sal_teach set sal=4000 where eno=eno本讲稿第三十一页，共五十九页An Introduction to Database System1.3 1.3 定义触发器定义触发器方法二：create trigger t2 on sal_teachfor insert,updateas update sal_teach set sal=4000 from sal_teach,inserted where inserted.eno=sal_teach.eno and sal_teach.Job=教授 and sal_t

22、each.sal=age 本讲稿第五十二页，共五十九页执行执行n执行1：不提供任何参数值。EXEC P_Studentn执行2：提供全部参数值。EXEC P_Student 信息系,女,19n执行3：只提供第二个参数的值。EXEC P_Student sex=女n执行4：只提供第一个和第三个参数的值。EXEC P_Student sex=女,age=19本讲稿第五十三页，共五十九页创建带有输出参数的存储过程创建带有输出参数的存储过程 n计算两个数的和。CREATE PROCEDURE sumvar1 int,var2 int,var3 int outputAs Set var3=var1*va

23、r2 n执行此存储过程：Declare res intExecute Proc1 5,7,res outputPrint res 本讲稿第五十四页，共五十九页创建带输入参数和一个输出参数的存储过程创建带输入参数和一个输出参数的存储过程 n统计指定课程的平均成绩，并将统计的结果用输出参数返回。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=cn 本讲稿第五十五页，共五十九页创建带输入参数和多个

24、输出参数的存储过程创建带输入参数和多个输出参数的存储过程 n统计指定课程的平均成绩和选课人数，将统计的结果用输出参数返回。CREATE PROCEDURE Avg_Count cn char(20),avg_grade int output,total int outputAS SELECT avg_grade=AVG(Grade),total=COUNT(*)FROM SC,C WHERE C.Cno=SC.Cno and Cname=cn 本讲稿第五十六页，共五十九页创建删除数据的存储过程创建删除数据的存储过程 n删除考试成绩不及格学生的修课记录。CREATE PROCEDURE p_DeleteSCASDELETE FROM sc WHERE grade 60 本讲稿第五十七页，共五十九页创建修改数据的存储过程创建修改数据的存储过程 n将指定课程的学分增加2分。CREATE PROCEDURE p_UpdateCredit cn varchar(20)AS UPDATE course SET credit=credit+2 WHERE cname=cn 本讲稿第五十八页，共五十九页2.4 删除存储过程删除存储过程n删除存储过程：DROP PROC EDURE 存储过程名本讲稿第五十九页，共五十九页