触发器和存储过程
1. 存储过程和触发器是什么?
存储过程不能可以显著提高系统的执行速度,还能提高效率确保一致性.
1.1存储过程:一种数据库对象,将负责不同功能的语句分类放置起来,以便能反复使用.
1.2特点:
1.3存储过程的分类
分为五类,系统存储过程,用户定义存储过程,临时存储过程,远程存储过程,扩展存储过程.
临时存储过程又分为本地临时存储过程,全局临时存储过程.
|
分类 |
说明 |
|
系统存储过程 |
存储在master中,以sp开头,调用时不必加库名,如果参数是保留字或者数据库对象,用单引号包围. |
|
用户定义存储过程 |
用户为完成一定的功能定义在数据库中存储过程. |
|
临时存储过程 |
本地临时:#开头,放在tempdb中,连接断开之后自动删除,本库使用. |
|
全局临时:##开头,放在tempdb中,连接断开后使用完之后自动删除,本连接的所有库可以使用.注意命名. |
|
|
远程存储过程 |
位于远程服务器上的存储过程.通过分布式查询和execute执行. |
|
扩展存储过程 |
外部程序写的存储过程,xp开头,动态链接库形式存在,也放在master |
1.4存储过程优点
1.5存储过程和视图的比较
1.6创建存储过程
格式:
例子:
create procedure ShowAllStudent
as
begin
select * from students
end
1.7执行存储过程
格式:exec procedure_name
例子:exec ShowAllStudent
1.8带输入参数存储过程
格式:
例子:
create procedure SelectStudentByStu_no
@sno char(12)='200501020319'
as
begin
select * from students where stu_no=@sno
end
1.9执行带输入参数的存储过程
格式:
例子:
exec SelectStudentByStu_no
@sno='200501030218'
当参数比较少的时候,可以按照位置传递参数.
例子:
exec SelectStudentByStu_no '200501030218'
1.10带输出参数的存储过程
格式:
例子:
create procedure GetStudentCountByStu_sex
@sex char(2)='男',
@count int=0 output
as
begin
set
@count=(select count(*) from students where stu_gender=@sex)
print @count
end
执行带输出参数的存储过程
例子:
declare @sex char(2) ,@count int
set @sex='男'
exec GetStudentCountByStu_sex @sex,@count
1.11 删除存储过程
Drop procedure GetPostsByBlogId
1.12 更新存储过程
alter procedure GetPostsByBlogId
(@blogid varchar(50))
As
Select top 5* from Posts where BlogId=@blogid
1.13 重命名存储过程
对象浏览器中修改.
1.14 重新编译存储过程
有三种方法:
1)创建的时候使用with Recompile 语句.
2)在执行过程中设定重新编译
3)调用系统存储过程重新编译
2. 触发器
基本表被在修改的时候通过事件触发而执行的存储过程.
作用是保证了由主键和外键所不能保证的参照完整性和数据完整性.
2.1触发器的优点
触发器可以包含复杂的处理逻辑,主要用来保持低级的数据完整性.优点如下.
2.2创建触发器
删除触发器:
例子:
create trigger dropStudent
on students
for delete
as
print '成功删除一条数据.'
更新触发器:
create trigger updateStudentName
on students
for update
as
if update (stu_name)
begin
print '不能更新主键,学生号码.'
rollback transaction
end
update students set
stu_name='五哥'
where stu_no='200501020319'
删除触发器:略
查看触发器:
1)
使用系统的存储过程查看: exec sp_helptrigger students
使用系统表: select name from sysobjects where type='TR'
2) 管理器查看 略
删除触发器: drop trigger updateStudent
修改触发器:
重命名: sp_rename dropStudent,deleteStudentItem
启动和停止触发器:
alter table students enable trigger all