前言
熟悉SqlServer中视图、存储过程、事务的使用会在操作数据时带来更大的方便。
正文
视图-VIEW
VIEW介绍
- 定义:
视图(VIEW)虚拟表,一般是不存储数据的,但是Sqlserver的索引视图例外(存储数据),以下不考虑索引视图。
是对查询的Sql语句的封装,只是预定义了一段sql语句,不会提高查询效率。 - 优点或用途:
降低查询复杂度:可以把很长的sql语句用一个viewName代替。
权限控制:防止未经许可用户访问敏感数据。比如普通用户只有查看数据的权利,这时候就可以让用户只调用视图。这样当用户账号被获取也不会对敏感数据造成影响。 - 系统视图
系统预定义的视图
VIEW使用
- 创建视图
--vw_ShowsTable是视图的名字
--as后面是你封装的sql语句
create View vw_ShowsTable
as
select * from Shows where id>2
然后在数据库下的视图文件夹里出现你刚才创建的视图了
- 使用视图
直接像表一样调用就行了。
select * from vw_ShowsTable
这里的view封装的sql语句很短并没体现优点,当sql语句很长时再通过view调用sql语句就会发现世界如此简单。
存储过程-SP
介绍
- 定义:
存储过程(Stored Procedure)相当于C#中的函数,通过SP的name调用,可以有参数和返回结果。 - 优点或用途:
- 经过编译-执行速度快
- 可以重复使用
- 提高安全性
- 使用简单
当压力比较大,可以把一部分压力放在数据库。但是以后修改起来比较麻烦。
- 系统SP
以sp_开头,用来进行系统的各项设定.取得信息.相关管理工作。
使用
- 创建SP
--usp_select_Shows是存储过程名称,后面可以跟参数
--begin end里是你的sql语句
create proc usp_select_Shows
@n1 int
as
begin
select * from Shows where id>@n1
end
- 使用SP
--exec调用存储过程,有参数的话在spName后面+空格+参数
exec usp_select_Shows 2
事务-Transaction
Transaction介绍
- 定义
事务(Transaction)是访问并可能更新数据库中各种数据项的一个程序执行单元(unit)。 优点或用途
正常sql出错的执行顺序:
开始执行-前几条执行完成-出错停止执行-后面几条不执行。
但是有时候有其它需求:- 开始执行-出现错误-全部sql回滚,数据恢复到全部未执行状态。
- 开始执行-出现错误-忽略出错的sql继续执行接下来的slq。
第一种是常用场合是转账。事务就能帮我们做到这两种情况。
事务种类
自动事务:其实执行的普通sql语句都是一个事务,比如插入数据的sql出错会恢复到未插入状态,这就是自动事务的功劳。
隐式事务:
显式事务:显式事务是常用的事务,需要手动打开事务,接下来讲如何使用显式事务。
使用显式事务
出错全部回滚:
begin tran -- 开始一个事务
declare @tran_error int --声明一个变量用于检测sql是否出错
begin try -- try catch结构来检测是否出错,
-- 你的sql语句
end try
begin catch
set @tran_error=@tran_error+1 --检测到你的错误后变量加1
end catch
if (@tran_error > 0)
rollback tran -- 变量值改变就回滚
else
commit tran -- 变量值不变就提交
出错后忽略错误语句继续执行接下来的sql:
这种情况是需要设置xact_abort(精确终止),设置为on时出错全部回滚(可代替上面的try-catch写法),为off时出错继续执行下面sql。
-- 精确终止关闭
set xact_abort off;
begin tran
insert into sorce values (1,60)
insert into sorce values (2,"aa")
insert into sorce values (3,90)
commit tran
第二条sql因为数据类型错误会失败,这种情况下,出错后会忽略错误语句继续执行下面的sql,第一第三句sql执行成功,第二句忽略。
请注意,不论提交或回滚,都代表当前事务结束,没有end tran这个命令
当然还有其他的写法不过大同小异,比如可以声明事务的名称、提交回滚都带上事务的名称。我觉得如果不是嵌套事务,没必要声明事务名称