存储过程和触发器

唔仄lo咚锵

发布于 2020-09-15 14:25:05

1.1K0

发布于 2020-09-15 14:25:05

文章被收录于专栏：blog(为什么会重名，真的醉了)

文章目录

存储过程
- 创建与执行
- 修改和删除
触发器
- 创建
- 修改和删除
小结

~~学习使我快乐~~

存储过程

存储过程（Stored Procedure）是一种在数据库中存储复杂程序，以便外部程序调用的一种数据库对象。在SQL Server 中，存储过程分为两类：系统提供的存储过程和用户自定义存储过程。前者以sp_为前缀且主要是从系统表中获取信息。后者是用户可以使用T-SQL语言编写。

创建与执行

语法格式 ①创建

CREATE { PROC | PROCEDURE } [架构名.] 过程名 [ ; 组号 ]	/*定义过程名*/
    [ { @参数 [ 类型架构名. ] 数据类型 }  /*定义参数的类型*/
        [ VARYING ] [ = default ] [ OUT | OUTPUT ] [READONLY]	/*定义参数的属性*/
    ]
 [ WITH  ENCRYPTION ]	/*说明是否采用加密方式*/
 [ FOR REPLICATION ]/*说明不能在订阅服务器上执行为复制创建的存储过程。*/
AS
{	 <SQL语句>		/*执行的操作*/
      ……
}

②执行

[ { EXEC | EXECUTE } ]
{    [ @返回状态 = ]
     { 模块名 | @模块名变量 }
     [ [ @参数名 = ] { 值 | @变量 [ OUTPUT ] | [ DEFAULT ] } ]
}

2.举例（数据表链接: spj库） ①创建存储过程p1，查询所有信息；运行之。

create procedure p1
as
	select * from s,p,j,spj
	where s.sno=spj.sno
	and p.pno=spj.pno
	and j.jno=spj.jno
go
execute p1

②创建带参数存储过程p2，输出某供应商所在城市；运行之。

create procedure p2 
@sno char(3),--输入参数
@city varchar(10) output--输出参数
as
	select @city=city from s where sno=@sno
go
declare @rlt varchar(10)--输出参数存在rlt变量
execute p2 's2',@rlt output--查询s2所在城市
select @rlt

③创建存储过程p3，向表p中插入一条记录，若没有提供参数则使用预设默认值；运行之。

create procedure p3 
@pno char(3),
@pname varchar(10),
@color char(2)='黑',--预设值
@weight int =10
as
	insert into p
	values(@pno,@pname,@color,@weight)
go
execute p3 'p7','螺丝'
execute p3 'p8','螺丝','银'
execute p3 'p9','螺丝刀',default,15

select *from p;

④创建加密存储过程p4，查询j表。

create procedure p4
with encryption
as
	select * from j
go 
execute sp_helptext p4
/*通过系统存储过程sp_helptext可显示规则、默认值、未加密的存储过程、
用户定义函数、触发器或视图的文本。*/

⑤创建存储过程p5，返回工程项目数，使用return返回参数;运行之。

create procedure p5
as
	declare @cnt int;
	select @cnt=count(jno) from j
	return @cnt
go
declare @rlt int;
set @rlt=0;
execute @rlt=p5
select @rlt

修改和删除

1.语法格式 ①修改：将上文create换成alter即可，不再赘述。 ②删除

DROP { PROC | PROCEDURE } { [ 架构名. ] 过程 } [ , ... ]

2.举例 ①将存储过程p3改为查询表p。

alter procedure p3
as
	select *from p
go
execute p3 ;

②删除存储过程p3

drop procedure p3

触发器

触发器(TRIGGER)是一种特殊类型的存储过程，不由用户直接调用。创建触发器时会对其进行定义，以便在对特定表或列作特定类型的数据修改时执行。执行触发器时，系统创建了两个特殊的临时表： inserted表：当向表中插入数据时，INSERT触发器触发执行，新的记录插入到触发器表和inserted表中。 deleted表：用于保存已从表中删除的记录，当触发一个DELETE触发器时，被删除的记录存放到deleted表中。修改一条记录等于插入一条新记录，同时删除旧记录。当对定义了UPDATE触发器的表记录进行修改时，表中原记录移到deleted表中，修改过的记录插入到inserted表中。

创建

语法格式 DML触发器

CREATE TRIGGER [ 架构名. ] 触发器名
    ON { 表 | 视图 }				/*指定操作对象*/
[ WITH  ENCRYPTION ]			/*说明是否采用加密方式*/
{ FOR | AFTER | INSTEAD OF }
{ [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] }
[ WITH APPEND ]	  /*指定应该再添加一个现有类型的触发器*/
[ NOT FOR REPLICATION ]		/*说明该触发器不用于复制*/
AS
{
    <SQL语句> 
    ……
}

DDL触发器

CREATE TRIGGER 触发器名
    ON { ALL SERVER | DATABASE }
[ WITH ENCRYPTION ]
{ FOR | AFTER } { 事件类型 | 事件组 } [ , ... ]
AS
{
    SQL语句 [ ; ] [ ... ]
    | EXTERNAL NAME  程序集名.类名.方法名
}

2.举例（数据表链接: spj库） ①创建insert触发器t1，在spj表插入记录时替换插入操作，检查完整性并执行相应语句。

create trigger t1 on spj
instead of insert
as 
	declare @sno char(3), @pno char(3),@jno char(3)
	declare @qty int
	select @sno=sno from inserted--赋值
	select @pno=pno from inserted
	select @jno=jno from inserted
	select @qty=qty from inserted
	if(exists (select * from spj where sno=@sno and pno=@pno and @jno=@jno)--实体完整性
	or not exists (select * from s where sno=@sno)--参照完整性
	or not exists (select * from p where pno=@pno)
	or not exists (select * from j where jno=@jno))
		print '插入失败'
	else 
		begin
		insert into spj values(@sno,@pno,@jno,@qty)
		print'插入成功'
		end
go
insert into spj values('s1','p1','j1',100)
insert into spj values('s9','p2','j1',200)
insert into spj values('s1','p3','j9',300)
insert into spj values('s1','p4','j3',400)

②创建delete触发器t2，在spj表删除记录后，显示剩余记录数。

create trigger t2 on spj
after delete
as 
	declare @cnt int
	select @cnt=count(sno) from spj
	select @cnt as '剩余记录总数'
go
delete spj where sno='s1' and pno='p4' and jno='j3'

③创建update触发器t3，在s表更新前显示被更新属性旧值。

create trigger t3 on s 
for update
as
	select * from deleted
go
update s set status=25, city='厦门' where sno='s4'
select *from s

④创建spj数据库作用域的DDL触发器t4，当删除一个数据表时，提示禁止该操作并回滚删除数据库的操作。

create trigger t4 
on database
after DROP_TABLE
as
	print'不能删除该数据表'
	rollback transaction
go
drop table spj

修改和删除

语法格式 ①修改：将上文create换成alter即可，不再赘述。 ②删除

DROP TRIGGER 架构名.触发器名 [ ,... ] [ ; ]		/*删除DML触发器*/
DROP TRIGGER 触发器名 [ ,... ] ON { DATABASE | ALL SERVER }[ ; ]	/*删除DDL触发器*/

举例 ①修改触发器t3，改为更新s表后显示所有信息。

alter trigger t3 on s 
after update
as
	select * from s
go
update s set status=20, city='天津' where sno='s4'

②删除触发器t3，t4。

drop trigger t3
drop trigger t4 on database

小结

存储过程 ①存储过程在服务器端运行，执行速度快。执行一次后，就驻留在高速缓冲存储器,提高了系统性能。 ②使用存储过程可以完成所有数据库操作，并可控制对数据库访问的权限，确保数据库的安全。
触发器 ①可实现比CHECK约束更复杂语句，方便地保证数据库的完整性。 ②触发器可通过数据库中的相关表实现级联更改/删除。
合理使用存储过程和触发器，可以降低代码冗余，但过多的话可能使数据逻辑变得复杂。