Oracle SQL高级编程（由资深Oracle专家撰写，OakTable团队推荐，并附带源代码）

简介：
CruiseYoung精心制作的电子书籍目录，其中包含详尽的书签功能。 该资料包含《Oracle SQL高级编程》中所提供的源代码。 相关书籍的详细信息请参考以下资源。 凭借资深Oracle专家的精心创作以及OakTable团队的鼎力推荐，本书汇集了Oracle SQL高级编程的精髓。 该书籍的正式名称为《Pro Oracle SQL》，由Apress出版社出版。作者团队包括Karen Morton、Kerry Osborne、Robyn Sands、Riyaj Shamsudeen和Jared Still。 译者为朱浩波。 这本书属于“图灵程序设计丛书”，由人民邮电出版社出版，并获得了ISBN码9787115266149。 该书籍于2011年11月9日上架，出版日期为2011年11月。 其装订形式为16开，总页数达到502页，目前是第一版。 凭借资深Oracle专家的精心策划和OakTable团队的鼎力支持，本书籍呈现出令人印象深刻的成果。内容涵盖广泛且深入，视角独特且透彻，提供了极为详尽的信息。本书尤其适合Oracle开发人员以及数据库管理员阅读，被誉为他们不可或缺的参考资料。 本书深入探讨了Oracle数据库中的SQL，它被认为是当前市场上最先进的SQL解决方案之一。为了帮助更多人有效地学习和熟练掌握这一工具，Karen Morton及其团队精心设计了本书的内容：首先，读者将掌握SQL语言的核心特性，随后学习Oracle为了提高语言效率而添加的支持功能，最后将两者相结合并应用于实际工作场景。作者凭借其在软件开发和教学培训领域的多年经验，分享了掌握Oracle SQL所独具的丰富技巧和实用方法，内容涵盖了SQL执行、连接、集合、分析函数、子句以及事务处理等诸多方面。读者将能够学习到以下关键技能： 探索并理解Oracle数据库中独特的SQL强大功能； 解析并优化执行计划以提升SQL性能； 利用提示和配置文件等手段来精确控制执行计划； 在程序中实现查询优化，而无需对代码进行修改。 作为一门经典的Oracle SQL著作，本书为SQL开发人员提供了清晰的前行方向和持续发展的动力。 作者介绍 KAREN MORTON 是一位研究人员、教育家以及顾问，同时也是Fidelity信息服务公司的资深数据库管理员和性能优化专家。她自20世纪90年代初起便开始运用Oracle技术，并且在Oracle教学方面积累了超过十年的经验。作为一名Oracle ACE，她还积极参与OakTable（即Oracle社区中备受推崇的“Oracle科学家”非正式组织）的活动，并经常在各类技术会议上进行专业演讲。她的学术成果包括《Expert Oracle Practices》和《Beginning Oracle SQL》，其个人博客地址为karenmorton.blogspot.com。 KERRY OSBORNE 是一位专注于Oracle咨询服务的Enkitec公司创始人之一。他自1982年起便开始使用Oracle（第二版），并曾担任开发人员和DBA。目前，他担任Oracle ACE总监以及OakTable成员，近年来主要致力于深入研究Oracle内部机制，并专注于解决性能相关问题。他的个人博客地址为 kerryosborne.oracle-guy.com。 ROBYN SANDS 作为思科公司的软件工程师，致力于为思科客户设计和开发嵌入式Oracle数据库产品。凭借从1996年开始使用Oracle积累的丰富经验，她在应用开发、大型系统构建以及性能评估等领域都表现出卓越的能力。她同时也是OakTable的成员，并且是《Expert Oracle Practices》（2010年Apress出版社出版）一书的合著者。 RIYAJ SHAMSUDEEN 是一位专注于性能数据恢复电子商务咨询服务的OraInternals公司的首席数据库管理员和董事长。他拥有近二十年的使用Oracle技术产品以及担任Oracle数据库管理员和应用管理员的宝贵经验，并且在真正应用集群、性能调优以及数据库内部属性方面的知识达到了专家级别。此外，他还是一位活跃的演讲家及一位获得认可的Oracle ACE。 JARED STILL 从1994年就开始使用Oracle技术。他坚信SQL的学习是一个持续不断的过程，并认为每一个与Oracle数据库交互的人都应该精通SQL语言以编写高效且优化的查询语句。他参与本书编写正是为了帮助他人实现这一目标。 目录 封面 -11 封底 -10 扉页 -9 版权 -8 版权声明 -7 致谢 -6 目录 -5 第1章 SQL核心 1 1.1 SQL语言 1 1.2 数据库的接口 2 1.3 SQL*Plus 回顾 3 1.3.1 连接到数据库 3 1.3.2 配置SQL*Plus环境 4 1.3.3 执行命令 6 1.4 5 个核心的SQL语句 8 1.5 SELECT语句 8 1.5.1 FROM子句 9 1.5.2 WHERE子句 11 1.5.3 GROUP BY子句 11 1.5.4 HAVING子句 12 1.5.5 SELECT列表 12 1.5.6 ORDERBY子句 13 1.6 INSERT语句 14 1.6.1 单表插入 14 1.6.2 多表插入 15 1.7 UPDATE语句 17 1.8 DELETE语句 20 1.9 MERGE语句 22 1.10 小结 24 第2章 SQL执行 25 2.1 Oracle架构基础 25 2.2 SGA-共享池 27 2.3 库高速缓存 28 2.4 完全相同的语句 29 2.5 SGA-缓冲区缓存 32 2.6 查询转换 35 2.7 视图合并 36 2.8 子查询解嵌套 39 2.9 谓语前推 42 2.10 使用物化视图进行查询重写 44 2.11 确定执行计划 46 2.12 执行计划并取得数据行 50 2.13 SQL执行——总览 52 2.14 小结 53 第3章 访问和联结方法 55 3.1 全扫描访问方法 55 3.1.1 如何选择全扫描操作 56 3.1.2 全扫描与舍弃 59 3.1.3 全扫描与多块读取 60 3.1.4 全扫描与高水位线 60 3.2 索引扫描访问方法 65 3.2.1 索引结构 66 3.2.2 索引扫描类型 68 3.2.3 索引唯一扫描 71 3.2.4 索引范围扫描 72 3.2.5 索引全扫描 74 3.2.6 索引跳跃扫描 77 3.2.7 索引快速全扫描 79 3.3 联结方法 80 3.3.1 嵌套循环联结 81 3.3.2 排序-合并联结 83 3.3.3 散列联结 84 3.3.4 笛卡儿联结 87 3.3.5 外联结 88 3.4 小结 94 第4章 SQL是关于集合的 95 4.1 以面向集合的思维方式来思考 95 4.1.1 从面向过程转变为基于集合的思维方式 96 4.1.2 面向过程vs.基于集合的思维方式：一个例子 100 4.2 集合运算 102 4.2.1 UNION和UNION ALL 103 4.2.2 MINUS 106 4.2.3 INTERSECT 107 4.3 集合与空值 108 4.3.1 空值与非直观结果 108 4.3.2 集合运算中的空值行为 110 4.3.3 空值与GROUP BY和ORDER BY 112 4.3.4 空值与聚合函数 114 4.4 小结 114 第5章 关于问题 116 5.1 问出好的问题 116 5.2 提问的目的 117 5.3 问题的种类 117 5.4 关于问题的问题 119 5.5 关于数据的问题 121 5.6 建立逻辑表达式 126 5.7 小结 136 第6章 SQL执行计划 137 6.1 解释计划 137 6.1.1 使用解释计划 137 6.1.2 理解解释计划可能达不到目的的方式 143 6.1.3 阅读计划 146 6.2 执行计划 148 6.2.1 查看最近生成的SQL语句 149 6.2.2 查看相关执行计划 149 6.2.3 收集执行计划统计信息 151 6.2.4 标识SQL语句以便以后取回计划 153 6.2.5 深入理解DBMS_XPLAN的细节 156 6.2.6 使用计划信息来解决问题 161 6.3 小结 169 第7章 高级分组 170 7.1 基本的GROUP BY用法 171 7.2 HAVING子句 174 7.3 GROUP BY的“新”功能 175 7.4 GROUP BY的CUBE扩展 175 7.5 CUBE的实际应用 179 7.6 通过GROUPING()函数排除空值 185 7.7 用GROUPING()来扩展报告 186 7.8 使用GROUPING_ID()来扩展报告 187 7.9 GROUPING SETS与ROLLUP() 191 7.10 GROUP BY局限性 193 7.11 小结 196 第8章 分析函数 197 8.1 示例数据 197 8.2 分析函数剖析 198 8.3 函数列表 199 8.4 聚合函数 200 8.4.1 跨越整个分区的聚合函数 201 8.4.2 细粒度窗口声明 201 8.4.3 默认窗口声明 202 8.5 Lead和Lag 202 8.5.1 语法和排序 202 8.5.2 例1：从前一行中返回一个值 203 8.5.3 理解数据行的位移 204 8.5.4 例2：从下一行中返回一个值 204 8.6 First_value和Last_value 205 8.6.1 例子：使用First_value来计算最大值 206 8.6.2 例子：使用Last_value来计算最小值 207 8.7 其他分析函数 207 8.7.1 Nth_value(11gR2) 207 8.7.2 Rank 209 8.7.3 Dense_rank 210 8.7.4 Row_number 211 8.7.5 Ratio_to_report 211 8.7.6 Percent_rank 212 8.7.7 Percentile_cont 213 8.7.8 Percentile_disc 215 8.7.9 NTILE 215 8.7.10 Stddev 216 8.7.11 Listagg 217 8.8 性能调优 218 8.8.1 执行计划 218 8.8.2 谓语 219 8.8.3 索引 220 8.9 高级话题 221 8.9.1 动态SQL 221 8.9.2 嵌套分析函数 222 8.9.3 并行 223 8.9.4 PGA大小 224 8.10 组织行为 224 8.11 小结 224 第9章 Model子句 225 9.1 电子表格 225 9.2 通过Model子句进行跨行引用 226 9.2.1 示例数据 226 9.2.2 剖析Model子句 227 9.2.3 规则 228 9.3 位置和符号引用 229 9.3.1 位置标记 229 9.3.2 符号标记 230 9.3.3 FOR循环 231 9.4 返回更新后的行 232 9.5 求解顺序 233 9.5.1 行求解顺序 233 9.5.2 规则求解顺序 235 9.6 聚合 237 9.7 迭代 237 9.7.1 一个例子 238 9.7.2 PRESENTV与空值 239 9.8 查找表 240 9.9 空值 242 9.10 使用Model子句进行性能调优 243 9.10.1 执行计划 243 9.10.2 谓语前推 246 9.10.3 物化视图 247 9.10.4 并行 249 9.10.5 Model子句执行中的分区 250 9.10.6 索引 251 9.11 子查询因子化 252 9.12 小结 253 第10章 子查询因子化 254 10.1 标准用法 254 10.2 SQL优化 257 10.2.1 测试执行计划 257 10.2.2 跨多个执行的测试 260 10.2.3 测试查询改变的影响 263 10.2.4 寻找其他优化机会 266 10.2.5 将子查询因子化应用到PLSQL中 270 10.3 递归子查询 273 10.3.1 一个CONNECT BY的例子 274 10.3.2 使用RSF的例子 275 10.3.3 RSF的限制条件 276 10.3.4 与CONNECT BY的不同点 276 10.4 复制CONNECT BY的功能 277 10.4.1 LEVEL伪列 278 10.4.2 SYS_CONNECT_BY_PATH函数 279 10.4.3 CONNECT_BY_ROOT运算符 281 10.4.4 CONNECT_BY_ISCYCLE伪列和NOCYCLE参数 284 10.4.5 CONNECT_BY_ISLEAF伪列 287 10.5 小结 291 第11章 半联结和反联结 292 11.1 半联结 292 11.2 半联结执行计划 300 11.3 控制半联结执行计划 305 11.3.1 使用提示控制半联结执行计划 305 11.3.2 在实例级控制半联结执行计划 308 11.4 半联结限制条件 310 11.5 半联结必要条件 312 11.6 反联结 312 11.7 反联结执行计划 317 11.8 控制反联结执行计划 326 11.8.1 使用提示控制反联结执行计划 326 11.8.2 在实例级控制反联结执行计划 327 11.9 反联结限制条件 330 11.10 反联结必要条件 333 11.11 小结 333 第12章 索引 334 12.1 理解索引 335 12.1.1 什么时候使用索引 335 12.1.2 列的选择 337 12.1.3 空值问题 338 12.2 索引结构类型 339 12.2.1 B-树索引 339 12.2.2 位图索引 340 12.2.3 索引组织表 341 12.3 分区索引 343 12.3.1 局部索引 343 12.3.2 全局索引 345 12.3.3 散列分区与范围分区 346 12.4 与应用特点相匹配的解决方案 348 12.4.1 压缩索引 348 12.4.2 基于函数的索引 350 12.4.3 反转键索引 353 12.4.4 降序索引 354 12.5 管理问题的解决方案 355 12.5.1 不可见索引 355 12.5.2 虚拟索引 356 12.5.3 位图联结索引 357 12.6 小结 359 第13章 SELECT以外的内容 360 13.1 INSERT 360 13.1.1 直接路径插入 360 13.1.2 多表插入 363 13.1.3 条件插入 364 13.1.4 DML错误日志 364 13.2 UPDATE 371 13.3 DELETE 376 13.4 MERGE 380 13.4.1 语法和用法 380 13.4.2 性能比较 383 13.5 小结 385 第14章 事务处理 386 14.1 什么是事务 386 14.2 事务的ACID属性 387 14.3 事务隔离级别 388 14.4 多版本读一致性 390 14.5 事务控制语句 391 14.5.1 Commit(提交) 391 14.5.2 Savepoint(保存点) 391 14.5.3 Rollback(回滚) 391 14.5.4 Set Transaction(设置事务) 391 14.5.5 Set Constraints(设置约束) 392 14.6 将运算分组为事务 392 14.7 订单录入模式 393 14.8 活动事务 399 14.9 使用保存点 400 14.10 序列化事务 403 14.11 隔离事务 406 14.12 自治事务 409 14.13 小结 413 第15章 测试与质量保证 415 15.1 测试用例 416 15.2 测试方法 417 15.3 单元测试 418 15.4 回归测试 422 15.5 模式修改 422 15.6 重复单元测试 425 15.7 执行计划比较 426 15.8 性能测量 432 15.9 在代码中加入性能测量 432 15.10 性能测试 436 15.11 破坏性测试 437 15.12 通过性能测量进行系统检修 439 15.13 小结 442 第16章 计划稳定性与控制 443 16.1 计划不稳定性：理解这个问题 443 16.1.1 统计信息的变化 444 16.1.2 运行环境的改变 446 16.1.3 SQL语句的改变 447 16.1.4 绑定变量窥视 448 16.2 识别执行计划的不稳定性 450 16.2.1 抓取当前所运行查询的数据 451 16.2.2 查看一条语句的性能历史 452 16.2.3 按照执行计划聚合统计信息 454 16.2.4 寻找执行计划的统计方差 454 16.2.5 在一个时间点附近检查偏差 456 16.3 执行计划控制：解决问题 458 16.3.1 调整查询结构 459 16.3.2 适当使用常量 459 16.3.3 给优化器一些提示 459 16.4 执行计划控制：不能直接访问代码 466 16.4.1 选项1：改变统计信息 467 16.4.2 选项2：改变数据库参数 469 16.4.3 选项3：增加或移除访问路径 469 16.4.4 选项4：应用基于提示的执行计划控制机制 470 16.4.5 大纲 470 16.4.6 SQL概要文件 481 16.4.7 SQL执行计划基线 496 16.4.8 基于提示的执行计划控制机制总结 502 16.5 结论 502 本书的作者均隶属于OakTable团队，并拥有从15到29年间的深厚Oracle开发实践经验。在对Oracle数据库进行深入研究，尤其是在一些其他专门书籍未能充分探讨的议题上，这种长期的专业积累无疑为本书提供了不可或缺的强大优势。 ——亚马逊读者评价 该资源汇集了引人入胜的丰富内容，旨在为用户提供极佳的阅读体验。它呈现出令人印象深刻的精彩信息，能够激发读者的兴趣和思考。 SQL核心 由凯伦·莫顿（Karen Morton）撰写。 无论您是刚开始学习编写SQL语句，还是已经积累了多年的经验，掌握编写出“高质量”SQL的技巧都需要具备扎实的核心语法和概念基础知识。本章将对SQL语言的关键概念及其性能进行回顾，并详细描述一些您可能已经非常熟悉的常用SQL命令。对于那些曾经使用过SQL并拥有相当牢固基础知识的读者而言，本章更像是一个简短的复习，旨在为后续更深入的SQL论述奠定基础。如果您是SQL领域的初学者，建议您先阅读《Beginning Oracle SQL》这本书，以确保对SQL的基础知识有充分的理解。无论您的背景如何，第1章的目的在于通过快速浏览5个核心SQL语句来评估您的SQL水平，同时还概述了我们用于执行SQL语句的工具：SQL*Plus。 1.1 SQL语言 SQL语言最早由IBM公司于20世纪70年代开发，最初被称为结构化英文查询语言（SEQUEL），简称SQL。该语言建立在E.F.Codd在1969年提出的关系型数据库管理系统（RDBMS）理论之上。由于商标纠纷，其简称被进一步缩短为SQL。1986年和1987年，ANSI（美国国家标准化组织）和ISO（国际标准化组织）先后将SQL语言确立为标准语言。值得注意的是，ANSI官方曾正式确定了“S-Q-L”作为SQL语言的读音；尽管如此，绝大多数人，包括我自己，仍然习惯于使用“sequel”的读音，这仅仅是因为它听起来更自然流畅。 SQL的主要目标是提供一个便捷的接口来访问数据库，在本书中我们将重点关注Oracle数据库。每一条有效的SQL语句对于数据库而言都等同于一条命令或指令。与诸如C或Java等编程语言相比，SQL的主要区别在于它处理的是数据集合而非单个数据行。此外，语言本身并不要求您提供如何访问数据的指令——这些操作会在后台自动完成透明地进行处理。然而，在后续章节中您将会看到，为了在Oracle中编写出高效的SQL语句，了解数据及其在数据库中的存储方式与存储位置至关重要。 由于不同供应商（例如甲骨文、IBM和微软）实现核心功能的机制存在一定的差异性,因此基于特定数据库所学到的技巧同样可以应用于其他类型的数据库上。您基本上可以使用相同的SQL语句来进行数据的查询、插入、更新和删除操作,以及创建、修改和删除对象,而无需关心数据库的具体供应商。 尽管 SQL 是一种各种关系型数据库管理系统的标准语言,但实际上它并非总是严格遵循关系模型原则。在本书后续章节中,我将对此点进行更详细的阐述. 如果您希望深入了解相关细节,我强烈推荐大家阅读C.J.Date 的《SQL and Relational Theory》一书. 务必记住的一点是, SQL 语言并不总是严格遵守关系模型的规定——它并没有完全实现关系模型的某些要素,并且对某些要素的处理也存在不一致之处. 事实上, 由于 SQL 基于关系模型, 为了编写出尽可能准确高效的 SQL 语句, 您不仅需要理解 SQL 语言本身, 还必须理解关系模型的相关概念. 1.2 数据库的接口 多年来,人们开发出多种途径来传递 SQL 语句到数据库并获取结果. Oracle 数据库本地接口界面是 Oracle 调用界面 (OCI)。OCI 将由 Oracle 内核传送而来的查询语句发送到数据库服务器. 当使用某种 Oracle 工具 (例如 SQL*Plus 或 SQL Developer) 时, 您实际上是在利用 OCI 进行操作. 其他 Oracle 工具 (例如 SQL*Loader、数据泵 (Data Pump) 以及 Real Application Testing (RAT)) 也可能使用 OCI 或采用特定的编程接口 (如 Oracle JDBC-OCI、ODP.Net、Oracle 预编译器、Oracle ODBC 以及 Oracle C++ 调用接口 (OCCI) 驱动器). 当使用编程语言 (例如 COBOL 或 C 语言) 时, 您所编写的代码片段被称为嵌入式 SQL 语句,并且在应用程序编译之前会由 SQL 预处理器进行预处理. 代码清单1-1展示了一段可以在 CC++ 程序块中使用的 SQL 语句示例代码. 其他工具，诸如SQL*Plus和SQL Developer等，都属于交互式工具。用户可以通过输入并执行命令来获得相应的输出结果。这些交互式工具无需在执行代码之前进行精确的编译过程，只需直接输入想要运行的命令即可。如图书法1-2所示，它展示了使用SQL*Plus执行SQL语句的具体示例。 书法1-2 使用SQL*Plus执行SQL语句 在本书中，为了确保内容的一致性，我们提供的示例代码清单均采用SQL*Plus工具。然而，请务必注意，无论您采用何种方式或工具来输入和执行SQL语句，最终的指令都会通过OCI机制传递到数据库。本书的核心思想在于，所有使用的工具都具备相同的本地接口。 1.3 SQL*Plus回顾 SQL*Plus是一种通用的命令行工具，可在Windows或Unix等多种安装平台上使用。它提供了一个纯文本环境，用于输入、执行SQL语句并显示结果。借助该工具，您可以直接输入和编辑命令，并可以选择将命令保存为单个文件或通过脚本文件批量执行，同时还能以清晰美观的报表形式呈现输出结果。启动SQL*Plus只需在系统的命令提示符中输入“sqlplus”即可。 1.3.1 连接到数据库 可以通过多种途径使用SQL*Plus连接数据库。在连接之前，您需要在$ORACLE_HOME/network/admin/tnsnames.ora文件中注册要连接的数据库信息。常见的连接方式有两种：一种是在启动SQL*Plus时直接提供连接信息（如代码清单1-3所示），另一种是在启动后使用“connect”命令进行连接（如代码清单1-4所示）。 代码清单1-3 通过窗口命令提示符连接到SQL*Plus 为了避免在启动SQL*Plus时看到连接到数据库后的常规提示信息，您可以采用使用nolog选项的方式来启动SQL*Plus。 代码清单1-4展示了通过“SQL>”提示符连接SQL*Plus并成功登录到数据库的过程。 1.3.2 配置 SQL*Plus 环境 SQL*Plus 提供了大量的命令，能够灵活地调整您的工作环境并定制显示选项。如图清单 1-5 所示，在 SQL> 提示符下输入“help index” 命令后所呈现的，便是可供您使用的命令列表。 如图清单 1-5 SQL*Plus 命令列表 The `set` command serves as the fundamental instruction for tailoring the operational environment. Appendix A through Appendix F provides the documentation detailing the functionality of the `set` command. Appendix A through Appendix F: Documentation for the `SET` Command in SQL*Plus 通过运用上述可用的命令，您能够便捷地配置出最符合您运行环境的方案。然而，需要注意的是，当您退出或关闭SQL*Plus时，这些配置命令将不再被保存。为了避免每次启动SQL*Plus都需要重新输入这些设置命令，建议您创建一份login.sql文件。实际上，每次启动SQL*Plus都会默认读取两个文件：首先是位于$ORACLE_HOME/sqlplus/admin目录下的glogin.sql文件。如果该文件存在，它将被加载并执行其中的命令语句。从而可以将定制您的会话体验的SQL*Plus命令和SQL语句保存下来。 随后，SQL*Plus会进一步搜索login.sql文件。这个文件必须位于SQL*Plus的启动目录中，或者其路径包含在环境变量SQLPATH所指向的文件夹路径中。login.sql文件中定义的命令具有比glogin.sql文件中命令更高的优先级。自10g版本起，Oracle在每次启动SQL*Plus或执行connect命令时都会同时读取glogin.sql和login.sql这两个文件。在此之前，在Oracle 10g之前的版本中，login.sql脚本仅在SQL*Plus启动时才会执行。代码清单1-7展示了一个常见的login.sql文件内容示例。 代码清单1-7 一个常见的login.sql文件 请留意在SET SQLPROMPT中使用的变量，例如_user和_connect_identifier。这些变量都属于预定义的系统变量，你可以将它们应用在login.sql文件中，或者任何你自行编写的脚本中。以下列出了一些常用的预定义变量： * _connect_identifier * _date * _editor（这个变量控制了当你使用edit命令时所启动的编辑器） * _o_version * _o_release * _privilege * _sqlplus_release * _user 1.3.3 关于命令的执行 SQL\*Plus提供了两种类型的命令可供执行：SQL语句以及SQL\*Plus特有的命令。代码清单1-5和代码清单1-6中展示的SQL\*Plus命令是专为该环境设计的，能够灵活地定制运行环境并执行诸如DESCRIBE和CONNECT等特定于SQL\*Plus的功能。要执行一个SQL\*Plus命令，只需在命令提示符后输入该命令并按下回车键即可，系统会自动将其执行。另一方面，若要执行SQL语句，则必须使用特定的字符来指示你想要运行的语句；分号（;）或斜杠（/）都可以作为标记符使用。当使用分号时，它可以直接放置在输入命令的末尾或后续空行中；而斜杠则必须放置在下一个空行中才能被识别。代码清单1-8详细说明了如何运用这两种字符进行标记。 代码清单1-8 关于执行字符的用法 请留意第5个语句末尾添加了斜线（）的示例。光标会移动到下一行，而非立即执行该语句命令。随后，若再次按下回车键，语句便会被暂存于SQL*Plus的缓冲器中，但不会立即执行。要查看SQL*Plus缓冲器中的内容，可以使用“list”命令（亦可简写为“l”）。然而，如果在缓冲器中尝试使用斜线（）来执行语句[尽管斜线（）命令本身就是为了这种用法设计的]，则会返回一个错误。这是由于您在最初的SQL语句结尾处敲入了一个斜线（），而斜线（）并非有效的SQL命令，因此在语句试图执行时会产生错误。 另一种执行命令的方式是将其放置在一个文件中。您可以利用文本编辑器在SQL*Plus之外直接创建这些文件，或者通过“EDIT”命令在SQL*Plus内部直接调用编辑器。如果已存在该文件，“EDIT”命令可以打开它；若文件不存在，则会创建一个新的文件。该文件必须位于默认文件夹中；否则，您需要明确指定文件的完整路径。为了设定所使用的编辑器，只需使用“define editor=myeditor.exe”这样的命令来设置预定义变量_editor。拥有“.sql”扩展名的文件在执行时无需输入扩展名，可以通过“@”或“START”命令进行执行。代码清单1-9详细列出了这两个命令的使用方法。 SQL*Plus拥有众多特性和选项，因此在此无法一一列举。对于本书的目的而言，这样的概述已经足够详尽。然而，Oracle的文档提供了对SQL*Plus使用方式的指导，并且许多书籍，例如《Beginning Oracle SQL》，对SQL*Plus进行了更为深入的阐述。如果您对此感兴趣，可以查阅相关资料。 1.4 五个核心的SQL语句 SQL语言包含多种不同的语句，但职业生涯中，您可能只会熟练运用其中的一部分。同样地，您在使用其他产品时是否也发现自己仅使用了其常用功能或编程语言的20%甚至更少？我虽然不确定这个统计数据是否准确，但根据我的经验来看，它似乎相当可靠。我观察到在大多数应用程序中，基本SQL语句格式的使用持续了近20年之久。即便那些经常使用的功能也常常没有得到充分利用。显而易见的是，我们不可能涵盖SQL语言的所有语句及其选项。本书旨在帮助您深入理解那些最常用的SQL语句并提升您的使用效率。 在本书中，我们将重点关注五个最常用的SQL语句：SELECT、INSERT、UPDATE、DELETE以及MERGE。尽管这些核心语句将被逐个讲解，但SELECT语句的重要性尤为突出。掌握这五个语句将为您在日常工作中高效地运用SQL语言奠定坚实的基础。 1.5 SELECT语句 SELECT语句用于从一个或多个表或其他数据库对象中检索数据。鉴于您可能已经对SELECT语句的基础知识有所了解，因此我将不再从初学者的角度进行详细介绍；而是首先回顾一下SELECT语句的执行逻辑流程。您应该已经学习了如何编写基本的SELECT语句，但为了培养良好的思维模式并确保编写符合语法规则的高效SQL代码，您需要理解SQL语句是如何执行的。 一个查询语句在逻辑层面上的处理方式可能与实际的物理处理过程存在差异显著的情况。“Oracle基于查询成本的优化器（cost-based optimizer, CBO）”负责生成实际的执行计划。我们在后续章节中将详细讲解优化器的作用、实现机制以及优化的重要性。目前而言，我们需要关注的是优化器将如何访问表、按照怎样的顺序处理它们以及如何将多个表连接起来及如何应用筛选器等问题 。查询的处理过程遵循特定的逻辑顺序；然而，“优化器”所选择的物理执行计划可能会采用完全不同的顺序来实际执行这些步骤. 代码清单1-10展示了一段包含SELECT语句主要子句的查询片段, 其中清晰地标出了每个子句对应的逻辑处理顺序. 代码清单-10 查询语句逻辑处理顺序 你应该立即关注SQL与其他编程语言的一个关键区别在于，它首先处理的是FROM子句，而非在第一行直接编写的SELECT语句。请注意，在提供的代码示例中，我展示了两个不同的FROM子句，标记为1.1的那个FROM子句代表了使用ANSI语法时的差异。我们可以将查询过程中的每一个环节视为生成一个临时数据集的过程。随着每个步骤的执行，这个数据集会不断地被修改和操作，直到最终产生所需的处理结果。查询最终返回给调用者的便是这个经过处理的最终数据集。为了更深入地理解SELECT语句的各个组成部分，你可以参考代码清单1-11所示的查询语句，该语句返回的结果集是下订单超过4次的女顾客列表。 1.5.1 FROM子句 FROM子句定义了查询所依据的数据源。该子句可以包含表、视图、物化视图、分区或子分区，或者通过子查询来构建子对象。若使用了多个数据源，其逻辑处理阶段将应用于每一个连接类型以及谓词ON条件（如同步骤1.1所示）。本书后续章节将深入探讨连接类型的细节，但请注意，在处理连接语句时，执行顺序通常遵循以下模式： (1) 交叉联结，也称为笛卡尔积； (2) 内联联结； (3) 外联结。 在代码清单1-11中展示的查询示例中，FROM子句指定了两张表：customers和orders，并利用customer_id列进行关联。因此，在处理这些信息时，FROM子句生成的初始数据集将包含两张表中customer_id相匹配的所有行。在本例中，结果集预计将包含105行记录。为了验证这一推测，您可以执行示例中的前四行语句（如代码清单1-12所示）。 代码清单1-12 展示了仅使用FROM子句部分查询语句的执行结果。 为了更好地适应页面布局，我手动调整了输出结果，最终呈现的行数超过了105行。 1.5.2 WHERE子句 WHERE子句提供了一种机制，用于根据特定条件来缩小查询返回结果集的行数。每个条件或谓语通常以两个值或表达式之间的比较形式呈现。该比较的结果要么是匹配（对应于TRUE值），要么是不匹配（对应于FALSE值）。如果比较结果为FALSE，则相应的行将不会包含在最终的结果集中。 在此，我将稍稍偏离主题，着重讨论与此步骤相关的SQL中的一个关键方面。实际上，SQL中逻辑比较可能产生三种结果：TRUE、FALSE以及未知。当比较中包含空值（null）时，其结果必然是未知的。空值与任何值进行比较或在表达式中使用都将产生空值或未知结果。一个空值代表缺失的相应值的概念，并且由于SQL语言的不同部分对空值的处理方式存在差异，这可能会导致困惑。关于空值如何影响SQL语句执行的细节将在本书后续章节中进一步阐述；然而，在此之前，我必须先提及这一重要问题。正如我之前所说，一个比较的返回值将是TRUE或FALSE。你会发现当筛选条件的比较中包含空值时，它们会被视为FALSE。 在我们的示例中，只有一个用于限定结果为下过订单的女性消费者的谓语。若您查看FROM子句执行之后的中间结果集（参考代码清单1-12），您会发现仅有31行是由女性消费者所下的订单（gender = F）。因此，应用WHERE子句后，中间结果集从105行减少到31行。 应用WHERE子句后得到了更为精确的结果集。请注意，“精确”指的是现在已经获得了能够满足您查询需求的有效数据行数。其他子句（如GROUP BY和HAVING）可以用来聚合并进一步限制调用程序接收到的最终结果集；但值得注意的是，目前已经获得了查询计算最终结果所需的所有必要数据。 WHERE子句的主要目的是限制或减小结果集的大小。您使用的限制条件越少，最终返回的结果集中包含的数据就越多；反之亦然。如果您需要返回的数据越多，那么执行查询所需的时间也会相应地增加。 1.5.3 GROUP BY子句 GROUP BY子句会将执行FROM和WHERE子句后经过筛选后的结果集进行聚合操作。查询的结果按照GROUP BY子句中指定的表达式进行分组, 从而为每个分组生成一行汇总信息. 您可以根据FROM子句中所列出的任何对象字段对结果进行分组, 即使这些字段并不需要在输出结果列表中显示. 相反而言, SELECT列表中的任何非聚合字段都必须包含在GROUP BY表达式中. 此外, GROUP BY子句还可以包含两个额外的运算：ROLLUP和CUBE. ROLLUP运算用于生成部分求和的值, CUBE运算则用于获得交互分类的值. 当使用这两种运算中的任何一个时, 您将会得到多于一行的汇总信息. 在第7章中将会对这两个运算进行更详细的讨论. 在示例查询中, 需要按照customer_id来进行分组, 这意味着对于每一个唯一的customer_id, 仅会返回一行汇总数据. 在WHERE 子句执行后所得到的代表下过订单的女性消费者的31行订单中, 存在11个独特的customer_id 值 (如代码清单 1-13所示)。 代码清单 1-13 展示了在 GROUP BY 子句中的特定查询执行情况。 你会注意到查询返回的数据是经过分组处理的，但并未进行任何排序。虽然表面上结果似乎按照order_ct字段进行了排序，但这仅仅是一种巧合，并非确定的行为。务必牢记的关键点是：GROUP BY子句并不能保证结果数据呈现的特定顺序。若您需要结果按照特定的顺序排列，则必须明确地添加一个order by子句。 1.5.4 HAVING子句 HAVING子句用于限定分组汇总后的查询结果，仅保留满足该子句条件的行。除非使用HAVING子句，否则将返回所有汇总行。实际上，GROUP BY子句和HAVING子句的顺序可以互换，两者之间的先后顺序并不影响最终结果。然而，在实际编码中，通常建议将GROUP BY子句放在前面执行，因为从逻辑上讲，GROUP BY子句先对数据进行分组处理。本质上来说，HAVING子句是在GROUP BY子句完成后再对分组值进行筛选的第二个WHERE 子句。 在我们的查询示例中，HAVING 子句HAVING COUNT(o.order_id) > 4, 将分组数据从11行减少到2行。您可以通过查看 GROUP BY 子句应用后返回的行数来验证这一点（如代码清单1-13所示），仅有146号和147号消费者所下的订单数超过4次。因此最终结果集中只包含两行数据。 1.5.5 SELECT列表 SELECT列表列出查询中需要显示的所有列，这些列可以是数据库表中的实际列、表达式或甚至另一个SELECT语句的结果（例如代码清单1-14所示）。 代码清单1-14 展示了SELECT列表各种可能情况的查询实例 SQL> select.customer_id, c.cust_first_name||c.cust_last_name, 当您利用另一个SELECT语句来检索结果集中特定列的一个值时，该查询必须仅返回单行单列的数据。这种类型的子查询通常被称为标量子查询。虽然标量子查询可能是一种非常有用的语法特性，但务必记住，在结果集每一行产生时，它都需要单独执行。为了优化性能并减少不必要的重复执行，在某些情况下可以对标量子查询进行调整；然而，如果每一行都需要执行此类查询，则可能会导致显著的性能下降。请想象一下，当您的结果集中包含数千行甚至上百万行数据时所产生的查询成本！后续章节中，我们将进一步回顾标量子查询并探讨更有效的应用方法。 此外，在SELECT列表中您还可以使用DISTINCT子句。尽管在当前示例中并未采用该子句，但我希望简要地提及一下其作用。DISTINCT子句用于在其他子句完成执行后，从结果集中移除重复的行。 完成SELECT列表的执行后，您将获得最终的查询结果集。如果您的结果集包含排序需求，那么接下来需要做的就是按照所需的顺序对结果进行排列。 1.5.6 ORDER BY子句 ORDER BY子句用于对最终返回的结果集进行排序。在本例中，我们需要按照orders_ct和customer_id这两个字段进行排序。orders_ct这一列的值是通过GROUP BY子句中的COUNT聚合函数计算得出的。如代码清单1-13所示, 两个消费者的订单数量超过4个。由于这两个消费者的订单数均为5份, orders_ct这一列的值相同, 因此第二个排序列将决定最终结果的显示顺序. 如代码清单1-15所示, 该查询经过排序后的最终输出结果是按照customer_id排序的两行数据集. 代码清单1-15 示例查询的最终输出 当输出结果需要进行排序时，Oracle系统必须在所有其他子句完成执行后，按照预先定义的顺序对最终的结果集进行排列。排序的数据量大小是至关重要的因素，这里所指的大小指的是结果集中包含的总字节数。你可以通过将行数乘以每一行的字节数来大致估算数据集的规模。每一行所包含的字节数则可以通过将选择列表中每列的平均长度相加来确定。 提供的查询示例仅需要在选择列表中列出customer_id和orders_ct两列的值。我们估计每一行输出值的字节数为10字节。在第六章中，我将详细说明如何获取优化器所估计的值。因此，如果结果集中只有两行数据，排序所需的空间相对较小，大约为20字节。请注意，这仅仅是一种估算，但准确的估算对于性能至关重要。 较小的排序操作通常可以在内存中完成，而较大的排序则需要借助临时磁盘空间来实现。正如你可能已经推断的那样，在内存中进行的排序比必须使用磁盘的排序速度更快。因此，当优化器评估排序数据的影响时，它必须充分考虑数据集的大小，从而选择最有效的方式来获得查询结果。一般来说，排序是查询过程中的一个显著开销步骤，尤其是在返回结果集很大的情况下。 1.6 INSERT语句 INSERT语句用于向表、分区或视图中添加新的行记录。可以向单个表或多个表中插入数据行。单表插入会将一行数据添加到单个表中；该行数据可以显式地指定插入值，也可以通过子查询动态获取值。多表插入则会将一行或多行数据插入一个或多个表中，并且会利用子查询计算所插入行的值。 1.6.1 单表插入 代码清单1-16展示了使用VALUES子句实现单表插入的方法。每一列的值都明确地输入到相应的列中。如果需要向表中插入所有定义的列的值，则列列表是可选的；然而，如果只想提供部分列的值，则必须在列列表中明确指定所需的列名。最佳实践是无论是否需要插入所有列的值都应明确列出所有列的列表；这类似于该语句的文档说明, 并且有助于减少将来别人在表中添加新列时可能出现的错误. 代码清单1-16 单表插入 第三个例子展示了通过子查询来完成插入操作。这种插入数据行的方法提供了极大的灵活性。所编写的子查询能够返回单行或多行数据，每行数据都将用于生成需要插入的新行的相应列值。根据实际需求，子查询可以设计得非常简单，也可以构建出复杂的逻辑。例如，在本例中，我们利用子查询实现了在现有薪资基础上为每位员工发放10%奖金的计算。值得注意的是，奖金表包含四列，但在本次插入中，我们仅选取了三列进行展示。具体而言，comm列在子查询中并未被包含在列列表中，且其值也将默认为null。若comm列具有非空约束条件，则可能导致返回约束错误并终止语句执行。 1.6.2 多表插入 代码清单1-17详细说明了通过子查询返回的数据行如何被用于向多个表中进行插入操作。该示例使用了三个表：small_customers、medium_customers以及large_customers。目标是将消费者的订单总金额作为依据，分别将数据插入这三个表中。子查询计算每一位消费者的order_total列的总和，从而确定该消费者的消费金额属于“小”（订单总金额小于10 000美元）、“中等”（订单总金额介于10 000美元与99 999.99美元之间）或“大”（订单总金额大于等于100 000美元）类别。随后，根据这些条件将相应的行插入到对应的表中。 代码清单1-17 多表插入 请务必留意INSERT关键字搭配ALL子句的应用。当您明确指定ALL子句时，该语句将执行无条件的多表插入，这意味着每一个WHEN子句会根据子查询返回的每一行来确定其值，而与前一个条件的输出结果无关。因此，您需要仔细考虑如何定义每个条件。例如，如果我采用WHEN sum_orders < 100000这个条件，而不是像之前那样列出范围，那么插入medium_customers表中的行也可能被插入到small_customers表中。关键在于选择最合适的选项——ALL还是FIRST——以满足您的具体需求。 1.7 更新语句 更新语句用于修改表中现有行的列值。该语句的语法包含三个部分：UPDATE、SET和WHERE。UPDATE子句用于指定要更新的表，SET子句用于指明哪些列将被修改以及调整后的值，而WHERE子句则用于根据条件筛选需要更新的行。值得注意的是，WHERE子句是可选的；如果省略了该子句，则更新操作将应用于指定表中的所有行。 代码清单1-18展示了多种UPDATE语句的不同表达方式。我首先创建了一个employees表的副本，命名为employees2，然后执行了几种完成相同任务的不同更新操作：将90部门员工的工资增加10%。在例5中，commission_pct这一列也一同进行了更新。以下是采用的不同方法示例。 例1：通过表达式更新单个列的值。 例2：通过子查询更新单个列的值。 例3：通过在WHERE子句中使用子查询来确定要更新的数据行并更新单列的值。 例4：通过使用SELECT语句来定义表及列的值来更新整个表。 例5：通过子查询来更新多列的值。 代码清单1-18 展示了UPDATE语句的示例. 1.8 DELETE语句 DELETE语句的功能是移除数据库表中特定的数据行。其语法结构包含三个主要部分：DELETE、FROM和WHERE。DELETE关键字通常单独出现，除非您选择采用后续讨论的提示（hint），否则不会与其他选项结合使用。FROM子句用于明确指定要从中删除数据的表，该表可以通过直接命名或通过子查询来确定，正如代码清单1-19所示。WHERE子句则提供筛选条件，有助于精确地定义需要删除的行。若未指定WHERE子句，则删除操作将影响指定表中的所有数据行。 代码清单1-19展示了DELETE语句的多样化表达方式。请注意，在这些示例中，我使用了代码清单1-8中创建的employees2表作为基础。您将在此看到各种不同的删除方法应用。 例如1：利用WHERE子句中的筛选条件来从特定表中移除数据行。 例如2：通过使用FROM子句中的子查询来执行删除操作。 例如3：运用WHERE子句中的子查询来从指定表中删除相关数据行。 代码清单1-19 DELETE语句示例 1.9 MERGE语句 MERGE语句能够根据预设的条件，精确地选取需要更新或插入到表中的数据行，并同时从一个或多个数据源对目标表进行更新操作，或者向表中添加新的行。该语句在数据仓库环境中被广泛应用于大规模数据的迁移和处理，但其应用范围远不止于此。MERGE语句的一个显著优势在于，它允许你将多个操作整合为一个单一的语句，从而避免了频繁使用多个INSERT、UPDATE和DELETE语句的麻烦。此外，本书后续章节将展示，通过减少不必要的操作，可以有效缩短响应时间。 MERGE语句的语法如下： 为了阐明MERGE语句的操作方式，代码清单1-20详细地演示了如何创建用于测试的表，并随后有效地运用MERGE条件来向该表中插入新的行或更新已存在的行。 MERGE语句执行了以下操作： · 成功地添加了两条新记录，分别对应员工ID 106和107。 · 对一条现有记录进行了更新，具体是员工ID 105的资料。 · 删除了对应员工ID 103的记录。 · 一条记录保持了原样，即员工ID 104的信息没有发生改变。 如果没有采用MERGE语句，完成这些任务至少需要编写三条独立的语句。 1.10 总结 从我们所探讨的示例中可以看出，SQL语言提供了多种实现相同结果集的途径。你可能也注意到这五个核心SQL语句都采用了相似的结构，例如子查询的使用。至关重要的是要掌握在各种不同的应用场景下，哪种结构能够达到最佳的效率。我们将会在本书后续章节中详细说明如何选择最合适的构造方法。 如果你对本章提供的示例存在任何疑问，请务必花时间回顾《Beginning Oracle SQL》或Oracle官方文档中的SQL Reference Guide。在本书的后续部分，我们假设你已经充分理解了这五个核心SQL语句的基本语法：SELECT、INSERT、UPDATE、DELETE和MERGE。