MSSQL基础知识点总结(下篇)

0x00 前言

SQL server是一个关系数据库管理系统。关系数据库将数据存储在不同的表中，而不是将所有数据放在一个大仓库中，从而提高了速度和灵活性。世界上许多知名网站都依赖SQL server来支持他们的关键业务，其中包含Google、Facebook等。现代计算机中存储的数据越来越多，程序设计师利用数据库来实现对数据的存储以及调用，这样大大增加了数据使用的方便性，同时数据更加的清晰明朗，同时在数据库中采用图形化的界面，在使用的时候将会更加方便。

0x01 安全性控制

数据库中通常由多个用户共享，为保证数据库的安全，SQL 语言提供数据控制语句 DCL（Data Control Language）对数据库进行统一的管理。

1.1 SQL安全模式

1.1.1 授权ID

授权 ID 是数据库安全的基础，是 SQL 安全模式的核心。授权 ID 表示被授予特定访问权限的一个用户或者一组用户，通过授权 ID 可以访问 SQL 环境中的所有对象，可以授予授权 ID 权限。

SQL支持两种类型的权限ID：用户ID和角色。

• 用户 ID 是一个个人的安全账户，可以表示个体、应用程序或系统服务。SQL 标准没有规定如何创建用户 ID，因此用户 ID 一般是在 RDBMS 环境中明确地创建。为了保证数据库系统的安全性，大部分数据库管理系统采用提供用户名和口令的方法来标识和鉴别用户 ID。

• 角色是一个定义的权限集，是多种权限的集合。可以将它分配给一个用户，也可以分配给另一个角色。当要为某一用户同时授予或收回多项权限时，可以把这些权限定义为一个角色，对此角色进行操作。这样就避免了许多重复性的工作，简化了操作。

除了用户 ID 和角色外，SQL 还有一个特定的授权 ID：PUBLIC。它是 SQL 内置的授权ID，包括了所有访问数据库的用户。和其他授权 ID 一样，我们也可以将访问权限授予 PUBLIC账户。

1.1.2 SQL的安全对象和权限

在 SQL 系统中，有以下两个安全机制：

• 一种是视图机制，当用户通过视图访问数据库时，不能访问此视图外的数据，它提供了一定的安全性。

• 另一种，也是主要的安全机制，为权限机制。

大多数DBMS产品都支持以下几种安全性权限：

• SELECT：允许用户ID使用SELECT查询数据。

• INSERT：允许用户 ID 利用 INSERT 语句向表中添加新行。

• UPDATE：允许用户 ID 使用 UPDATE 语句更新表中的数据。

• DELETE：允许用户 ID 使用 DELETE 语句删除表中的数据。

• REFERENCES：允许用户 ID 在外键或检查约束中引用表或视图中的列。

• USAGE：允许用户 ID 使用列定义的对象，包括域、用户定义的数据类型、字符集、顺序序列和转译。

• TRIGGER：允许用户 ID 在表上创建触发器。

• EXECUTE：允许用户 ID 调用 SQL 调用的例程。

• EXEC：允许用户 ID 执行存储过程。

• DRI：允许用户 ID 在表上添加 FOREIGN KEY 约束。

• CREATE TABLE：允许用户 ID 向数据库中添加新表。

• ALTER TABLE：允许用户 ID 改变表的结构。

• DROP TABLE：允许用户 ID 删除表。

• BACKUP DATEBASE：允许用户 ID 将整个数据库备份到备份设备上。

• 
  

   BACKUP LOG：允许用户 ID 将数据库事务处理日志备份到其他设备上。

  
  

1.2 SQL Server安全管理

SQL Server 的安全性管理是建立在认证（Authentication）和访问许可（Permission）两者机制上的。认证是指确定登录 SQL Server 的用户登录账号和密码是否正确，以此来验证其是否具有连接 SQL Server 的权限。但是通过认证并不代表能够访问 SQL Server 中的数据，用户只有在获取访问数据库的权限之后，才能够对数据库进行权限许可下的各种操作。针对数据库对象，如表、视图、存储过程等，这种权限的设置是通过用户账号来实现的。

1.2.1 SQL Server登录认证

在SQL Server中登录到服务器的方式有两种：Windows NT认证和SQL Server认证。

• Windows NT 认证利用 NT 系统本身提供的用户安全性和账号管理的机制。SQL Server 也可以使用 NT 的用户名和口令，在该模式下用户只要通过 WINDOWS 的认证就可连接到 SQL Server，而 SQL Server 本身也没有必要管理一套登录数据。

• SQL Server 认证是指用户在连接 SQL Server 时，必须提供登录名和登录密码。这些登录信息存储在系统表 syslogins 中，与 NT 的登录账号无关。

SQL Server 能在两种安全模式下运行：Windows NT 认证模式和混合模式。我们可以使用SQL Server 提供的企业管理器来设置安全模式。

1.2.2 SQL Server用户ID的管理

SQL Server中的用户ID由两个部分组成：登录账号和用户账号。

• 登录账号只表明该账号通过了NT认证或SQL Server认证，允许用户ID与SQL Server连接。

• 用户账号总是基于数据库的，即两个不同数据库中可以有两个相同的用户账号。SQL Server通过用户账号来控制用户ID在数据库上的权限。

通常而言，用户账号总是与某一登录账号相关联，但有一个例外，那就是 guest 用户。在安装系统时，guest 用户被加入到 master、pubs、tempdb 和 northwind 数据库中。它主要是让那些没有账号的 SQL Server 登录者将其作为缺省的用户，从而能够访问具有 guest 用户的数据库。

SQL Server 的 Transact-SQL 中，一些系统过程提供了管理 SQL Server 用户 ID 的主要功能，其中管理登录账户的有关系统过程包括以下几种：

• sp_granlogin：设定一个 WINDOWS NT 用户或用户组为 SQL Server 登录者。

• sp_addlogin：创建新的使用 SQL Server 认证模式的登录账号。

• sp_revokelogin：删除 NT 用户或用户组在 SQL Server 上的登录信息。

• sp_denylogin：拒绝某一 NT 用户或用户组连接到 SQL Server。

• sp_droplogin：从 SQL Server 中删除登录账号。

• sp_helplogins：显示 SQL Server 所有登录者的信息，包括每一个数据库里与该登录者相对应的用户名称。

1.2.3 SQL Server权限管理

在SQL Server中，权限可分为系统权限和对象权限。系统权限由数据库管理员授予其他用户，是指数据库用户能够对数据库系统进行某种特定操作的权力。SQL Server 中主要的系统权限如下：

对象权限由创建基本表、视图的用户授予其他用户，是指用户在指定的数据库对象上进行某种操作的权力。SQL Server中主要的对象权限如下：

1.2.4 SQL Server角色管理

SQL Server中主要由两种角色类型：服务器角色与数据库角色。

服务器角色是指根据 SQL Server 的管理任务以及这些任务相对的重要性等级，把具有SQL Server 管理职能的用户划分成不同的用户组，每一组所具有的权限已被预定义。服务器角色适用于服务器范围内，并且其权限不能被修改。SQL Server 共有 7 种预定义的服务器角色，各种角色的具体含义如下：

SQL Server 提供了两种角色类型：预定义的数据库角色和用户自定义的数据库角色。

• 预定义数据库角色：指这些角色所有具有的管理、访问数据库权限已被 SQL Server定义，并且 SQL Server 管理者不能对其所具有的权限进行任何修改。在 SQL Server中，预定义的数据库角色如下：

• 用户自定义的数据库角色：当我们希望为某些用户设置相同的权限，但是预定义的角色没有这个权限时，就可以定义新的数据库角色来满足这一要求，从而使这些用户能够在数据库中实现某一特定功能。

0x02 完整性控制

2.1 完整性约束简介

完整性约束简称约束，是关系数据库中的对象，用来存放插入到一个表某一列数据的规则。SQL 中有多种不同类型的约束。

2.1.1 数据的完整性

约束是用来确保数据的准确性和一致性。数据的完整性就是对数据的准确性和一致性的一种保证。数据完整性（Data Integrity）是指数据的精确（Accuracy）和可靠性（Reliability）。

数据完整性分为以下四类：

• 实体完整性：规定表的每一行在表中是唯一的实体。

• 域完整性：是指表中的列必须满足某种特定的数据类型或约束，其中约束又包括取值范围、精度等规定。

• 参照完整性：是指两个表的主关键字和外关键字的数据应一致，保证表之间的数据的一致性，防止数据丢失或无意义的数据在数据库中扩散。

• 用户定义的完整性：不同的关系数据库系统根据其应用环境的不同，往往还需要一些特殊的约束条件。用户定义的完整性即针对某个特定关系数据库的约束条件，它反映某一具体应用必须满足的语义要求。

