-- 作者:admin
-- 发布时间:2008/2/20 15:12:30
-- [推荐]第六部分课堂讲稿——SQL语言之三
8 游标 游标是一种可以让用户逐行定位记录来操作表数据的特殊变量。
8.1 概念 强调它与基本表的关系,它与查询、视图的联系和区别。 强调它与SQL操纵记录方式的区别。 说明任何编程语言中对数据库的访问都是通过游标进行的,相关的语句也有很多,如VFP的skip、VB的moveNext()和Java的next()等。
8.2 定义和使用 利用游标显示男女生的汉字性别: declare stuCh cursor static for select name,sex from stu
declare @num nvarchar(8) declare @sex int
open stuCh fetch first from stuCh into @num,@sex while (@@fetch_status=0) begin if @sex=1 print @num+\'——男\' else print @num+\'——女\' fetch next from stuCh into @num,@sex end close stuCh deallocate stuCh
输出每个学生平均成绩的优良中差: declare stugrade cursor static for select number,avg(grade) from grade group by number
declare @num char(6) declare @gra int
open stugrade fetch first from stugrade into @num,@gra while (@@fetch_status=0) begin if @gra>85 print @num+\'优秀,平均成绩为\'+convert(char(3),@gra)
else print @num+\'一般,平均成绩为\'+convert(char(3),@gra) fetch next from stugrade into @num,@gra end close stugrade deallocate stugrade
8.3 可更新的游标 将第一条记录的姓名加上一个“*”号: DECLARE abc CURSOR FOR SELECT name FROM stu
OPEN abc GO
FETCH NEXT FROM abc GO
UPDATE stu SET name = name+\'*\' WHERE CURRENT OF abc GO
CLOSE abc DEALLOCATE abc GO
10 触发器 使用触发器,可以让数据库产生主动和智能化的效果,它广泛的应用于各种大型数据库管理系统。 为说明必要性,举两个例子: 一是防止无意删除全部记录; 二是对下面的两个表实现一种特殊的约束,即两表的生日和年龄必须对应: create table stu1 ( number char(4) not null, birthday datetime )
create table stu2 ( number char(4) not null, age int )
使用传统方法,解决这些问题就需要用户在客户端编程才能实现。通过触发器可以将这些逻辑功能内置在数据库中。不同于存储过程,这些触发器所关联的代码会在某种操作发生时自动被触发执行。
10.1 触发器的定义和使用 10.1.1 相关语句 create trigger ins on stu for insert --delete update as print \'添加记录一条\'
可通过此语句观察: insert into stu(number) values(\'000200\')
对于更新操作的触发器,还可以判定操作的字段列: create trigger ins1 on stu for update as if update(name) print \'添加姓名一个\'
可通过下面的语句观察: update stu set name=\'Tom\' where number=\'000200\' update stu set height=2 where number=\'000200\'
10.1.2 图形界面操作 在企业管理器中只有打开表设计器方可编辑触发器,右击表——“所有任务”——“管理触发器”。 10.2 INSERTED表和DELETED表 每次插入记录,都会将新插入的记录存入INSERTED表; 每次删除记录,都会将被删除的记录存入DELETED表; 每次更新记录,都会将旧记录存入DELETED表,再把新记录存入INSERTED表。
例1:查看被删除的记录: create trigger delTri on stu for delete as select * from deleted
可通过此语句观察: delete from stu where number=\'000200\'
例2:查看被插入的记录: create trigger insTri on stu for insert as select * from inserted
可通过此语句观察: insert into stu(number) values(\'000300\')
例3:查看更新前和更新后的记录: create trigger updTri on stu for update as select * from deleted select * from inserted
可通过此语句观察: update stu set height=1 where number=\'000300\'
10.3 综合应用 1)将删除记录自动备份 select * into stubak from stu where 1=2
create trigger backupDelRecs on stu for delete as select * into stubak from deleted
可通过此语句观察: delete from stu where number=\'000006\'
2)防止删除全部记录,当用户删除全部记录时,操作无效 create trigger antiDelAllRec on stu for delete as declare @total int select @total=count(*) from stu if @total=0 insert into stu select * from deleted
可通过此语句观察: delete from stu
3)对上述两个学生信息表实现一种特殊的约束,即两表的生日和年龄必须对应: 插入触发器: create trigger triIns on stu1 for insert as declare @num char(4) declare @birth datetime select @num=number from inserted select @birth=birthday from inserted insert into stu2 values(@num,year(getdate())-year(@birth))
更新触发器: create trigger triUpd on stu1 for update as if update(birthday) declare @num char(4) declare @birth datetime select @num=number from deleted select @birth=birthday from inserted update stu2 set age=year(getdate())-year(@birth) where number=@num
删除触发器: create trigger triDel on stu1 for delete as declare @num char(4) select @num=number from deleted delete from stu2 where number=@num
[此贴子已经被作者于2010-12-11 19:50:30编辑过]
|