事务和游标

--删除表
drop table bank

--创建表
create table bank
(
bankid int primary key,
bankname varchar(20) not null,
bankmoney money check(bankmoney>=0)
)

--插入数据
insert into bank values(1,'张三',1000)
insert into bank values(2,'李四',1)
--查询表
select * from bank

--创建一个事务
begin tran tranbank
--定义变量
declare @error int
set @error=0
--从张三中减去1000元
update bank set bankmoney=bankmoney-1000 where bankname='张三'
set @error=@error+@@error
--让李四增加1000元
update bank set bankmoney=bankmoney+1000 where bankname='李四'
set @error=@error+@@error
--逻辑判断
if(@error<>0) --如果有错误的情况下，
begin
rollback tran --回滚事务
print '交易失败'
end
else
begin
commit tran --提交事务
print '交易成功'
end
go

 

--游标

if exists(select * from sys.objects where name='stuinfo')
drop table stuinfo
if exists(select * from sys.objects where name='exam')
drop table exam
create table stuinfo(stuid int primary key,stuName varchar(50),stusex varchar(2),classid int)
create table exam(examno int primary key,stuid int,subject varchar(30),score int)

insert into stuinfo values(1,'张三','男',1)
insert into stuinfo values(2,'李四','女',1)
insert into stuinfo values(3,'王五','男',2)
insert into stuinfo values(4,'赵六','女',1)
insert into stuinfo values(5,'田七','男',2)

insert into exam values(1,1,'HTML',85)
insert into exam values(2,1,'JAVA',80)
insert into exam values(3,1,'SQL',82)
insert into exam values(4,2,'HTML',70)
insert into exam values(5,2,'JAVA',80)
insert into exam values(6,2,'SQL',60)
insert into exam values(7,3,'HTML',70)
insert into exam values(8,3,'JAVA',90)
insert into exam values(9,3,'SQL',85)
insert into exam values(10,4,'HTML',61)
insert into exam values(11,4,'JAVA',68)
insert into exam values(12,4,'SQL',90)
insert into exam values(13,5,'HTML',81)
insert into exam values(14,5,'JAVA',65)
insert into exam values(15,5,'SQL',75)

select * from stuinfo --查询表

declare cu_stuinfo cursor --定义游标 declare【定义】 游标名字 cursor
for select stuname,stusex from stuinfo -- for【来自】 select 列 from 表
open cu_stuinfo --打开游标 open 游标名字
declare @stuname varchar(20) --定义变量 存储名字
declare @stusex char(2) --定义变量 存储性别
fetch cu_stuinfo into @stuname,@stusex --检索游标 fetch【检索】 游标名字 into 变量
while(@@fetch_status=0) --循环输出 while(@@fetch_status=0)
begin -- 0:语句成功 -1:语句失败或者不在结果集中 02:被提取行不存在
print '名字是：'+@stuname+' '+'性别是：'+@stusex--打印 print ...
fetch next from cu_stuinfo into @stuname,@stusex --
end
close cu_stuinfo
deallocate cu_stuinfo

 实际操作

存储过程加事务

create proc DeleteCheckResult--删除检查结果数据
@checkid int
as
begin
DECLARE @tid int
declare @trans_error int
set @trans_error=0
begin transaction
select @tid=Tid from CheckResult where id=@checkid
update CheckResult set isDelete=1 where id=@checkid
set @trans_error=@trans_error+@@error

update Task set isDelete=1 where tid=@tid
set @trans_error=@trans_error+@@error
if @trans_error!=0 begin
rollback transaction
end
else begin
commit transaction
end
end