2.1.2 完整性约束的类型

SQL完整性约束可分为3种类型：与表有关的约束、域(Domain)约束、断言(Assertion)。

• 与表有关的约束，是表中定义的一种约束。可以在列定义时定义该约束，此时称为列约束；也可以在表定义时定义该约束，此时称为表约束。

• 域约束是定义在域定义中的一种约束，它与在特定域中定义的任何列都有关系。

• 断言是在断言定义时定义的一种约束，它可以与一个或多个表进行关联。

2.2 与表有关的约束

2.2.1 列约束与表约束的创建

表约束与列定义相互独立，不包括在列定义中，通常对多个列一起进行约束，与列定义用逗号分隔，定义表约束时必须指出要约束的那些列的名称。表约束的基本语法格式如下：

[CONSTRAINT constraint_name ] 
constraint_type (column1,column2,…… );

constraint_name 为约束名，约束不指定名称时，系统会给定一个名称
constraint_ type 为约束类型，可以是 UNIQUE、PRIMARY KEY、FOREIGN KEY、CHECK 等约束。

2.2.2 NOT NULL(非空)约束

NOT NULL 约束只能用于定义列约束，而表约束、域约束和断言都不支持 NOT NULL约束。NOT NULL 约束的实现语法如下：

Column_name datetype | domain NOT NULL;

2.2.3 UNIQUE(唯一)约束

UNIQUE 约束用于指明某一列或多个列的组合上的取值必须唯一。定义了 UNIQUE 约束的那些列称为唯一键，系统自动为它建立唯一索引，从而保证了它的唯一性。唯一键允许为NULL，但系统为保证其唯一性，最多只可以出现一个 NULL 值。UNIQUE 约束既可用于列约束，也可用于表约束。UNIQUE 约束用于定义列约束时，我们可以把它作为列定义的一部分添加进去，语法格式如下：

Column_name datetype | domain UNIQUE;

2.2.4 PRIMARY KEY(主键)约束

PRIMARY KEY 约束用于定义基本表的主键，起唯一标识作用，其值不能为 NULL，也不能重复，以此来保证实体的完整性。PRIMARY KEY 与 UNIQUE 约束类似，通过建立唯一索引来保证基本表在主键列取值的唯一性，但它们之间存在着很大的区别。

• 一个表中只能定义一个 PRIMARY KEY 约束，但可定义多个 UNIQUE 约束。

• 对于指定为 PRIMARY KEY 的一个列或多个列的组合，其中任何一个列都不能出现空值；而对于 UNIQUE 所约束的唯一键，则允许为 NULL，只是 NULL 值最多有一个。

注：不能为同一个列或一组列既定义UNIQUE约束，又定义PRIMARY KEY约束。

2.2.5 FOREIGN KEY(外键)约束

FOREIGN KEY 约束指定某一个列或一组列作为外部键，其中，包含外部键的表称为子表，包含外部键所引用的主键的表称为父表。系统保证表在外部键上的取值要么是父表中某一个主键，要么取空值，以此保证两个表之间的连接，确保了实体的参照完整性。FOREIGN KEY 约束既可用于列约束，也可用于表约束。FOREIGN KEY 约束用于定义列约束时，我们可以把它作为列定义的一部分添加进去。语法如下：

Column_name datetype | domain REFERENCES table_name (column) 
[MATCH FULL|PARTIAL|SIMPLE] 
[referential triggered action];

Column_name 为添加 FOREIGN KEY 约束的列
REFERENCES 关键字后面的 table_name 为父表的名字 
column 则为父表中与外键对应的主键值
MATCH FULL|PARTIAL|SIMPLE 子句为可选子句，用于设置如何处理外键中的 NULL值
referential triggered action 为可选子句，用于设置更新、删除外键列时的操作准则

2.2.6 CHECK(校验)约束

CHECK 约束用来检查字段值所允许的范围。每个 CHECK 约束由关键字 CHECK 后跟搜索条件组成。DBMS 每当执行 DELETE、INSERT 或 UPDATE 语句改变表的内容时，都对这些搜索条件求值，如果修改后，搜索条件为 True，则 DBMS 允许修改；如果结果为 False，则系统取消操作并给出错误报告。CHECK 约束是所有约束中最灵活的约束。若要创建列约束，我们应在列定义时使用下列创建语法：

Column_name datetype | domain CHECK (search condition);

若要创建表约束，我们应在表定义时使用下列创建语法:

CONSTRAINT constraint name CHECK (search condition);

search condition 为 CHECK 约束的搜索条件,可以是基于比较运算符简单表达式，也可以是复杂的子查询

2.3 深入探讨外键与完整性检查

FOREIGN KEY 约束允许建立两个表的父子关系，关系数据规定子表中的每个 FOREIGN KEY 值必须作为主键值存在于父表中，该准则为引用完整性约束。

2.3.1 引用完整性检查

保持一个表（子表）中的 FOREIGN KEY 列与另一表（父表）的 PRIMARY KEY 列（或UNIQUE 列）的引用完整性，主要涉及以下 4 种可能破坏其引用完整性的检查：

• 向子表中插入行。

• 从父表中删除行。

• 更新子表中的FOREIGN KEY值。

• 更新父表中的参考列值。

2.3.2 更新、删除操作规则

FOREIGN KEY 约束的创建语法中提供了可选的 ON UPDATE 和 ON DELETE 子句，也就是referential triggered action 子句，通过该子句可以定义在删除和更新操作时如何保持引用的完整性。ON UPDATE 和 ON DELETE 子句可表示如下：

ON UPDATE 
NO ACTION|CASCADE|RESTRICT|SET NULL|SET DEFAULT 
ON DELETE 
NO ACTION|CASCADE|RESTRICT|SET NULL|SET DEFAULT

在应用中，我们可以按照任何次序定义这两个子句，对于每个子句都可以选择如下 5 个参数之一。

• NO ACTION：更新或删除父表中的数据时如果会使子表中的外键违反引用完整性，该动作将被禁止执行。不过在某些条件下，系统允许出现暂时的违反；但在数据的最终状态中，不能违反外键的引用完整性。

• CASCADE：当父表中被引用列的数据被更新或删除时，子表中相应的数据也被更新或删除。

• RESTRICT：与 NO ACTION 规则基本相同，只是引用列中的数据不能违反外键的引用完整性，即使暂时也不行。

• SET NULL：当父表数据被更新或删除时，子表中的相应数据要设置为 NULL 值，当然，其前提是子表中的相应列允许 NULL 值。

• SET DEFAULT：当父表的数据被更新或删除时，子表中的数据被设置为默认值，当然，前提是子表中的相应列设置有默认值。

2.4 域约束与断言

2.4.1 域与域约束

域是列中合法数据值的集合。它是一种用户定义的对象，在定义列时，我们可以指定它代替数据类型。域基于 SQL 数据类型，但它可以包括默认值和约束，通过约束进一步限制可以存储在列中的值。域中的约束只能是 CHECK 约束。创建域及 CHECK 约束的语法如下：

CREATE DOMAIN domain_name AS data type 
[DEFAULT default_value] 
[CONSTRAINT constraint_name] CHIECK (VALUE condition expression);

注：SQL Server 中不支持 CREATE DOMAIN 语句，但在 SQL Server 中可以创建用户自己的数据类型。

2.4.2 利用断言创建多表约束

与域相似，断言也是一种数据库对象，但它不必与特定的列绑定，可以理解为能应用于多个表的 CHECK 约束，因此必须在表定义之外独立地创建断言。语法如下：

CREATE ASSERTION constraint name 
CHECK search condition;

创建断言与创建表 CHECK 约束非常相似，在 CHECK 关键字之后必须提供搜索条件。

2.5 SQL Server中的完整性控制

2.5.1 创建规则(Rule)

规则（Rule）就是对存储在表的列或自定义数据类型中的值的规定和限制。规则是单独存储的、独立的数据库对象，它与其作用的表或用户自定义数据类型是相互独立的，即表或用户自定义对象的删除修改不会对规则产生影响。SQL Server在进行CHECK约束检查时使用规则。在SQL Server中，规则的创建可使用CREATE RULE语句或者企业管理器。

2.5.1.1 使用CREATE RULE语句创建规则

CREATE RULE 命令用于在当前数据库中创建规则。语法如下：

CREATE RULE rule_name AS condition_expression;

注：condition_expression子句中，表达式必须以"@"开头。

2.5.1.2 使用企业管理器创建规则

在企业管理器中，选择“规则（Rules）”，单击右键，从快捷菜单中选择“新建规则（New Rule）”选项，会弹出“规则属性”对话框。输入规则名称和表达式之后，单击“确定”按钮即完成规则的创建。

