事务演示--游标

最新推荐文章于 2024-03-05 14:03:22 发布

tourstar

最新推荐文章于 2024-03-05 14:03:22 发布

阅读量431

点赞数

分类专栏： SQL SERVER 文章标签： insert null scroll table database float

本文链接：https://blog.youkuaiyun.com/tourstar/article/details/6001751

版权

SQL SERVER 专栏收录该内容

15 篇文章

订阅专栏

---事务演示
create database TRANS_DEMO;
go

use TRANS_DEMO;
go

--银行账户表
create table BANK_ACCOUNTS
(
ACCOUNT_NUM nvarchar(20) not null constraint PK_BANK_ACCOUNTS primary key, --帐号(主键)
CUSTOMER_NAME nvarchar(20) not null, --客户姓名
BALANCE float not null --余额
);

alter table BANK_ACCOUNTS add constraint CK_BANK_ACCOUNTS_BALANCE check (BALANCE >= 0 AND BALANCE <= 13000);

--银行业务日志表
create table BANK_TRANS_LOGS
(
SEQ_NO bigint not null identity constraint PK_BANK_TRANS_LOGS primary key, --流水号(主键)
ACCOUNT_NUM nvarchar(20) not null constraint FK_BANK_TRANS_LOGS_ACCOUNT_NUM_BANK_ACCOUNTS_ACCOUNT_NUM
   foreign key references BANK_ACCOUNTS(ACCOUNT_NUM),     --帐号
AMOUNT float not null,              --金额
DESCRIPTION nvarchar(100) not null,           --操作说明
OPERATOR nvarchar(20) not null,            --操作人
OPER_TIME datetime not null,            --操作时间
)

insert into BANK_ACCOUNTS (ACCOUNT_NUM, CUSTOMER_NAME, BALANCE) values ('20090101','张三',5000);
insert into BANK_ACCOUNTS (ACCOUNT_NUM, CUSTOMER_NAME, BALANCE) values ('20090102','李四',5000);
insert into BANK_ACCOUNTS (ACCOUNT_NUM, CUSTOMER_NAME, BALANCE) values ('20090103','王五',5000);
go

select * from bank_accounts;
go

--利用事务完成转帐过程
BEGIN TRANSACTION
update BANK_ACCOUNTS set BALANCE=BALANCE-5000 where ACCOUNT_NUM = '20090101';
if(@@ERROR <> 0) --如果前面一条语句的执行过程出现了错误则回滚事务并结束批处理
begin
ROLLBACK TRANSACTION
return;
end
update BANK_ACCOUNTS set BALANCE=BALANCE+5000 where ACCOUNT_NUM = '20090103';
if(@@ERROR <> 0)
begin
ROLLBACK TRANSACTION
return;
end
insert into BANK_TRANS_LOGS(ACCOUNT_NUM,AMOUNT,DESCRIPTION,OPERATOR,OPER_TIME) values
('20090101',-5000,'转出','Tom',GetDate());
if(@@ERROR <> 0)
begin
ROLLBACK TRANSACTION
return;
end
insert into BANK_TRANS_LOGS(ACCOUNT_NUM,AMOUNT,DESCRIPTION,OPERATOR,OPER_TIME) values
('20090103',5000,'转入','Tom',GetDate());
if(@@ERROR <> 0)
begin
ROLLBACK TRANSACTION
return;
end
Commit TRANSACTION

--采用结构化错误处理流程处理事务回滚
declare @trans_amount float;
set @trans_amount = 2000;

SET XACT_ABORT ON; --设置当事务出错时中止事务执行

BEGIN TRY
BEGIN TRANSACTION
update BANK_ACCOUNTS set BALANCE=BALANCE-@trans_amount where ACCOUNT_NUM = '20090101';
update BANK_ACCOUNTS set BALANCE=BALANCE+@trans_amount where ACCOUNT_NUM = '20090103';
insert into BANK_TRANS_LOGS(ACCOUNT_NUM,AMOUNT,DESCRIPTION,OPERATOR,OPER_TIME) values
  ('20090101',-@trans_amount,'转出','Tom',GetDate());
insert into BANK_TRANS_LOGS(ACCOUNT_NUM,AMOUNT,DESCRIPTION,OPERATOR,OPER_TIME) values
  ('20090103',@trans_amount,'转入','Tom',GetDate());
Commit TRANSACTION
print '转帐成功';
END TRY
BEGIN CATCH
print '错误：' + convert(nvarchar,ERROR_NUMBER()) + ':' + ERROR_MESSAGE();
if(XACT_STATE() = -1)
begin
  ROLLBACK TRANSACTION
end
END CATCH

go
select * from BANK_ACCOUNTS;
select * from BANK_TRANS_LOGS;
go

----游标高级选项演示

----只读、前向游标(FAST_FORWARD选项,速度最快,最节省资源的选项)
declare @acc_num nvarchar(20);
declare @cust_name nvarchar(20);
declare @balance float;

