SQL复习文档
第一章 数据库设计
1
．1
数据库的开发过程：建立数据模型可以分为3个主要步骤：
l 建立数据库外模式（业务规则：客户所需要达到的目标，对需求的抽象）
抽象的3种类型：
1. 分类2. 聚集
3. 归纳
l 建立数据库概念模式（建立出来的表和模型）
一个数据库中只有一个概念模式l 建立数据库内模式（存储格式位置等）
一个数据库中只有一个内模式---------------------------------------------------------------------------------------------------------------
业务示图 系统示图业务信息需求 à 建立外模式 à 数据库概念模式 à 构建数据库内模式 à 可操作的数据库
----------------------------------------------------------------------------------------------------------------
1 ．2 数据库模型：
u 层次模型
u 网状模型
u 实体关系模型
u 对象模型
1 ．3 实体关系模型：
该模型将现实世界视为一组基本对象以及这些对象之间的关系。
n 实体：
实体可以是具体的（例如一个人或一本书），也可以是抽象的（如一个节日或一个概念）。
n 属性：
实体是由一组属性来表示的。例如：Person（个人）实体的属性有 Name（名称）、SSN、Age（年龄）、Street（街道）、City（城市）。
n 关系：
关系是两个或多个实体之间的联系。
1 ．4 四种映射机制：
----------------------------------------------------------------------------------------------------------------------
|
X X X |
|
Y Y Y Y |
|
一对一 |
|
X
X X X |
|
Y Y Y Y |
|
一对多 |
|
X X X X |
|
Y Y Y Y |
|
多对一 |
|
X X X X |
|
Y Y Y Y |
|
多对多 |
1 ．5 三种泛式：
注：公认的规范化等级有5种——（第一泛式：1NF；第二泛式：2NF；第三泛式：3NF；第四泛式：4NF；第五泛式：5NF。 通常只考虑前3种泛式）
u 第一范式的定义：如果一个表中没有重复组（即行与列的交叉点上只有一个值，而不是一组值），且定义了关键字、所有非关键属性都依赖于关键字，则这个表属于第一范式（常记成1NF）。
解决方式：删除表中重复的列。
u 第二范式的定义：
如果一个表属于1NF，且不包含部分依赖性，既没有任何属性只依赖于关键字的一部分，则这个表属于第二范式（常记成2NF ）。
解决方式：删除表中不完全依赖于主键的数据。
u 第三范式的定义：
如果一个表属于2NF，且不包含传递依赖性（即没有一个非关键属性依赖于另一个非关键属性，或者说没有一个非关键属性决定另一个非关键属性），则这个表是第三范式（常记成3NF）。
解决方式：删除那些不属于该表的数据。
第二章 T-SQL程序设计
2
．1
控制流语句：----------------------------------------------------------------------------------------------------------------------
| 控制关键字 | 目的 |
| BEGIN ... END | 创建语句块。 |
| GOTO label <label>: | 将流程转到指定标签。 |
| IF<condition> ... ELSE | 根据指定条件执行不同的几组语句。 |
| WHILE<condition> BEGIN ... <on some condition> BREAK <on some condition> CONTINUE END | 当指定条件为 TRUE 时重复执行语句。 |
| BREAK | 终止执行操作并跳出当前的WHILE循环。 |
| CONTINUE | 终止执行操作并继续执行下一次WHILE循环。 |
| WAITFOR Time WAITFOR Delay | 设置语句执行延迟的时间。 |
| RETURN | 无条件退出。 |
| CASE<expression> WHEN<expression1>THEN<expression1> [WHEN<expression2>THEN<expression2>] [ELSE<expression>] END | 该结构可以根据条件的值来执行不同的几组语句 |
2．2 CASE示列
----------------------------------------------------------------------------------------------------------------------
UPDATE publishers
SET state =
CASE
WHEN country <> "USA"
THEN "--"
ELSE state
END,
city =
CASE
WHEN pub_id = "9999"
THEN "LYON"
ELSE city
END
WHERE country <> "USA" OR
pub_id = "9999"
----------------------------------------------------------------------------------------------------------------------
2．3 SQL中的全局变量
----------------------------------------------------------------------------------------------------------------------
| 变量 | 含义 |
| @@IDENTITY | 最后一次插入的标识值 |
| @@CPU_BUSY | 自 SQL Server 启动至今，系统持续运行的毫秒数。 |
| @@CURSOR_ROWS | 最近打开的游标中的行数 |
| @@DATEFIRST | SET DATEFIRST 参数的当前值，该参数用于设置一个星期的第一天为哪一天。 |
| @@ERROR | 最后一个 T-SQL 错误的错误号 |
| @@FETCH_STATUS | 如果最后一次提取的状态为成功状态，则为 0。如果出错，则为 -1 |
| @@IDENTITY | 最后一次插入的标识值 |
| @@LANGUAGE | 当前使用的语言的名称 |
| @@MAX_CONNECTIONS | 可以创建的同时连接的最大数 |
| @@ROWCOUNT | 受上一个 SQL 语句影响的行数 |
| @@SERVERNAME | 本地服务器的名称 |
| @@SERVICENAME | 该计算机上的 SQL 服务的名称 |
| @@TIMETICKS | 当前计算机上每指令周期的微秒数 |
| @@TRANSCOUNT | 当前连接打开的事务数 |
| @@VERSION | SQL Server 的版本信息 |
----------------------------------------------------------------------------------------------------------------------
| 函数 | 返回值 |
| Sum(col_name) | 返回一系列值的总和 |
| Avg(col_name) | 返回一系列值的平均值 |
| COUNT(*) | 其功能是检索表中满足给定条件的记录数 |
| Max(col_name) | 返回一系列值中的最大值 |
| Min(col_name) | 返回一系列值中的最小值 |
2．4 SQL中的WAITFOR的用法
----------------------------------------------------------------------------------------------------------------------
用法有两个：
1、延迟一段时间后执行
比如：
WAITFOR DELAY '00:00:05'
PRINT '延迟5秒执行！'
--5秒后执行PRINT
2、指定从何时起执行
比如：
WAITFOR TIME '21:17'
----------------------------------------------------------------------------------------------------------------------
第三章 事务和锁
3．1 事务的四种特性：1. 原子性：
事务的所有操作在数据库中要么全部正确反映出来要么全部不反映。
2. 一致性：
事务隔离执行时（即在没有其他事务并发执行的情况下）保持数据库的一致性。
3. 隔离性：
尽管多个事务可以并发执行，但系统保证，对于任何以对事务T1和T2，在T1看来，T2或者在T1开始之前已经停止执行，或者在T1完成之后开始执行。这样，每个事务都感觉不到系统中有其他事务在并发地执行。
4. 持久性：
一个事务成功完成后，它对数据库的改变必须是永久的，即使是系统出现故障时也是如此。
3．2 事务的三种分类：
1. 显式事务：
显式定义事务: begin transaction ...... end transaction
2. 隐式事务
隐式事务: 自动启动, 步骤:打开/关闭(set implicit_transactions on/off)
3. 自动提交事务（SQL Srver默认）
所有Transcat-SQL语句完成时，都会提交或回滚
3．3 事务日志：
事务日志是用来恢复数据的。
• 事务日志是一个单独的文件
• 通过在日志中对操作进行跟踪，使得数据库服务器从各种灾难中恢复成为可能
• 事务日志确保原子性和持久性
• SQL Server 将所有对数据库的修改写入事务日志
3．4 事务中几条重要语句：
----------------------------------------------------------------------------------------------------------------------
— BEGIN TRANSACTION：开始事务
— COMMIT TRANSACTION：提交事务
— ROLLBACK TRANSACTION：回滚事务
— SAVE TRANSACTION：在事务内设置保存点
----------------------------------------------------------------------------------------------------------------------
3．5 锁的概念：（解决并发问题）
• 锁是在多用户环境中对数据访问的限制。
• SQL Server 2000 使用锁定来确保事务的完整和数据库的一致。
• 锁定蕴含的基本概念是用户需要对表进行排它访问，所以服务器为该特定用户锁定该表。当用户完成对该表的操作后，服务器就会释放锁。
3．6 锁的分类（6类）：
— 共享锁
— 更新锁
— 排它锁
— 意向锁
— 架构锁
— 大容量更新锁
3．7 死锁：
事务1请求了事务2加锁的资源，事务2又请求了事务1加锁的资源。
3．8 解除死锁：
• SET DEADLOCK_PRIORITY { LOW | NORMAL | @deadlock_var }
• SET LOCK_TIMEOUT 毫秒数
3．8 事务并发控制的问题：
如果没有锁定且多个用户同时访问一个数据库，则当他们的事务同时使用相同的数据时可能会发生问题。并发问题包括：
¨ 丢失或覆盖更新（A：写中----B：写中）
¨ 未确认的相关性（脏读）（A：写中----B：读）
¨ 不一致的分析（非重复读）（多次读取修改中的文件，造成修改后文件数据与之前不一致）
¨ 幻像读（读取时，另外一个事务在对读取范围中的元素进行添加删除操作，添加则读取后缺少添加的元素，删除则读取了不存在的数据）
3．8 事务的隔离和隔离级别：
• 未提交读（不使用共享锁,可以读未提交的事务，没解决任何问题，最低级别）
• 提交读（默认使用共享锁，解决脏读的问题）
• 可重复读（解决非重复读的问题）
• 可串行读（解决幻像读的问题）
----------------------------------------------------------------------------------------------------------------
| 隔离级别 | 脏读 | 不可重复读取 | 幻像读 |
| 未提交读 | 是 | 是 | 是 |
| 提交读 | 否 | 是 | 是 |
| 可重复读 | 否 | 否 | 是 |
| 可串行读 | 否 | 否 | 否 |
第四章 用户和安全性管理
4．1 创建登录 / 用户：----------------------------------------------------------------------------------------------------------------
sp_grantlogin ‘OnlineDOMAIN/Arwen'
--允许OnlineDOMAIN/Arwen连接数据库
sp_defaultdb @loginame = ‘OnlineDOMAIN/Arwen', @defdb='books'
--将‘OnlineDOMAIN/Arwen'登录后的默认数据库设置为'books'数据库
sp_addlogin ‘OnlineDOMAIN/Arwen', ‘princess'
--创建一个登录
sp_droplogin ‘OnlineDOMAIN/Arwen'
--删除一个登录
sp_grantdbaccess ‘OnlineDOMAIN/Arwen', ‘Arwen'
--为OnlineDOMAIN/Arwen登录创建一个Arwen用户名,并赋予该用户访问权
sp_revokedbaccess ‘OnlineDOMAIN/Arwen'
--从数据库中删除一个安全帐户
----------------------------------------------------------------------------------------------------------------
4．2 创建角色 / 在角色中添加成员：
----------------------------------------------------------------------------------------------------------------
sp_addrole ‘Teacher' --添加一个角色
go
sp_addrolemember ‘Teacher', ‘Peter' --赋予Peter"Teacher"的角色
go
sp_addsrvrolemember ‘Peter', ‘sysadmin' --赋予Peter服务器角色sysadmin
go --(注)：以上服务器角色和普通角色的添加顺序刚好相反
GRANT SELECT, UPDATE, DELETE ON SOMETABLE TO Teacher
--将对SOMETABLE查询，修改，删除的权限赋予Teacher角色
REVOKE SELECT, UPDATE, DELETE ON SOMETABLE TO Teacher
--撤消Teacher角色上对SOMETABLE的查询，修改，删除的权限
DENY SELECT, UPDATE, DELETE ON SOMETABLE TO Teacher
--拒绝Teacher角色使用对SOMETABLE的查询，修改，删除的权限
----------------------------------------------------------------------------------------------------------------
第五章 高级查询
5．1 子查询：一个 SELECT 语句嵌套在另一个 SELECT 语句中。
5．2 嵌套子查询：子查询自身可以包括一个或多个子查询。一个语句中可以嵌套任意数量的子查询。
（先执行内查询，再将值提供给外查询进行查询）
5．3 相关子查询：
Ø 许多查询都可以通过执行一次子查询并将结果值代入外部查询的 WHERE 子句进行评估。
Ø 在包括相关子查询（也称为重复子查询）的查询中，子查询依靠外部查询获得值。
（先执行外查询获得内查询所依赖的值，再完成查询）
5．3 关键词：EXISTS
• EXISTS 用于检查子查询返回的行是否存在
• 该子查询实际上并不返回任何数据，而是返回 TRUE 或 FALSE
ALL SOME ANY• 可以用 ALL 或 ANY 关键字修改引入子查询的比较运算符。SOME 是 SQL-92 标准的 ANY 的等效物。
UNION
• 将两个或更多个SELECT语句的结果合并为一个结果集。• 可以指定为如下形式：
SELECT 语句
UNION [ALL]
SELECT 语句
• 使用 ALL 子句表示不删除重复的行。
• 每个Select必须具有相同的结构
• 兼容的列类型和相同数目的列
DISTINCT
• 结合使用Select 和Distinct，将删除重复的行SELECT DISTINCT deptno FROM emp
COMPUTE
• COMPUTE 子句为行聚合函数生成汇总值，该汇总值作为附加行显示在结果集中。• 汇总值作为附加行出现。
SELECT Stor_id, Qty FROM Sales COMPUTE MAX(Qty)
SELECT type, price, advance FROM titles ORDER BY type COMPUTE SUM(price), SUM(advance)
COMPUTE BY
• COMPUTE BY 子句使得可以用同一 SELECT 语句既查看明细行，又查看汇总行。可以计算子组的汇总值，也可以计算整个结果集的汇总值。SELECT type, price, advance FROM titles
ORDER BY type
COMPUTE SUM(price), SUM(advance)
BY type
(注)：两个BY后面的字段要一致
(注意)：COMPUTE/COMPUTE BY语句所返回的值，都不能用在记录集里
SELECT INTO
• SELECT INTO 语句创建一个新表，并用 SELECT 的结果集填充该表
• 新表的结构由选择列表中表达式的特性定义
SELECT Shippers.*, Link.Address,
Link.City, Link.Region, Link.PostalCode
INTO NewShippers
FROM Shippers JOIN LinkServer.DB.dbo.Shippers
AS Link
ON (Shippers.ShipperID = Link.ShipperID)
第六章 索引和全文索引
6．1 索引概念：— 索引是对数据库表中一个或多个列的值进行排序的结构
— 索引提供指针以指向存储在表中指定列的数据值，然后根据指定的排序次序排列这些指针
6．2 索引的优 / 缺点：
— 优点
Ø 加快访问速度（数据量很大的前提下，数据少会起到反作用）
Ø 加强行的唯一性
— 缺点
Ø 带索引的表在数据库中需要更多的存储空间。
Ø 操纵数据的命令需要更长的处理时间，因为
它们需要对索引进行更新。
6．3 索引分类：
— 聚集索引
表中各行的物理顺序与键值的逻辑（索引）顺序相同。表只能包含一个聚集索引
— 非聚集索引
非聚集索引指定表的逻辑顺序。物理顺序与键值的逻辑（索引）顺序不相同。一个表最多可以有249个
6．4 创建索引的指导原则：
— 只有表的所有者能为表创建索引。
— 适合创建索引情况：
Ø 该列频繁用于进行搜索。
Ø 该列用于对数据进行排序。
— 不适合创建索引情况：
Ø 列中仅包含几个不同的值。
Ø 表中仅包含几行。
6．5 创建索引：
CREATE [UNIQUE] [CLUSTERED|NONCLUSTERED]
INDEX index_name
ON table_name(column_name[, column_name]...)
[WITH
[PAD_INDEX]
[[,]FILLFACTOR=x]
[[,]DROP_EXISTING]
]
6．6 填充因子：
1) 创建聚集索引后，表中数据会按照索引列的值的顺序存储在数据页中。
2) 表中添加了新记录或索引列的值发生修改时，SQL Server 需要重新组织数据存储模式，以便为新记录腾出空间并维持数据的有序存储。
3) 向已满的索引页中添加新行时，SQL Server 会将大约一半的行移到新的一页上，以便为新行腾出空间，从而导致页拆分。
4) 页拆分会降低性能，且割裂了表中数据的存储。
5) 填充因子是在创建索引时指定的，它用来在索引的各个页上预留一定比例的可用空间，以供将来进行数据扩充时使用。
6) 填充因子指定了创建索引后要在页中输入数据的量。
7) 如果无须再修改数据，可以指定填充因子100，这样将完全占满索引页，因而所占用的存储空间最少。
8) 如果需要频繁地修改表中的数据，则应指定一个较低的填充因子，以便在数据页中保留更多的空白空间（10~20） 。
6．7 创建聚集索引：
— 聚集索引决定数据的物理存储顺序。
— 一个表只能有一个聚集索引。
— 索引可以包含多个数据列。
— PRIMARY KEY约束会自动创建聚集索引。
CREATE CLUSTERED
INDEX CLINDX_titleid ON roysched (title_id)
创建聚集索引的指导原则
¨ 选择唯一值比例高的数据列，一般应选择主键列
¨ 先创建聚集索引，再创建非聚集索引
¨ 在创建聚集索引时使用FILLFACTOR选项以确保在插入新行时有足够的可用空间。
6．8 创建非聚集索引：
— 非聚集索引指定表的逻辑顺序。数据存储在一个位置，索引存储在另一个位置，索引中包含指向 数据存储位置的指针。
— 一个表可以有多个（最多249个）非聚集索引。
CREATE NONCLUSTERED
INDEX NCLINDX_ordnum ON sales (ord_num)
创建非聚集索引的指导原则
¨ 除非绝对必要，否则请不要添加非聚集索引，这是因为非聚集索引需要额外的维护开销
¨ 在频繁访问的列上建立索引
¨ 默认情况下，CREATEINDEX语句创建的是非聚集索引
¨ 在相异值多的数据列上创建非聚集索引，如名和姓的组合项
6．9 索引的特性：
— 聚集索引和非聚集索引可以创建为唯一索引或组合索引。
— 唯一索引不允许索引列中存在重复的值。
— 组合索引允许在创建索引时使用两列或更多的列。
6．10 创建唯一索引的指导原则：
— 由于每一行都包含一个唯一的值，唯一索引可以确保实体完整性。
— 键列中的NULL 值也算作唯一值。
— 不能在包含重复数据的列上创建唯一索引。
— 在创建唯一索引前必须更新或删除重复的数据。
6．11 创建组合索引的指导原则：
— 当需要频繁地将两列或多列作为一个整体进行 搜索时，可以创建组合索引。
— 创建组合索引时先列出唯一性最好的列。
— 组合索引中列的顺序和数量会影响查询的性能。
6．12 在查询强制使用中某个特定索引，以及删除索引：
SELECT * FROM sales (INDEX =nclindx_ordnum)
WHERE ord_num = 'P3087a'
DROP INDEX sales.NCLINDX_ordnum
6．13 使用全文索引：
— 可以在企业管理器中使用全文索引向导创建全文索引。
— 创建全文索引后，就可以对创建了该索引的表进行全文查询。
— 全文查询使用CONTAINS和FREETEXT这两个谓词。
6．14 CONTAINS 谓词：
— 搜索包含基于字符的数据类型的列，查找：
¨ 单个词语或短语的匹配项
¨ 特定距离内的词语或
¨ 加权匹配项
— 搜索不区分大小写。
SELECT title FROM titles WHERE
CONTAINS(title,'"computer" or "cooking" or "silicon"')
6．15 FREETEXT 谓词：
— FREETEXT关键字在列中搜索与搜索条件中的词语
意义相匹配但形式却并不完全匹配的值
— 搜索字符串被分割成多个搜索短词，然后再查找 匹配项
— 搜索字符串可以是一组单词或短语，甚至可以是一个完整的句子
SELECT title FROM titles WHERE
FREETEXT(title, 'computer cooking and silicon')
第七章 视图和游标
7．1 视图概念：— 视图是一种虚拟表，通常是作为来自一个或多个表的行或列的子集创建的。
— 视图并不是数据库中存储的数据值的集合。
— 视图充当着查询中指定的表的筛选器。
7．2 视图的优点：
— 对最终用户的好处
A. 结果更容易理解
B. 获得数据更容易
— 对开发人员的好处
A. 限制数据检索更容易
B. 维护应用程序更方便
7．3 创建视图：
CREATE VIEW <Viewname> AS <Select Statement>
7．4 创建视图的指导原则：
— 只能在当前数据库中创建视图
— 视图名称应与表名近似
— 视图名称应遵循标识符的命名规则
— 可以基于其他视图建立视图
— 不能将默认值、规则和触发器与视图相关联
— 不能为视图建立索引
— 创建视图时不能使用临时表。
— 即使表被删除，视图定义仍将保留。
— 定义视图的查询不能包含 以下语句：
— ORDER BY
— COMPUTE 子句
— COMPUTE BY 子句
— INTO 关键字
7．5 通过视图修改数据：
满足下列条件时，可以使用视图修改表中的数据：
— 视图定义的FROM 子句中应至少包含一个表。
— 选择列表中没有使用聚合函数或GROUP BY、UNION、DISTINCT或TOP子句。
— 视图的选择列表中不包含派生列。
7．6 修改视图定义和删除视图：
修改：
USE Northwind
GO
ALTER VIEW dbo.EmployeeView
AS
SELECT LastName, FirstName, Extension
FROM Employees
删除：
DROP VIEW NewMoon_employee
7．7 游标概念：
— 游标实际上是一种能从包括多条数据记录的结果集中每次提取一条记录的机制
— 游标提供了一种对从表中检索出的数据进行操作的灵活手段
— 使用游标，可以实现以下目标：
- 允许定位到结果集中的特定行。
- 从结果集的当前位置检索一行或多行数据。
- 支持对结果集中当前位置的行进行修改。
- 对于其他用户对结果集包含的数据库
数据所做的修改，支持不同的可见性级别
7．8 游标优点：
— 游标允许应用程序对查询语句select 返回的行结果集中每一行进行相同或不同的操作
— 它还提供对基于游标位置而对表中数据进行删除或更新的能力
— 游标把作为面向集合的数据库管理系统和面向行的程序设计两者联系起来使两个数据处理方式能够进行沟通
7．9 游标实现方式：
— T_SQL游标
— API 服务器游标
¨ 只进游标
¨ 静态游标
¨ 动态游标
¨ 键集驱动游标
— 客户端游标
DECLARE authors_cursor CURSOR
FOR SELECT * FROM authors
OPEN authors_cursor
FETCH NEXT FROM authors_cursor
第八章 存储过程
8．1 存储过程概念：— 一组使用一个名称存储的预编译T-SQL语句
— 由数据库开发人员或数据库管理员编写
— 用来执行管理任务或应用复杂的业务规则
— 包含DML语句
8．2 存储过程的优点：
• 执行速度更快
• 数据访问速度更快
• 实现多个程序共享应用程序逻辑
• 能够屏蔽数据库的结构，实现更高的安全性
• 减少网络流通量
8．3 存储过程的类型 ：
• 系统存储过程
sp_databases 列出服务器上的所有数据库
sp_server_info 列出服务器信息
sp_stored_procedures 列出当前环境中的所有存储过程
sp_tables 列出当前环境中所有可以查询的对象
sp_start_job 立即启动自动化任务
sp_password 添加或修改登录帐户的密码
sp_configure 修改SQL Server全局配置选项
sp_help 显示任意数据库对象的相关信息
sp_helptext 显示规则、默认值、未加密存储过程、用户定义函数、触发器、视图的实际文本
• 用户定义的存储过程
8．4 用户定义 / 修改 / 删除 / 执行的存储过程：
定义：CREATE PROC[EDURE] procedure_name AS ......
修改：ALTER PROC[EDURE] procedure_name AS ......
删除：DROP PROC[EDURE] procedure_name AS ......
执行：EXEC[UTE] procedure_name
8．5 用户定义存储过程的指导原则：
• 存储过程的名称必须遵守标识符的命名规则
• 存储过程中可以创建所有数据库对象，但默认值、 规则、触发器、过程和视图除外。创建对象后，就可以在过程内部进行引用。
• 存储过程可以引用临时表。
• 在存储过程内调用的过程可以访问所有在调用过程中创建的对象
• 在存储过程中可使用2100个参数
• 只要内存空间足够，可以在存储过程中创建任意多个局部变量。
• 存储过程的最大大小为128 MB
8．6 输入参数示例：
----------------------------------------------------------------------------------------------------------------------
CREATE PROCEDURE Titles_Pub
@v_pubid char(4)
AS
SELECT * FROM titles WHERE pub_id = @v_pubid
GO
EXECUTE Titles_Pub '0877'
----------------------------------------------------------------------------------------------------------------------
8．7 输出参数示例：
----------------------------------------------------------------------------------------------------------------------
声明存储过程
USE pubs
GO
CREATE PROCEDURE myProc
@outparm int OUTPUT,
@inparm int
AS
SELECT * FROM titles WHERE royalty > @inparm
SELECT @outparm = COUNT (*) FROM TITLES WHERE royalty > @inparm
IF (@outparm > 0)
RETURN 0
ELSE
RETURN 99
GO
执行存储过程示例
declare @parm int
select @parm=0
exec myProc @outparm=@parm output,@inparm=10
select @parm as 'parm'
----------------------------------------------------------------------------------------------------------------------
8．8 重新编译过程：
• 需要重新编译存储过程，以反映对索引所做的修改
• 重新编译过程的方法有三种：
Ø 使用系统存储过程sp_recompile
Ø 使用CREATE PROCEDURE时指定WITH RECOMPILE
Ø 使用EXECUTE时指定WITH RECOMPILE
8．9 处理错误信息：
• 可以使用返回代码或RAISERROR语句向用户报告错误。
• 存储过程的返回代码返回整数值。
• RAISERROR语句记录错误并指定严重性级别。
8．10 返回代码：
• 返回代码只返回整数值。默认情况下，返回代码值为0
• 返回代码必须将其值返回到变量中
ALTER PROCEDURE Titles_Pub
@v_pubid char(4)
AS
DECLARE @v_return int
SELECT @v_return=COUNT(*)
FROM titles WHERE pub_id = @v_pubid
IF @v_return>0
SELECT * FROM titles WHERE pub_id = @v_pubid
ELSE
RETURN @v_return+1
8．11 RAISERROR 语句：
CREATE PROCEDURE Myproc AS
DECLARE @v_ctr INT
SELECT @v_ctr = 5
WHILE @v_ctr > 0
BEGIN
SELECT @v_ctr * @v_ctr
SELECT @v_ctr = @v_ctr - 1
IF @v_ctr = 2
BEGIN
RAISERROR('计数器已小于 3', 1, 2)
BREAK
END
END
第九章 触发器
9．1 触发器概念：• 触发器是在对表进行插入、更新或删除操作时自动执行的存储过程
• 触发器通常用于强制业务规则
• 触发器可以确保数据的完整性和一致性
9．2 触发器的种类：
| 功能 / 种类 | AFTER触发器 | INSTEAD OF触发器 |
| 适用范围 | 表 | 表和视图 |
| 每个表或视图含触发器数量 | 每个触发动作(UPDATE、DELETE和INSERT)含多个触发器 | 每个触发动作(UPDATE、DELETE和INSERT)含一个触发器 |
| 级联引用 | 不应用任何限制 | 在作为级联引用完整性约束目标的表上限制应用 |
| 执行 | 晚于： 约束处理 声明引用操作 Inserted和deleted表的创建 触发动作 | 早于： 约束处理 代替： 触发动作 晚于： Inserted、deleted表的创建 |
| 执行顺序 | 可指定第一个和最后一个执行 | 不可用 |
| 在inserted和deleted表中引用text、ntext、和image列 | 不允许 | 允许 |
CREATE TRIGGER Trigger_name
ON table
[WITH ENCRYPTION]
{FOR | AFTER | INSTEAD OF } [DELETE, INSERT, UPDATE]
AS Sql_statements
9．4 创建触发器的指导原则：
• 触发器可以与对表执行的三个操作（INSERT、 UPDATE 和 DELETE）相关联
• 一个触发器只应用于单独一个表
• WITH ENCRYPTION选项可用于对用户隐藏触发器的定义。但是，加密的触发器无法进行解密
• 触发器可以引用视图或临时表，但不能和它们相关联
• 触发器可以包含任意数量的SQL语句
• 默认情况下，只有数据库所有者才具有创建触发器的权限。此权限不可转让。
• 触发器只能在当前数据库中创建。但是，触发器可以引用其他数据库中的对象。
9．4 使用 Inserted 和 Deleted 表：
• 触发器可以访问两个逻辑表
- Inserted表
- Deleted表
• Inserted 和Deleted 表包含更新前和更新后的数据映像
• Inserted和Deleted表中不包含表中不受更新操作影响的数据
9．5 从触发条件对触发器分为三类：
1．当试图向表中插入数据时，将执行 INSERT 触发器
INSERT 触发器执行下列操作：
• 向Inserted表中插入一个新行的副本。
• 检查Inserted 表中的新行，确定是否要阻止该插入操作。
• 如果所插入的行中的值是有效的，则将该行插入到触发器表中。
2．当试图更新表中的数据时，将执行UPDATE触发器
UPDATE 触发器执行下列操作：
• 将原始数据行移到逻辑Deleted表中
• 将一个新行插入Inserted表中，然后插入触发器表中
• 计算Deleted表和Inserted表中的值以确定是否需要进行干预
可以创建Update触发器以验证对单个列或整个表的更新
IF UPDATA(COLNAME)
3．当试图从表中删除数据时，将执行DELETE 触发器。
DELETE触发器执行下列操作：
• 从触发器表中删除行。
• 将删除的行插入到Deleted表中。
• 检查Deleted表中的行，以确定是否需要或应如何执行触发器操作。
9．6 Inserted和Deleted表的作用
| 触发器类型 | Inserted表 | Deleted表 |
| UPDATA | 包含执行命令后更新了的记录的副本 | 包含执行命令后更新的记录的原始副本 |
| DELETE | 不使用 | 包含执行命令或所删除的记录 |
| INSERT | 包含执行命令后插入的记录的副本 | 不使用 |
9．7 不能在触发器中使用的 SQL 语句
| SQL 语句 |
| CREATE DATABASE |
| ALTER DATABASE |
| DROP DATABASE |
| LOAD DATABASE |
| RECONFIGURE |
| RESTORE LOG |
| DISK INIT |
| DISK RESIZE |
| LOAD LOG |
| RESTORE DATABASE |
• 级联触发器用于强制引用的完整性
• 当某个表发生修改时，级联触发器会修改相关表中的数据
• 触发器不能用于执行会影响外键或主键约束的级联更新或删除
• 在检验约束后，才会执行触发器；如果操作不能满足约束，则不会执行触发器
9．8 嵌套触发器
• 如果一个触发器在执行操作时会启动另一个触发器，而第二个触发器又接着启动下一个触发器，以次类推，这些触发器称为嵌套触发器。
• 触发器最多可以嵌套 32 层。
• 要启用触发器嵌套，请以以下方式执行 sp_configure 系统存储过程： sp_configure 'nested trigger', 1
• 要禁用触发器嵌套，请执行下面的语句：
• sp_configure 'nested trigger', 0
9．9 INSTEAD OF 触发器
• 包含代替原始数据操作语句的代码
• INSTEAD OF触发器的主要优点是可以使不能更新的视图支持更新
• INSTEAD OF触发器的另一个优点是使得以编写这样的逻辑代码：可以拒绝批处理中的某些部分同时允许批处理的其它部分成功
9．10 触发器与性能
• 由触发器引起的开销通常较低。
• 大部分的时间花费在引用逻辑表以外的其他表上。
• Deleted和Inserted逻辑表始终位于内存中。
4483
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
