QL Server 事務(wù)、異常和游標(biāo)

事務(wù)

在數(shù)據(jù)庫中有時候需要把多個步驟的指令當(dāng)作一個整體來運行，這個整體要么全部成功，要么全部失敗，這就需要用到事務(wù)。

1、事務(wù)的特點

事務(wù)有若干條T-SQL指令組成，并且所有的指令昨晚一個整體提交給數(shù)據(jù)庫系統(tǒng)，執(zhí)行時，這組指令要么全部執(zhí)行完成，要么全部取消。因此，事務(wù)是一個不可分割的邏輯單元。

事務(wù)有4個屬性：原子性（Atomicity）、一致性（Consistency）、隔離性（Isolation）以及持久性（Durability），也稱作事務(wù)的ACID屬性。

原子性：事務(wù)內(nèi)的所有工作要么全部完成，要么全部不完成，不存在只有一部分完成的情況。

一致性：事務(wù)內(nèi)的然后操作都不能違反數(shù)據(jù)庫的然后約束或規(guī)則，事務(wù)完成時有內(nèi)部數(shù)據(jù)結(jié)構(gòu)都必須是正確的。

隔離性：事務(wù)直接是相互隔離的，如果有兩個事務(wù)對同一個數(shù)據(jù)庫進(jìn)行操作，比如讀取表數(shù)據(jù)。任何一個事務(wù)看到的所有內(nèi)容要么是其他事務(wù)完成之前的狀態(tài)，要么是其他事務(wù)完成之后的狀態(tài)。一個事務(wù)不可能遇到另一個事務(wù)的中間狀態(tài)。

持久性：事務(wù)完成之后，它對數(shù)據(jù)庫系統(tǒng)的影響是持久的，即使是系統(tǒng)錯誤，重新啟動系統(tǒng)后，該事務(wù)的結(jié)果依然存在。

2、事務(wù)的模式

a、顯示事務(wù)

顯示事務(wù)就是用戶使用T-SQL明確的定義事務(wù)的開始（begin transaction）和提交（commit transaction）或回滾事務(wù)（rollback transaction）

b、自動提交事務(wù)

自動提交事務(wù)是一種能夠自動執(zhí)行并能自動回滾事務(wù)，這種方式是T-SQL的默認(rèn)事務(wù)方式。例如在刪除一個表記錄的時候，如果這條記錄有主外鍵關(guān)系的時候，刪除就會受主外鍵約束的影響，那么這個刪除就會取消。

可以設(shè)置事務(wù)進(jìn)入隱式方式：set implicit_transaction on;

c、隱式事務(wù)

隱式事務(wù)是指當(dāng)事務(wù)提交或回滾后，SQL Server自動開始事務(wù)。因此，隱式事務(wù)不需要使用begin transaction顯示開始，只需直接失業(yè)提交事務(wù)或回滾事務(wù)的T-SQL語句即可。

使用時，需要設(shè)置set implicit_transaction on語句，將隱式事務(wù)模式打開，下一個語句會啟動一個新的事物，再下一個語句又將啟動一個新事務(wù)。

3、事務(wù)處理

常用T-SQL事務(wù)語句：

a、 begin transaction語句

開始事務(wù)，而@@trancount全局變量用來記錄事務(wù)的數(shù)目值加1，可以用@@error全局變量記錄執(zhí)行過程中的錯誤信息，如果沒有錯誤可以直接提交事務(wù)，有錯誤可以回滾。

b、 commit transaction語句

回滾事務(wù)，表示一個隱式或顯示的事務(wù)的結(jié)束，對數(shù)據(jù)庫所做的修改正式生效。并將@@trancount的值減1；

c、 rollback transaction語句

回滾事務(wù)，執(zhí)行rollback tran語句后，數(shù)據(jù)會回滾到begin tran的時候的狀態(tài)

4、事務(wù)的示例

--開始事務(wù)
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 '出現(xiàn)異常，錯誤編號：' + convert(varchar, error_number()) + '， 錯誤消息：' + error_message(); 
        set @tran_error = @tran_error + 1;
    end catch
if (@tran_error > 0)
    begin
        --執(zhí)行出錯，回滾事務(wù)
        rollback tran;
        print '轉(zhuǎn)賬失敗，取消交易';
    end
