04-事务-索引-视图-游标

一、事务

人员信息如下：（第二列是身份证号，第三列是银行卡卡号）

--刘备 420107198905064135 6225125478544587
--关羽 420107199507104133 6225547858741263
--张飞 420107199602034138 6225547854125656

（1）假设刘备取款6000，(添加check约束，设置账户余额必须>=0)，要求：使用事务实现，修改余额和添加取款记录两步操作使用事务

begin transaction
declare @MyError int = 0
update BankCard set CardMoney = CardMoney-6000 where CardNo = '6225125478544587'
set @MyError = @MyError + @@ERROR
insert into CardExchange(CardNo,MoneyInBank,MoneyOutBank,ExchangeTime)
values('6225125478544587',0,6000,GETDATE())
set @MyError = @MyError + @@ERROR
if @MyError = 0
begin
    commit transaction
    print '取款成功'
end 
else
begin
    rollback transaction
    print '余额不足'
end

（2）假设刘备向张飞转账1000元，(添加check约束，设置账户余额必须>=0)；分析步骤有三步（1）张飞添加1000元，（2）刘备扣除1000元，（3）生成转账记录；使用事务解决此问题。

begin transaction
declare @Error int = 0
update BankCard set CardMoney = CardMoney -1000 where CardNo = '6225125478544587'
set @Error = @@ERROR + @Error
update BankCard set CardMoney = CardMoney + 1000 where CardNo = '6225547854125656'
set @Error = @@ERROR + @Error
insert into CardTransfer(CardNoOut,CardNoIn,TransferMoney,TransferTime)
values('6225125478544587','6225547854125656',1000,GETDATE())
set @Error = @@ERROR + @Error
if @Error = 0
    begin
        commit
        print '转账成功'
    end
else
    begin
        rollback
        print '转账失败'        
    end

二、索引

索引：提高检索查询效率。

SQL SERVER索引类型：按存储结构区分：“聚集索引（又称聚类索引，簇集索引）”，“非聚集索引（非聚类索引，非簇集索引）”；

聚集索引：根据数据行的键值在表或视图中的排序存储这些数据行，每个表只有一个聚集索引。聚集索引是一种对磁盘上实际数据重新组织以按指定的一列或多列值排序(类似字典中的拼音索引)(物理存储顺序)。

非聚集索引：具有独立于数据行的结构，包含非聚集索引键值，且每个键值项都有指向包含该键值的数据行的指针。（类似字典中的偏旁部首索引）（逻辑存储顺序）。

SQL SERVER索引其他分类：

按数据唯一性区分：“唯一索引”，“非唯一索引”；按键列个数区分：“单列索引”，“多列索引”。

创建索引的方式：

1. 通过显式的CREATE INDEX命令
2. 在创建约束时作为隐含的对象
   1. 主键约束(聚集索引)
   2. 唯一约束(唯一索引)

创建索引语法：

CREATE [UNIQUE] [CLUSTERED | NONCLUSTERED]
INDEX <index name> ON <table or view name>(<column name> [ASC|DESC][,...n])

索引基本示例语法：

--exp:创建一个非聚集索引
--create nonclustered index indexAccount on AccountInfo(AccountCode)
--删除一个索引
--drop index indexAccount on AccountInfo

备注：索引信息存储在系统视图sys.indexes中。

按照指定索引进行查询

select * from AccountInfo with(index=indexAccount) where AccountCode='6225125478544587'

三、视图

视图：可以理解成虚拟表。

（1）编写视图实现查询出所有银行卡账户信息，显示卡号，身份证，姓名，余额。

create view CardAndAccount as
select CardNo 卡号,AccountCode 身份证,RealName 姓名,CardMoney 余额 from BankCard 
left join AccountInfo on BankCard.AccountId = AccountInfo.AccountId
go

如果要进行相应信息的查询，不需要编写复杂的SQL语句，直接使用视图，如下：

select * from CardAndAccount

四、游标

游标：定位到结果集中某一行。

