SQL存储过程，事务，触发器

存储过程
              概念：数据库中一个内置的程序段，当执行存储过程到时候，就会将其内部的代码运行一遍。
              优点：1执行速度'比较'快 2安全性比较高(传参方式决定)
              语法:procdurce过程 简称proc

解释：1.执行效率快：SQL语句在执行的时候，"每次"运行，数据库都会进行编译。
              存储过程是在创建的时候编译，一旦创建成功，就不会再进行编译。由于少了一次编译，所以速度提升了。
            2.存储过程可以防止SQL注入式攻击

              SQL注入攻击：
              如何防范：1过滤特殊的字符 ' " \ /
                                2md5
                                3使用存储过程

--1.无参存储过程
create proc pr_stuquery
as
select stuid from Students
go

--2.有参数的存储过程
create proc pr_stuquerybyid
@stuID int
as
select * from Students where stuID=@stuID

go
exec pr_stuquerybyid 5

--3有返回值的存储过程
--3.1return类型:在需要返回的数字前加return
--限制：1只能返回数字
--2只能返回一个
alter proc pr_insertstudent_return
@stuname nvarchar(50),
@hobby nvarchar(500),
@createtime datetime
as
insert into Students values(@stuname,@hobby,@createtime)
--希望返回一个主键
--全局变量@@identity获取新插入数据库的主键
return @@identity
go

--声明变量
declare @newid int
exec @newid=pr_insertstudent_return 'admin','编程','2021-05-24'
select @newid

--3.2output类型
--output不限制返回值的个数和返回值的数据类型
create proc pr_insertstudent_output
@stuname nvarchar(50),
@hobby nvarchar(500),
@createtime datetime,
@newid int output,--注明参数为output类型
@helloworld nvarchar(50) output
as
insert into Students values(@stuname,@hobby,@createtime)
--希望返回一个主键
--全局变量@@identity获取新插入数据库的主键
set @newid=@@IDENTITY
set @helloworld='helloworld'
go

declare @newid int
declare @helloworld nvarchar(50)
exec pr_insertstudent_output 'admin','编程','2021-05-24',@newid output,@helloworld output
select @newid as nid,@helloworld

 

事务：

事务特性

事务是作为单个逻辑工作单元执行的一系列操作。一个逻辑工作单元必须有四个属性，称为原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）和持久性（Durability）属性，只有这样才能成为一个事务。

原子性

事务必须是原子工作单元；对于其数据修改，要么全都执行，要么全都不执行。

一致性

事务在完成时，必须使所有的数据都保持一致状态。在相关数据库中，所有规则都必须应用于事务的修改，以保持所有数据的完整性。事务结束时，所有的内部数据结构（如 B 树索引或双向链表）都必须是正确的。

隔离性

由并发事务所做的修改必须与任何其他并发事务所做的修改隔离。事务识别数据时数据所处的状态，要么是另一并发事务修改它之前的状态，要么是第二个事务修改它之后的状态，事务不会识别中间状态的数据。这称为可串行性，因为它能够重新装载起始数据，并且重播一系列事务，以使数据结束时的状态与原始事务执行的状态相同。

持久性

事务完成之后，它对于系统的影响是永久性的。该修改即使出现系统故障也将一直保持。

事务隔离

隔离级别用于决定如何控制并发用户读写数据的操作。

读操作默认使用共享锁；写操作需要使用排它锁。

读操作能够控制他的处理的方式，写操作不能控制它的处理方式。

例：
……关键语句讲解………
BEGIN TRANSACTION
/*--定义变量，用于累计事务执行过程中的错误--*/
DECLARE @errorSum INT
SET @errorSum=0 --初始化为0，即无错误
/*--转账：张三的账户少1000元，李四的账户多1000元*/
UPDATEbankSET currentMoney=currentMoney-1000
WHERE customerName='张三'
SET @errorSum=@errorSum+@@error
UPDATE bank SET currentMoney=currentMoney+1000
WHERE customerName='李四'
SET @errorSum=@errorSum+@@error --累计是否有错误
IF @errorSum<>0 --如果有错误
BEGIN
print '交易失败，回滚事务'
ROLLBACK TRANSACTION --回滚
END?
ELSE
BEGIN
print '交易成功，提交事务，写入硬盘，永久的保存'
COMMIT TRANSACTION
END
GO
print '查看转账事务后的余额'
SELECT * FROM bank?
GO

 

触发器：
            概念：触发器是一个特殊的存储过程。 特殊在触发器是自动执行的，不需要进行调用。

           需求：希望删除用户的时候，将用户的日志也进行删除。

           思考：如果我有很多个用户都要删除

           inserted添加临时表:存放新数据
           deleted 删除临时表:存放旧数据

delete from Students where stuID in (33,34,35,36)

--删除的触发器
create trigger tr_deleteuser_deletelog
on Students for delete
as
   --从删除临时表中得到刚才清除的数据
   declare @stuid int
   select @stuid=stuid from deleted
   delete from logs where stuid=@stuid
go

--添加临时表
--向a表插入一条记录，此时我希望把数据在b表中也插入一条
alter trigger tr_inserta_insertb
on a for insert
as
    declare @aid int 
    declare @aname nvarchar(50)
     select @aid=aid ,@aname=aname from inserted
    insert into b values(@aid,@aname,0)
go

 insert into a values('测试员AA')
select * from a
select * from b


update Students set isdelete=1000 where stuID=6
--更新
alter trigger tr_deletestu_deletelog
on students for update
as
     if(UPDATE(isdelete))
     begin
         declare @isdelete int,@oldisdelete int  
         declare @stuid int
         select @isdelete=isdelete from inserted
         select @stuid=stuid,@oldisdelete=isdelete from deleted
         update logs set isdelete=@isdelete where stuid=@stuid
         --希望你打印原来的逻辑状态
         print @oldisdelete
     end
go

update Students set stuName='admin' where stuID=6


--总结：
--1触发器她的使用场景并不是很大，因为她比较麻烦，效率低。
--2触发器在逻辑关系复杂的情况下，可能会导致数据处理不完整。