SQL-存储过程和触发器

Crystal ·

更新时间:2024-11-10

· 754 次阅读

最近事情一直比较多，博客也好久没有更新了(访问量日渐降低?)，此文即作学习总结吧.

文章目录存储过程创建与执行修改和删除触发器创建修改和删除小结 存储过程

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

创建与执行 语法格式
①创建

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

②执行

[ { 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
{
    ……
}

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约束更复杂语句，方便地保证数据库的完整性。
②触发器可通过数据库中的相关表实现级联更改/删除。合理使用存储过程和触发器，可以降低代码冗余，但过多的话可能使数据逻辑变得复杂。

你的点赞将会是我最大的动力

作者：唔仄lo咚锵

SQL 存储存储过程触发器

1024 个赞