Oracle数据库是目前市场占有率最高的商业数据库，功能非常强大，随着Oracle管理工具的智能化，如Oracle EM 提供了强大的管理和调优功能，Oracle DBA 的管理工作逐渐趋于简单化。正因为如此，当前国内Oracle开发人员的岗位需求大幅度增加，投身Oracle开发的人数也日益增多。 一个好的Oracle开发人员不仅要知道一些基本语法和常见用法，还需要知道何时用、如何用、怎么避免相关误区、写出高质量的代码。本书面向有Oracle使用经验或有其他数据库使用经验的人，重点讲述利用好Oracle的特色功能进行高效开发的思路和技巧，帮助读者快速掌握中高级Oracle开发技术。 本书主要覆盖Oracle开发必备的重要知识点：数据库编程规范、Oracle开发常用工具及使用、MERGE方法、神秘的NULL和讨厌的CHAR、扩展GROUPBY、Oracle自动类型转换、Oracle分析函数、Oracle层次查询、11g R2新特性之递归WITH编程、动态SQL扩展。此外还有大量案例：Where In List问题解析，数据库设计和大数据量处理、数据审核、号段选取应用、分析SQL执行计划的关注点、Oracle开发误区探索、提升PL/SQL开发性能漫谈、管道函数的学习与实战应用、巧用锁特性避免重复启动同一程序、不可能的任务？超越Oracle等。 本书作者均有多年Oracle开发管理实战经验，他们期望以自己的经验和教训，分享分析和解决问题的方法，探讨如何用相关的技术去解决特定的需求。希望读者在看完本书后，能受到良好的启迪，进而能极大地提升自己在Oracle数据库开发方面的能力！