2.5.2 规则的绑定与松绑

创建规则后，规则只是一个存在于数据库中的对象，并未发生作用。因此我们必须先将规则与其作用的对象绑定在一起。表的一列或一个用户自定义数据类型只能与一个规则相绑定，而一个规则可以绑定多个对象。解除规则的绑定称为“松绑”。规则的绑定与松绑操作可以通过 SQL Server 提供的存储过程进行，也可以通过企业管理器进行。

2.5.2.1 用存储过程Sp_bindrule绑定规则

存储过程 Sp_bindrule 可以绑定一个规则到指定对象上。语法如下：

sp_bindrule [@rulename =] 'rule', 
[@objname =] 'object_name' 
[, 'futureonly'];

“[@rulename =] 'rule'”指定了规则的名称
“[@objname =] 'object_ name'”指定规则绑定的对象
“'futureonly'”选项仅在绑定规则到用户自定义数据类型上时才可以使用，

2.5.2.2 使用企业管理器绑定规则

在企业管理器中选择要绑定的规则“Wage_Rule”，单击右键，从快捷菜单中选择“属性”选项，即会出现“规则属性”对话框。“绑定 UDT…”按钮用于绑定用户自定义数据类型，“绑定列…”按钮用于绑定表的列。单击“绑定列…”按钮，出现“将规则绑定到列”对话框。在“表”下拉列表中，选中需要绑定规则的表“Emp_Sal”，在列出的表中选择要绑定规则的列“SALARY”，单击“添加”按钮即实现了列与规则的绑定。同样，如果要实现规则的“松绑”，我们只需要在右侧窗口中选中该列，单击“删除”按钮即可。

2.5.3 创建缺省值(Default)

缺省值（Default）对象是指当用户没有指定具体数据时，向相应的列中自动插入的数据。缺省值对象可以用于多个列或用户自定义数据类型，与规则有许多相似之处。表的一列或一个用户自定义数据类型也只能与一个缺省值相绑定。在 SQL Server 中，缺省值的创建可使用 CREATE DEFAULT 语句或者企业管理器。

2.5.3.1 使用CREATE DEFAULT语句创建缺省值

CREATE DEFAULT 命令用于在当前数据库中创建缺省值对象。语法如下：

CREATE DEFAULT default_name AS constant_expression;

constant_expression 子句是缺省值的定义，constant_expression 子句可以是数学表达式或者函数，其中也可以包含表的列名或其他数据库对象。

2.5.3.2 使用企业管理器创建缺省值

在企业管理器中选择数据库对象“默认值（Defaults）”，单击右键，在快捷菜单中选择“新建默认（New Default）”选项，会弹出“默认属性”对话框。输入缺省值名称和值表达式，单击"确认"按钮即完成缺省值对象的创建。

2.5.4 缺省值的绑定与松绑

缺省值的绑定与松绑操作可以通过 SQL Server 提供的存储过程进行，也可以通过企业管理器进行。

2.5.4.1 用存储过程Sp_bindefault绑定缺省值

存储过程 Sp_bindefault 可以绑定一个缺省值到表的一列或一个用户自定义数据类型上。语法如下：

Sp_bindefault [@defname =] 'default', 
[@objname =] 'object_name' 
[, 'futureonly'];

2.5.4.2 使用企业管理器绑定缺省值对象

在企业管理器中，选择要进行绑定设置的缺省值“Phone_Com”，单击右键，在快捷菜单中选择”属性”选项，即出现“默认属性”对话框。“绑定 UDT…”按钮用于绑定用户自定义数据类型，“绑定列…”按钮用于绑定表的列。单击“绑定列…”按钮，则出现“将默认值绑定到列”对话框。定规则的列“PHONE”，单击“添加”按钮即实现了列与规则的绑定。同样，如果要实现规则的“松绑”，我们只需要在右侧窗口中选中绑定缺省值的列，单击“删除”按钮即可。

0x03 存储过程与函数

3.1 SQL中的存储过程 

存储过程是由SQL语句和控制流语句构成的语句串(语句集合)。它不仅可以带有输入参数，而且可以带有输出参数，存储过程能够通过接收参数向调用者返回结果集，结果集的格式由调用者确定；返回状态值给调用者，指明调用是成功或是失败，包括针对数据库的操作语句，并且可以在一个存储过程中调用另一存储过程。相比交互式的SQL语句，存储过程主要具有如下优点：

• 存储过程允许组件式编程。

• 存储过程能够实现较快的执行速度。

• 存储过程能够减少网络流量。

3.2 SQL Server中的流控制语句

3.2.1 BEGIN···END语句

该语句用来定义一串由顺序执行的SQL语句构成的块。语法规则如下：

BEGIN 
 Statement block 
END;

3.2.2 IF···ELSE语句

该语句用来定义有条件执行的某些语句，其中ELSE子句是可选的，语法规则如下：

IF boolean_expression 
 statement 
[ELSE [IF boolean_expression] statentent];

3.2.3 WHILE、BREAK和CONTINUE语句

WHILE 语句是用来表示当某条件满足时，重复执行某语句或语句块的情况。语法如下：

WHILE boolean_expression 
 Statement;

3.2.4 DECLARE语句

DECLARE 语句用来定义一个局部变量，可用 SELECT 语句为该变量赋初值。这个变量必须用@开头，跟着一个标识符。语法如下：

DECLARE @variable_name datatype 
 [，@variable_name datatype] …;
 
 变量名 variable_name 是一个合法标识符，datatype 可以是一个系统数据类型，也可以是一个用户定义的数据类型。

用SELECT语句给局部变量赋初值的语法格式如下：

SELECT @variable_name = 
{expression | (SELECT_statement)} 
[，@variable ＝{expression | (SELECT_statement)} …] 
[FROM clause] 
[WHERE clause];

注：局部变量的定义和使用必须在同一个存储过程中。

3.2.5 GOTO label语句

GOTO label语句用来无条件地将语句的执行顺序转到用户定义的标号(label)处，语法如下：

label:
GOTO label;

3.2.6 RETURN语句

RETURN 语句用来无条件地退出一个查询或一个过程，语法如下：

RETURN [int_expression];

3.2.7 WAITFOR语句

WAITFOR 语句用来定义某天中的一个时刻，执行一个语句块。语法如下：

WAITFOR { DELAY ‘time’ | TIME ‘time’};

3.2.8 PRINT语句

PRINT 语句用来在屏幕上显示最长为 255 个字符的信息，或类型为 char、varchar 的局部变量或全局变量的内容。语法如下：

 PRINT{ string_expr | @local_variable |@global_variable };

3.2.9 注释

可以对某些SQL语句及存储过程做注释，注释的长度没有限制。语法如下：

/* text_of_comment */

text_of_comment 为具体的注释内容。对于多行注释，第—行以“/*”开头，其余行以“**”开头，最终以“*/”结尾。

3.3 SQL Server中的存储过程和函数

在 SQL Server 中，存储过程分为两类：系统提供的存储过程和用户自定义存储过程。

3.3.1 系统存储过程

系统存储过程能够方便地从系统表中查询信息或完成其他的系统管理任务。系统存储过程主要包括以下几类：

• 目录存储过程：主要有 sp_column_privileges、sp_special_columns、sp_columns、sp_sproc_columns、sp_databases、sp_statistics、sp_fkeys、sp_stored_procedures 等。

• 复制类存储过程：主要有 sp_addarticle、sp_adddistpublisher、sp_adddistributiondb、sp_adddistributor、sp_addpublication、sp_help_agent_profile、sp_addpublication_snapshot、sp_help_publication_access、sp_addpublisher70、sp_helparticle 等。

• 安全管理类存储过程：主要有 sp_addalias、sp_droprole、sp_addapprole、sp_droprolemember、sp_addgroup、sp_dropServer、sp_addlinkedsrvlogin、sp_dropsrvrolemember、sp_addlogin、sp_dropuser 等。

• 分布式查询存储过程：主要有 sp_addlinkedServer、sp_indexes、sp_addlinkedsrvlogin、sp_linkedServers、sp_catalogs、sp_primarykeys 等。

3.3.2 使用CREATE PROCEDURE创建存储过程

在 SQL Server 中，我们可以使用 CREATE PROCEDURE 语句创建、编译存储过程。在默认情况下，只有数据库的拥有者具有 CREATE PROCEDURE 权限。我们可通过 GRANT 语句将 CREATE PROCEDURE 权限授予其他用户。

GRANT CREATE PROCEDURE username

语法如下：

CREATE PROCEDURE procedure_name [ ; version number ] 
 [ { @parameter data_type } 
 [ VARYING ] [ = default value ] [ OUTPUT ] 
 ] [ ,...n ] 