declare bankcur cursor FAST_FORWARD for select ACCOUNT_NUM, CUSTOMER_NAME, BALANCE from BANK_ACCOUNTS;
open bankcur;

fetch bankcur into @acc_num, @cust_name, @balance;

while(@@FETCH_STATUS = 0)
begin
print '帐号:' + @acc_num + ' 姓名:' + @cust_name + ' 余额:' + convert(nvarchar,@balance);
fetch bankcur into @acc_num, @cust_name, @balance;
end

close bankcur;
deallocate bankcur;
go

----利用游标更新数据(利用For update选项打开游标)

declare @acc_num nvarchar(20);
declare @cust_name nvarchar(20);
declare @balance float;

declare bankcur cursor for select ACCOUNT_NUM, CUSTOMER_NAME, BALANCE from BANK_ACCOUNTS for update;
open bankcur;

fetch bankcur into @acc_num, @cust_name, @balance;

while(@@FETCH_STATUS = 0)
begin
if(@balance < 10000) --如果账户余额小于10000，则在账户上增加2000
begin
update bank_accounts set BALANCE=BALANCE+2000 where current of bankcur; --在游标当前指向的记录上更新
end
fetch bankcur into @acc_num, @cust_name, @balance;
end

close bankcur;
deallocate bankcur;

select * from bank_accounts;
go

----使用静态滚动游标（使用SCROLL和STATIC选项）
declare @acc_num nvarchar(20);
declare @cust_name nvarchar(20);
declare @balance float;

declare bankcur cursor SCROLL STATIC for select ACCOUNT_NUM, CUSTOMER_NAME, BALANCE from BANK_ACCOUNTS;
open bankcur;

----从前向后依次顺序取出数据
fetch bankcur into @acc_num, @cust_name, @balance;

while(@@FETCH_STATUS = 0)
begin
print '帐号:' + @acc_num + ' 姓名:' + @cust_name + ' 余额:' + convert(nvarchar,@balance);
fetch bankcur into @acc_num, @cust_name, @balance;
end

----绝对定位取出数据
fetch absolute 2 from bankcur into @acc_num, @cust_name, @balance; --(取第二条记录)
if(@@FETCH_STATUS = 0)
begin
print '帐号:' + @acc_num + ' 姓名:' + @cust_name + ' 余额:' + convert(nvarchar,@balance);
end
else
begin
print '记录不存在';
end

fetch absolute 4 from bankcur into @acc_num, @cust_name, @balance; --(取第四条记录)
if(@@FETCH_STATUS = 0)
begin
print '帐号:' + @acc_num + ' 姓名:' + @cust_name + ' 余额:' + convert(nvarchar,@balance);
end
else
begin
print '记录不存在';
end

----利用相对定位取出数据
fetch relative -2 from bankcur into @acc_num, @cust_name, @balance; --(取相对于当前位置的前面第二条记录)
if(@@FETCH_STATUS = 0)
begin
print '帐号:' + @acc_num + ' 姓名:' + @cust_name + ' 余额:' + convert(nvarchar,@balance);
end
else
begin
print '记录不存在';
end

fetch relative 1 from bankcur into @acc_num, @cust_name, @balance; --(取相对于当前位置的后面第一条记录)
if(@@FETCH_STATUS = 0)
begin
print '帐号:' + @acc_num + ' 姓名:' + @cust_name + ' 余额:' + convert(nvarchar,@balance);
end
else
begin
print '记录不存在';
end

----取前面一条记录
fetch prior from bankcur into @acc_num, @cust_name, @balance;
if(@@FETCH_STATUS = 0)
begin
print '帐号:' + @acc_num + ' 姓名:' + @cust_name + ' 余额:' + convert(nvarchar,@balance);
end
else
begin
print '记录不存在';
end

----取后面一条记录
fetch next from bankcur into @acc_num, @cust_name, @balance;
if(@@FETCH_STATUS = 0)
begin
print '帐号:' + @acc_num + ' 姓名:' + @cust_name + ' 余额:' + convert(nvarchar,@balance);
end
else
begin
print '记录不存在';
end

----取第一条记录
fetch first from bankcur into @acc_num, @cust_name, @balance;
if(@@FETCH_STATUS = 0)
begin
print '帐号:' + @acc_num + ' 姓名:' + @cust_name + ' 余额:' + convert(nvarchar,@balance);
end
else
begin
print '记录不存在';
end

----取最后一条记录
fetch last from bankcur into @acc_num, @cust_name, @balance;
if(@@FETCH_STATUS = 0)
begin
print '帐号:' + @acc_num + ' 姓名:' + @cust_name + ' 余额:' + convert(nvarchar,@balance);
end
else
begin
print '记录不存在';
end

close bankcur;
deallocate bankcur;
go

SET XACT_ABORT OFF
go