游标分类：
（1）静态游标(Static)：在操作游标的时候，数据发生变化，游标中数据不变
（2）动态游标(Dynamic)：在操作游标的时候，数据发生变化，游标中数据改变，默认值。
（3）键集驱动游标(KeySet)：在操作游标的时候，被标识的列发生改变，游标中数据改变，其他列改变，游标中数据不变。

假设有如下表结构和数据：

create table Member
(
    MemberId int primary key identity(1,1),
    MemberAccount nvarchar(20) unique check(len(MemberAccount) between 6 and 12),
    MemberPwd nvarchar(20),
    MemberNickname nvarchar(20),
    MemberPhone nvarchar(20)
)

insert into Member(MemberAccount,MemberPwd,MemberNickname,MemberPhone)
values('liubei','123456','刘备','4659874564')
insert into Member(MemberAccount,MemberPwd,MemberNickname,MemberPhone)
values('guanyu','123456','关羽','42354234124')
insert into Member(MemberAccount,MemberPwd,MemberNickname,MemberPhone)
values('zhangfei','123456','张飞','41253445')
insert into Member(MemberAccount,MemberPwd,MemberNickname,MemberPhone)
values('zhangyun','123456','赵云','75675676547')
insert into Member(MemberAccount,MemberPwd,MemberNickname,MemberPhone)
values('machao','123456','马超','532523523')

创建游标：

--1.创建游标(Scroll代表滚动游标，不加Scroll则是只进的，只能支持fetch next)
declare CURSORMember cursor scroll 
for select MemberAccount from Member

打开游标：

open  CURSORMember

提取数据：

fetch first from CURSORMember --结果集的第一行
fetch last from CURSORMember  --最后一行
fetch absolute 1 from CURSORMember --从游标的第一行开始数，第n行。
fetch relative 3 from CURSORMember --从当前位置数，第n行。
fetch next from CURSORMember --当前位置的下一行
fetch prior from CURSORMember --当前位置的上一行

提取数据给变量以供它用（取出第3行用户名，查询该用户详细信息）：

declare @MemberAccount varchar(30)
fetch absolute 3 from CURSORMember into @MemberAccount
select * from Member where MemberAccount = @MemberAccount

利用游标提取所有的账户信息：

--方案一：
fetch absolute 1 from CURSORMember
while @@FETCH_STATUS = 0  --@@FETCH_STATUS=0,提取成功，-1提取失败，-2行不存在
    begin
        fetch next from CURSORMember
    end
    
--方案二：
declare @MemberAccount varchar(30)
--fetch next from CURSORMember into @MemberAccount
fetch absolute 1 from CURSORMember into @MemberAccount
while @@FETCH_STATUS = 0  --@@FETCH_STATUS=0,提取成功，-1提取失败，-2行不存在
    begin
        print '提取成功:' + @MemberAccount
        fetch next from CURSORMember into @MemberAccount
    end

利用游标修改和删除数据：

fetch absolute 3 from CURSORMember
update Member set MemberPwd = '1234567' where Current of CURSORMember

fetch absolute 3 from CURSORMember
delete Member where Current of CURSORMember

关闭游标：

close CURSORMember

删除游标：

deallocate CURSORMember

创建游标指向某行多列数据,并循环显示数据：

--此处如果指向所有数据，可以将for后面的语句修改成select * from Member
declare CURSORMember cursor scroll
for select MemberAccount,MemberPwd,MemberNickname,MemberPhone from Member

open CURSORMember

declare @MemberAccount varchar(30)
declare @MemberPwd nvarchar(20)
declare @MemberNickname nvarchar(20)
declare @MemberPhone nvarchar(20)
fetch next from CURSORMember into @MemberAccount,@MemberPwd,@MemberNickname,@MemberPhone
while @@FETCH_STATUS = 0  --@@FETCH_STATUS=0,提取成功，-1提取失败，-2行不存在
    begin
        print '提取成功:' + @MemberAccount+','+@MemberPwd+','+@MemberNickname+','+@MemberPhone
        fetch next from CURSORMember into @MemberAccount,@MemberPwd,@MemberNickname,@MemberPhone
    end
close CURSORMember