SQL课件存储过程和触发器

《SQL课件存储过程和触发器》由会员分享，可在线阅读，更多相关《SQL课件存储过程和触发器（24页珍藏版）》请在装配图网上搜索。

1、上 页返 回n 教 学 提 示 ： 开 发 中 编 写 的 一 些 SQL语 句 会 占 用 程 序 的 很 大篇 幅 ， 而 且 不 便 于 在 其 他 地 方 重 用 ， 且 由 于 这 些 SQL语 句通 常 还 要 跨 越 传 输 途 径 从 外 部 不 仅 会 导 致 程 序 的 运 行 效 率低 ， 还 会 产 生 安 全 隐 患 ， 而 存 储 过 程 则 能 克 服 以 上 的 缺 陷 。 触 发 器 可 以 大 大 增 强 应 用 程 序 的 健 壮 性 、 数 据 库 可 恢 复 性和 数 据 库 的 可 管 理 性 。 存 储 过 程 和 触 发 器 都 是 SQL Se

2、rver的 数 据 库 对 象 。 存 储 过程 的 存 在 独 立 于 表 ， 它 存 放 在 服 务 器 上 ， 供 客 户 端 调 用 。 上 页返 回 n 教 学 目 标 ： 本 章 主 要 包 含 以 下 内 容 。存 储 过 程 概 述设 计 存 储 过 程实 现 和 管 理 存 储 过 程触 发 器 概 述设 计 触 发 器实 现 和 管 理 触 发 器 通 过 本 章 的 学 习 ， 使 读 者 掌 握 存 储 过 程 和 触 发 器 的 基 础 知 识 ，并 基 本 学 会 编 写 简 单 的 存 储 过 程 和 触 发 器 ， 为 以 后 在 实 际应 用 中 不 断 提

3、高 自 己 编 写 存 储 过 程 和 触 发 器 的 技 能 打 下 良好 的 基 础 。 上 页返 回n 10.1 存 储 过 程 概 述n 10.2 设 计 存 储 过 程n 10.3 实 现 和 管 理 存 储 过 程n 10.4 触 发 器 概 述n 10.5 触 发 器 设 计 规 则n 10.6 实 现 和 管 理 触 发 器 上 页返 回 10.1 存 储 过 程 概 述10.1.1 存 储 过 程 的 特 点 能 够 在 单 个 存 储 过 程 中 执 行 一 系 列 的 Transact-SQL语 句 ，能 够 以 在 一 个 存 储 过 程 中 调 用 其 他 的 存 储

4、 过 程 。存 储 过 程 是 保 存 在 服 务 器 端 的 已 经 编 译 的 Transact-SQL语 句 。存 储 过 程 可 以 使 用 控 制 流 语 句 和 变 量 ， 大 大 增 强 了 SQL的 功 能 。存 储 过 程 在 提 交 前 会 自 动 检 查 语 法 。存 储 过 程 是 管 理 员 放 在 服 务 器 端 的 Transact-SQL语 句 。 上 页返 回 10.1.2 存 储 过 程 的 分 类1. 用 户 存 储 过 程2. 系 统 存 储 过 程3. 扩 展 存 储 过 程 上 页返 回 10.2 设 计 存 储 过 程10.2.1 一 些 设 计

5、规 则 用 户 在 设 计 数 据 库 (的 存 储 过 程 )时 ， 应 遵 守 以 下 规 则 ： 在 SQL Server 2005中 ， 存 储 过 程 可 以 使 用 Transact-SQL中 的 任 何 语 句 ， 但 是 表 10.1中 的 语 句 除 外 。 略 上 页返 回 10.2.2 存 储 过 程 的 内 部 命 名 规 范 化 存 储 过 程 中 的 Transact-SQL语 句 使 用 的 对 象 名 称 必 须 符 合规 范 的 模 式 ， 如 果 未 指 明 对 象 所 属 的 模 式 ， 存 储 过 程 将 使 用当 前 默 认 的 模 式 。 每 个 存

