SQL Server 事務,異常和遊標詳解

begin transaction tran_bank;
declare @tran_error int;
    set @tran_error = 0;
    begin try
        update bank set totalMoney = totalMoney - 10000 where userName = 'jack';        
        set @tran_error = @tran_error + @@error;
        update bank set totalMoney = totalMoney + 10000 where userName = 'jason';
        set @tran_error = @tran_error + @@error;
    end try
    begin catch        
        print '出現異常，錯誤編號：' + convert(varchar, error_number()) + '， 錯誤消息：' + error_message(); 
        set @tran_error = @tran_error + 1;
    end catch
if (@tran_error > 0)
    begin
        --執行出錯，回滾事務
        rollback tran;
        print '轉賬失敗，取消交易';
    end
else
    begin
        --沒有異常，提交事務
        commit tran;
        print '轉賬成功';
    end
go

TRY...CATCH 使用錯誤函數來捕獲錯誤信息。
    ERROR_NUMBER() 返回錯誤號。
    ERROR_MESSAGE() 返回錯誤消息的完整文本。此文本包括為任何可替換參數（如長度、對象名稱或時間）提供的值。
    ERROR_SEVERITY() 返回錯誤嚴重性。
    ERROR_STATE() 返回錯誤狀態號。
    ERROR_LINE() 返回導致錯誤的例程中的行號。
    ERROR_PROCEDURE() 返回出現錯誤的存儲過程或觸發器的名稱。

--錯誤消息存儲過程
if (object_id('proc_error_info') is not null)
    drop procedure proc_error_info
go
create proc proc_error_info
as
    select 
        error_number() '錯誤編號',
        error_message() '錯誤消息',
        error_severity() '嚴重性',
        error_state() '狀態好',
        error_line() '錯誤行號',
        error_procedure() '錯誤對象(存儲過程或觸發器)名稱';
go

--簡單try catch示例
begin try
    select 1 / 0;
end try
begin catch
    exec proc_error_info; --調用錯誤消息存儲過程
end catch
go

--
--簡單try catch示例，無法處理錯誤
begin try
    select * * from student;
end try
begin catch
    exec proc_error_info;
end catch
go
--
--簡單try catch示例，不處理錯誤(不存在的表對象)
begin try
    select * from st;
end try
begin catch
    exec proc_error_info;
end catch
go
--
--異常處理，能處理存儲過程（觸發器）中（不存在表對象）的錯誤信息
if (object_id('proc_select') is not null)
    drop procedure proc_select
go
create proc proc_select
as
    select * from st;
go
begin try
    exec proc_select;
end try
begin catch    
    exec proc_error_info;
end catch
go

--創建臨時用表
if (object_id('temp_tab', 'u') is not null)
    drop table temp_tab
go
create table temp_tab(
    id int primary key identity(100000, 1),
    name varchar(200)
)
go
begin try
    begin tran;
    --沒有createTime字段
    alter table temp_tab drop column createTime;
    commit tran;
end try
begin catch
    exec proc_error_info;--顯示異常信息
    if (xact_state() = -1)
    begin
        print '會話具有活動事務，但出現瞭致使事務被歸類為無法提交的事務的錯誤。'
            + '會話無法提交事務或回滾到保存點；它隻能請求完全回滾事務。'
            + '會話在回滾事務之前無法執行任何寫操作。會話在回滾事務之前隻能執行讀操作。'
            + '事務回滾之後，會話便可執行讀寫操作並可開始新的事務。';
    end
    else if (xact_state() = 0)
    begin
        print '會話沒有活動事務。';
    end
    else if (xact_state() = 1)
    begin
        print '會話具有活動事務。會話可以執行任何操作，包括寫入數據和提交事務。';
    end
end catch
go

--
---異常、錯誤信息表
if (object_id('errorLog', 'U') is not null)
    drop table errorLog
go
create table errorLog(
    errorLogID int primary key identity(100, 1),    --ErrorLog 行的主鍵。
    errorTime datetime default getDate(),            --發生錯誤的日期和時間。
    userName sysname default current_user,            --執行發生錯誤的批處理的用戶。
    errorNumber int,                                --發生的錯誤的錯誤號。
    errorSeverity int,                                --發生的錯誤的嚴重性。
    errorState int,                                    --發生的錯誤的狀態號。
    errorProcedure nvarchar(126),                    --發生錯誤的存儲過程或觸發器的名稱。
    errorLine int,                                    --發生錯誤的行號。
    errorMessage nvarchar(4000)
)
go
--
--存儲過程：添加異常日志信息
if (object_id('proc_add_exception_log', 'p') is not null)
    drop proc proc_add_exception_log
go
create proc proc_add_exception_log(@logId int = 0 output)
as
begin
    set nocount on;
    set @logId = 0;
    begin try
        if (error_number() is null)
            return;
        if (xact_state() = -1)
        begin
            print '會話具有活動事務，但出現瞭致使事務被歸類為無法提交的事務的錯誤。'
                + '會話無法提交事務或回滾到保存點；它隻能請求完全回滾事務。'
                + '會話在回滾事務之前無法執行任何寫操作。會話在回滾事務之前隻能執行讀操作。'
                + '事務回滾之後，會話便可執行讀寫操作並可開始新的事務。';
        end
        else if (xact_state() = 0)
        begin
            print '會話沒有活動事務。';
        end
        else if (xact_state() = 1)
        begin
            print '會話具有活動事務。會話可以執行任何操作，包括寫入數據和提交事務。';
        end
        --添加日志信息
        insert into errorLog values(getDate(), 
            current_user, error_number(), 
            error_severity(), error_state(), 
            error_procedure(), 
            error_line(), error_message());
        --設置自增值
        select @logId = @@identity;
    end try
    begin catch
        print '添加異常日志信息出現錯誤';
        exec proc_error_info;--顯示錯誤信息
        return -1;
    end catch
end
go
--
---處理異常信息示例
declare @id int;
begin try
    begin tran;
    --刪除帶有外鍵的記錄信息
    delete classes where id = 1;
    commit tran;
end try
begin catch
    exec proc_error_info;--顯示錯誤信息
    if (xact_state() <> 0)
    begin
        rollback tran;
    end
    exec proc_add_exception_log @id output
end catch
select * from errorLog where errorLogID = @id;
go

declare cursor_name    --遊標名稱
cursor [local | global]    --全局、局部
[forward only | scroll]    --遊標滾動方式
[read_only | scroll_locks | optimistic]    --讀取方式
for select_statements                    --查詢語句
[for update | of column_name ...]        --修改字段

--創建一個遊標
declare cursor_stu cursor scroll for
    select id, name, age from student;
--打開遊標
open cursor_stu;
--存儲讀取的值
declare @id int,
        @name nvarchar(20),
        @age varchar(20);
--讀取第一條記錄
fetch first from cursor_stu into @id, @name, @age;
--循環讀取遊標記錄
print '讀取的數據如下：';
--全局變量
while (@@fetch_status = 0)
begin
    print '編號：' + convert(char(5), @id) + ', 名稱：' + @name + ', 類型：' + @age;
    --繼續讀取下一條記錄
    fetch next from cursor_stu into @id, @name, @age;
end
--關閉遊標
close area_cursor;
--刪除遊標
--deallocate area_cursor;