长久以来，Oracle管理类的书籍遍地开花，并涌现出了一批批优秀的实战作品，但在Oracle开发方面，多数都是基础性的语法书籍，很少有较为深入的实战性的作品问世。久而久之，大多数人认为Oracle开发就是select、insert之类的DML语句，最多再有点集合操作外加触发器、函数之类的存储过程，但这就好像冰山一样，粗略地一看，只是看到冰山露出水面的尖角而已。而我们知道，冰山在水面下的部分，远比其水面上的部分要巨大得多。要想探究水面下的冰山，其中之一的方法就是分而治之深入研究，跟随7位有多年Oracle开发经验的作者的指引，欣赏他们各自的拿手好戏，学习他们的思路与方法，破开冰山深入其中，从而提升自己的Oracle开发水平。 本书是一本通过讲解Oracle数据库开发案例来说明Oracle数据库的功能特性、编程思路和设计方法的书籍。通过对每个案例的详细分析和讲解，使读者了解对同一类开发任务应如何做并能够领会为什么这么做，达到融会贯通的目的。 本书主要面向中、高级用户，所以读者最好具有一定的Oracle数据库使用基础和程序设计基础（如熟悉基本SQL语句和任何一种过程语言），最好有一定的程序设计经验。但初级用户也能从中学到很多有用的东西，包括设计思想和技巧等。通过对本书的学习，相信读者的Oracle开发水平会上一个新的台阶。 本书的案例内容涉及面比较广泛，包括编程规范、表结构设计、表压缩、分析函数及高级查询，从常规数据处理的设计到各类行列转换技巧，从Oracle的各种实用工具的用法到问题诊断、优化等步骤都进行了讲解。另外，还针对Oracle某些容易出错的陷阱进行了介绍，并提出了相应的解决办法，对某些操作提出了提高效率的措施并进行了验证。 在众多内容中，本书也有所侧重，力求将作者理解最深入的部分介绍给读者，例如，动态SQL、PL/SQL的使用、影响执行计划的因素、减少日志生成及并行技术等。选择这些作为侧重点的原因有4个方面：第一，目前市面上类似书籍对这些方面的内容涉及不多，而且不够深入；第二，这些内容在通常的程序设计中使用非常频繁；第三，这些内容能使读者更加了解Oracle系统的工作机制；第四，消除很多读者的认识误区，例如，认为Oracle数据库的SQL只是在数据查询方面很强大，并因此将SQL仅仅定位在查询语言上。事实上，通过阅读本书，读者将会发现 Oracle的SQL功能是非常强大的。在开发中，很多过去必须依靠过程语言实现的功能，比如循环、行间运算，通过分析函数、递归子查询等，SQL基本上都能实现。而且，利用SQL能使开发工作事半功倍，极大地提高开发效率。 本书精选了10多个有独特的设计思想和编写技巧的案例来进行分析讲解。出版社网站提供包含所有案例的源程序下载，读者可复制这些代码到自己的系统中，随时查看程序的运行结果，以加深对代码的理解。 我们7位Oracle技术专家来自北京、加拿大多伦多、上海、福建、海南、河北等地，在电信、制造、统计、航空、金融、证券等行业工作多年，工作领域包括项目管理、系统架构、数据库、数据仓库和数据挖掘等。 关于本书 苏旭晖（newkid）是一名定居境外的资深程序员，也是ITPUB上最为活跃的热心网友之一，非常关注国内Oracle数据库领域，在百忙之中还为我们送来了大礼：第18章“11g R2 新特性之递归with编程精粹”，让读者有机会使用Oracle提供的最新技术开发项目。他认真审核了我们每个人的文章，指出了很多错误和不完善的地方，为本书的完成发挥了重要的作用。 王保强（bq_wang）编著了第1章、第10章、第11章和第20章。丁俊（dingjun123）编著了第4章、第5章、第6章和第7章。梁敬彬（wabjtam123）编著了第2章、第3章、第13章、第14章、第15章、第16章和第17章。怀晓明（lastwinner）编著了第12章。贾书民编著了第9章。卢涛（〇〇）编著了第8章、第19章，并对第2章、第9章的部分内容作了补充。 各章的主要内容如下： 第1章 “大话数据库编程规范”通过风趣的语言讲解了Oracle开发过程中的书写规范、命名规范、变量命名、注释规范、语法规范、脚本规范的一些规则和建议。 第2章 “Oracle开发常用工具及使用”的“AWR工具与性能优化”一节描述了AWR的基本功能和使用步骤，并通过几个经典案例的演示让读者加深印象，同时证实该工具的强大。“SQL_TRACE/10046事件”一节描述了如何通过该工具了解SQL的解析过程、执行计划、绑定变量、递归调用等详细信息。最后通过系列10046事件跟踪的成功案例来阐述该如何一步步打开各扇SQL困惑大门。“计时和剖析工具”一节主要介绍了利用DBMS_PROFILER包分析PL/SQL代码的方法和步骤，并根据剖析结果对代码进行精细的调整。 第3章 “探索MERGE方法”介绍了MERGE存在的原因、具备的特性及哪些是注意点，通过实例讲述了MERGE用于复杂更新时的优势所在，希望读者能记住这些经典的方法，给自己的项目开发带来便利。 第4章 “神秘的NULL和令人讨厌的CHAR”，第一部分通过大量的实例分析NULL的理论和使用，使读者对NULL一目了然，从而更好地使用NULL。第二部分通过对CHAR类型多角度的分析，讲述特殊的字符类型CHAR的使用和注意点。这两个知识点是Oracle开发中很重要的知识点，也是出问题较多的知识点，希望通过本章的学习，读者能够对这两个知识点熟练掌握。 第5章 “报表开发之扩展GROUP BY”，这属于高级SQL方面的知识，在报表开发中是很常见的，本章通过对扩展GROUP BY各个知识点的讲解，并通过实例分析，讲述如何高效地实现数据的多维分析统计，从而为决策者提供强大的报表支持。 第6章 “探索Oracle自动类型转换”，这是Oracle开发中很重要的知识点，往往也是Oracle开发人员容易忽视或不完全知晓的知识点，本章通过对各种规则及实例的讲述，使读者对隐式类型转换有更深刻的认识，从而有助于检查错误和开发高效的程序。 第7章 “Where in list问题解析”，通过对一个常见应用的探讨，分析如何通过多种方法解决问题，重点讲解如何对问题进行多角度分析，从提出问题到解决问题的过程，希望读者能够从这个实例中领会到一般复杂问题的分析和解决过程。 第8章 “例说数据库表设计和大量数据处理方法”通过各种不同实现的比较讲述了一些表设计应遵循的规则，并分析了开发数据加载和卸载、数据汇总应用时可供采用的各种方法的特点。本章侧重利用数据库本身提供的SQL功能完成数据加工，而第19章侧重使用过程语言开发。两章的例子有部分重叠，读者可鉴别阅读。 第9章 “数据审核”讲述了在Oracle中对表中数据进行高效列间审核和行间审核的各种方法，包括静态SQL、PL/SQL、动态SQL的使用方法和技巧。 第10章 “趣谈Oracle分析函数”讲述了分析函数在BI场景中的一些应用，以及自定义聚集函数的实现。 第11章 “Oracle层次查询”介绍了基本的层次查询语法SELECT...START WITH... CONNECT BY ...语法，以及SYS_CONNECT_BY_PATH和WMSYS.WM_CONCAT函数的应用。 第12章 “号段选取应用”主要利用层次查询和分析函数，通过对常见的号段选取的4种情况进行剖析解答，并通过扩展环节拓展了这几种情况的应用范围，循序渐进地带领读者全面掌握此类问题的攻克方法。 第13章 “分析SQL执行计划的关注点”并未对各种调优知识进行特别深入的描述，而是将最实用的部分提炼出来告之读者，力求读者在读完本章后能面对纷繁复杂的执行计划而保持清晰的头脑，以最敏锐的视角去关注最需了解的信息，从而能迅速、准确地抓出主要矛盾，快速解决问题。 第14章 “Oracle开发误区探索”来源于日常工作中开发人员所经历的各个易错环节，具有较强的隐蔽性和欺骗性，希望读者在阅读完本章后能借鉴经验，在项目开发中少犯错。 第15章 “提升PL/SQL开发性能漫谈”主要从重视解析、关注调用、简洁为王、细致入微4个角度入手，结合详细的案例说明和读者共同探索PL/SQL开发优化的方法和思想。 第16章 “管道函数的学习与实战应用”结合详细的示例描述了管道函数的特性、注意点、使用管道函数的场合等知识点。适当地使用管道函数技术将给项目开发带来很大的便利。 第17章 “巧用锁特性避免重复启动同一程序”来源于笔者项目中一次未考虑周全而导致系统数据错误的经验教训，文中描述的技术方案是将锁机制和异常捕获巧妙结合，完美地解决了笔者的问题，并成功将该技术广泛地应用到各个项目中。 第18章 “11g R2 新特性之递归with编程精粹”，本章中的大部分例子看起来像是趣味数学题，似乎实用性不强，读者们不妨视之为工作之余放松脑筋的益智类游戏，并从中得到某些启发。也许在你解决下一个工作中的难题时，递归的思想就会大放光彩！ 第19章“不可能的任务？超越Oracle”讲解了用Oracle内部和第三方过程语言实现数据立方体的方法及提高效率的优化方法，说明了在特定条件下，不用SQL，而采用高级语言编程解决高性能要求问题的必要性和可能性。 第20章“动态SQL扩展”通过一些翔实的案例讲解了动态SQL的不同应用场景。 书中案例的基本运行环境是Oracle 10g R2，除非有特殊说明，在Oracle 9i中也能运行。个别Oracle 11g的新特性都有专门指出。 本书在很多案例的重要部分都有提示、注意等特殊说明。目的是为了加深读者的印象，避免不必要的错误，以使读者能够更高效地利用Oracle强大的功能和开发工具。 感谢ITPUB网站的创始人黄志洪（tigerfish）为本书写序，他在推动中国Oracle 数据库的发展上功不可没。本书正是在他的倡议和帮助下完成的。 感谢杨廷琨（yangtingkun）、罗海雄（rollingpig）、张涵（zhangfengh）、杨向勇（yxyup）和侯圣文（secooler），他们都是国内顶尖的Oracle专家，感谢他们审核了书稿，并提出了宝贵意见。 感谢ITPUB的王蓓小姐（贝贝），在本书的出版过程中，她做了大量的协调工作，才保证这本书的顺利出版。 感谢梁敬弘博士对梁敬彬的章节做的仔细修改。 感谢电子工业出版社的张月萍策划，是她的努力让本书更具可读性和完整性。 由于作者水平有限，书中还存在一些错误和不当之处，敬请批评指正。 编 者 2010年10月

