《学习情境十一 存储过程和触发器》由会员分享,可在线阅读,更多相关《学习情境十一 存储过程和触发器(22页珍藏版)》请在金锄头文库上搜索。
1、学习情境十一 存储过程和 触发器情境描述n存储过程和触发器是由一系列的Transact- SQL语句组成的子程序,用来满足更高的应 用需求,触发器也是一种存储过程,它是一 种在基本表被修改时自动执行的内嵌过程, 它主要是通过事件进行触发而被执行,而存 储过程可以通过存储过程的名字被直接调用 ,它们可以说是SQL Server程序设计的灵魂 ,掌握和使用好它们对数据库的开发与应用 非常重要。 任务一 应用“存储过程”n什么是存储过程n存储过程是指封装了可重用代码的、存储在服务器上的程序模块 。存储过程是数据库对象之一,它类似于其它高级语言中的过程 。其优点表现在:n提高程序的执行效率。存储过程在
2、第一次执行以后,其执行规划 就驻留在高速缓冲存储储器中。在以后每次操作中,只需从高速 缓冲存储器中调用执行即可。n具有安全特性。作为一种数据库对象,存储过程要求拥有相应权 限的用户才能执行存储过程,而不必对存储过程中引用的对象拥 有访问权限。n减少网络通信流量。由于存储过程在服务器端执行,用户每次只 需发出一条执行命令,而不必发出所有的程序代码,因而减少了 网络的数据流量。n允许模块化的程序设计,提高代码重用性。存储过程一旦被创建 ,以后就可以在所有程序中多次调用。这有利于程序的结构化设 计,提高程序的可维护性和代码的重用性。 存储过程的分类n存储过程可以分为系统存储过程、用户存储过程、扩展存
3、储过 程。n系统存储过程系统存储过程是Sql Server2005本身定义的、当作命令来执行的各 种操作的一类存储过程。它主要用于管理和显示有关数据库及用 户的信息,通常前缀”sp_”。n扩展存储过程扩展存储过程是指SQL Server实例可以动态加载和运行的动态链 接库(DLL)。通过扩展存储过程,可以使其它编程语言创建自己 的外部程序,实现了t-sql程序和其它语言的融合。扩展存储过程 以“XP_”开头。n用户存储过程用户存储过程是指用户通过t-sql语句编写、具有特定功能的一类存 储过程。推荐命名方式以“UP_”开头。 存储过程的创建和执行 n创建存储过程的语法如下:Create Pro
4、cedure 存储过程名称(参数列 表)ASt-sql语句 课堂训练n创建简单的存储过程 use pubs go create procedure up_titles as select * from titles where price10n执行存储过程一般用Execute(Exec)语句来完成。但也可以 直接用存储过程的名称作为命令来执行,但是必须是批处理的 第一条命令。如下: up_titles execute up_titles exec up_titles课堂训练n创建具有输入参数的存储过程 create procedure up_titles_price (price money)
5、 as select * from titles where pricepricen执行: exec up_titles_price 10 或 exec up_titles_price price=10 课堂训练n创建模糊查询的存储过程 create procedure up_authors (state varchar(20) as select * from authors where state like staten执行: exec up_authors state=%c%课堂训练n创建具有输出参数的存储过程 create procedure up_ontitle (avg money
6、 output,sum money output,price money) as select avg=avg(price),sum=sum(price) from titles where pricepricen对于带有output参数的存储过程的调用方法 与其它存储过程的调用方法不同。首先要声 明变量用来存放存储过程返回的结果,然后 在调用的过程的时候要带关键字output,否 则无法返回结果保存下来。 declare avg money,sum money exec up_ontitle avg output,sum output,price=10 print 平均价格为:+cast(a
7、vg as varchar(20) + 总价格为:+Cast(sum as varchar(20) 课堂训练n创建带有返回值的存储过程。 create procedure up_quantity (state varchar(20) as declare count int select count=count(*) from authors where state=state return countn SQL Server允许设置存储过程的返回值,它用返回的数值来 确定存储过程的执行状态。Return 只能返回整数类型的数据。 declare count int exec count=up
8、_quantity ca print count 存储过程的修改和删除 n课堂训练 修改存储过程 use pubs go alter procedure up_titles as select * from titles where price20n课堂训练 删除存储过程 drop procedure up_quanity,up_titles 任务二 应用“触发器” n什么是触发器n 触发器是数据库服务器中发生事件时自动执行 的特殊的存储过程。与一般存储过程不同的是, 触发器不是被调用执行,而是在指定的事件发生 触发执行。它与数据表的关系密切,一般用于保 证的完整性、检查数据的有效性、实现数据
9、库的 一些管理任务和其它的一些附加功能。n 触发器被执行的前提是要有相应的事件发生, 这些事件主要是针对数据表而言的。在Sql Server 中,引发事件的主要是DML和DDL语言,因此又 有DML事件和DDL事件以及DML触发器和DDL触发 器之称。在这里我们重点介绍DML触发器。 DML触发器n根据触发器执行与触发事件发生的先后关系,又可 以将DML触发器分为After触发器和Instead OF 触发 器。 n1、After触发器:在触发事件发生后才触发执行的触发器, 也就是说先执行Insert、Update、 Delete语句然后才执行 的After触发器。这类触发器只适合数据表,不适
10、合视图。n创建触发器的语法格式如下: Create Trigger 触发器名称 On 表名 For|instead of insert|Update|delete As 操作语句。 课堂训练n创建触发器实现当删除authors表中的数据时,向 authorLog表中添加修改信息。 create table authorslog (ID int identity primary key,tablename varchar(20) not null, operateDate smalldatetime not null default getdate() create trigger tri_au
11、thor_delete on authors for delete as insert into authorslog(tablename)values(authors) 注意n在临时表中不能创建触发器,在视图上 只能创建替代触发器。n对于每个表不管修改和删除多少数据对 应的存储过程每次只触发一次。nCreate Trigger必须是批处理的第一条 语句。n不能在系统表上创建触发器。 利用触发器实现数据完整性 nInserted和Deleted表:在使用触发器过程中 ,SQL Server使用了两张特殊的临时表,这 两张表分别存储在高速的缓存中,当对数据 库实行Insert、Update、De
12、lete操作时将数据 添加到这两张表中。nDeleted表:表中存储着被Update和Insert语 句影响的旧数据行。就是说当基本表中的数 据被删除时它将数据添加到Deleted表中。nInserted表:表中存储着被Update和Insert语 句影响的旧数据行。就是说当基本表中插入 和修改时会将数据添加到Inserted表中。课堂训练n利用Deleted表实现当删除Suppliers供应商时 对应的产品(Products)也删除。 create trigger tri_suppliers on suppliers for delete as if rowcount=0 return de
13、lete products from products p inner join deleted s on p.supplierid=s.supplierid delete suppliers where supplierid=1 列级触发器 n我们还可以在表中的某列上创建触发器实现对某列的实施监控。n课堂训练 监控products表中的Unprice列当被修改时向mytablelog中 添加记录信息。 create trigger tri_products on products for insert,update as if update(unitprice) begin insert i
14、nto mytablelog values(unitprice被修改了) end insert into products(productname,supplierid,categoryid,unitprice,Discontin ued) values(fasdfsda,2,2,20,0) update products set unitprice=unitprice*1.2 where productid=4 建立Instead of of 替代触发 器n替代触发器就是用触发器替代执行的语句完成功能。n课堂训练 利用替代触发器实现工资的自动计算。 create table salary (
15、id int identity primary key ,salary money,percent numeric(2,1),realsalary money) /建立表 create trigger tri_salary on salary instead of insert as declare salary money,percent numeric(2,1) select salary=salary,percent=percent from inserted insert into salary(salary,percent,realsalary)values(salary,perce
16、nt,salary*(1- percent) /建立触发器 insert into salary(salary,percent) values(1500,0.2) /测试触发器。 修改和删除触发器 n修改触发器语法: Create Trigger 触发器名称 On 表名 For|insteadof insert|Update|delete As 操作语句。n 删除触发器语法:Drop Trigger 触发器的名称,.n 小 结n本章主要介绍了存储过程和触发器,它们是 一组T-SQL语句集。存储过程是提高效率的 有效方法之一,允许0个至多个参数输入输出 ,用户外部程序可以直接执行存储过程。触 发器是一种特殊的存储过程,但触发器不能 直接调用执行,只能通过事件触发。触发器 在数据库开发过程中,在对数据库的维护和 管理等任务中,特别是在维护数据完整性等 方面具有不可替代的作用