sql 的存储过程和事务
首先从sql的存储过程说起,sql的存储过程可以理解为是sql的一个自定义的方法,我们定义好一个存储过程之后只需要去调用它就行了。
定义存储过程的语法 CREATE PROC[EDURE] 存储过程名 @参数1 数据类型 = 默认值, …… , @参数n 数据类型 OUTPUT AS SQL语句 GO 1,参数是可选的 2,参数分为输入参数、输出参数 3,输入参数允许有默认值
当然SQL Server 也存在着系统自定义的存储过程
exec sp_databases; --查看数据库 exec sp_tables; --查看表 exec sp_columns student;--查看列 exec sp_helpIndex student;--查看索引 exec sp_helpConstraint student;--约束 exec sp_stored_procedures; exec sp_helptext 'sp_stored_procedures';--查看存储过程创建、定义语句 经常用到这句话来查看存储过程,like sp_helptext sp_getLoginInfo. exec sp_rename student, stuInfo;--修改表、索引、列的名称 exec sp_renamedb myTempDB, myDB;--更改数据库名称 exec sp_defaultdb 'master', 'myDB';--更改登录名的默认数据库 exec sp_helpdb;--数据库帮助,查询数据库信息 exec sp_helpdb master;
存储过程的使用
EXEC 存储过程名 EXEC 存储过程名 参数1,参数2
接下来说说事务
事务是指是程序中一系列严密的逻辑操作,而且所有操作必须全部成功完成,否则在每个操作中所作的所有更改都会被撤消。可以通俗理解为:就是把多件事情当做一件事情来处理,好比大家同在一条船上,要活一起活,要完一起完
事务的使用
BEGIN TRANSACTION SQL语句 ----- 该表含有触发器,UPDATE其他表 IF @@error <> 0 --发生错误 BEGIN ROLLBACK TRANSACTION END ELSE BEGIN COMMIT TRANSACTION END
事务的特性:
● 原子性(Atomicity):操作这些指令时,要么全部执行成功,要么全部不执行。只要其中一个指令执行失败,所有的指令都执行失败,数据进行回滚,回到执行指令前的数据状态。 ● 一致性(Consistency):事务的执行使数据从一个状态转换为另一个状态,但是对于整个数据的完整性保持稳定。 ● 隔离性(Isolation):隔离性是当多个用户并发访问数据库时,比如操作同一张表时,数据库为每一个用户开启的事务,不能被其他事务的操作所干扰,多个并发事务之间要相互隔离。 即要达到这么一种效果:对于任意两个并发的事务T1和T2,在事务T1看来,T2要么在T1开始之前就已经结束,要么在T1结束之后才开始,这样每个事务都感觉不到有其他事务在并发地执行。 ● 持久性(Durability):当事务正确完成后,它对于数据的改变是永久性的。
事务的隔离级别:
事务隔离级别总共有6个隔离级别: READ UNCOMMITTED(未提交读,读脏),相当于(NOLOCK) READ COMMITTED(已提交读,默认级别) REPEATABLE READ(可以重复读),相当于(HOLDLOCK) SERIALIZABLE(可序列化) SNAPSHOT(快照) READ COMMITTED SNAPSHOT(已经提交读隔离) 对于前四个隔离级别:READ UNCOMMITTED<READ COMMITTED<REPEATABLE READ<SERIALIZABLE 隔离级别越高,读操作的请求锁定就越严格,锁的持有时间久越长;所以隔离级别越高,一致性就越高,并发性就越低,同时性能也相对影响越大.
获取当前数据的默认隔离级别
DBCC USEROPTIONS
接下来我们来使用事务和存储过程来写一个小例子:(直接截取项目中的一段代码了)
create procedure dbo.sp_AysncBak as BEGIN set XACT_ABORT on BEGIN TRAN --移植发送完毕的Email INSERT INTO [AsyncEmail_Bak] ([SysNo] ,[MailAddress] ,[MailSubject] ,[MailBody] ,[Status]) select [SysNo] ,[MailAddress] ,[MailSubject] ,[MailBody] ,[Status] from AsyncEmail where Status=1 or Status=-1 delete from [AsyncEmail] where Status=1 or Status=-1 --移植发送完毕的SMS INSERT INTO [SMS_Bak] (SysNo ,[CellNumber] ,[SMSContent] ,[Priority] ,[RetryCount] ,[CreateUserSysNo] ,[CreateTime] ,[ExpectSendTime] ,[HandleTime] ,[Status] ,[CreateUserType] ,[ProductSaleType]) select * from SMS where Status=1 or Status=-1 delete from SMS where Status=1 or Status=-1 print @@ERROR IF @@ERROR <> 0 BEGIN ROLLBACK TRAN return END
其实就是两个表中插入数据和删除已经处理的数据,如果插入或者删除出现错误,整个事务进行回滚。封装为一个存储过程,提供给我们调用,这个是一个不需要参数的存储过程,直接使用 EXEC调用就好。