else
    begin
        --沒有異常，提交事務(wù)
        commit tran;
        print '轉(zhuǎn)賬成功';
    end
go

異常

在程序中，有時候完成一些Transact-SQL會出現(xiàn)錯誤、異常信息。如果我們想自己處理這些異常信息的話，需要手動捕捉這些信息。那么我們可以利用try catch完成。

TRY…CATCH 構(gòu)造包括兩部分：一個 TRY 塊和一個 CATCH 塊。如果在 TRY 塊中所包含的 Transact-SQL 語句中檢測到錯誤條件，控制將被傳遞到 CATCH 塊（可在此塊中處理該錯誤）。

CATCH 塊處理該異常錯誤后，控制將被傳遞到 END CATCH 語句后面的第一個 Transact-SQL 語句。如果 END CATCH 語句是存儲過程或觸發(fā)器中的最后一條語句，控制將返回到調(diào)用該存儲過程或觸發(fā)器的代碼。將不執(zhí)行 TRY 塊中生成錯誤的語句后面的 Transact-SQL 語句。

如果 TRY 塊中沒有錯誤，控制將傳遞到關(guān)聯(lián)的 END CATCH 語句后緊跟的語句。如果 END CATCH 語句是存儲過程或觸發(fā)器中的最后一條語句，控制將傳遞到調(diào)用該存儲過程或觸發(fā)器的語句。

TRY 塊以 BEGIN TRY 語句開頭，以 END TRY 語句結(jié)尾。在 BEGIN TRY 和 END TRY 語句之間可以指定一個或多個 Transact-SQL 語句。CATCH 塊必須緊跟 TRY 塊。CATCH 塊以 BEGIN CATCH 語句開頭，以 END CATCH 語句結(jié)尾。在 Transact-SQL 中，每個 TRY 塊僅與一個 CATCH 塊相關(guān)聯(lián)。

# 錯誤函數(shù)

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

示例

--錯誤消息存儲過程
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() '嚴(yán)重性',
        error_state() '狀態(tài)好',
        error_line() '錯誤行號',
        error_procedure() '錯誤對象(存儲過程或觸發(fā)器)名稱';
go

# 示例：用異常處理錯誤信息

--簡單try catch示例
begin try
    select 1 / 0;
end try
begin catch
    exec proc_error_info; --調(diào)用錯誤消息存儲過程
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
--
--異常處理，能處理存儲過程（觸發(fā)器）中（不存在表對象）的錯誤信息
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

異常不能處理編譯期的錯誤，如語法錯誤。以及重編譯造成部分名稱對象得不到正確解析的時候所出現(xiàn)的錯誤。

# 示例：無法提交的事務(wù)

--創(chuàng)建臨時用表
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 '會話具有活動事務(wù)，但出現(xiàn)了致使事務(wù)被歸類為無法提交的事務(wù)的錯誤。'
            + '會話無法提交事務(wù)或回滾到保存點；它只能請求完全回滾事務(wù)。'
            + '會話在回滾事務(wù)之前無法執(zhí)行任何寫操作。會話在回滾事務(wù)之前只能執(zhí)行讀操作。'
            + '事務(wù)回滾之后，會話便可執(zhí)行讀寫操作并可開始新的事務(wù)。';
    end
    else if (xact_state() = 0)
    begin
        print '會話沒有活動事務(wù)。';
    end
    else if (xact_state() = 1)
    begin
        print '會話具有活動事務(wù)。會話可以執(zhí)行任何操作，包括寫入數(shù)據(jù)和提交事務(wù)。';
    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(),            --發(fā)生錯誤的日期和時間。
    userName sysname default current_user,            --執(zhí)行發(fā)生錯誤的批處理的用戶。
    errorNumber int,                                --發(fā)生的錯誤的錯誤號。
    errorSeverity int,                                --發(fā)生的錯誤的嚴(yán)重性。
    errorState int,                                    --發(fā)生的錯誤的狀態(tài)號。
    errorProcedure nvarchar(126),                    --發(fā)生錯誤的存儲過程或觸發(fā)器的名稱。
    errorLine int,                                    --發(fā)生錯誤的行號。
    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 '會話具有活動事務(wù)，但出現(xiàn)了致使事務(wù)被歸類為無法提交的事務(wù)的錯誤。'
                + '會話無法提交事務(wù)或回滾到保存點；它只能請求完全回滾事務(wù)。'
                + '會話在回滾事務(wù)之前無法執(zhí)行任何寫操作。會話在回滾事務(wù)之前只能執(zhí)行讀操作。'
                + '事務(wù)回滾之后，會話便可執(zhí)行讀寫操作并可開始新的事務(wù)。';
        end
        else if (xact_state() = 0)
        begin
            print '會話沒有活動事務(wù)。';
        end
        else if (xact_state() = 1)
        begin
            print '會話具有活動事務(wù)。會話可以執(zhí)行任何操作，包括寫入數(shù)據(jù)和提交事務(wù)。';
        end
        
        --添加日志信息
        insert into errorLog values(getDate(), 
            current_user, error_number(), 
            error_severity(), error_state(), 
            error_procedure(), 
            error_line(), error_message());
        --設(shè)置自增值
        select @logId = @@identity;
    end try
    begin catch
        print '添加異常日志信息出現(xiàn)錯誤';
        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