[WITH 
 { RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION } ] 
[FOR REPLICATION ] 
AS sql_statement [ ...n ];

procedure_name 是存储过程的名称，最多可有 128 个字符。
version number 为版本号。
@parameter 是存储过程的参数名。
Data_type 是参数的数据类型。
VARYING 指定由 OUTPUT 参数支持的结果集，仅应用于游标型参数。
default value 是指参数的缺省值。
OUTPUT表明该参数是一个返回参数。
RECOMPILE 指明 SQL Server 并不保存该存储过程的执行计划，该存储过程每执行一次都要重新编译。
ENCRYPTION 为 SYSCOMMENTS 表中存储过程条目加密，防止用户查看编译后的语句。
FOR REPLICATION选项指明了该存储过程只能在复制过程中执行。
sql_statement 是包含在存储过程中的任何数量和类型的 SQL 语句。

注：在命名存储过程时，应尽量避免使用“sp_”前缀。因为在接收到以“sp__”开头的存储过程命令后，SQL Server 首先会在 Master 数据库中寻找存储过程，这会影响存储过程的执行效率。

3.3.3 使用EXECUTE语句调用存储过程

要执行已创建的存储过程，可以使用EXECUTE命令。语法如下：

[[EXEC [UTE]] 
 {[@return_status =] 
 {procedure_name [ ;number ]|@procedure_name_var} 
 [[@parameter=] {value|@variable[OUTPUT ]|[DEFAULT]] 
 [,...n] 
[WITH RECOMPILE];

@return_status 是可选的整型变量，用来存储返回的值。
@procedure_name_var 是一变量名，用来代表存储过程的名字。

3.3.4 使用CREATE FUNCTION创建函数

在 SQL Server 中，根据函数返回值形式的不同，用户自定义函数分为 3 种类型：

• 标量型函数（Scalar Functions）：返回一个确定类型的标量值。

• 内联表值型函数（Inline Table-valued Functions）：以表的形式返回一个返回值，即返回的是一个表，相当于一个参数化的视图。

• 多声明表值型函数（Multi-statement Table-valued Functions）：可以看作标量型和内联表值型函数的结合体。返回值是一个表，但它和标量型函数一样，有一个 BEGIN-END 语句括起来的函数体，该函数体包含插入语句，向返回表中插入数据。

3.3.4.1 创建标量型函数

语法如下：

CREATE FUNCTION [owner_name.] function_name 
 ([{@parameter_name[AS]scalar_parameter_data_type[=default]} [,...n]]) 
RETURNS scalar_return_data_type 
[WITH {ENCRYPTION|SCHEMABINDING}] 
[AS] 
BEGIN 
 function_body 
 RETURN scalar_expression 
END;

owner_name：创建函数的用户名，该用户名必须是已有的用户 ID。
function_name：用户自定义函数的名称。
@parameter_name：自定义函数的参数名，参数的作用范围是整个函数。
scalar_parameter_data_type：标量型参数的数据类型。参数可以是任何有效的 SQL 标量数据类型，不能是自定义的数据类型、表或游标。
scalar_return_data_type：返回值的数据类型，可以是任何有效的 SQL 标量数据类型，不能是用户自定义的数据类型、表或游标。
ENCRYPTION：加密选项，对 CREATE FUNCTION 的声明加密，防止用户自定义函数被发布。
SCHEMABINDING：计划绑定选项，将自定义函数绑定到它所引用的数据库对象。
function_body：指定一系列的 Transact-SQL 语句，它们决定了函数的返回值。
scalar_expression：指定自定义函数返回的标量值表达式。

3.3.4.2 创建内联表值型用户自定义函数

语法如下：

CREATE FUNCTION [ owner_name. ] function_name 
 ([{@parameter_name[AS]scalar_parameter_data_type[=default]} [,...n]]) 
RETURNS TABLE 
[WITH {ENCRYPTION|SCHEMABINDING}] 
 [AS] 
RETURN [()select-stmt[]];

TABLE：指定返回值为一个表。
select-stmt：单个 SELECT 语句，确定返回表的数据。

3.3.4.3 创建多声明表值型用户自定义函数

语法如下：

CREATE FUNCTION [owner_name.] function_name 
 ([{@parameter_name[AS]scalar_parameter_data_type[=default]} [,...n]]) 
RETURNS@return_variable TABLE({column_definition|table_constraint} 
 [,...n]) 
[WITH {ENCRYPTION|SCHEMABINDING}] 
 [AS] 
BEGIN 
 function_body 
 RETURN 
END;

3.3.5 修改和删除存储过程和函数

创建存储过程以后，它的名字存储在系统表sysobjects中，源代码存放在系统表syscomments 中，可以通过系统存储过程查看关于用户创建的存储过程信息。

3.3.5.1 通过系统存储过程sp_helptext查看存储过程的源代码

语法如下：

sp_helptext sp_name

sp_name为存储过程的名称。

注：如果在创建存储过程时，我们使用了 WITH ENCRYPTION 选项，那么无论是使用企业管理器还是系统存储过程 sp_helptext，都无法查看到存储过程的源代码。

3.3.5.2 使用sp_rename重命名存储过程

修改存储过程的名字，可使用系统存储过程 sp_rename。语法如下：

sp_rename old_name,new_name

old_name为原存储过程的名字
new_name为新存储过程的名字

3.3.5.3 使用DROP命令删除存储过程

使用 DROP 命令可以删除存储过程，DROP 命令可将一个或多个存储过程从当前数据库中删除。语法如下。 

DROP PROCEDURE {procedure} [,…n];

0x04 SQL触发器 

4.1 触发器的基本概念

触发器是一种特殊的存储过程，它在表的数据变化时发生作用。触发器可以维护数据完整性。

4.1.1 触发器简介

触发器在数据库里以独立的对象存储。与存储过程不同的是，存储过程通过其他程序来启动运行，而触发器是由一个事件来启动运行。当某个事件发生时，触发器自动地隐式运行，并且，它不能接收参数。触发器对象定义了触发器的特征和被调用时采取的行动。而这些动作是通过一个或多个SQL 语句来实现的。SQL 支持 3 种类型的触发器：INSERT（插入）、UPDATE（更新）和 DELETE（删除）。当向表中插入数据、更新数据或删除数据时，触发器就被调用。通过给表定义一个或多个触发器，我们可以指定哪个数据修改时，可以激发触发器。

4.1.2 触发器执行环境

触发器执行环境包含了触发器正确执行所必需的信息。这些信息主要是触发器本身的细节和触发器所定义的目标表。另外，触发器执行环境还包括一个或两个测试表，我们称之为INSERTED 表和 DELETED 表。测试表是虚表，用于保存目标表更新、插入或删除的数据信息。这些测试表用来测试数据修改的结果以及设置触发器行动的条件。用户不能直接修改测试表中的数据，但能在 SELECT 语句中，使用这些表来检测 INSERT、UPDATE 或 DELETE的结果。各种类型触发器用到的测试表如下：

• deleted 表存放了 DELETE 和 UPDATE 语句中相关行的副本。在 DELETE 或 UPDATE语句的执行中，这些相关行从 trigger 表中移到了 deleted 表中。一般情况下，这两张表中无共同行。

• 
  

   inserted 表存放了 INSERT 和 UPDATE 语句中的副本。在 INSERT 或 UPDATE 语句的执行中，这些新行同时被加到 inserted 表和 trigger 表中。inserted 表中的行是 trigger表中新行的副本。

  
  

• 一个 UPDATE 效果上等价于一个 DELETE 再接着一个 INSERT，首先“旧”行被复制到 deleted 表中，然后新行被复制到 trigger 表和 inserted 表中。

4.2 SQL Server中的触发器

4.2.1 SQL Server触发器的种类

SQL Server支持两种类型的触发器：AFTER触发器和INSTEAD OF触发器，其中AFTER触发器即为UPDATE、INSERT、DELETE触发器。INSTEAD OF 触发器表示并不执行其定义的操作（INSERT、UPDATE、DELETE），而只是执行触发器本身。我们既可在表上定义 INSTEAD OF 触发器，也可以在视图上定 INSTEAD OF 触发器，但对同一操作只能定义一个 INSTEAD OF 触发器。

4.2.2 使用CREATE TRIGGER命令创建触发器

在SQL Server 中，我们可以采用 CREATE TRIGGER 命令创建触发器。语法如下：

CREATE TRIGGER trigger_name 
ON { table | view } 
[ WITH ENCRYPTION ] 
{ 
 {{ FOR|AFTER|INSTEAD OF} {[DELETE] [,] [INSERT] [,] [UPDATE]} 
 [NOT FOR REPLICATION] 
 AS 
 [{IF UPDATE (column) 
 [{AND|OR } UPDATE (column)] 
 [...n ] 
 |IF ( COLUMNS_UPDATED ( ) { bitwise_operator } updated_bitmask ) 
 { comparison_operator } column_bitmask [ ...n ] 
 }] 
 sql_statement [ ...n ] 
 } 
}; 

- trigger_name：为用户要创建的触发器的名字，它必须符合 SQL Server 的命名规则，并且名字在当前数据库中必须是唯一的。
- Table、view：与触发器相关联的表或视图的名字，并且该表或视图必须已经在数据库中存在。
- WITH ENCRYPTION：表示对含有 CREATE TRIGGER 文本的 syscomments 表进行加密，防止用户通过查询 syscomments 表获取触发器的代码。
- AFTER：表示只有执行了指定的操作（INSERT、DELETE、或 UPDATE）之后，触发器才被激活，执行触发器中的 SQL 语句。
- FOR：表示为 AFTER 触发器，并且该触发器仅能在表上创建。
- INSTEAD OF：指定触发器为 INSTEAD OF 触发器。
- DELETE、INSERT、UPDATE：指明执行哪种操作将激活触发器，至少要包含 3 种操作类型种的一种，也可以是 3 种操作语句的任意组合，其中三者的顺序不受限制，各选项要用逗号隔开。
- NOT FOR REPLICATION：告诉 DBMS，当复制表时触发器不能被执行。
- AS：后面列出触发器将要执行的动作。
- IF UPDATE column：用来测定对某一确定列是 INSERT 操作还是 UPDATE 操作。如果要测试是 INSERT 还是 UPDATE 操作的列多于一列，可用 AND 或 OR 逻辑连接向 IF UPDATE 子句添加所希望的附加列名。
- IF COLUMNS_UPDATED( )：仅在 INSERT 和 UPDATE 类型的触发器中使用，检查列是被更新还是被插入。
- bitwise_operator：代表位逻辑运算符，常用“&”。
- updated_bitmask：表示列的整位掩码，其中最右边的位表示表或视图的第 1 列，左边第 2 位代表第 2 列，依此类推。
- comparison_operator：表示比较操作符，可以是“=”或者“>”。“=”表示检查在updated_bitmask 中定义的所有列是否都被更新，“>”表示检查是否在 updated_bitmask中定义的某些列被更新。
- column_bitmask：该字段是要检查的列的整型位掩码，用来检查是否已更新或插入了这些列。
- sql_statement：代表包含在触发器中的处理语句。

当不在需要触发器时，我们可用DROP TRIGGER语句删除触发器。语法如下：

DROP TRIGGER trigger_name [ ...n ];

4.2.3 嵌套触发器

当某一个触发器执行时，能够触发另外一个触发器，这种情况称之为触发器嵌套。在执行过程中，如果一个触发器修改某个表，而这个表已经有其他触发器，这是就使用了嵌套触发器。在SQL Server中，触发器能够嵌套至32层。如果不需要嵌套触发器，我们可以通过sp_configure选项来进行设置。

4.2.4 递归触发器

递归可以分为两种：间接递归和直接递归。我们举例解释如下，假如有表 Table1、表 Table2， 在 Table11、Table12 上分别有触发器 Trigger1、Trigger2。

• 间接递归：对 Table1 操作触发 Trigger1，Trigger1 对 Table12 操作从而触发 Trigger2，Trigger2 对 Table1 操作从而再次触发 Trigger1，……。

• 直接递归：对 Table1 操作从而触发 Trigger1，Trigger1 对 Table1 操作从而再次触发Trigger1，……。

在默认情况下，SQL Server 是禁止直接递归的。要使 SQL Server 支持直接递归，我们可采用如下两种方法进行设置。

4.2.4.1 通过系统存储过程sp_dboption进行设置

语法如下：

sp_dboption 'dbName', 'Recursive Triggers', TRUE;

dbName为数据库名

4.2.4.2 通过Enterprise Manager进行设置

启动企业管理器 r 后，右击要设置的数据库，在弹出菜单中，选择“属性”命令，在弹出的“属性”对话框中，选择“选项”标签页，选中“递归触发器”选项，单击“确定”按钮即可。

4.2.5 SQL Server中触发器的管理

在SQL Server中，可以通过企业管理器以及通过系统存储过程 sp_help、sp_helptext 和sp_depends的方法查看触发器信息。

4.2.5.1 通过企业管理器方法

通过企业管理器方法非常简单。启动企业管理器，在要查询的表上单击右键，在弹出菜单中选择“所有任务”→“管理触发器”，弹出对话框，在“名称”下拉菜单中列出了基于该表的所有触发器，选择查询的触发器，在“文本”窗口中就会显示该触发器定义的详细代码。

4.2.5.2 通过系统存储过程方法

• Sp_help

  使用sp_help命令可以查询触发器的基本信息，如触发器的名字、属性、类型、创建时间等。

• sp_helptext

使用sp_helptext命令能够查看触发器的正文信息，即触发器的创建语句。

• Sp_depends

通过sp_depends命令能够查看指定触发器所引用的表，或指定的表涉及到的所有触发器。

0x05 SQL中游标的使用

5.1 SQL游标的基本概念

在数据库中，游标是一个十分重要的概念。游标提供了一种灵活手段，可以对表中检索出的数据进行操作。就本质而言，游标实际上是一种能从包括多条数据记录的结果集中每次提取一条记录的机制。

5.1.1 游标的概念

游标由结果集（可以是零条、一条或由相关的 SELECT 语句检索出的多条记录）和结果集中指向特定记录的游标位置组成，游标起到指针的作用。尽管游标能够遍历查询结果中的所有行，但它一次只指向一行。概括来讲，SQL 的游标是一种临时的数据库对象，既可以用来存放储存在数据库表中的数据行的副本，也指向存储在数据库表中的数据行的指针。游标提供了在逐行的基础上操作表中数据的方法。

5.1.2 游标的作用及其应用

我们使用数据游标可以选择一组数据，可以在记录集上滚动游标，并检查游标指向的每一行数据；可以用局部变量和游标的组合来分别检查每个记录，并且在转移到下一个记录之前，进行所需的任何外部操作。游标的另一个常见用途就是保存查询的结果，以便以后使用。游标的结果集由 SELECT语句产生，如果处理过程需要重复使用一个记录集，那么创建一次游标而重复使用若干次，比重复查询数据库要快得多。SQL 中游标的使用通常有以下几个步骤。

• 创建游标，也称为声明游标：定义游标的名称、特性以及可调用的查询表达式。

• 打开游标，调用查询表达式，以便在过程或应用中使用它。

• 每次检出（FETCH）数据记录中的一行，直到记录集中的最后一行。

• 完成之后关闭游标并清除分配给游标的内存。

注：我们必须先声明游标后才能打开，通常在主语言定义的循环结构中根据需要来使用FETCH语句，检索到所需的数据后应该关闭游标。

5.2 SQL游标的使用

5.2.1 使用DECLARE CURSOR语句创建游标

在SQL Server数据库系统中，创建游标的语法可表示如下：

DECLARE cursor_name [INSENSITIVE] [SCROLL] CURSOR 
FOR select_statement 
[FOR {READ ONLY | UPDATE [OF column_name [,...n]]}];

- cursor_name：为游标的名字。
- INSENSITIVE：告诉 DBMS 制作查询结果集数据的临时副本，而不是使用指针指向数据库表中真实数据行中的列。
- SCROLL：表明所有的提取操作，即 FETCH 选项，如 FIRST、LAST、PRIOR、NEXT、RELATIVE 和 ABSOLUTE 都可用。
- select_statement：定义结果集的 SELECT 语句。
- READ ONLY：防止正在使用游标的用户或应用程序通过更新数据或删除行来改变游标的内容。因此，我们不能用 READ ONLY 游标修改游标的底层表中的数据。在缺省状态下，游标是允许更新的。
- UPDATE [OF column_name [,...n]]：定义在游标中可被修改的列。

5.2.2 使用OPEN/CLOSE语句打开/关闭游标

打开游标的过程非常简单，我们只需使用关键字 OPEN 和游标的名字。打开游标的语法可表示如下：

OPEN cursor_name;

5.2.3 使用FETCH语句检索数据

FETCH 语句用于找出数据集中的单行并从中提取单个值传给主变量，语法如下：

FETCH [ [fetch_orientation]FROM] 
cursor_name INTO host_variables;

cursor_name 为游标的名称
host_variables 为接收游标检索行值的主变量
fetch_orientation 和 FROM 关键字为可选项,fetch_orientation 为游标的定位选项，当FETCH 语句中有定位选项时，必须加上 FROM 关键字。

SQL 中，游标有 6 种定位选项，用来确定从游标的查询结果中选择哪一行，我们将这些选项列举如下:

• NEXT：返回结果集中当前行的下一行。

• PRIOR：返回结果集中当前行的前一行。

• FIRST：返回游标中第一行。

• LAST：返回游标中的最后一行。

• ABSOLUTE n：移动到结果集中的第 n 行。

• RELATIVE n：从游标指针的当前位置移动 n 行。

注：仅当使用 SCROLL 游标时，才能使用 FETCH 语句的行定位选项（除了 NEXT 以外）。

5.2.4 基于游标的定位DELETE语句

基于游标的 DELETE 语句称为定位 DELETE 语句，因为它告诉 DBMS 从基于游标行指针的当前位置的表中删除一行。语法如下：

DELETE FROM table_name 
WHERE CURRENT OF cursor_name;

table_name 为要删除记录的表名
cursor_name 为游标名

注：如果游标的 SELECT 子句中包含有 ORDER BY 子句，DBMS 将把游标限制为 READ ONLY，因此不能在相同的游标 SELECT 语句中同时有 ORDER BY 子句和 FOR UPDATE语句。也就是说，只要游标的 SELECT 子句中包含有 ORDER BY 子句，那么该游标就是不可更新的，即不能使用定位 DELETE 语句或者定位 UPDATE 语句对其源表进行删除或更新操作。

5.2.5 基于游标的定位UPDATE语句

在标准的 UPDATE 语句中，WHERE 子句用于搜索基于一列或多列要修改值的行；而在定位 UPDATE 语句的 WHERE 子句中，搜索条件是基于游标的当前行指针的位置，而不是搜索基于一列或多列要修改值的行。语法如下：

UPDATE table_name 
SET column_name1 = value1} 
[, ……,column_name n = value n}
WHERE CURRENT OF cursor_name;

table_name 为要更新的表名
cursor_name 为游标名
column_name 为UPDATE 的列名
WHERE 子句必须与 CURRENT OF 选项一起使用，用来确定打开的游标

5.3 SQL Server中游标的扩展

5.3.1 Transact_SQL扩展DECLARE CURSOR语法

SQL Server 扩展了 Transact_SQL 语法创建游标，通过增加另外的保留字，使游标的功能

得到了进一步增强。语法如下:

DECLARE cursor_name CURSOR 
[LOCAL | GLOBAL] 
[FORWARD_ONLY | SCROLL] 
[STATIC | KEYSET | DYNAMIC | FAST_FORWARD] 
[READ_ONLY | SCROLL_LOCKS | OPTIMISTIC] 
[TYPE_WARNING] 
FOR select_statement 
[FOR UPDATE [OF column_name [,...n]]];

- LOCAL：定义游标的作用域仅限于其所在的存储过程、触发器或批处理中。
- GLOBAL：定义游标的作用域是整个会话层。
- FORWARD_ONLY：该选项指明从游标中提取数据记录时，只能按照从第一行到最后一行的顺序，此时，只能选用 FETCH NEXT 定位游标操作.
- STATIC：该选项的含义与 INSENSITIVE 选项一样，SQL Server 会将游标定义所选取出来的数据记录，存放在一个临时表内（建立在 tempdb 数据库下）。
- KEYSET：当游标被打开时，游标中列的顺序是固定的，并且 SQL Server 会在 tempdb内建立一个名为 KEYSET 的表，该表的键值可唯一识别游标中的某行数据。
- DYNAMIC：指明源表的变化将反映到游标中，即游标能够反映出 UPDATE、DELETE 和 INSERT 语句对源表所做的任何修改。
- FAST_FORWARD：指明一个 FORWARD_ONLY、READ_ONLY 型游标，并且此选项为执行进行了优化。
- SCROLL_LOCKS：当服务器向游标添加行时，在源表的每行上放置一个锁定。
- OPTIMISTIC：当数据被读入游标后，如果游标中的某行数据发生了变化，那么对游标的任何定位 UPDATE 或定位 DELETE 操作都将失败。
- TYPE_WARNING：指明若游标类型被修改成与用户定义的类型不同时，DBMS 将发送一个警告信息给客户端。

5.3.2 @@CURSOR_ROWS全局变量确定游标的行数

在 SQL Server 中，当我们使用 OPEN 语句打开游标后，@@CURSOR_ROWS 全局变量将用来记录游标内数据行数。@@CURSOR_ROWS 为全局系统变量，其返回值共有 4 个，含义如下：

5.3.3 @@FETCH_STATUS全局变量检测FETCH操作的状态

SQL Server 中提供了全局变量@@FETCH_STATUS，用于检测最近执行的 FETCH 语句的执行状态。在每次用 FETCH 语句从游标中读取数据时，我们都应检查该变量，以确定上次 FETCH操作是否成功，从而决定如何进行下一步处理。@@FETCH_STATUS 变量有 3 个不同的返回值，如下：

5.3.4 游标的关闭与释放

在 SQL Server 中，要释放游标占用的数据结构需要使用 DEALLOCATE 命令。DEALLOCATE 语句不仅删除游标中的数据，而且将游标作为对象从数据库中删除。当使用DEALLOCATE 语句释放游标后，我们就不能通过 OPEN 语句再次打开游标，因为游标对象已经被删除，因此需要使用 DECLARE CUSOR 语句重新创建游标。DEALLOCATE 命令的语法如下：

DEALLOCATE cusor_name;

要使事务结束后不关闭游标，我们可以使用 SET 命令将 CURSOR_CLOSE_ON_ COMMIT这一参数设置为 OFF 状态，其目的就是让游标在事务结束时，仍保持打开状态。SET 命令的格式为如下:

SET CURSOR_CLOSE_ON_COMMIT OFF;

5.3.5 游标变量

在SQL Server中，游标可以看作是一种变量类型，即可以定义游标变量。定义一个游标变量有两种方法。

• 先创建一游标，而后通过SET语句将游标赋值给游标变量。

• 直接将创建游标语句放置在游标变量的赋值语句中。

5.3.6 使用系统过程管理游标

SQL Server 的企业管理器中并没有提供对游标的管理，但是可以利用系统过程进行管理。管理游标的系统过程及其功能如下：

利用系统过程管理游标。语法如下：

sp_cursor_list [@cursor_return = ] cursor_variable_name OUTPUT , 
[@cursor_scope = ] cursor_scope;

cursor_scope 指出游标的作用域，其取值及含义如下：

0x06 事务控制与并发处理 

6.1 SQL事务控制

SQL采用事务将一系列不可分割的数据库操作作为整体来执行，保证数据库数据的完整性和有效性。

6.1.1 事务的特性

事务是用户对数据库进行的一系列操作的集合。事务需要具有 ACID 特性，即 ATOMIC（原子性）、CONSESTENT（一致性）、ISOLATED（隔离性）、DURABLE（永久性）。具体含义如下：

• 原子性：指事务全有活全无的性质。

• 一致性：事务完成或者撤销后，都应该处于一致的状态。

• 隔离性：多个事务同时进行，它们之间应该互不干扰。

• 永久性：一旦由事务引发了变化，事务提交以后，所做的工作就被永久保存下来，即使硬件和应用程序发生错误，这些数据也会可靠一致。

6.1.2 SQL中与事务有关的语句

6.1.3 SQL 事务控制的使用流程

6.2 事务控制的具体实现

6.2.1 开始事务

在 SQL Server 中有 3 种开始事务的方式，分别是隐式事务、显式开始、自动提交，缺省为自动提交。

• 自动提交：是指对于用户发出的每条 SQL 语句，SQL Server 都会自动开始一个事务，并且在执行后自动进行提交操作来完成这个事务。可以说，在这种事务模式下，一个 SQL 语句就是一个事务。

• 显式开始：以 BEGIN TRANSACTION 命令开始一个事务。

• 隐式开始：是指在当前会话中，通过 Set Implicit_Transactions On 命令设置事务类型，这时，任何 DML 语句（DELETE、UPDATE、INSERT）都会开始一个事务，而事务的结束也是使用 COMMIT 或 ROLLBACK。

BEGIN TRANSACTION 命令的语法可表示如下：

BEGIN TRAN[SACTION] [transaction_name | @tran_name_variable];

transaction_name：指定事务的名称，只有前 32 个字符会被系统识别。
@tran_name_variable：用变量来指定事务的名称。变量只能声明为 CHAR、VARCHA、NCHAR 或 NVARCHAR 类型。

6.2.2 SET CONSTRAINTS语句设置约束的延期执行

在实际应用中，用户在一个事务中，经常需要违反约束在表中修改数据。

注：在SQL Server中不支持SET CONSTRAINTS语句。

6.2.3 终止事务

SQL 终止事务主要有两种方法：COMMIT（提交）和 ROLLBACK（回滚）。当执行 COMMIT命令时，事务发生的变化都保存在数据库中并终止事务；而当执行 ROLLBACK 命令时，数据库将返回到事务开始时的初始状态并终止事务。直接使用 ROLLBACK 命令回滚事务将取消整个事务操作。但如果用户想在事务执行语句的中途“半提交”事务，则可以使用保存点（SavePoint）来保存事务。在执行ROLLBACK 命令时，我们就可以引用该保存点，将事务回滚到该保存点，只取消保存点以下的操作。

注：使用ROLLBACK语句使事务回滚到保存点时，事务并不终止。

在SQL Server中，设置保存点点语法如下：

SAVE TRAN[SACTION] {savepoint_name | @savepoint_variable};

avepoint_name：指定保存点的名称。
@savepoint_variable：用变量来指定保存点的名称变量，只能声明为 CHAR、VARCHAR、NCHAR 或 NVARCHAR 类型。

回滚到保存点点语法如下：

ROLLBACK [TRAN[SACTION] [transaction_name | @tran_name_variable 
| savepoint_name | @savepoint_variable] ];

6.3 并发控制

6.3.1 并发操作的问题

一般来说，如果DBMS不能适当地处理数据库的并发事务处理过程，则可能出现以下4种问题。

• 数据丢失

• 未提交的数据读取(错读)

• 不一致的读(不可重复读)

• 幻影读(假读)

6.3.2 事务隔离级别

隔离级别是高级的 DBMS 锁定技术，通过使用隔离级别达到多用户环境中保证每个用户都在一人专用数据库的目的，同时仍然让尽量多的用户同时访问数据库中的数据。在SQL标准中，事务隔离级别分为以下4种：

• READ UNCOMMITTED：未提交的读取

该隔离级别是 4 个隔离级别中限制最少的一个，它允许读取已经被其他用户修改但尚未提交确定的数据。

• READ COMMITTED：提交读取

READ COMMITTED 隔离级别比 READ UNCOMMITTED 高一层，通过隐藏未提交的变化解决了读“脏数据”（错读）的并发事务问题。虽然 READ COMMITTED 避免了读取未提交数据，但并不能防止不可重复的读取和幻影读（假读）。

• REPEATABLE READ：可重复读取

  该隔离级别层次又在 READ COMMITTED 之上。在此隔离级别下，用 SELECT 命令读取的数据在整个命令执行过程中均由 DBMS 实施锁定，不会被更改。

• SERIALLZABLE：可串行化

该隔离级别是 4 个隔离级别中限制最大的级别，指的是将事务以一种顺序方式连接起来，防止一个事务影响其他事务。

6.3.3 SET TRANSACTION设置事务属性

在标准 SQL 规范中，隔离级别是通过使用 SET TRANSACTION 语句来设置的。语法如下：

SET TRANSATION [READ ONLY | READ WRITE] 
ISOLATION LEVEL level 
DIAGNOSTECS SIZE size;

SET TRANSACTION 可指定 3 种事务模式：访问层、隔离级别和诊断区大小。

在SQL Server中，我们只能用SET TRANSACTION ISOLATION LEVEL 语句设置隔离级别。

6.4 SQL Server中的并发事务控制

6.4.1 锁的分类

在SQL Server中，我们可以对以下的对象进行锁定。

• 数据行（Row）：数据页中的单行数据。

• 索引行（Key）：索引页中的单行数据，即索引的键值。

• 页（Page）：SQL Server 存取数据的基本单位，其大小为 8KB。

• 盘区（Extent）：一个盘区由 8 个连续的页组成。

• 表（Table）。

• 数据库（Database）。

在SQL Server中，锁有两种分类方法：从数据库系统角度分类和从程序员的角度分类。

• 从数据库系统角度看，锁的功能描述如下：

• 从程序员的角度来看，锁的功能描述如下：

6.4.2 SQL Server中表级锁的使用

在 SQL Server 中，提供了几种表级锁定提示（locking hints）。通过使用这些悲观锁，我们可以在多个同时修改数据库的用户间实现悲观并发控制。对数据库表加锁后，其他人不可操作，直到加锁用户用COMMIT命令或ROLLBACK命令解锁。

锁定数据库的一个表的方法可表示如下：

SELECT * FROM table [WITH] (HOLDLOCK|TABLOCKX|……);

6.4.3 设置隔离级别实现并发控制

在 SQL Sever 中，指定事务的隔离级别与在 SELECT 语句中使用锁定选项来控制锁定方式具有相同的效果。设置事务的隔离级别是通过 SET TRANSATION 语句实现的。语法如下：

SET TRANSATION ISOLATION LEVEL level;

可选的隔离级别level从低到高有以下4种：

• READ UNCOMMITTED

这表示不发出共享锁，也不接受独占锁。当设置该选项时，我们可以对数据执行未提交读或脏读；在事务结束前可以更改数据内的数值。该选项的作用与在事务内所有语句中的所有表上设置 NOLOCK 相同。这是 4 个隔离级别中限制最小的级别。

• READ COMMITTED

该隔离级别指定在读取数据时控制独占锁以避免读“脏数据”，但数据可在事务结束前更改，从而产生不可重复读取或幻影读。

注：READ COMMITTED 隔离级别是 SQL Sever 的默认值。

• REPEATABLE READ

该隔离级别将锁定查询中使用的所有数据，以防止其他用户更新。但是其他用户可以将新的幻影行插入到数据集，并且幻影行包含在当前事务的后续读取中。

• SERIALIZABLE

该隔离级别将在数据集上放置一个范围锁，以防止其他用户在事务完成之前更新数据集或将行插入数据集内。这是 4 个隔离级别中限制最大的级别。因为并发级别较低，所以我们应只在必要时才使用该选项。该隔离级别的作用与在事务内所有 SELECT 语句中的所有表上设置 HOLDLOCK 相同。

6.4.4 死锁及其预防

死锁（Deadlocking）是在多用户或多进程状况下，为使用同一资源而产生的无法解决的争用状态。通俗地讲，就是两个用户各占用一个资源，两人都想使用对方的资源，但同时又不愿放弃自己的资源，就一直等待对方放弃资源，如果不进行外部干涉就将一直耗下去。如下：

防止死锁的途径就是避免满足死锁条件的情况发生，为此用户需要遵循以下原则：

• 尽量避免并发地执行涉及到修改数据的语句。

• 要求每个事务一次就将所有要使用的数据全部加锁，否则就不予执行。

• 预先规定一个封锁顺序，所有的事务都必须按这个顺序对数据执行封锁，如不同的

• 过程在事务内部对对象的更新执行顺序应尽量保持一致。

• 每个事务的执行时间不可太长，对程序段长的事务可考虑将其分割为几个事务。

0x07 嵌入式SQL

7.1 SQL的调用

7.1.1 直接调用SQL

直接调用 SQL 也称为交互式 SQL。通常大多数数据库产品都会提供客户端应用软件，用户在该软件中可以直接运行 SQL 语句并返回它的运行结果。

7.1.2 嵌入式SQL

SQL 语言可以嵌入到高级语言中，如 PL/1、COBOL、FORTRAN、C，利用高级语言的过程性结构，可以弥补 SQL 语言在实现复杂应用方面的不足，在这种方式下使用的 SQL 语言称为嵌入式SQL。嵌入式 SQL 的实现主要有两种方法：扩充宿主语言（高级语言）的编译程序，使之能处理 SQL 语句和预处理方式。其中常用的是后者，即预处理方式。预处理方式的处理过程可表示如下：

• 由DBMS的预处理程序对源程序进行扫描，识别出SQL语句。

• 把识别出的SQL语句转换成主语言调用语句，以使主语言编译程序能识别它。

• 由主语言的编译程序将整个源程序编译成目标码。

7.1.3 SQL调用层接口(CLI)

SQL 调用层接口（CLI）是一种应用程序接口（API），它支持一种已经预定义的例程，通过这些例程实现程序设计语言与 SQL 数据库之间的交流。程序设计语言调用例程，然后连接到数据库。这些例程主要负责访问数据库中的数据信息，如果需要，也可把这些信息返回给程序。程序设计语言与SQL数据库通过CLI进行交流的过程如下：

7.2 嵌入式SQL的使用

7.2.1 创建嵌入式SQL语句

当开发一种包含嵌入式 SQL 的程序时，必须遵守一些明确的协定，用来决定 SQL 代码被添加到程序中的方式。要在主语言中得以使用，嵌入式 SQL 语句必须遵守以下原则：

• 每个SQL语句都必须以限定前缀开头，并且指定结束符。

• 必须根据主语言的要求，处理SQL语言中出现的行中断。

• 注释的位置必须根据主语言的风格来处理。

几种主要语言的嵌入 SQL 语句的前缀和结束符如下：

7.2.2 SQL通信区

SQL 语句与主语言之间的通信方式有 SQL 通信区、主变量和游标 3 种方式，其中 SQL通信区（SQL Communication Area，SQLCA）是一个数据结构，用于存储 SQL 语句运行时DBMS 反馈给应用程序的状态信息。这些信息主要描述系统当前工作状态以及运行环境等。应用程序从 SQLCA 中取出这些状态信息，据此决定接下来执行的语句。使用 SQLCA 时，首先需要在主语言中定义，定义语句如下：

EXEC SQL INCLUDE SQLCA;

SQLCA中包含的字段及各字段的含义如下：

注：应用程序每执行完一条 SQL 语句之后，都应该测试一下 SQLCODE 的值，以了解该SQL 语句执行情况并做相应处理。

SQLCODE常用的取值及表示的含义如下：

7.2.3 主变量

嵌入式 SQL 语句中可以使用主语言的程序变量来输入或输出数据。在 SQL 语句中使用的主语言程序变量简称为主变量。

7.2.3.1 主变量的分类及其功能

主变量根据作用的不同，分为输入主变量和输出主变量。输入主变量由应用程序对其赋值，SQL 语句引用。输出主变量由 SQL 语句对其赋值或设置状态信息，返回给应用程序。一个主变量有可能既是输入主变量，又是输出主变量。

利用输入主变量，可以实现如下功能：

• 指定向数据库中插入的数据。

• 将数据库中的数据修改为指定值。

• 指定WHERE子句或HAVING子句中的条件。

  利用输出主变量，则可以得到SQL语句的结果数据和状态。

一个主变量可以附带一个任选的指示变量（Indicator Variable）。所谓指示变量是一个整型

变量，用来指示返回给宿主变量的值是否为 NULL 值以及返回给宿主变量的字符串是否发生

了截断。如果一个宿主变量所对应的数据库字段允许空值，或者字符串类型的宿主变量的长

度可能小于所对应的数据库字段的长度，则需要一个指示变量来指明数据库访问的返回状态。

指示变量的返回值及其含义如下：

7.2.3.2 主变量的定义和使用

输入主变量出现于 SQL 语句中时，前面加冒号（:）以区别于数据库对象名（表名、视图名、列名等）。

注：指示变量使用时，必须紧跟在它所指示的主变量的后面，如代码中的“:E_age:Ind_age”。

7.2.3.3 主变量的应用环境

主变量可用于以下环境中：

• 位于任何允许使用数字或字符串常量位置的SELECT、INSERT、UPDATE和DELETE语句。

• SELECT 和 FETCH 语句的 INTO 子句。

• 主变量也可用来代替语句名、游标名或嵌入式 SQL 特定命令中的选项名。

• 对于 CONNECT、DISCONNECT 和 SET CONNECT，可以用主变量代替用户 ID、口令、连接名、连接字符串或数据库环境名。

• 对于 SET OPTION 和 GET OPTION 命令，可以用主机变量代替用户 ID、选项名或选项值。

7.2.4 嵌入式SQL中使用游标

在嵌入式 SQL 中，游标是系统为用户开设的一个数据缓冲区，存放 SQL 语句的执行结果。每个游标区都有一个名字，用户可以用 SQL 语句逐一从游标中获取记录并赋给主变量，然后由主语言进一步处理。

在嵌入式SQL中使用游标主要有以下几个步骤：

• 定义一个游标，使之对应一个SELECT语句。实现过程可简单表示如下：

EXEC SQL DECLARE <游标名> CURSOR FOR <SELECT 语句>;

• 打开游标，执行游标对应的查询，结果集合为该游标的活动集。实现过程可简单表示如下。此时游标指针定位于结果集第一行的前一行。

EXEC SQL OPEN <游标名>;

• 在活动集中将游标移到特定的行，并且取出该行数据放到相应的主变量中，这一过程称为游标推进。实现过程可简单表示如下：

EXEC SQL FETCH FROM <游标名> INTO <主变量名>;

• 关闭游标，释放活动集及其所占资源，使它不再和查询结果相联系；需要再使用该游标时，执行 OPEN 语句。实现过程可简单表示如下:

EXEC SQL CLOSE <游标名>;

7.3 检索、操作SQL数据

7.3.1 不需要游标的SQL DML操作

在嵌入式SQL中，不用游标的SQL DML语句主要包括以下几种：

7.3.1.1 查询结果为单记录的SELECT语句

语法如下：

EXEC SQL SELECT [ALL|DISTINCT] 
 <目标列表达式>{,<目标列表达式>} 
 INTO <主变量>[<指示变量>] 
 {,<主变量>[<指示变量>]} 
 FROM <表名或视图名>{,<表名或视图名>} 
 [WHERE <条件表达式>];

7.3.1.2 非CURRENT形式的UPDATE语句

所谓非 CURRENT 形式的 UPDATE 语句，是指执行 UPDATE 操作时，所有满足更新条件的记录都进行更新，而不是一行一行地单独更新。语法与交互式 UPDATE 语句完全相同，只是在 SET 子句和 WHERE 子句中可以使用主变量，在 SET 子句中还可以使用指示变量。

7.3.1.3 非CURRENT形式的DELETE语句

嵌入式 SQL 语句中的 DELETE 语句的语法与交互式 DELETE 语句完全相同，只是在WHERE 子句中可以使用主变量。

7.3.1.4 INSERT语句

嵌入式 SQL 语句中的 INSERT 语句的语法与交互式 INSERT 语句完全相同，只是在VALUES 子句中可以使用主变量和指示变量。

7.3.2 使用游标的SQL DML操作

在嵌入式SQL中，必须使用游标的SQL DML操作主要包括以下几种：

7.3.2.1 查询结果为多条记录的SELECT语句

当 SELECT 语句查询结果是多个元组时，主语言程序无法使用，要用游标把多个元组依次传递给宿主语言程序处理，具体过程如下：

• 用游标定义语句定义一个游标，与某个SELECT语句对应。

• 游标用 OPEN 语句打开后，处于活动状态，此时游标指向第一个元组之前。

• 每执行一次 FETCH 语句，游标指向下一个元组并把其值送到主变量供程序处理，如此重复，直到所有查询结果处理完毕。

• 用 CLOSE 语句关闭游标。关闭的游标可以被重新打开，与新的查询结果相联系，没有打开前，不能使用。

7.3.2.2 CURRENT形式的UPDATE语句和DELETE语句

非 CURRENT 形式的 UPDATE 语句和 DELETE 语句都是集合操作，一次修改或删除所有满足条件的记录。如果只想修改或删除其中某个记录，则需要用带游标的 SELECT 语句查出所有满足条件的记录，从中进一步找出要修改或删除的记录，然后修改或删除。具体介绍如下：

• 用DECLARE语句说明游标。如果是为 CURRENT 形式的 UPDATE 语句作准备，则SELECT 语句中要用 FOR UPDATE OF <列名>子句，指明将来检索出的数据在指定列是可修改的。如果是为 CURRENT 形式的 DELETE 语句作准备，则不必使用上述子句。

• 用 OPEN 语句打开游标，把所有满足查询条件的记录从指定表取到缓冲区中。

• 检查该记录是否为要修改或删除的记录。如果是，则用 UPDATE 语句或 DELETE 语句修改或删除该记录。这时 UPDATE 语句和 DELETE 语句中要用 WHERE CURRENT OF <游标名>子句，表示修改或删除的是该游标中最近一次取出的记录，即游标指针指向的记录。

• 处理完毕用CLOSE语句关闭游标，释放结果集占用的缓冲区和其他资源。

注：当游标定义中的 SELECT 语句带有 UNION 或 ORDER BY 子句时，或者该 SELECT语句相当于定义了一个不可更新的视图时，不能使用 CURRENT 形式的 UPDATE 语句和DELETE 语句。

7.3.3 动态SQL技术

动态SQL方法允许在程序运行过程中临时"组装"SQL语句，主要有3种形式：

• 语句可变：允许用户在程序运行时临时输入完整的SQL语句。

• 条件可变：对于非查询语句，条件子句有一定的可变性；对于查询语句，SELECT子句是确定的，即语句的输出是确定的，其他子句（如 WHERE 子句）有一定的可变性。

• 数据库对象、查询条件均可变：对于查询语句，SELECT 子句中的列名、FROM 子句中的表名或视图名、WHERE 子句等均可由用户临时构造，即语句的输入和输出可能都是不确定的。

0x08 常用的SQL命令

0x09 总结

如有错误欢迎各位师傅及时纠正。