【SQL Server】数据库开发指南（八）高级数据处理技术 MS-SQL 事务、异常和游标的深入研究

今天药忘吃喽~ 2023-10-12 10:23 13阅读 0赞

本系列博文还在更新中，收录在专栏：[\#MS-SQL Server][MS-SQL Server] 专栏中。

本系列文章列表如下：

[【SQL Server】 Linux 运维下对 SQL Server 进行安装、升级、回滚、卸载操作][SQL Server_ Linux _ SQL Server]  
[【SQL Server】数据库开发指南（一）数据库设计的核心概念和基本步骤][SQL Server]  
[【SQL Server】数据库开发指南（二）MSSQL数据库开发对于库、表、数据类型、约束等相关操作][SQL Server_MSSQL]  
[【SQL Server】数据库开发指南（三）面向数据分析的 T-SQL 编程技巧与实践][SQL Server_ T-SQL]  
[\[ 云原生 | Docker \] 构建高可用性的 SQL Server：Docker 容器下的主从同步实现指南][_ _ Docker _ _ SQL Server_Docker]  
[【SQL Server】数据库开发指南（五）T-SQL 高级查询综合应用与实战][SQL Server_T-SQL]  
[【SQL Server】数据库开发指南（六）索引和视图的使用技巧、方法与综合应用][SQL Server 1]  
[【SQL Server】数据库开发指南（七）MS-SQL存储过程全面解析：种类、优点和创建方法详解][SQL Server_MS-SQL]  
[【SQL Server】数据库开发指南（八）高级数据处理技术 MS-SQL 事务、异常和游标的深入研究][SQL Server_ MS-SQL]  
[【SQL Server】数据库开发指南（九）详细讲解 MS-SQL 触发器的基础概念与应用场景][SQL Server_ MS-SQL 1]

### 文章目录 ###

*   *  前言
     *  一、MS-SQL 事务
     *   *  1.1 事务的特点
         *  1.2 事务的模式
         *  1.3 事务处理
         *  1.4 事务的示例
     *  二、MS-SQL 异常处理
     *   *  2.1 错误函数
         *  2.2 异常的实用场景举例
         *   *  2.2.1 用异常处理错误信息
             *  2.2.2 处理存储过程等异常
             *  2.2.3 判断会话等异常处理
             *  2.2.4 处理异常日志信息
     *  三、游标
     *   *  3.1 初识游标
         *  3.2 游标的基本操作
         *  3.3 游标操作示例

## 前言 ##

本文将全面介绍 MS-SQL Server 中的关键概念：事务处理、异常处理和游标的使用。你将了解事务的特点和不同模式，掌握异常处理函数及其在实际场景的应用，深入了解游标的基本操作和示例。通过本文，你将获得实用的数据库管理和开发技能，提高数据的完整性和一致性，有效处理各种错误和异常情况，以及实现高级数据操作。无论是新手还是有经验的开发人员，本文都将为你提供全面而有用的指导，助你在 MS-SQL Server 环境中取得成功。

## 一、MS-SQL 事务 ##

无论是在哪种数据库，SQL Server、MySQL也好，Oracle也罢，在关系型数据库中有时候需要把多个步骤的指令当作一个整体来运行，这个整体要么全部成功，要么全部失败，这就需要用到事务。

**SQL Server事务有若干条T-SQL指令组成，是一组数据库操作的逻辑单元，被视为一个不可分割的工作单元。**

### 1.1 事务的特点 ###

事务有4个属性：原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）以及持久性（Durability），也称作事务的`ACID`属性。

![请添加图片描述][c11c3a9760bc47669bacab53737c16a0.png]

**原子性**：事务内的所有工作要么全部完成，要么全部不完成，不存在只有一部分完成的情况。

**一致性**：事务内的然后操作都不能违反数据库的然后约束或规则，事务完成时有内部数据结构都必须是正确的。

**隔离性**：事务直接是相互隔离的，如果有两个事务对同一个数据库进行操作，比如读取表数据。任何一个事务看到的所有内容要么是其他事务完成之前的状态，要么是其他事务完成之后的状态。一个事务不可能遇到另一个事务的中间状态。

**持久性**：事务完成之后，它对数据库系统的影响是持久的，即使是系统错误，重新启动系统后，该事务的结果依然存在。

### 1.2 事务的模式 ###

在 SQL Server 中，有几种常见的事务模式，包括：

1.  手动模式（Manual Mode）：在手动模式下，事务需要显式地开始（BEGIN TRANSACTION）、提交（COMMIT）或回滚（ROLLBACK）。开发人员需要明确控制事务的边界，并确保事务的正确管理。
2.  隐式模式（Implicit Mode）：在隐式模式下，事务会自动启动，并在每个SQL语句的执行结束时自动提交。这种模式适用于简单的事务场景，无需显式控制事务的边界和提交。隐式事务是指当事务提交或回滚后，SQL Server自动开始事务。因此，隐式事务不需要使用begin transaction显示开始，只需直接失业提交事务或回滚事务的T-SQL语句即可。
    
    使用时，需要设置 `set implicit_transaction on` 语句，将隐式事务模式打开，下一个语句会启动一个新的事物，再下一个语句又将启动一个新事务。
3.  显式模式（Explicit Mode）：在显式模式下，事务需要使用显式的BEGIN TRANSACTION语句来启动，并使用COMMIT或ROLLBACK语句来结束事务。这种模式适用于需要明确控制事务范围的复杂事务场景。
4.  自动提交模式（Auto-Commit Mode）：在自动提交模式下，每个SQL语句都会被视为一个单独的事务，并在执行完成后立即提交。这种模式适用于每个SQL语句都是独立的，不需要进行批量操作或事务管理的简单场景。

### 1.3 事务处理 ###

常用 T-SQL 事务语句：

*  begin transaction语句

开始事务，而@@trancount全局变量用来记录事务的数目值加1，可以用@@error全局变量记录执行过程中的错误信息，如果没有错误可以直接提交事务，有错误可以回滚。

*  commit transaction语句

回滚事务，表示一个隐式或显示的事务的结束，对数据库所做的修改正式生效。并将@@trancount的值减1；

*  rollback transaction语句

回滚事务，执行rollback tran语句后，数据会回滚到begin tran的时候的状态

### 1.4 事务的示例 ###

下面对事务进行一个举例，让我们更加深入的了解一下事务的应用

![在这里插入图片描述][ec04d24961b145128c1138ad15920f74.png]

--开始事务
    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

## 二、MS-SQL 异常处理 ##

在程序中，有时候完成一些Transact-SQL会出现错误、异常信息。如果我们想自己处理这些异常信息的话，需要手动捕捉这些信息。那么我们可以利用try catch完成。

TRY…CATCH 构造包括两部分：一个 TRY 块和一个 CATCH 块。如果在 TRY 块中所包含的 Transact-SQL 语句中检测到错误条件，控制将被传递到 CATCH 块（可在此块中处理该错误）。

CATCH 块处理该异常错误后，控制将被传递到 END CATCH 语句后面的第一个 Transact-SQL 语句。如果 END CATCH 语句是存储过程或触发器中的最后一条语句，控制将返回到调用该存储过程或触发器的代码。将不执行 TRY 块中生成错误的语句后面的 Transact-SQL 语句。

如果 TRY 块中没有错误，控制将传递到关联的 END CATCH 语句后紧跟的语句。如果 END CATCH 语句是存储过程或触发器中的最后一条语句，控制将传递到调用该存储过程或触发器的语句。

TRY 块以 BEGIN TRY 语句开头，以 END TRY 语句结尾。在 BEGIN TRY 和 END TRY 语句之间可以指定一个或多个 Transact-SQL 语句。CATCH 块必须紧跟 TRY 块。CATCH 块以 BEGIN CATCH 语句开头，以 END CATCH 语句结尾。在 Transact-SQL 中，每个 TRY 块仅与一个 CATCH 块相关联。

### 2.1 错误函数 ###

SQL Server 中常见的错误函数：

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

### 2.2 异常的实用场景举例 ###

#### 2.2.1 用异常处理错误信息 ####

--简单try catch示例
    begin try
        select 1 / 0;
    end try
    begin catch
        exec proc_error_info; --调用错误消息存储过程
    end catch
    go

#### 2.2.2 处理存储过程等异常 ####

--简单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

#### 2.2.3 判断会话等异常处理 ####

--创建临时用表
    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

#### 2.2.4 处理异常日志信息 ####

--异常、错误信息表
    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

## 三、游标 ##

### 3.1 初识游标 ###

游标可以对一个`select`的结果集进行处理，或是不需要全部处理，就会返回一个对记录集进行处理之后的结果。

游标实际上是一种能从多条数据记录的结果集中每次提取一条记录的机制。游标可以完成：

1.  允许定位到结果集中的特定行
2.  从结果集的当前位置检索一行或多行数据
3.  支持对结果集中当前位置的进行修改

由于游标是将记录集进行一条条的操作，所以这样给服务器增加负担，一般在操作复杂的结果集的情况下，才使用游标。

### 3.2 游标的基本操作 ###

游标的基本操作有定义游标、打开游标、循环读取游标、关闭游标、删除游标。

**定义游标**

declare cursor_name    --游标名称
    cursor [local | global]    --全局、局部
    [forward only | scroll]    --游标滚动方式
    [read_only | scroll_locks | optimistic]    --读取方式
    for select_statements                    --查询语句
    [for update | of column_name ...]        --修改字段

参数解释：

forward only | scroll：前一个参数，游标只能向后移动；后一个参数，游标可以随意移动  
read\_only：只读游标  
scroll\_locks：游标锁定，游标在读取时，数据库会将该记录锁定，以便游标完成对记录的操作  
optimistic：该参数不会锁定游标；此时，如果记录被读入游标后，对游标进行更新或删除不会超过

**打开游标**

open cursor_name;

游标打开后，可以使用全局变量@@cursor\_rows显示读取记录条数

**检索游标**

fetch cursor_name;

检索方式如下：

fetch first; 读取第一行
    fetch next; 读取下一行
    fetch prior; 读取上一行
    fetch last; 读取最后一行
    fetch absolute n; 读取某一行
    --如果n为正整数，则读取第n条记录
    --如果n为负数，则倒数提取第n条记录
    --如果n为，则不读取任何记录
    
    fetch pelative n
    --如果n为正整数，则读取上次读取记录之后第n条记录
    --如果n为负数，则读取上次读取记录之前第n条记录
    --如果n为，则读取上次读取的记录

**关闭游标**

close cursor_name;

**删除游标**

deallocate cursor_name;

### 3.3 游标操作示例 ###

--创建一个游标
    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;

[ 本文作者 ]   bluetata
    [ 原文链接 ]   https://bluetata.blog.csdn.net/article/details/131039893
    [ 最后更新 ]   06/05/2023 2:46
    [ 版权声明 ]   如果您在非 CSDN 网站内看到这一行，
    说明网络爬虫可能在本人还没有完整发布的时候就抓走了我的文章，
    可能导致内容不完整，请去上述的原文链接查看原文。

[MS-SQL Server]: https://blog.csdn.net/dietime1943/category_6436988.html
[SQL Server_ Linux _ SQL Server]: https://bluetata.blog.csdn.net/article/details/129864499
[SQL Server]: https://bluetata.blog.csdn.net/article/details/129905010
[SQL Server_MSSQL]: https://bluetata.blog.csdn.net/article/details/129928569
[SQL Server_ T-SQL]: https://bluetata.blog.csdn.net/article/details/130040273
[_ _ Docker _ _ SQL Server_Docker]: https://bluetata.blog.csdn.net/article/details/130124814
[SQL Server_T-SQL]: https://bluetata.blog.csdn.net/article/details/130168306
[SQL Server 1]: https://bluetata.blog.csdn.net/article/details/130899849
[SQL Server_MS-SQL]: https://bluetata.blog.csdn.net/article/details/130907754
[SQL Server_ MS-SQL]: https://bluetata.blog.csdn.net/article/details/131039893
[SQL Server_ MS-SQL 1]: https://bluetata.blog.csdn.net/article/details/131093456
[c11c3a9760bc47669bacab53737c16a0.png]: https://img-blog.csdnimg.cn/c11c3a9760bc47669bacab53737c16a0.png
[ec04d24961b145128c1138ad15920f74.png]: https://img-blog.csdnimg.cn/ec04d24961b145128c1138ad15920f74.png