数据库原理及应用.索引.doc

1、一、创建索引CREATE INDEX 语句用于在表中创建索引。CREATE UNIQUE INDEX index ON tablename (field ASC|DESC, field ASC|DESC, .) WITH PRIMARY | DISALLOW NULL | IGNORE NULL 索引分为聚簇索引和非聚簇索引。1.聚簇索引聚簇索引也叫簇类索引，是一种对磁盘上实际数据重新组织以按指定的一个或多个列的值排序。由于聚簇索引的索引页面指针指向数据页面，所以使用聚簇索引查找数据几乎总是比使用非聚簇索引快。每张表只能建一个聚簇索引，并且建聚簇索引需要至少相当该表 120%的附加空间，以存放

2、该表的副本和索引中间页。聚簇索引确定表中数据的物理顺序。聚簇索引类似于电话簿，后者按姓氏排列数据。由于聚集索引规定数据在表中的物理存储顺序，因此一个表只能包含一个聚集索引。但该索引可以包含多个列（组合索引），就像电话簿按姓氏和名字进行组织一样。汉语字典也是聚簇索引的典型应用，在汉语字典里，索引项是字母+声调，字典正文也是按照先字母再声调的顺序排列。 聚簇索引对于那些经常要搜索范围值的列特别有效。使用聚簇索引找到包含第一个值的行后，便可以确保包含后续索引值的行在物理相邻。例如，如果应用程序执行的一个查询经常检索某一日期范围内的记录，则使用聚集索引可以迅速找到包含开始日期的行，然后检索表中所有相邻

3、的行，直到到达结束日期。这样有助于提高此类查询的性能。同样，如果对从表中检索的数据进行排序时经常要用到某一列，则可以将该表在该列上聚簇（物理排序），避免每次查询该列时都进行排序，从而节省成本。 建 立 聚 簇 索 引 的 思 想1、 大 多 数 表 都 应 该 有 聚 簇 索 引 或 使 用 分 区 来 降 低 对 表 尾 页 的 竞 争 ， 在 一 个 高 事 务的 环 境 中 ， 对 最 后 一 页 的 封 锁 严 重 影 响 系 统 的 吞 吐 量 。 2、 在 聚 簇 索 引 下 ， 数 据 在 物 理 上 按 顺 序 排 在 数 据 页 上 ， 重 复 值 也 排 在 一 起 ， 因

4、 而在 那 些 包 含 范 围 检 查 (between、 =)或 使 用 group by 或 orderby 的 查 询时 ， 一 旦 找 到 具 有 范 围 中 第 一 个 键 值 的 行 ， 具 有 后 续 索 引 值 的 行 保 证 物 理 上 毗 连 在 一 起而 不 必 进 一 步 搜 索 ， 避 免 了 大 范 围 扫 描 ， 可 以 大 大 提 高 查 询 速 度 。 3、 在 一 个 频 繁 发 生 插 入 操 作 的 表 上 建 立 聚 簇 索 引 时 ， 不 要 建 在 具 有 单 调 上 升 值 的列 (如 IDENTITY)上 ， 否 则 会 经 常 引 起 封 锁

5、 冲 突 。 4、 在 聚 簇 索 引 中 不 要 包 含 经 常 修 改 的 列 ， 因 为 码 值 修 改 后 ， 数 据 行 必 须 移 动 到 新的 位 置 。 5、 选 择 聚 簇 索 引 应 基 于 where 子 句 和 连 接 操 作 的 类 型 。 聚 簇 索 引 的 侯 选 列1、 主 键 列 ,该 列 在 where 子 句 中 使 用 并 且 插 入 是 随 机 的 。 2、 按 范 围 存 取 的 列 ， 如 pri_order 100 and pri_order = $1000 AND range $20000B. 列表规则下例创建一个规则，用以将输入到该规则所绑定

