数据库基础-储存过程和触发器

储存过程

什么是储存过程?

我们可以储存过程可以理解成数据库的子程序,在客户端和服务端可以直接调用它。

储存过程的优点

  1. 储存过程在服务器端运行,执行速度快
  2. 储存过程执行一次之后,就驻留在高速缓冲存储器里了,在以后的操作中,只需要从高速缓冲存储器中调用已编译好的二进制代码执行即可,提高了系统性能
  3. 使用储存过程可以完成所有的数据库操作,并且可通过编程方式控制对数据库信息访问的权限,确保数据库的安全
  4. 自动完成需要预先执行的任务,储存过程可以在SQL server启动时自动执行,而不必在系统启动后再进行手工操作,大大方便了用户的使用,可以自动完成一些需要预先执行的任务

储存过程的类型

类型说明
系统储存过程系统储存过程由SQL server提供,定义在master(系统数据库)中,其前缀时“sp_”;例如,常用的显示系统对象信息的sp_help系统储存过程,为检索系统表的信息提供了方便快捷的方法
用户存储过程在SQL server中,用户储存过程可以使用T-SQL语言编写,也可以CLR方式编写(CLR储存过程是对Microsoft .NETFramework公共语言运行时的引用)

储存过程的创建与执行

储存过程的创建

语法格式如下:

create {proc|procedure}[架构名.] 过程名 [;组号]			/*定义过程名*/
[
	{@参数[类型架构名.]数据类型}							/*定义参数的类型*/
	[varying][=default][out][output][readonly]			/*定义参数的属性*/
]
[for replicaition]
as
{
	<SQL语句>
	.......
}
语句说明
过程名用于指定储存过程名,必须符合标识规则,且对数据库以及所在架构必须唯一,不能与系统内置数据库名称相同,另外,也应当尽量避免使用“sp_”作为前缀 。创建局部临时储存过程可以在过程名加“#”,创建全局临时变量可以在过程名前加“##”
@参数指定参数名称(符合标识规则)
数据类型可以是任何数据库支持的类型。但是cursor类型只能用于output参数。如果指定参数的数据类型为cursor,则必须同时指定varyin和output关键字
varying指定作为输出参数支持的结果集。该参数由储存过程动态构造,其内容可能发生改变,仅适用于cursor参数
default指定存储过程输入参数的默认值,默认值必须是常量或者是null。如果存储过程使用了带like关键字的参数,默认值可以包含通配符(%,_,[],[^])。执行储存过程时可根据情况提供实参
out/output指示参数为输出参数,输出参数可以从储存过程返回信息
readonly指定不能在存储过程的主体中更新或修改参数。如果参数类型为用户定义的类型,则必须指定readonly
for replication用于说明不能在订阅服务器上执行为复制创建的储存过程。如果指定了for replication,则无法声明参数
SQL语句可以是T-SQL语句,过程式语句,定义与赋值,流程控制语句

储存过程的执行

通过execute或者exec语句执行,语法格式如下:

[execute|exec]
{
	[@返回状态 =]
	{模块名|@模块变量名}
	[[@参数名 =]{值|@变量[output]|[default]}]
}
语句说明
@返回状态为可选的整性变量,保存储存过程的返回状态。execute语句使用该变量前。必须对其声明
模块名局部定义的变量名,保存存储过程或者用户定义函数的名称
@模块名变量局部定义的变量名,也就是储存过程或者用户定义函数的名称
@参数名为create procedure语句中定义的参数名,“值”为实参。如果省略了“@参数名”,则后面的实参顺序要与定义时的参数顺序要一致
@变量名为了局部变量,用于保存output参数返回值
defaultdefault关键字表示不提供实参,而是使用对应的默认值

案例1 :设计简单的储存过程

use pxckj;
go

create procedure cjb_info
as 
select * from student where 学号='19045'
go

案例2 :创建带参数的储存过程

use pxscj;
go