游標(biāo)

游標(biāo)可以對一個select的結(jié)果集進(jìn)行處理，或是不需要全部處理，就會返回一個對記錄集進(jìn)行處理之后的結(jié)果。

1、游標(biāo)實際上是一種能從多條數(shù)據(jù)記錄的結(jié)果集中每次提取一條記錄的機制。游標(biāo)可以完成：

# 允許定位到結(jié)果集中的特定行

# 從結(jié)果集的當(dāng)前位置檢索一行或多行數(shù)據(jù)

# 支持對結(jié)果集中當(dāng)前位置的進(jìn)行修改

由于游標(biāo)是將記錄集進(jìn)行一條條的操作，所以這樣給服務(wù)器增加負(fù)擔(dān)，一般在操作復(fù)雜的結(jié)果集的情況下，才使用游標(biāo)。SQL Server 2005有三種游標(biāo)：T-SQL游標(biāo)、API游標(biāo)、客戶端游標(biāo)。

2、游標(biāo)的基本操作

游標(biāo)的基本操作有定義游標(biāo)、打開游標(biāo)、循環(huán)讀取游標(biāo)、關(guān)閉游標(biāo)、刪除游標(biāo)。

A、定義游標(biāo)

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

參數(shù)：

forward only | scroll：前一個參數(shù)，游標(biāo)只能向后移動；后一個參數(shù)，游標(biāo)可以隨意移動

read_only：只讀游標(biāo)

scroll_locks：游標(biāo)鎖定，游標(biāo)在讀取時，數(shù)據(jù)庫會將該記錄鎖定，以便游標(biāo)完成對記錄的操作

optimistic：該參數(shù)不會鎖定游標(biāo)；此時，如果記錄被讀入游標(biāo)后，對游標(biāo)進(jìn)行更新或刪除不會超過

B、打開游標(biāo)

open cursor_name;

游標(biāo)打開后，可以使用全局變量@@cursor_rows顯示讀取記錄條數(shù)

C、檢索游標(biāo)

fetch cursor_name;

檢索方式如下：

fetch first; 讀取第一行

fetch next; 讀取下一行

fetch prior; 讀取上一行

fetch last; 讀取最后一行

fetch absolute n; 讀取某一行

如果n為正整數(shù)，則讀取第n條記錄

如果n為負(fù)數(shù)，則倒數(shù)提取第n條記錄

如果n為，則不讀取任何記錄

fetch pelative n

如果n為正整數(shù)，則讀取上次讀取記錄之后第n條記錄

如果n為負(fù)數(shù)，則讀取上次讀取記錄之前第n條記錄

如果n為，則讀取上次讀取的記錄

D、關(guān)閉游標(biāo)

close cursor_name;

E、刪除游標(biāo)

deallocate cursor_name;

3、游標(biāo)操作示例

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

--刪除游標(biāo)
--deallocate area_cursor;

本站僅提供存儲服務(wù)，所有內(nèi)容均由用戶發(fā)布，如發(fā)現(xiàn)有害或侵權(quán)內(nèi)容，請點擊舉報。

免费视频淫片aa毛片_日韩高清在线亚洲专区vr_日韩大片免费观看视频播放_亚洲欧美国产精品完整版

QL Server 事務(wù)、異常和游標(biāo)