6、的列中的实际值限制为只能是该规则中列出的值。CREATE RULE list_ruleAS list IN (1389, 0736, 0877)C. 模式规则下例创建一个遵循这种模式的规则：任意两个字符的后面跟一个连字符和任意多个字符（或没有字符），并以 0 到 9 之间的整数结尾。CREATE RULE pattern_rule ASvalue LIKE _ _-%0-9CREATE RULE 定义一个适用于特定表或者视图的新规则。 CREATE OR REPLACE RULE 要么是创建一个新规则， 要么是用一个同表上的同名规则替换现有规则。 NAMECREATE RULE - 定义一个新

7、的重写规则SYNOPSISCREATE OR REPLACE RULE name AS ON eventTO table WHERE condition DO INSTEAD NOTHING | command | ( command ; command . ) DESCRIPTION 描述CREATE RULE 定义一个适用于特定表或者视图的新规则。 CREATE OR REPLACE RULE 要么是创建一个新规则， 要么是用一个同表上的同名规则替换现有规则。PostgreSQL 规则系统允许我们在从数据库或表中更新， 插入或删除东西时定义一个其它的动作来执行。 简单说，规则就是当我们在指

8、定的表上执行指定的动作的时候，导致一些额外的动作被执行。 另外，规则可以用另外一个命令取代某个特定的命令，或者令命令完全不被执行。 规则还用于实现表视图。我们要明白的是规则实际上只是一个命令转换机制，或者说命令宏。 这种转换发生在命令开始执行之前。如果你实际上想要一个为每个物理行独立发生的操作， 那么你可能还是要用一个触发器，而不是规则。有关规则的更多信息可以在 The Rule System 找到。目前，ON SELECT 规则必须是无条件的 INSTEAD 规则并且必须有一个由一条 SELECT 查询组成的动作。 因此，一条 ON SELECT 规则有效地把对象表转成视图， 它的可见内容是

9、规则的 SELECT 查询返回的记录而不是存储在表中的内容（如果有的话） 。 我们认为写一条 CREATE VIEW 命令比创建一个表然后定义一条 ON SELECT 规则在上面的风格要好。你可以创建一个可以更新的视图的幻觉， 方法是在视图上定义 ON INSERT，ON UPDATE，和 ON DELETE 规则（或者满足你需要的任何上述规则的子集） ，用合适的对其它表的更新替换在视图上更新的动作。如果你想在视图更新上使用条件规则，那么这里就有一个补充： 对你希望在视图上允许的每个动作，你都必须有一个无条件的 INSTEAD 规则。 如果规则是有条件的，或者它不是 INSTEAD， 那么系统

10、仍将拒绝执行更新动作的企图，因为它认为它最终会在某种程度上在虚拟表上执行动作。 如果你想处理条件规则上的所由有用的情况，那也可以；只需要增加一个无条件的 DO INSTEAD NOTHING 规则确保系统明白它将决不会被调用来更新虚拟表就可以了。 然后把条件规则做成非 INSTEAD；在这种情况下，如果它们被触发，那么它们就增加到缺省的 INSTEAD NOTHING 动作中。 PARAMETERS 参数name 创建的规则名。它必须在同一个表上的所有规则的名字中唯一。 同一个表上的同一个事件类型的规则是按照字母顺序运行的。 event 事件是 SELECT， UPDATE，DELETE 或

11、INSERT 之一。 table 规则施用的表或者视图的名字（可以有模式修饰） 。 condition 任意 SQL 条件表达式（返回 boolean） 。 条件表达式除了引用 NEW 和 OLD 之外不能引用任何表，并且不能有聚集函数。 command 组成规则动作的命令。有效的命令是 SELECT，INSERT， UPDATE，DELETE，或 NOTIFY 语句之一。 在 condition 和 command 里， 特殊表名字 NEW 和 OLD 可以用于指向被引用表里的数值 new 在 ON INSERT 和 ON UPDATE 规则里可以指向被插入或更新的新行。 OLD 在 ON

