/*
事务:begin transaction开始事务 、
commit transaction 提交事务、
rollback transaction 回滚事务、
SAVE TRAN 保存事务从本质上说就是创建书签(bookmark)
异常:
begin TRY
...异常逻辑
end TRY
begin CATCH
...异常处理
end CATCH
游标: 定义游标 以及使用
*/
--PS 游标、事务、异常的使用 如下例子:
go
--如果存储过程存在,则删除
if OBJECT_ID(N'Remove_Ticket',N'P') is not null
drop proc Remove_Ticket
--创建存储过程
go
create proc Remove_Ticket
@id nvarchar(50),
@optUser nvarchar(50),
@result int output,
@msg nvarchar(255) output
as
set nocount on --关闭影响行数的输出
declare @errInt int,@TicketId nvarchar(50),@TicketEntryId nvarchar(50), @CustomerName nvarchar(255),@SenderName nvarchar(255),
@ReceiverName nvarchar(255),@CompanyName nvarchar(255),@TypeName nvarchar(50),@Cargoname nvarchar(255),
@Specificaiton nvarchar(50), @Price decimal(18,3),@PriceType nvarchar(50),@Amount decimal(18,3),@TotalUse decimal(18,3),
@TicketVerify nvarchar(50), @TicketStates nvarchar(50),@TicketDel nvarchar(50),@optime nvarchar(50)
set @errInt = 0
set @optime =CONVERT(nvarchar(50),GETDATE(),20) --时间转换成字符串格式:yyyy-MM-dd HH:mm:ss
if len(@id) = 0 --判断重要参数是否是空
begin
set @result= -1
end
else
begin
--开启事务
begin transaction
--定义游标
declare Ticket_cursor cursor fast_forward for
select TicketId,TicketEntryId,CustomerName, SenderName,ReceiverName,CompanyName,TypeName,Cargoname,
Specificaiton,Price,PriceType,Amount,TotalUse,TicketVerify,TicketStates,TicketDel from g_contractAll_View
where TicketId=@id
--打开游标
open Ticket_cursor
begin try --捕捉异常
if exists(select * from g_contract where ticketid= @id)
begin
insert into g_contractDel select * from g_contract where ticketid= @id
set @errInt = @errint+@@ERROR --@@ERROR返回执行的上一个 SQL 语句的错误号。
if exists(select * from g_contractDetail where TicketId= @id)
begin
fetch next from Ticket_cursor into @TicketId,@TicketEntryId,@CustomerName,
@SenderName,@ReceiverName,@CompanyName,@TypeName,@Cargoname,
@Specificaiton,@Price,@PriceType,@Amount,@TotalUse,@TicketVerify,@TicketStates,@TicketDel
while(@@FETCH_STATUS = 0) --@@FETCH_STATUS 0 FETCH 语句成功,-1 FETCH 语句失败或此行不在结果集中,-2 被提取的行不存在
begin
insert into g_contractLog(ticketId,ticketEntryId,curstomer,sender,receiver,company,type,cargoname,spec,price,
priceType,amount,totalUse,ticketVerify,ticketStates, ticketDel, optUser, optTime,optAction, upload,downLoad)
values(@TicketId,@TicketEntryId,@CustomerName, @SenderName,@ReceiverName,@CompanyName,@TypeName,@Cargoname,
@Specificaiton,@Price,@PriceType,@Amount,@TotalUse,@TicketVerify,@TicketStates,@TicketDel, @optUser,@optime,'删除','0','0')
set @errInt = @errint+@@ERROR
fetch next from Ticket_cursor into @TicketId,@TicketEntryId,@CustomerName,
@SenderName,@ReceiverName,@CompanyName,@TypeName,@Cargoname,
@Specificaiton,@Price,@PriceType,@Amount,@TotalUse,@TicketVerify,@TicketStates,@TicketDel
end
insert into g_contractDetailDel select * from g_contractDetail where TicketId=@id
set @errInt = @errint+@@ERROR
delete from g_contractDetail where TicketId=@id
set @errInt = @errint+@@ERROR
end
delete from g_contract where ticketid= @id
set @errInt = @errint+@@ERROR
end
else
set @result = 1;
end try
begin catch --异常处理
set @errInt = @errint+@@ERROR
set @msg = '状态:'+Convert(varchar, ERROR_STATE())+',行号:'+Convert(varchar, ERROR_LINE())+',消息:'
+Convert(varchar, ERROR_MESSAGE())+',级别:'+Convert(varchar, ERROR_SEVERITY())
end catch
if(@errInt = 0)
begin
commit transaction --提交事务
set @result = 1;
set @msg='执行成功!'
end
else
begin
rollback transaction --回滚事务
set @result = -1
set @msg = '任务失败:'+@msg
end
--关闭游标
close Ticket_cursor
--释放游标
deallocate Ticket_cursor
end