create procedure cjb_warn @name char(8),@cname char(16)
as
select * from a.学号,姓名,课程名,成绩,t.学分
from xsb a inner join cjb b on a.学号=b.学号 inner join kcb t on b.课程号=t.课程号
where a.姓名=@name and t.课程名=@cname
go

案例3 :使用带output参数的储存过程
第一个储存过程:

create procedure dbo.xsb_insert
as
insert into xsb values('091201','陶伟',1,'1999-01-01','软件工程',50,null);

第二个储存过程:

create procedure xsb_iud @x bit,@str char(8) output
as
begin
	execute xsb_insert
	if @x=0
	begin
		update xsb set 姓名='刘英',性别=0 where 学号='091201'
		set @str='修改成功'
	end
	if @x=1
	begin
		delete from xsb where 学号='091201'
		set @str='删除成功'
	end
end

执行语句

declare @str char(8);
execute xsb_iud 0,@str output
select @str;

执行结果显示’修改成功’

案例四 :使用with encryption加密储存过程文本,对用户隐藏

create procedure xbs with encryption
as
select * from student;

通过系统储存过程sp_helptext可以显示规则,默认值,未加密的储存过程,用户定义函数,触发器或者视图的文本

execute sp_helptext xsb

结果集为提示信息 “对象xsb的文本已加密”

储存过程的修改

alter {proc|procedure}[架构名.] 过程名
[	{@参数[类型架构名]}[varying][=default][output]
]
[for replication]
as
{
	<SQL 语句>
	....
}

参数的使用说明与参考储存过程的创建
实例

use pxjsc;
go

alter procedure cjb_info @name char(6),@cname char(16)
as
select 学号,课程名,成绩
from cjb,kcb
where cjb.学号=@number and kcb.课程号=@cname

储存过程的删除

当不再需要储存过程了,就可以从数据库中直接删除

drop {proc|procedure} 过程名[,...]

例子 :删除数据库的cjb_info储存过程

use pcksj;
go

if exists(select name from sysobjects where name='cjb_info')
begin
	drop procedure cjb_info
end

说明:在删除之前,可以先查询系统表(sysobjects)中是否存在这个储存过程,再删除

触发器

什么是触发器

触发器是一个被指定关联到一个表的数据对象,触发器是不需要调用的,当对一个的特别事件出现时,它就会被激活。触发器的代码也是SQL语句组成的,因此用再储存过程中的语句也可以用在触发器的定义中。触发器是一类特殊的储存过程,与表的关系密切,用于保护表中的数据

触发器的类型

类型含义
DML触发器当数据库中发生数据操纵语言(DML)事件时将DML触发器。DML事件包括对表或视图的insert语句,update语句和delete语句
DDL触发器DDL触发器触发的事件是数据定义语句(DDL)。这些语句主要是以create,alter,drop等关键字开头的语句

触发器的创建

语法格式

create trigger [架构名.]触发器名				/*触发器名*/
on {表名|视图名}								/*说明作用在那个对象上*/
[with encryption]							/*是否采取加密格式*/
{for|after|instead of}						/*触发器的类型*/
{[insert][,][update][,][delete]}			/*激活触发器的类型*/
[with append]
[not for replication]
as
{
	<SQL语句>
}
语句说明
after用于说明触发器在指定操作后触发。注意不能在视图上定义after触发器。如果为了向前兼容而仅指定了for关键字,则after是默认值。一个表可以创建多个给定类型的触发器
instead of指定用DML触发器中的操作代替触发器的操作。在表或者视图上,每个insert,update,delete语句最多可以定义一个instead of触发器。另外,instead of触发器不可以使用在用了with check option选项的可更新视图上。如果触发器表存在约束,则在instead of触发器执行后和after触发器执行前检查这些约束。如果违反了约束,则回滚insteaf of触发器的操作,且不执行after触发器

