存储过程和触发器

港控/mmm° 2023-07-23 03:52 8阅读 0赞

### 文章目录 ###

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

![表情包][20200508155438324.jpg_pic_center]  
学习使我快乐

# 存储过程 #

--------------------

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

## 创建与执行 ##

1.  语法格式  
    ①创建

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

②执行

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

2.举例  
（数据表链接: [spj库][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

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ1MDM0NzA4_size_16_color_FFFFFF_t_70]  
②创建带参数存储过程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

![在这里插入图片描述][20200406231341995.png]  
③创建存储过程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;

![在这里插入图片描述][20200406233028422.png]  
④创建加密存储过程p4，查询j表。

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

![在这里插入图片描述][20200407094028793.png]  
⑤创建存储过程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

![在这里插入图片描述][20200407102639946.png]

## 修改和删除 ##

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表中。

## 创建 ##

1.  语法格式  
    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库][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)

![在这里插入图片描述][20200407113534316.png]  
②创建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'

![在这里插入图片描述][20200407114743725.png]  
③创建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

![在这里插入图片描述][20200407120116769.png]  
④创建spj数据库作用域的DDL触发器t4，当删除一个数据表时，提示禁止该操作并回滚删除数据库的操作。

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

![在这里插入图片描述][20200407122617197.png]

## 修改和删除 ##

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

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

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

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

![在这里插入图片描述][20200407123632390.png]  
②删除触发器t3，t4。

drop trigger t3
    drop trigger t4 on database

![在这里插入图片描述][20200407123828502.png]

# 小结 #

--------------------

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

> **原创不易，请勿转载**（本不富裕的访问量雪上加霜 ）  
> 博主首页：[https://blog.csdn.net/qq\_45034708][https_blog.csdn.net_qq_45034708]  
> *如果文章对你有帮助，记得关注点赞收藏❤*

[20200508155438324.jpg_pic_center]: /images/20230528/4d0e42cfca444ef1bbf6980027059a44.png
[spj]: https://blog.csdn.net/qq_45034708/article/details/105087048/
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ1MDM0NzA4_size_16_color_FFFFFF_t_70]: /images/20230528/f04c106457444799a63eb9462078d21d.png
[20200406231341995.png]: /images/20230528/084d518342df4259aa11ebffc3437c44.png
[20200406233028422.png]: /images/20230528/39b8defa8e4442378610ca5386f53992.png
[20200407094028793.png]: /images/20230528/7aca80d40b204e538683dce646d46b7d.png
[20200407102639946.png]: /images/20230528/1d8a141f94ee4bf3a83addee4a6cd823.png
[20200407113534316.png]: /images/20230528/9883a9b56e8a469b8846bae061573336.png
[20200407114743725.png]: /images/20230528/c5ff3643703f478ba69ed23999b99c9e.png
[20200407120116769.png]: /images/20230528/b4056ba4ab084d48815d965c067fb3b4.png
[20200407122617197.png]: /images/20230528/1c3ba47dbcd7443084bcb184e078dde7.png
[20200407123632390.png]: /images/20230528/c6b62d32201b4158a09a214fd5488e5a.png
[20200407123828502.png]: /images/20230528/a9230b88a2f54500b3290fdbca0609ad.png
[https_blog.csdn.net_qq_45034708]: https://blog.csdn.net/qq_45034708