存储过程与触发器

60 阅读 0 评论 40 点赞

我是靠谱客的博主无情狗，最近开发中收集的这篇文章主要介绍存储过程与触发器，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

第一部分存储过程总结
1.存储过程：个人理解，就是经过预编译的SQL代码。与系统提供编译的函数，有相同的意思。
2.存储过程包含：系统存储过程、用户自定义存储过程、临时存储过程、扩展存储过程。
3.系统存储过程，顾名思义即由系统提供的存储过程，通常以sp_为前缀。
4.用户自定义存储过程，即由用户创建并完成某一特定功能的存储过程。名称自取。
5.临时存储过程，分为局部临时存储过程和全局临时存储过程，在定义名称前，前者用‘ # ’，后者用‘ ## ’。局部临时存储是只能在当前会话中打开；全局是指可以在多个页面会话中访问。但这些有一个特点：当SQL Server 关闭后，这些临时存储过程被删除。
6.扩展存储，作为初学者不做为重点，知道在系统中是以xp_作为前缀。
7.存储过程的使用有哪些好处：可重复使用、执行效率高、减少网络通信量、安全性、保证操作的一致性。
8.用SQL创建存储过程

#创建存储过程，命名为zimo3，连续输出九次1.
create procedure zimo3 as
declare @ni int ,@mi int
set @ni=1
set @mi=1
while @mi<10
	begin
		print @ni
		set @mi=@mi+1
	end
exec zimo3

#创建输入参数的存储过程
create procedure gread2  @sno int  as
select b.cno  from student a,sc b
where a.sno=b.sno and b.sno=@sno

exec gread2 @sno=201215121

#输入参数，输出参数
create procedure gread4 @sno int ,@cmo real output
as
select @cmo=sum(grade) from student a,sc b
where a.sno=b.sno and b.sno=@sno
group by a.sno

declare @noi real
exec gread4 201215121,@noi  output
select @noi

#查看存储过程文本内容的两种方法（sp_helptext,sysobjects syscomments)
1.exec sp_helptext zimo3
2.select text from sysobjects ,syscomments
where sysobjects.id=syscomments.id and sysobjects.name='zimo3'

#修改存储过程。
alter procedure zimo3 as
declare @ni int ,@mi int
set @ni=1
set @mi=2
while @mi<10
	begin
		print @ni
		set @mi=@mi+1
	end
exec zimo3

#重新命名，将zimo3改为zimo4
exec sp_rename 'zimo3','zimo4'

#删除存储过程
drop procedure zimo4

#创建存储过程的返回状态
alter procedure gread5 @sno int 
as
if @sno=null
	return 10
if not exists
	(select * from student where sno=@sno)
	 return -1
else
	select sum(grade) from sc 
	where sno=@sno
	group by sno
		return 0
declare @return int 
exec @return=gread5 201215120
if @return =10
	PRINT '语法错误'
if @return =-1
	print '没有该学生'
if @return =0
	print'找到了'

第二部分触发器的创建和管理
触发器：是一种与表紧密结合的特殊类型的存储过程，当对表进行update,insert,delete操作时，所设置的触发器会自动被执行，以维护数据的完整性。

#创建一个delete触发器
create trigger student2 on 选课
for delete 
as 
declare @ms char(50)
select @ms=STR(@@rowcount)+'名学生被删除'
select @ms
return 
delete from 选课 where 学号='S0101'

#创建一个insert触发器
create trigger intre on 借阅信息
for insert 
as
declare @iny char(50) ,@mi char(39)
select @iny =(select 借阅证号 from inserted)
select @mi =(select 图书编号 from inserted)
if not exists (select 借阅证号 from 读者信息 where  借阅证号=@iny)
	begin 
		delete from 借阅信息 where 借阅证号=@iny
		print'不能插入记录'
	end
if not exists(select 图书编号 from 图书信息  where 图书编号=@mi)
	begin 
		delete from 借阅信息 where 图书编号=@mi
		print '不能插入记录'
	end
if exists (select 借阅证号 from 读者信息 where 借阅证号=@iny) and exists( select 图书编号 from 图书信息 where 图书编号=@mi)
begin
	print'插入成功了'
end

#创建一个更新触发器
create trigger intre1 on 图书信息
for update
as
declare @iny char(50) ,@mi char(39)
select @iny =(select 图书编号 from inserted)
select @mi =(select 图书编号 from deleted)
if @iny!=@mi
	begin
		print '不可更改数据'
		update 图书信息 set 图书编号=@mi where 图书编号=@iny
	end
update 图书信息 set 图书编号=10150020 where 图书编号=10150001

#修改触发器
alter trigger intre1 on 图书信息
for update
as
declare @iny char(20) ,@mi char(30)
select @iny =(select 图书编号 from inserted)
select @mi =(select 图书编号 from deleted)
if @iny!=@mi
	begin
		print '不可更改数据'
		update 图书信息 set 图书编号=@mi where 图书编号=@iny
	end