sql学习总结（8）——游标和触发器技术

8.1创建游标

语法格式：

declare 游标名 [insensitive] cursor

[local | global]

[forward_only | scroll]

[static | keyset | dynamic |fast_forward]

[read_only | scroll_locks | optimistic]

[type_warning]

for SQL语句

[for [read only | update]]

8.2读取游标中的数据信息

语法格式：

fetch [next | prior |first | last]

[absolute n |relative n]

from 游标名 [into 变量名]

注意：@@fetch_status是一个全局变量，表示返回fetch命令的状态：

use db_company

declare db_cursor5 scroll cursor

for

select * from 职工 where 性别 is not null

open db_cursor5

fetch first from db_cursor5

fetch next from db_cursor5

fetch last from db_cursor5

fetch prior from db_cursor5

fetch absolute 2 from db_cursor5

fetch relative 2 from db_cursor5

close db_cursor5

deallocate db_cursor5

8.3利用游标判断并输出数据

if @@fetch_status=0

begin

declare @t varchar(10)

declare @t1 varchar(10)

declare @t2 int

fetch first from db_cursor6 into @t,@t1,@t2

select @t as 职工号,@t1 as 职工名,@t2 as 工资

end

else

begin

print ‘不存在该记录’

end

8.4利用游标修改和删除数据

use db_company

declare db_cursor7 cursor scroll

for

select * from 职工

open db_curror7

fetch first from db_cursor7

update 职工 set 工资=工资+88 where current of db_curror7

fetch absolute 4 from db_cursor7

delete from 职工 where current of db_cursor7

close db_cursor7

deallocate db_cursor7

8.5利用游标逐条显示数据库中的记录

fetch first from db_cursor8

while @@fetch_status=0

begin

fetch next from db_curror8

end

8.6在存储过程中使用游标

create procedure db_mypro

@x int

as

begin

declare mycursor scroll cursor

for

select * from 职工

open mycursor

fetch absolute @x from mycursor

close mycursor

deallocate mycursor

end

db_mypro 2

8.7查看游标属性

利用sp_cursor_list查看当前服务器的游标及其属性，语法格式：

sp_cursor_list [@cursor_return= ] cursor_variable_name output,[@cursor_scope= ] cursor_scope

利用sp_describe_cursor查看游标的全局属性，语法格式：

sp_describe_cursor [@cursor_return= ] output_cursor_variable output

{ [ , [ @cursor_source= ] N’local’

, [ @cursor_identity= ] N’local_cursor_name’ ]

| [ , [ @cursor_source= ] N’global’

, [ , [ @cursor_identity= ] N’global_cursor_name’ ]

| [ , [ @cursor_source= ] N’variable’

, [@cursor_identity= ] N’input_cursor_variable’ ]

}

8.8创建触发器

触发器是一种特殊类型的存储过程，主要通过事件触发而被执行。

事后触发器

create trigger 触发器名 on 表名 [with encryption] for insert[,update,delete] as

begin

命令行或程序块

end

替代触发器

create trigger 触发器名 on 表名或视图名 instead of insert [, update,delete] as

begin

命令行或程序块

end

注意：触发器只能关联表或视图。为了保护代码触发器的代码内容，可以利用with encryption加密触发器中的代码内容。after只能定义在表中，不能到视图上。而instead of可以定义在视图上。

create trigger db_trigger4 on 仓库 for update

as

begin

declare @old char(10)

declare @new char(10)

select @new=仓库号 from inserted

select @old=仓库号 from deleted

update 职工set 仓库号=@new where 仓库号=@old

end

8.9修改、删除和查看触发器

8.9.1修改触发器

把create改成alter即可。

8.9.2查看触发器基本信息

execute sp_help db_trigger1       select * from sys.triggers (查看所有)

8.9.3查看触发器所使用数据对象信息

execute sp_depends db_trigger1

8.9.4查看触发器功能代码

execute sp_helptext db_trigger1

8.9.5删除触发器

drop trigger {triggername}[,n]

triggername表示要删除的触发器名，n表示可以同时删除多个触发器。