触发器说明

  1. 执行触发器时,系统创建了两个特殊的临时表,即inserted和deleted表
    • inserted表:当向表中插入数据时,insert触发器触发执行,新记录插入到触发器和inserted表中
    • deleted表:用于保存已从表中删除的记录,当触发一个delete触发器时,被删除的记录存放到delete表中
  2. 修改一条记录等于插入一条新记录,同时删除旧记录。当对定义了update触发器的表记录进行修改时,表中原记录移动到deleted表中,修改过的记录插入到inserted表中。由于inserted表和deleted表都是临时表,他们在触发器执行时被创建,触发器执行后就消失了,所有只可以在触发器语句里使用select语句查询着两个表

实例(insert触发器)
本例使用了select语句从inserted临时表中找出插入到cjb表的一行记录,然后根据课程号的值查到学分值,最好增加xsb表的总学分

create trigger cjb_insert
on cjb after insert
as
begin
	declare @num char(6),@kc_num char(3),@xf int;
	select @num=学号,@kc_num=课程号 from inserted;
	select @xf=学分 from kcb where 课程号=@kc_num;
	update xsb set 总学分=总学分+@xf where 学号=@num;
	print '修改成功'
end

创建update触发器
创建xsb表修改触发器,当修改xsb表中的学号时,同时也要将cjb表中的学号修改成相应的学号

create trigger xsb_update
on xsb after update
as
begin
	declare @old_num char(6),@new_num char(6);
	select @old_num=学号 from deleted
	select @new_num=学号 from inserted
	update cjb set 学号=@new_num where 学号=@old_num
end

实例delete触发器
在删除xsb表中的一条学生记录时,将cjb表中该学生的相应记录也删除

create trigger xsb_delete
on xsb after delete
as
begin
	delete from cjb where 学号 in(select 学号 from deleted)
end

实例创建instead of触发器

instead of触发器在触发时只触发内部的SQL语句,而不执行激活该触发器的语句。

创建视图stu_view,包含学号,专业,课程号,成绩,该视图依赖表xsb和表cjb,是不可更新表。
可以在视图上创建instead of触发器,当向视图插入数据时分别向表xsb和cjb插入数据,从而实现向视图插入数据的功能

-- create a new view
create view stu_view 
as
select x.学号,专业,课程,成绩
from xsb x inner join cjb c on x.学号=c.学号 
go

-- create insert trigger on the stu_view
create trigger stu_view_instead_inser
on stu_view
instead of insert
as
begin
	declare @xh char(6),@xm char(8),@zy char(12),@kch char(3),@cj int;
	set @xm='施名'
	select @xh=学号,@zy=专业,@kch=课程号,@cj=成绩 from inserted 
	insert into xsb(学号,姓名,专业) values (@xh,@xm,@zy)
	insert into cjb values (@xh,@kch,@cj)
end

说明 :由于xsb表中的姓名不能为空,所以在向xsb表插入时给姓名设置了一个默认值

创建DDL触发器

语法格式:

create trigger trigger_name
on {all server|database}
[with encryption]
{for|after}{事件类型|事件组}[,...]
as
{
	SQL语句
	|external name 程序集名.类名.方法名
}

|

语句说明
all server与databaseall server关键字是指将当前DDL触发器的作用域应用与当前服务器。database是指当亲DDL触发器作用与当前数据库
事件类型这个要额为注意;在对应的事件都是以“事件类型_对象的形式书写”;例如在创建数据库触发,形式:create_database,又比如在修改表时触发,形式:alter_table;

实例 :创建pxsj数据库作用域的DDL触发器,当删除一个表时,提示禁止该操作,然后回滚删除表的操作

create trigger drop_t
on database
alter drop_table
as
begin
	print '不能删除表'
	rollback transaction
end

修改触发器

.语法格式如下

-- 修改 DML 触发器的语法格式
alter trigger 触发器名称
on (|视图)
[with encryption]
(for|after|instead of)
{[delete][update][insert]}
[not for replication]
as
{
	<SQL语句>
}

-- 修改DDL触发器
alter trigger 触发器名
on {database|all server}
[with encryption]
{for|after}{事件类型}
as
{
	<SQL语句>
}

触发器的删除

语法格式

drop trigger 触发器名
  • 1
    点赞
  • 6
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值