12、UPDATE，和 ON DELETE 规则里可以指向现存的被更新，或者删除的行。NOTES 注意为了在表上定义规则，你必须有 RULE 权限。有一件很重要的事情是要避免循环规则。 比如，尽管下面两条规则定义都是 PostgreSQL 可以接受的， 但一条 SELECT 命令会导致 PostgreSQL 报告一条错误信息，因为该查询循环了太多次：CREATE RULE “_RETURN“ ASON SELECT TO t1DO INSTEAD SELECT * FROM t2;CREATE RULE “_RETURN“ ASON SELECT TO t2DO INSTEAD SELECT * F

13、ROM t1;SELECT * FROM t1;目前，如果一个规则包含一个 NOTIFY 查询，那么该 NOTIFY 将被无条件执行 - 也就是说，如果规则不施加到任何行上头， 该 NOTIFY 也会被发出。比如，在CREATE RULE notify_me AS ON UPDATE TO mytable DO NOTIFY mytable;UPDATE mytable SET name = foo WHERE id = 42;里，一个 NOTIFY 事件将在 UPDATE 的时候发出，不管是否有某行的 id = 42。这是一个实现的限制，将来的版本应该修补这个毛病。 三、创建存储过程存储过程

14、（Stored Procedure）是在大型数据库系统中，一组为了完成特定功能的 SQL 语句集，经编译后存储在数据库中，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。CREATE PROCEDURE 创建存储过程，存储过程是保存起来的可以接受和返回用户提供的参数的 Transact-SQL 语句的集合。 可以创建一个过程供永久使用，或在一个会话中临时使用（局部临时过程），或在所有会话中临时使用（全局临时过程）。 也可以创建在 Microsoft SQL Server? 启动时自动运行的存储过程。语 法CREATE PROC EDURE procedure_name

15、; number parameter data_type VARYING = default OUTPUT ,.n WITH RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION FOR REPLICATION AS sql_statement .n 参 数procedure_name新 存 储 过 程 的 名 称 。 过 程 名 必 须 符 合 标 识 符 规 则 ， 且 对 于 数 据 库 及 其 所 有 者 必 须 唯一 。 有 关 更 多 信 息 ， 请 参 见 使 用 标 识 符 。 要 创 建 局 部 临 时 过 程 ， 可 以 在 pro

