《学习情境十一 存储过程和触发器》由会员分享，可在线阅读，更多相关《学习情境十一 存储过程和触发器（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个至多个参数输入输出 ，用户外部程序可以直接执行存储过程。触 发器是一种特殊的存储过程，但触发器不能 直接调用执行，只能通过事件触发。触发器 在数据库开发过程中，在对数据库的维护和 管理等任务中，特别是在维护数据完整性等 方面具有不可替代的作用