6、 储 过 程 必 须 有 惟 一 的 名 称 ， 存 储 过 程 中 使 用 的 变 量类 型 必 须 符 合 Transact-SQL的 要 求 ， 变 量 名 必 须 以 开 头 ，并 且 存 储 过 程 的 名 称 和 变 量 的 命 名 要 符 合 Transact-SQL标 识符 规 则 。 上 页返 回 10.2.3 存 储 过 程 的 加 密 定 义 如 果 用 户 想 定 义 一 个 不 让 其 他 用 户 看 到 具 体 操 作 的 存 储过 程 ， 可 以 使 用 WITH ENCRYPTION参 数 ， 那 么 存 储 过 程 将 会以 不 可 读 的 状 态 存 在 ，

7、 并 且 存 储 过 程 拥 有 者 、 创 建 者 和 数 据库 管 理 员 都 不 可 见 。 上 页返 回 10.3 实 现 和 管 理 存 储 过 程10.3.1 创 建 存 储 过 程 由 于 必 须 使 用 Transact-SQL语 句 创 建 存 储 过 程 ， 所 以 用 户必 须 首 先 掌 握 创 建 存 储 过 程 的 Transact-SQL语 法 结 构 ， 然 后 再学 习 如 何 使 用 Management Studio创 建 存 储 过 程 。 上 页返 回 10.3.2 执 行 存 储 过 程执 行 存 储 过 程 有 多 种 方 式 ， 比 较 常 用

8、的 有 以 下 几 种 。1. 通 过 Execute或 Exec语 句 执 行2. 通 过 设 置 ， 使 存 储 过 程 自 动 执 行3. 作 为 批 处 理 的 第 一 行 ， 直 接 输 入 存 储 过 程 名 上 页返 回 10.3.3 修 改 存 储 过 程1. 修 改 存 储 过 程 的 语 法注 意 ： 修 改 存 储 过 程 的 名 称 会 影 响 已 关 联 对 象 对 此 存 储 过 程的 调 用 。2. 使 用 Management Studio修 改 存 储 过 程 上 页返 回 10.3.4 重 新 编 译 存 储 过 程 1.sp_recompile系 统 存

9、储 过 程 可 以 强 制 指 定 的 存 储 过 程 在 下 次调 用 时 重 新 编 译 。 其 调 用 的 语 法 结 构 为 ： sp_recompile objname = object， 其 中 的 object为 存 储 过 程 的 名称 。 2.在 创 建 存 储 过 程 时 使 用 WITH RECOMPILE参 数 项 ， SQL Server将 不 会 把 此 存 储 过 程 放 在 缓 冲 存 储 器 中 ， 并 且 每 次 调用 此 存 储 过 程 时 都 会 重 新 编 译 。 3.在 调 用 存 储 过 程 时 ， 可 以 使 用 参 数 选 项 WITH REC

10、OMPILE强 制重 新 编 译 。 使 用 这 个 参 数 项 要 求 存 储 过 程 的 参 数 必 须 是 非 典型 的 或 者 数 据 发 生 了 很 大 的 变 化 ， 否 则 一 般 不 用 它 。 上 页返 回 10.3.5 删 除 存 储 过 程 如 果 一 个 存 储 过 程 不 再 需 要 ， 用 户 就 可 以 删 除 它 ， 但 是 如果 此 存 储 过 程 有 关 联 存 储 过 程 调 用 ， 系 统 会 返 回 错 误 信 息 。但 是 删 除 存 储 过 程 后 ， 用 户 重 新 定 义 的 同 名 称 同 参 数 的 存 储过 程 可 以 被 原 来 关 联

11、 的 对 象 使 用 。删 除 存 储 过 程 的 Transact-SQL语 法 结 构 为 ：DROP PROCEDURE schema_name. procedure ,.n 上 页返 回 10.4 触 发 器 概 述 触 发 器 是 一 种 特 殊 类 型 的 存 储 过 程 ， 这 种 存 储 过 程 能 够 在某 个 规 定 的 事 件 发 生 时 触 发 执 行 。 触 发 器 通 常 可 以 强 制 执 行一 定 的 业 务 规 则 ， 以 保 持 数 据 完 整 性 、 检 查 数 据 有 效 性 、 实现 数 据 库 管 理 任 务 和 一 些 附 加 的 功 能 。 触

12、发 器 是 数 据 库 中 比 较 高 级 的 应 用 ， 灵 活 使 用 触 发 器 可 以大 大 增 强 应 用 程 序 的 健 壮 性 、 数 据 库 的 可 恢 复 性 和 数 据 库 的可 管 理 性 。 同 时 可 以 使 开 发 人 员 和 数 据 库 管 理 员 实 现 一 些 复杂 的 功 能 ， 简 化 开 发 步 骤 ， 降 低 开 发 成 本 ， 增 加 开 发 效 率 ，提 高 数 据 库 的 可 靠 性 。 上 页返 回 10.4.1 触 发 器 的 特 点 触 发 器 是 一 种 特 殊 的 存 储 过 程 ， 除 了 存 储 过 程 的 特 点 外 ，它 还 另

13、 外 有 以 下 特 点 ：触 发 器 是 自 动 执 行 的 ， 可 以 在 一 定 条 件 下 触 发 。触 发 器 可 以 同 步 数 据 库 的 相 关 表 ， 进 行 级 联 更 改 。触 发 器 可 以 实 现 更 复 杂 的 安 全 检 查 。 它 可 以 实 现 比 CHECK更 复 杂 的 业 务 规 则 ， 还 可 以 引 用 其 他 表 中 的 列 。触 发 器 可 以 实 现 数 据 库 的 管 理 任 务 。 如 DDL触 发 器 ， 在DDL语 句 执 行 后 触 发 ， 可 以 实 现 一 些 统 一 的 数 据 库 管 理策 略 。 上 页返 回 10.4.2

14、触 发 器 的 种 类1. DML触 发 器 DML触 发 器 是 在 执 行 数 据 操 作 语 言 事 件 时 被 调 用 的 触 发 器 ，其 中 数 据 操 作 语 言 事 件 包 括 ： INSERT、 UPDATE和 DELETE语 句 。触 发 器 中 可 以 包 含 复 杂 的 Transact-SQL语 句 ， 触 发 器 整 体 被看 作 一 个 事 务 ， 可 以 回 滚 。2. DDL触 发 器 与 DML触 发 器 类 似 ， 与 DML不 同 的 是 ， 它 相 应 的 触 发 事 件是 由 数 据 定 义 语 言 引 起 的 事 件 ， 包 括 ： CREATE、

15、 ALTER和DROP语 句 ， DDL触 发 器 用 于 执 行 数 据 库 管 理 任 务 ， 如 调 节 和审 计 数 据 库 运 转 。 DDL触 发 器 只 能 在 触 发 事 件 发 生 后 才 会 调用 执 行 ， 即 它 只 能 是 AFTER类 型 的 。 上 页返 回 10.4.3 inserted表 和 deleted表 触 发 器 中 两 个 比 较 特 殊 的 对 象 ： inserted表 和 deleted表 。 在 使 用 触 发 器 过 程 中 ， SQL Server使 用 到 了 两 张 特 殊 的临 时 表 ， 分 别 是 inserted表 和 del

16、eted表 。 这 两 张 表 都 存 在于 高 速 缓 存 中 (当 然 ， 如 果 内 存 不 够 用 ， 也 可 能 存 储 在 硬 盘上 )， 实 际 上 是 事 务 日 志 的 视 图 ， 它 们 与 创 建 了 触 发 器 的 表有 着 相 同 的 结 构 。 上 页返 回 10.5 触 发 器 设 计 规 则10.5.1 确 定 触 发 器 的 种 类 首 先 用 户 应 根 据 相 应 的 触 发 事 件 确 定 到 底 是 DML触 发 器 还是 DDL触 发 器 ， 如 果 是 DML触 发 器 ， 用 户 在 要 根 据 数 据 操 作 类型 确 定 是 INSERT、

17、UPDATE还 是 DELETE触 发 器 ， 然 后 再 根 据 约束 规 则 ， 是 希 望 触 发 事 件 发 生 后 还 是 发 生 前 来 触 发 执 行 触 发器 ， 从 而 确 定 是 AFTER还 是 INSTEAD OF类 型 的 触 发 器 。 对 于 DDL触 发 器 ， 它 只 有 AFTER一 种 类 型 ， 只 需 根 据 触 发 事件 的 类 型 确 定 触 发 器 ， 来 做 出 相 应 的 响 应 。 上 页返 回10.5.2 确 定 触 发 器 的 响 应 范 围 对 于 DML触 发 器 ， 其 响 应 范 围 是 在 数 据 库 范 围 中 ， 我 们

18、无需 讨 论 。 而 对 于 DDL触 发 器 ， 就 需 要 确 定 它 的 响 应 范 围 是 数据 库 级 的 还 是 服 务 器 级 的 响 应 。 在 此 只 列 出 了 SQL Server 2005中 服 务 器 级 的 DDL触 发 事 件 ， 如 表 10.2所 示 ， 其 他 的 DDL事 件 都 是 数 据 库 级 的 。 上 页返 回 10.5.3 一 些 设 计 规 则在 设 计 触 发 器 时 ， 用 户 可 以 参 考 以 下 的 设 计 规 则 ：DML触 发 器 的 实 现 者 是 表 的 默 认 拥 有 者 ， 权 限 不 能 转 移 给 别 的 用户 。D

19、ML触 发 器 必 须 是 在 当 前 数 据 库 上 创 建 ， 尽 管 它 可 以 引 用 别 的 数据 库 。不 能 对 系 统 表 和 临 时 表 创 建 触 发 器 。每 个 表 可 以 有 多 个 不 同 名 称 的 AFTER触 发 器 ， 但 每 种 触 发 事 件 只能 有 一 个 INSTEAD OF类 型 触 发 器 。触 发 器 只 能 创 建 在 表 或 者 视 图 的 模 式 中 。 上 页返 回 10.6 实 现 和 管 理 触 发 器10.6.1 创 建 触 发 器1. 创 建 DML触 发 器 的 语 法2. 创 建 DDL触 发 器 的 语 法3. 创 建

20、DML触 发 器 的 步 骤4. 创 建 作 用 范 围 为 数 据 库 的 DDL触 发 器 的 步 骤5. 创 建 作 用 范 围 为 服 务 器 的 DDL触 发 器 上 页返 回 10.6.2 更 改 触 发 器 当 一 个 触 发 器 必 须 修 改 时 ， 用 户 可 以 先 删 除 此 触 发 器 然后 重 建 新 的 触 发 器 ， 也 可 以 使 用 Transact-SQL语 句 对 它 进 行修 改 。 修 改 DML和 DDL触 发 器 的 语 法 结 构 与 创 建 它 们 的 语 法 结构 类 似 ， 除 了 使 用 的 开 始 关 键 词 变 为 ALTER和 在

21、 修 改 DML触 发器 时 不 能 使 用 WITH APPEND参 数 选 项 外 ， 其 他 语 法 结 构 都 相同 ， 在 此 我 们 不 再 给 出 详 细 的 语 法 列 表 。 在 Microsoft SQL Server Management Studio中 更 改 触发 器 的 具 体 步 骤 如 下 。 略 上 页返 回 10.6.3 删 除 触 发 器 和 停 止 触 发 器 当 一 个 触 发 器 不 再 使 用 时 ， 用 户 可 以 删 除 它 ， 也 可 以 停 止使 用 它 ， 停 止 使 用 的 触 发 器 没 有 被 删 除 ， 仍 然 保 存 在 数 据

22、 库中 ， 当 用 户 想 要 使 用 的 时 候 还 可 以 激 活 它 ， 新 创 建 的 触 发 器默 认 为 激 活 状 态 。1. 使 用 Management Studio删 除 触 发 器2. 使 用 Transact-SQL删 除 触 发 器 和 停 止 (disable)触 发 器 上 页返 回 10.6.4 激 活 触 发 器 对 于 停 止 的 触 发 器 ， 用 户 可 以 重 新 激 活 。 激 活 后 的 触 发器 就 会 在 触 发 条 件 下 正 常 运 行 。激 活 触 发 器 的 Trasact-SQL语 法 如 下 ： ENABLE TRIGGER schema_name . trigger_name ,.n | ALL ON object_name | DATABASE | ALL SERVER ;