16、cedure_name 前 面 加 一 个 编 号 符 (#procedure_name)， 要 创 建 全 局 临 时 过 程 ， 可 以 在 procedure_name 前 面 加 两 个 编 号符 (#procedure_name)。 完 整 的 名 称 （ 包 括 # 或 #） 不 能 超 过 128 个 字 符 。 指 定过 程 所 有 者 的 名 称 是 可 选 的 。 number是 可 选 的 整 数 ， 用 来 对 同 名 的 过 程 分 组 ， 以 便 用 一 条 DROP PROCEDURE 语 句即 可 将 同 组 的 过 程 一 起 除 去 。 例 如 ， 名 为

17、orders 的 应 用 程 序 使 用 的 过 程 可 以 命 名 为 orderproc;1、 orderproc;2 等 。 DROP PROCEDURE orderproc 语 句 将 除 去 整 个 组 。 如果 名 称 中 包 含 定 界 标 识 符 ， 则 数 字 不 应 包 含 在 标 识 符 中 ， 只 应 在 procedure_name 前后 使 用 适 当 的 定 界 符 。 parameter过 程 中 的 参 数 。 在 CREATE PROCEDURE 语 句 中 可 以 声 明 一 个 或 多 个 参 数 。 用户 必 须 在 执 行 过 程 时 提 供 每 个

18、 所 声 明 参 数 的 值 （ 除 非 定 义 了 该 参 数 的 默 认 值 ） 。 存 储过 程 最 多 可 以 有 2.100 个 参 数 。 使 用 符 号 作 为 第 一 个 字 符 来 指 定 参 数 名 称 。 参 数 名 称 必 须 符 合 标 识 符 的 规 则 。每 个 过 程 的 参 数 仅 用 于 该 过 程 本 身 ； 相 同 的 参 数 名 称 可 以 用 在 其 它 过 程 中 。 默 认 情 况 下 ，参 数 只 能 代 替 常 量 ， 而 不 能 用 于 代 替 表 名 、 列 名 或 其 它 数 据 库 对 象 的 名 称 。 有 关 更 多 信息 ， 请

19、 参 见 EXECUTE。 data_type参 数 的 数 据 类 型 。 所 有 数 据 类 型 （ 包 括 text、 ntext 和 image） 均 可 以 用 作 存 储过 程 的 参 数 。 不 过 ， cursor 数 据 类 型 只 能 用 于 OUTPUT 参 数 。 如 果 指 定 的 数 据 类 型为 cursor， 也 必 须 同 时 指 定 VARYING 和 OUTPUT 关 键 字 。 有 关 SQL Server 提供 的 数 据 类 型 及 其 语 法 的 更 多 信 息 ， 请 参 见 数 据 类 型 。 说 明 对 于 可 以 是 cursor 数 据

20、类 型 的 输 出 参 数 ， 没 有 最 大 数 目 的 限 制 。 VARYING指 定 作 为 输 出 参 数 支 持 的 结 果 集 （ 由 存 储 过 程 动 态 构 造 ， 内 容 可 以 变 化 ） 。 仅 适用 于 游 标 参 数 。 default参 数 的 默 认 值 。 如 果 定 义 了 默 认 值 ， 不 必 指 定 该 参 数 的 值 即 可 执 行 过 程 。 默 认 值 必须 是 常 量 或 NULL。 如 果 过 程 将 对 该 参 数 使 用 LIKE 关 键 字 ， 那 么 默 认 值 中 可 以 包含 通 配 符 （ %、 _、 和 ） 。 OUTPUT

21、表 明 参 数 是 返 回 参 数 。 该 选 项 的 值 可 以 返 回 给 EXECUTE。 使 用 OUTPUT 参数 可 将 信 息 返 回 给 调 用 过 程 。 Text、 ntext 和 image 参 数 可 用 作 OUTPUT 参 数 。 使用 OUTPUT 关 键 字 的 输 出 参 数 可 以 是 游 标 占 位 符 。 n表 示 最 多 可 以 指 定 2.100 个 参 数 的 占 位 符 。 RECOMPILE | ENCRYPTION | RECOMPILE, ENCRYPTION RECOMPILE 表 明 SQL Server 不 会 缓 存 该 过 程 的

22、 计 划 ， 该 过 程 将 在 运 行 时 重 新编 译 。 在 使 用 非 典 型 值 或 临 时 值 而 不 希 望 覆 盖 缓 存 在 内 存 中 的 执 行 计 划 时 ， 请 使 用 RECOMPILE 选 项 。 ENCRYPTION 表 示 SQL Server 加 密 syscomments 表 中 包 含 CREATE PROCEDURE 语 句 文 本 的 条 目 。 使 用 ENCRYPTION 可 防 止 将 过 程 作 为 SQL Server 复 制 的 一 部 分 发 布 。 说 明 在 升 级 过 程 中 ， SQL Server 利 用 存 储 在 sysc

23、omments 中 的 加 密 注 释 来 重新 创 建 加 密 过 程 。 FOR REPLICATION指 定 不 能 在 订 阅 服 务 器 上 执 行 为 复 制 创 建 的 存 储 过 程 。 .使 用 FOR REPLICATION 选 项 创 建 的 存 储 过 程 可 用 作 存 储 过 程 筛 选 ， 且 只 能 在 复 制 过 程 中 执 行 。本 选 项 不 能 和 WITH RECOMPILE 选 项 一 起 使 用 。 AS指 定 过 程 要 执 行 的 操 作 。 sql_statement过 程 中 要 包 含 的 任 意 数 目 和 类 型 的 Transact

24、-SQL 语 句 。 但 有 一 些 限 制 。 n 是 表 示 此 过 程 可 以 包 含 多 条 Transact-SQL 语 句 的 占 位 符 。 注 释存 储 过 程 的 最 大 大 小 为 128 MB。 用 户 定 义 的 存 储 过 程 只 能 在 当 前 数 据 库 中 创 建 （ 临 时 过 程 除 外 ， 临 时 过 程 总 是 在 tempdb 中 创 建 ） 。 在 单 个 批 处 理 中 ， CREATE PROCEDURE 语 句 不 能 与 其 它 Transact-SQL 语 句 组 合 使 用 。 默 认 情 况 下 ， 参 数 可 为 空 。 如 果 传

25、递 NULL 参 数 值 并 且 该 参 数 在 CREATE 或 ALTER TABLE 语 句 中 使 用 ， 而 该 语 句 中 引 用 的 列 又 不 允 许 使 用 NULL， 则 SQL Server 会 产 生 一 条 错 误 信 息 。 为 了 防 止 向 不 允 许 使 用 NULL 的 列 传 递 NULL 参 数值 ， 应 向 过 程 中 添 加 编 程 逻 辑 或 为 该 列 使 用 默 认 值 （ 使 用 CREATE 或 ALTER TABLE 的 DEFAULT 关 键 字 )。 建 议 在 存 储 过 程 的 任 何 CREATE TABLE 或 ALTER T

26、ABLE 语 句 中 都 为 每 列 显式 指 定 NULL 或 NOT NULL， 例 如 在 创 建 临 时 表 时 。 ANSI_DFLT_ON 和 ANSI_DFLT_OFF 选 项 控 制 SQL Server 为 列 指 派 NULL 或 NOT NULL 特 性 的 方式 （ 如 果 在 CREATE TABLE 或 ALTER TABLE 语 句 中 没 有 指 定 的 话 ） 。 如 果 某 个连 接 执 行 的 存 储 过 程 对 这 些 选 项 的 设 置 与 创 建 该 过 程 的 连 接 的 设 置 不 同 ， 则 为 第 二 个 连接 创 建 的 表 列 可 能 会

27、 有 不 同 的 为 空 性 ， 并 且 表 现 出 不 同 的 行 为 方 式 。 如 果 为 每 个 列 显 式声 明 了 NULL 或 NOT NULL， 那 么 将 对 所 有 执 行 该 存 储 过 程 的 连 接 使 用 相 同 的 为 空性 创 建 临 时 表 。 在 创 建 或 更 改 存 储 过 程 时 ， SQL Server 将 保 存 SET QUOTED_IDENTIFIER 和 SET ANSI_NULLS 的 设 置 。 执 行 存 储 过 程 时 ， 将 使 用 这 些 原 始 设 置 。 因 此 ， 所 有 客 户端 会 话 的 SET QUOTED_IDEN

28、TIFIER 和 SET ANSI_NULLS 设 置 在 执 行 存 储 过 程时 都 将 被 忽 略 。 在 存 储 过 程 中 出 现 的 SET QUOTED_IDENTIFIER 和 SET ANSI_NULLS 语 句 不 影 响 存 储 过 程 的 功 能 。 其 它 SET 选 项 （ 例 如 SET ARITHABORT、 SET ANSI_WARNINGS 或 SET ANSI_PADDINGS） 在 创 建 或 更 改 存 储 过 程 时 不 保 存 。 如 果 存 储 过 程 的 逻 辑 取 决 于 特 定的 设 置 ， 应 在 过 程 开 头 添 加 一 条 SET

29、语 句 ， 以 确 保 设 置 正 确 。 从 存 储 过 程 中 执 行 SET 语 句 时 ， 该 设 置 只 在 存 储 过 程 完 成 之 前 有 效 。 之 后 ， 设 置 将 恢 复 为 调 用 存 储 过 程时 的 值 。 这 使 个 别 的 客 户 端 可 以 设 置 所 需 的 选 项 ， 而 不 会 影 响 存 储 过 程 的 逻 辑 。 说 明 SQL Server 是 将 空 字 符 串 解 释 为 单 个 空 格 还 是 解 释 为 真 正 的 空 字 符 串 ， 由兼 容 级 别 设 置 控 制 。 如 果 兼 容 级 别 小 于 或 等 于 65， SQL Ser

30、ver 就 将 空 字 符 串 解 释 为单 个 空 格 。 如 果 兼 容 级 别 等 于 70， 则 SQL Server 将 空 字 符 串 解 释 为 空 字 符 串 。 有关 更 多 信 息 ， 请 参 见 sp_dbcmptlevel。 获 得 有 关 存 储 过 程 的 信 息 若 要 显 示 用 来 创 建 过 程 的 文 本 ， 请 在 过 程 所 在 的 数 据 库 中 执 行 sp_helptext， 并使 用 过 程 名 作 为 参 数 。 说 明 使 用 ENCRYPTION 选 项 创 建 的 存 储 过 程 不 能 使 用 sp_helptext 查 看 。 若

31、要 显 示 有 关 过 程 引 用 的 对 象 的 报 表 ， 请 使 用 sp_depends。 若 要 为 过 程 重 命 名 ， 请 使 用 sp_rename。 执 行 存 储 过 程成 功 执 行 CREATE PROCEDURE 语 句 后 ， 过 程 名 称 将 存 储 在 sysobjects 系 统表 中 ， 而 CREATE PROCEDURE 语 句 的 文 本 将 存 储 在 syscomments 中 。 第 一 次 执行 时 ， 将 编 译 该 过 程 以 确 定 检 索 数 据 的 最 佳 访 问 计 划 。 使 用 EXECUTE 执 行 存 储 过 程 。临

32、时 存 储 过 程SQL Server 支 持 两 种 临 时 过 程 ： 局 部 临 时 过 程 和 全 局 临 时 过 程 。 局 部 临 时 过 程 只能 由 创 建 该 过 程 的 连 接 使 用 。 全 局 临 时 过 程 则 可 由 所 有 连 接 使 用 。 局 部 临 时 过 程 在 当 前会 话 结 束 时 自 动 除 去 。 全 局 临 时 过 程 在 使 用 该 过 程 的 最 后 一 个 会 话 结 束 时 除 去 。 通 常 是在 创 建 该 过 程 的 会 话 结 束 时 。 临 时 过 程 用 # 和 # 命 名 ， 可 以 由 任 何 用 户 创 建 。 创 建

33、 过 程 后 ， 局 部 过 程 的 所 有者 是 唯 一 可 以 使 用 该 过 程 的 用 户 。 执 行 局 部 临 时 过 程 的 权 限 不 能 授 予 其 他 用 户 。 如 果 创建 了 全 局 临 时 过 程 ， 则 所 有 用 户 均 可 以 访 问 该 过 程 ， 权 限 不 能 显 式 废 除 。 只 有 在 tempdb 数 据 库 中 具 有 显 式 CREATE PROCEDURE 权 限 的 用 户 ， 才 可 以 在 该 数 据 库中 显 式 创 建 临 时 过 程 （ 不 使 用 编 号 符 命 名 ） 。 可 以 授 予 或 废 除 这 些 过 程 中 的

34、权 限 。 示 例A. 使 用 带 有 复 杂 SELECT 语 句 的 简 单 过 程 下 面 的 存 储 过 程 从 四 个 表 的 联 接 中 返 回 所 有 作 者 （ 提 供 了 姓 名 ） 、 出 版 的 书 籍 以及 出 版 社 。 该 存 储 过 程 不 使 用 任 何 参 数 。 USE pubs IF EXISTS (SELECT name FROM sysobjects WHERE name = au_info_all AND type = P) DROP PROCEDURE au_info_all GO CREATE PROCEDURE au_info_all AS S

35、ELECT au_lname, au_fname, title, pub_name FROM authors a INNER JOIN titleauthor ta ON a.au_id = ta.au_id INNER JOIN titles t ON t.title_id = ta.title_id INNER JOIN publishers p ON t.pub_id = p.pub_id GO au_info_all 存 储 过 程 可 以 通 过 以 下 方 法 执 行 ： EXECUTE au_info_all - Or EXEC au_info_all 如 果 该 过 程 是 批

36、 处 理 中 的 第 一 条 语 句 ， 则 可 使 用 ： au_info_all B. 使 用 带 有 参 数 的 简 单 过 程 下 面 的 存 储 过 程 从 四 个 表 的 联 接 中 只 返 回 指 定 的 作 者 （ 提 供 了 姓 名 ） 、 出 版 的 书籍 以 及 出 版 社 。 该 存 储 过 程 接 受 与 传 递 的 参 数 精 确 匹 配 的 值 。 USE pubs IF EXISTS (SELECT name FROM sysobjects WHERE name = au_info AND type = P) DROP PROCEDURE au_info GO

37、USE pubs GO CREATE PROCEDURE au_info lastname varchar(40), firstname varchar(20) AS SELECT au_lname, au_fname, title, pub_name FROM authors a INNER JOIN titleauthor ta ON a.au_id = ta.au_id INNER JOIN titles t ON t.title_id = ta.title_id INNER JOIN publishers p ON t.pub_id = p.pub_id WHERE au_fname

38、= firstname AND au_lname = lastname GO au_info 存 储 过 程 可 以 通 过 以 下 方 法 执 行 ： EXECUTE au_info Dull, Ann - Or EXECUTE au_info lastname = Dull, firstname = Ann - Or EXECUTE au_info firstname = Ann, lastname = Dull - Or EXEC au_info Dull, Ann - Or EXEC au_info lastname = Dull, firstname = Ann - Or EXEC

39、au_info firstname = Ann, lastname = Dull 如 果 该 过 程 是 批 处 理 中 的 第 一 条 语 句 ， 则 可 使 用 ： au_info Dull, Ann - Or au_info lastname = Dull, firstname = Ann - Or au_info firstname = Ann, lastname = Dull C. 使 用 带 有 通 配 符 参 数 的 简 单 过 程 下 面 的 存 储 过 程 从 四 个 表 的 联 接 中 只 返 回 指 定 的 作 者 （ 提 供 了 姓 名 ） 、 出 版 的 书籍 以 及

40、 出 版 社 。 该 存 储 过 程 对 传 递 的 参 数 进 行 模 式 匹 配 ， 如 果 没 有 提 供 参 数 ， 则 使 用 预设 的 默 认 值 。 USE pubs IF EXISTS (SELECT name FROM sysobjects WHERE name = au_info2 AND type = P) DROP PROCEDURE au_info2 GO USE pubs GO CREATE PROCEDURE au_info2 lastname varchar(30) = D%, firstname varchar(18) = % AS SELECT au_lna

41、me, au_fname, title, pub_name FROM authors a INNER JOIN titleauthor ta ON a.au_id = ta.au_id INNER JOIN titles t ON t.title_id = ta.title_id INNER JOIN publishers p ON t.pub_id = p.pub_id WHERE au_fname LIKE firstname AND au_lname LIKE lastname GO au_info2 存 储 过 程 可 以 用 多 种 组 合 执 行 。 下 面 只 列 出 了 部 分

42、 组 合 ： EXECUTE au_info2 - Or EXECUTE au_info2 Wh% - Or EXECUTE au_info2 firstname = A% - Or EXECUTE au_info2 CKarsOEn - Or EXECUTE au_info2 Hunter, Sheryl - Or EXECUTE au_info2 H%, S% D. 使 用 OUTPUT 参 数 OUTPUT 参 数 允 许 外 部 过 程 、 批 处 理 或 多 条 Transact-SQL 语 句 访 问 在 过 程 执行 期 间 设 置 的 某 个 值 。 下 面 的 示 例 创 建

43、 一 个 存 储 过 程 (titles_sum)， 并 使 用 一 个 可 选的 输 入 参 数 和 一 个 输 出 参 数 。 首 先 ， 创 建 过 程 ： USE pubs GO IF EXISTS(SELECT name FROM sysobjects WHERE name = titles_sum AND type = P) DROP PROCEDURE titles_sum GO USE pubs GO CREATE PROCEDURE titles_sum TITLE varchar(40) = %, SUM money OUTPUT AS SELECT Title Name

44、= title FROM titles WHERE title LIKE TITLE SELECT SUM = SUM(price) FROM titles WHERE title LIKE TITLE GO 接 下 来 ， 将 该 OUTPUT 参 数 用 于 控 制 流 语 言 。 说 明 OUTPUT 变 量 必 须 在 创 建 表 和 使 用 该 变 量 时 都 进 行 定 义 。 参 数 名 和 变 量 名 不 一 定 要 匹 配 ， 不 过 数 据 类 型 和 参 数 位 置 必 须 匹 配 （ 除 非 使 用 SUM = variable 形 式 ） 。 DECLARE TOTA

45、LCOST money EXECUTE titles_sum The%, TOTALCOST OUTPUT IF TOTALCOST 200 BEGIN PRINT PRINT All of these titles can be purchased for less than $200. END ELSE SELECT The total cost of these titles is $ + RTRIM(CAST(TOTALCOST AS varchar(20) 下 面 是 结 果 集 ： Title Name - The Busy Executives Database Guide Th

46、e Gourmet Microwave The Psychology of Computer Cooking (3 row(s) affected) Warning, null value eliminated from aggregate. All of these titles can be purchased for less than $200. E. 使 用 OUTPUT 游 标 参 数 OUTPUT 游 标 参 数 用 来 将 存 储 过 程 的 局 部 游 标 传 递 回 调 用 批 处 理 、 存 储 过 程 或 触发 器 。 首 先 ， 创 建 以 下 过 程 ， 在 tit

47、les 表 上 声 明 并 打 开 一 个 游 标 ： USE pubs IF EXISTS (SELECT name FROM sysobjects WHERE name = titles_cursor and type = P) DROP PROCEDURE titles_cursor GO CREATE PROCEDURE titles_cursor titles_cursor CURSOR VARYING OUTPUT AS SET titles_cursor = CURSOR FORWARD_ONLY STATIC FOR SELECT * FROM titles OPEN titl

48、es_cursor GO 接 下 来 ， 执 行 一 个 批 处 理 ， 声 明 一 个 局 部 游 标 变 量 ， 执 行 上 述 过 程 以 将 游 标 赋 值 给局 部 变 量 ， 然 后 从 该 游 标 提 取 行 。 USE pubs GO DECLARE MyCursor CURSOR EXEC titles_cursor titles_cursor = MyCursor OUTPUT WHILE (FETCH_STATUS = 0) BEGIN FETCH NEXT FROM MyCursor END CLOSE MyCursor DEALLOCATE MyCursor GO F. 使 用 WITH RECOMPILE 选 项 如 果 为 过 程 提 供 的 参 数 不 是 典 型 的 参 数 ， 并 且 新 的 执 行 计 划 不 应 高 速 缓 存 或 存 储 在内 存 中 ， WITH RECOMPILE 子 句 会 很 有 帮 助 。 USE pubs IF EXISTS (SELECT name FROM sysobjects WHERE name = titles_by_author AND type = P) DROP PROCEDURE titles