第1章 大话数据库编程规范 1 1.1 编程规范概述 1 1.2 书写规范 2 1.2.1 大小写风格 2 1.2.2 缩进风格 2 1.2.3 空格及换行 3 1.2.4 其他 4 1.3 命名规范 4 1.3.1 表和字段命名规范 4 1.3.2 其他对象命名 5 1.4 变量命名 7 1.5 注释规范 8 1.6 语法规范 9 1.7 脚本规范 12 规则1.7.1 13 规则1.7.2 13 规则1.7.3 13 第2章 Oracle开发常用工具及使用 14 2.1 AWR工具与性能优化 14 2.1.1 AWR介绍 14 2.1.2 AWR的安装与配置 15 2.1.3 AWR报表使用 15 2.1.4 分析AWR报表基本思路 17 2.1.5 案例 17 案例1根据AWR报表发现I/O瓶颈 17 案例2根据AWR报表解决RAC争用 22 2.2 SQL_TRACE/10046事件 24 2.2.1 SQL_TRACE/10046事件是什么 24 2.2.2 总体流程介绍 24 2.2.3 SQL_TRACE/10046事件具体使用方法 24 2.2.4 案例分析 28 2.2.5 小结 32 2.3 计时和剖析工具 32 2.3.1 PLSQL_PROFILER概述 34 2.3.2 剖析举例 37 2.3.3 安全性 45 第3章 探索MERGE方法 46 3.1 MERGE是什么 46 3.2 Oracle 10g中MERGE的完善 48 3.2.1 UPDATE和INSERT动作可只出现其一 48 3.2.2 可对MERGE语句加条件 48 3.2.3 可用DELETE子句清除行 49 3.2.4 可采用无条件方式INSERT 49 3.3 MERGE误区探索 49 3.3.1 无法在源表中获得一组稳定的行 49 3.3.2 DELETE子句的WHERE顺序必须在最后 51 3.3.3 DELETE 子句只可以删除目标表，而无法删除源表 52 3.3.4 更新同一张表的数据，需担心USING的空值 53 3.4 MERGE的巧妙运用 54 第4章 神秘的NULL和令人讨厌的CHAR 58 4.1 神秘的NULL 59 4.1.1 NULL概述 59 4.1.2 NULL与函数 64 4.1.3 NULL与索引 79 4.1.4 NULL与SQL 87 4.1.5 NULL与PL/SQL 98 4.1.6 再谈NULL和空字符串区别 102 4.1.7 NULL总结 102 4.2 令人讨厌的CHAR 103 4.2.1 CHAR与VARCHAR2基础 103 4.2.2 CHAR与SQL 106 4.2.3 CHAR与绑定变量 110 4.2.4 CHAR与PL/SQL 111 4.2.5 CHAR总结 112 第5章 报表开发之扩展GROUP BY 113 5.1 扩展GROUP BY概述 113 5.2 ROLLUP 114 5.2.1 UNION ALL实现ROLLUP功能 114 5.2.2 ROLLUP分组 116 5.2.3 部分ROLLUP分组 120 5.2.4 ROLLUP总结 121 5.3 CUBE 121 5.3.1 CUBE分组 121 5.3.2 部分CUBE分组 123 5.3.3 CUBE总结 124 5.4 GROUPING SETS实现小计 124 5.4.1 GROUPING SETS分组 124 5.4.2 部分GROUPING SETS分组 126 5.4.3 CUBE、ROLLUP作为GROUPING SETS的参数 126 5.4.4 GROUPING SETS总结 128 5.5 组合列分组、连接分组、重复列分组 128 5.5.1 组合列分组 130 5.5.2 连接分组 132 5.5.3 重复列分组 134 5.5.4 组合列分组、连接分组、重复列分组总结 134 5.6 3个扩展分组函数：GROUPING、GROUPING_ID、GROUP_ID 135 5.6.1 GROUPING函数 135 5.6.2 GROUPING_ID函数 138 5.6.3 GROUP_ID函数 142 5.6.4 扩展GROUP BY函数总结 144 5.7 扩展分组综合实例 144 第6章 探索Oracle自动类型转换 149 6.1 为什么不建议使用自动类型转换 150 6.2 自动类型转换规则 156 6.3 自动类型转换常见错误 166 第7章 Where in list问题解析 169 7.1 问题引入：动态SQL构造 170 7.2 使用正则表达式解决 172 7.3 使用常规字符串函数及动态视图 173 7.4 使用集合构造伪表 176 7.5 Where in list性能问题 178 7.6 Where in list问题总结 188 第8章 例说数据库表设计和大量数据处理方法 189 8.1 根据业务需求规划表结构 189 8.2 主键的确定 195 8.3 表的拆分、合并及数据的规范和反规范化 199 8.4 数据类型的选择 199 8.5 表的分区 203 8.6 表的压缩 203 8.7 抽取数据 205 8.7.1 利用SQL*PLUS的SPOOL功能 205 8.7.2 利用UTL_FILE 207 8.7.3 利用第三方工具 207 8.8 转换数据 207 8.8.1 数据的增删改 208 8.8.2 数据汇总 211 8.9 加载数据 227 8.9.1 利用SQLLDR加载 227 8.9.2 利用外部表加载 229 8.9.3 利用数据库链接加载 233 8.9.4 利用分区交换技术加载 234 8.10 逻辑导入/导出数据 240 第9章 数据审核 242 9.1 审核的分类 242 9.2 列审核设计 243 9.2.1 直接使用SQL语句 245 9.2.2 提高审核效率 248 9.2.3 管理审核关系 254 9.2.4 单个列的复杂审核 257 9.3 行审核设计 258 9.3.1 重码的审核 259 9.3.2 直接使用SQL语句 260 9.3.3 管理审核关系 261 9.3.4 行间包含四则运算的审核思路 266 9.3.5 更复杂的行间审核思路 267 9.4 小结 267 第10章 趣谈Oracle分析函数 269 10.1 概述 269 10.2 函数语法 270 10.3 函数列表 276 10.4 函数用法 277 10.4.1 最常见的分析函数应用场景 279 10.4.2 ROWS的具体用法 280 10.4.3 RANGE的具体用法 281 10.4.4 KEEP的用法 283 10.5 函数功能详解 284 10.5.1 统计函数 284 10.5.2 排序函数 285 10.5.3 数据分布函数 287 10.5.4 统计分析函数 289 10.6 分析函数在BI及统计上的应用 290 10.6.1 现状分析 290 10.6.2 发展分析 297 10.7 自定义聚集函数 298 10.7.1 自定义聚集函数接口简介 298 10.7.2 应用场景一：字符串聚集 299 10.7.3 应用场景二：指数移动平均线 301 第11章 Oracle层次查询 305 11.1 概述 305 11.2 样例数据 306 11.3 CONNECT BY和START WITH查询 306 11.3.1 概述 306 11.3.2 基本语法 306 11.3.3 样例 308 11.4 SYS_CONNECT_BY_PATH函数 313 11.5 WMSYS.WM_CONCAT非公开函数 314 第12章 号段选取应用 316 12.1 问题的提出 316 12.2 相关基础知识 317 12.2.1 伪列rownum和level 317 12.2.2 利用层次查询构造连续的数 317 12.2.3 用lead和lag获得相邻行的字段值 318 12.3 解决问题 320 12.3.1 已知号码求号段 320 12.3.2 根据号段求出包含的数 324 12.3.3 求缺失的号 326 12.3.4 求尚未使用的号段 330 12.4 小结 334 第13章 分析SQL执行计划的关注点 335 13.1 返回行与逻辑读的比率 335 13.2 聚合查询 338 13.3 返回行的数量 341 13.4 Oracle预测行准确与否 342 13.5 Predicate Information 346 13.6 动态采样 348 13.7 谁是主要矛盾 349 第14章 Oracle开发误区探索 352 14.1 避免对列运算 352 14.2 消除隐式转换 354 14.3 关注空格 354 14.4 存储过程与权限 355 14.5 提防DDL提交事务 357 14.6 lnsert into 错误 359 14.7 关于or 360 14.8 sequence中的cache 361 14.9 树形查询易错处 364 14.10 小心保留字 367 14.11 函数索引陷阱 371 14.12 外连接陷阱 374 14.13 标量子查询 375 第15章 提升PL/SQL开发性能漫谈 378 15.1 重视解析 378 15.1.1 触发器尽量考虑内部代码过程封装 378 15.1.2 避免动态SQL 380 15.1.3 OLTP系统中尽量使用绑定变量 382 15.2 关注调用 384 15.2.1 减少对sysdate的调用 384 15.2.2 避免对mod函数的调用 385 15.2.3 设法减少表扫描次数 386 15.2.4 避免SQL中的函数调用 391 15.3 简洁为王 395 15.3.1 尽量用简单SQL替代PL/SQL逻辑 395 15.3.2 避免不必要的排序 397 15.3.3 利用Oracle现有功能 399 15.4 细致入微 400 15.4.1 使用pls_integer类型 400 15.4.2 避免数据类型转换 400 15.4.3 IF的顺序有讲究 402 15.4.4 设计开发对列是否为空慎重决定 404 15.4.5 分布式应用开发不可不知的HINT（driving_site） 407 第16章 管道函数的学习与实战应用 410 16.1 管道函数编写要点 410 16.2 功能描述及案例 411 16.2.1 可直接用TABLE()操作符方式输出结果集 411 16.2.2 结果集可作为表来使用，直接和其他表相关联 411 16.2.3 实现实时跟踪 412 16.3 管道函数注意点 414 16.3.1 清楚管道函数只是表函数的一种 414 16.3.2 管道函数与DML 415 16.3.3 管道函数集合的限制 416 16.3.4 与管道函数相关的异常NO_DATA_NEEDED 417 第17章 巧用锁特性避免重复启动同一程序 420 17.1 基本技巧介绍 420 17.2 原理介绍 422 17.3 程序代码 423 17.4 测试 426 17.5 需要注意的点 427 第18章 11g R2 新特性之递归with编程精粹 428 18.1 上下级关系 429 18.2 构造数列 435 18.3 排列组合问题 441 18.4 沿路径计算 457 18.5 国际SQL挑战赛 473 第19章 不可能的任务？超越Oracle 479 19.1 为何选择cube 480 19.1.1 cube简介 480 19.1.2 cube优势 480 19.1.3 测试环境 481 19.2 newkid的设计思路和优化步骤 482 19.2.1 一次扫描求出16种分组 482 19.2.2 一次扫描求出1种分组，并基于此再一次扫描求出其余分组 484 19.2.3 一次扫描求出1种分组，并基于此再多次分层累计求出其余分组 485 19.2.4 变量长度和类型的优化 487 19.3 为何选择OCI 488 19.3.1 汇总算法的翻译和改进 488 19.3.2 输入和输出的实现和改进 489 19.3.3 不要忽视物理存储对数据库操作的影响 490 19.3.4 不成功的优化教训 490 19.4 总结 493 第20章 动态SQL扩展 494 20.1 动态SQL用途和应用场景 494 20.2 EXECUTE IMMEDIATE语法 495 20.2.1 执行DDL、DCL语句 495 20.2.2 构造通用SQL处理 496 20.2.3 执行复杂业务逻辑查询 498 20.2.4 绑定变量 502 20.2.5 BULK COLLECT动态SQL 505 20.3 使用DBMS_SQL包 507 20.3.1 DBMS_SQL函数和过程说明 507 20.3.2 DBMS_SQL执行步骤 508 20.3.3 DBMS_SQL应用场景 509 20.3.4 DBMS_SQL与EXECUTE IMMEDIATE比较 509 后记 511