今天来总结下存储过程stored procedure中几个实用的知识点。
1. WITH (NOLOCK)
WITH (NOLOCK)可以高效读取数据,不受其它进程影响,减少阻塞,从而提高并发时的性能。但是在提升性能的同时,也会产生脏读现象。WITH (NOLOCK)通常加在SELECT语句或者JOIN语句之后。这里举例来理解:
首先,我们在假设在student table中执行一个select 查询语句,查询学生的id 和姓名:
Select student id, student name from student
得到结果后想插入一组数据:
Begin tran
Insert Into student values (10, '花花')
如果我们这时候再查询学生的id 和姓名:
Select student id, student name from student
这时候,就会变得超级卡,原因在于上一个插入数据进程没有完成,下一个查询进程又开始,就导致结果卡。
解决方法有两种:
一 在插入数据进程中,添加commit语句,写出一个完整的事务(记住在事务语法中Begin tran--commit--roll back 是一组好朋友,不可分割。)
二 添加WITH (NOLOCK)
执行语句:Select student id, student name from student WITH (NOLOCK)
WITH (NOLOCK)专门解决卡的问题,这里我们在没有添加commit做一个完整事务的前提下,直接执行带有WITH (NOLOCK)的select语句,依然能得到新插入的数据。
2 Transaction 事务
事务是用户定义的一组不可分割的操作命令,作为一个整体向系统提交或撤消,主要用于处理操作量大,复杂度高的数据,用来维护数据库的完整性,保证成批的 SQL 语句要么全部执行,要么全部不执行。通常用来管理 insert,update,delete 这些SQL语句。
这里举两个例子便于理解:
例子1员工离职,既要删除员工基本资料,也要删除与此员工相关信息,这里我们不需要一次次地删除,我们定义一个事务,把所有删除的操作语句放在这一个事务中进行执行。
例子2 银行取钱,用银行卡取100元,包括两步操作:卡上少100元,拿到现金100元,这两个操作是一组打包操作,不可分割;如果改变注意不取钱了,那么这两个操作都不执行。
再来看下事务的语法:
通常是以begin transaction开始的,以commit或rollback 结束,commit表示提交,既提交事务的所有操作。Rollback表示回滚,在事务运行的过程中发生了某种故障,事务不能继续执行,系统将事务中对数据库的所有已完成的操作全部取消,回滚到事务开始时的状态。
3 SET NOCOUNT ON
我们在执行也insert,update,delete 语句的时候,通常会看到以下的message计数窗口,提示我们受影响的行数。在存储过程中,我们往往在头部加上SET NOCOUNT ON,这样就不会显示这样的message,优化存储过程,提高性能。
4 事务与 TRY CATCH的搭配使用
如果我们在存储过程事务中出现了错误,我们可以回滚rollback或者显示错误信息,这里就会用到try--catch block。
系统提供的以下函数显示错误信息:
error_line 返回导致错误的行号
error_number 返回错误代码
error_serverity 返回错误的严重级别
error_state 返回错误状态代码
error_message 返回完整的错误信息
基本代码如下例子: