前    言

本书介绍如何使用PL/SQL编程语言。书中包含很多例子和技术，可以帮助创建稳健的以数据库为中心的应用程序。附录A介绍Applications DBA或者开发人员应该了解的基本信息，例如启动和停止Oracle数据库及侦听器、使用SQL*Plus作为命令行接口、使用SQL Developer作为免费的跨平台的GUI界面，以及SQL调优的技术。附录B、C和D介绍如何编写SQL、使用SQL内置函数，以及使用PL/SQL内置包。其余附录介绍如何使用正则表达式函数、包装PL/SQL代码、对PL/SQL使用分层的配置文件，并了解保留字和关键字。

对于作者来说，前言通常是最后或最初撰写。与前面版本的策略不同，这次我选择在开始时写前言，这帮助我始终如一地按照计划完成本书。正如我在致谢中说过的，项目成员也帮助我理清了本书的内容，而且他们的才能是出版这一本好书的关键。

前言包含以下方面：

● “本书大纲”小节用一到两句话总结了每一章，值得快速浏览一下，以便对本书的组织结构有一个大概的了解。

● “词法”小节介绍本书中的变量命名规则的基本原理，并为调试代码提供推荐的、节省时间的技术。

● “数据模型和源代码下载”小节介绍示例的基本信息，并告知哪里可以找到创建和生成示例音像店数据库的代码。

本书大纲

本书分为三部分：“Oracle PL/SQL”、“PL/SQL编程”以及“附录和术语表”。在本书的前两个部分中，每一章的每一个重要的小节都以“章节回顾”结尾，列出该小节中的关键点。而且，前两部分中的每一章都包含“精熟测验”，含有10道判断题和5道多选题，帮助你确认对本章覆盖的知识的理解程度。答案在附录I中。

第Ⅲ部分“附录和术语表”包含有关Oracle Database 12c、SQL、SQL内置函数、PL/SQL内置包、正则表达式、包装PL/SQL代码、PL/SQL分层的配置文件，以及保留字和关键字的入门知识。如提到过的，附录I中提供“精熟测验”的答案。术语表在最后一个附录之后。

第Ⅰ部分：Oracle PL/SQL

● 第1章“Oracle PL/SQL程序开发概览”介绍PL/SQL的历史和背景，并介绍Oracle开发架构。历史和背景小节介绍SQL如何成为主要接口和PL/SQL如何使用内置命令式编程语言扩展SQL的行为，启用对象关系型功能，允许DBA和开发人员利用Oracle Database 12c的能力。Oracle开发架构小节介绍SQL接口如何作为一个交互式的调用命令行接口(CLI)，以及两层或者n层模型如何与Oracle Database 12c一起使用。

● 第2章“新特性”介绍Oracle Database 12c SQL和PL/SQL新特性。该章假定你了解Oracle Database 11g特性。新的SQL特性包含非可见列和标识列、VARCHAR2数据类型扩展过的长度、强化过的外联接操作。新的PL/SQL特性包含调用者权限结果缓存函数、PL/SQL调用者白名单、新的错误堆栈管理特性、在SQL WITH子句中嵌入函数，在嵌入式的SQL语句中使用局部PL/SQL数据类型。

● 第3章“PL/SQL基础知识”介绍并提供PL/SQL编程语言基础知识的示例。该章介绍PL/SQL块结构、块中变量的行为、基本标量和复合数据类型、控制结构、异常、批量操作、函数、过程、包、事务作用域和数据库触发器。在第3章中可以找到PL/SQL语法的所有基本元素的示例。如果你需要知道如何编写PL/SQL程序的基础知识，这也是一个很好的起点。

● 第4章“语言基本知识”介绍词汇单元(分隔符、标识符、字面值或注释)以及变量和数据类型。在第4章中，可以学习PL/SQL程序单元的基本构建块。还可以学习哪些数据类型可用及如何声明这些数据类型的变量。后面的章节会假定你知道哪些数据类型可用及如何在匿名和命名PL/SQL块中声明它们，这使得该章成为很重要的章节。在深入研究PL/SQL语言核心特性之前，要先阅读或者研究第4章。

● 第5章“控制结构”介绍条件语句、迭代语句、游标结构和批语句。该章全面地介绍IF语句和循环。Oracle实现IF语句或CASE语句来管理条件逻辑，用简单、FOR和WHILE循环来管理迭代语句。关于循环的讨论介绍了守卫和标记值以及对动态标记值的保护。这一章介绍如何在循环中管理游标和如何管理批处理DML语句。

● 第6章“集合”介绍如何使用SQL变长数组和表集合以及PL/SQL关联数组(之前称为PL/SQL表或索引表)。该章通过讨论变长数组和表集合，介绍了如何使用属性数据类型(ADT )和用户自定义类型(UDT)。同时介绍了使用和处理ADT和UDT变量之间的区别。这一章还介绍了如何处理使用标量数据类型或者复合数据类型的PL/SQL专有关联数组，它可能是记录类型或者对象类型。该章最后介绍Oracle集合API的函数和过程，并提供使用这些函数和过程的示例。

● 第7章“错误管理”介绍如何在PL/SQL中使用异常。该章介绍异常类型和作用域、异常管理内置函数、用户自定义异常和异常堆栈函数。同时介绍如何找到并解决编写PL/SQL程序时发生的典型的错误。该章还介绍如何编写异常处理程序，用于管理意外出现的运行时异常。还可以学习到如何管理异常栈。

第Ⅱ部分：PL/SQL编程

● 第8章“函数和过程”介绍PL/SQL函数和过程的架构、事务作用域、函数选项和实现以及过程的实现。架构部分介绍按值传递和按引用传递的函数和过程的工作方式，包括如何为Oracle Database 12c中的独立函数和过程创建白名单。同时介绍SQL的位置、命名、混合和排除调用表示法。它还介绍用于定义函数的各种方式，例如确定性的、启用并行的、管道的和结果缓存的函数。它介绍如何实现返回用户自定义类型的集合的对象表函数。该章还介绍如何编写递归和自治函数。

● 第9章“包”介绍如何操作和使用包。该章涉及了包结构、包规范和包主体。还将定义者权限和调用者权限模型进行比较，并介绍数据库目录如何管理包规范和包主体的状态和有效性。该章介绍了如何编写向前引用的占位程序以及如何重载函数和过程。还介绍了如何为包规范创建白名单。

● 第10章“大对象”介绍如何处理BLOB、CLOB和NCLOB内部管理数据类型和BFILE外部管理数据类型。该章介绍如何创建和处理内部管理的字符和二进制大型对象，以及如何处理外部管理的二进制文件。

● 第11章“对象类型”介绍如何处理对象类型。该章介绍如何声明、实现、过滤对象类型，还有如何实现getter、setter和对象比较函数。在介绍了这些基本信息之后，该章介绍在实现对象类型集合之前如何实现继承和多态。

● 第12章“触发器”介绍数据库触发器，然后介绍如何理解并实现各种类型的触发器，包括DDL、DML、复合、INSTEAD OF以及系统和数据库事件触发器，还介绍触发器约束。

● 第13章“动态SQL”介绍动态SQL语句的基本信息。该章涉及本地动态SQL(NDS)和dbms_sql包。对动态SQL语句的4个方法都予以介绍，例如静态DDL和DML语句、动态DML语句、带静态SELECT列表的动态SELECT语句和带动态SELECT列表的动态SELECT语句。

第Ⅲ部分：附录和术语表

● 附录A“Oracle数据库入门”介绍Oracle Database 12c架构、如何启动和停止Oracle Database 12c服务器和Oracle侦听器、多版本并发控制、定义者权限和调用者权限、SQL交互和批处理、数据库管理、SQL调优和数据库跟踪。

● 附录B“SQL入门”介绍在Oracle Database 12c中使用SQL。该附录包含SQL数据类型、DDL语句、DML语句、TCL语句、SELECT语句和SET语句。该附录还介绍如何及嵌套查询和如何使用持久对象类型。

● 附录C“SQL内置函数”提供代码完整的示例，介绍如何使用Oracle Database 12c的关键SQL内置函数。该附录包含字符函数、数据类型转换函数、日期时间函数、集合管理函数、集合运算符、数字函数、错误处理函数和杂项函数。

● 附录D“PL/SQL内置程序包和类型”介绍如何在Oracle Database 12c中使用SQL。该附录介绍新的PL/SQL内置包并提供一些关键包的示例。

● 附录E“正则表达式入门”介绍在SQL和PL/SQL中如何使用正则表达式。

● 附录F“包装PL/SQL代码入门”介绍如何使用dbms_ddl包的create_wrapped或wrap过程。

● 附录G“PL/SQL分层的配置文件入门”介绍如何使用PL/SQL Hierarchical Profiler。该附录展示如何配置这个模式、收集分析数据、理解Profiler输出，以及使用plshprof命令行工具。

● 附录H“PL/SQL保留字和关键字”指出哪些是Oracle Database 12c中的保留字和关键字。

● 附录I“精熟测试答案”提供各章中“精熟测试”小节的答案。

● 术语表提供本书中出现的关键概念的定义。

词法

编写程序有很多方式，通常编程语言之间是有区别的。SQL和PL/SQL代码分享这一共性：它们是不同的语言，需要不同的方法。3个小节分别介绍：SQL词法、PL/SQL存储程序以及语法中的其他约定。

SQL词法

对于SQL语句，我的建议是将关键字排列在左边。那意味着将SELECT列表逗号和WHERE子句逻辑AND [NOT]或者OR [NOT]放在左边，因为它允许你视读(sight read)代码错误。这个建议很容易理解，但是对于如何编写联接语法，我的建议更复杂一些，因为你可能会编写使用ANSI SQL-89或ANSI SQL-92的联接。ANSI SQL-89允许你使用逗号分隔列表来组织表，而ANSI SQL-92允许你使用关键字指定联接类型。

下面是我对于联接语法的建议：

● 总是使用表的别名，因为它们保证当SELECT列表能够返回两个或者多个同名列的时候，不会发生歧义列的错误。当联接那些共享相同列名的表时，会发生这种情况。当编写单一表查询的时候，这也是个很好的操作，因为你随后可能会通过联接添加另一个表。附录B包含了SELECT语句和支持这个建议的语法。

● 当使用ANSI SQL-89和逗号分隔表的时候，将每个表放到它自己的行上，并将分隔列放到左边，例如SELECT列表列。这帮助你视读你的程序。这个不能应用于附录B中提到的多表UPDATE和DELETE语句，你可以参考那些章中的示例。

● 当使用ANSI SQL-92的时候，使用ON子句或者USING子句将联接条件放到FROM子句中。对于含有ON或者USING子句的FROM子句，有两个通用的方法看起来对多数开发人员是最合适的。在小的(两个或者最多三个)表联接中，在联接同一行之后加上ON或USING子句。在大的联接中(三个或者更多)，在联接语句的下一行添加ON或USING子句。当联接涉及多个列的时候，左对齐逻辑AND [NOT]或者OR [NOT]语法允许你视读你的代码。这和本小节开始的时候我对WHERE子句提出的建议是相同的，通常它确实很有用。

● ANSI SQL-92允许你使用充分描述的关键字或者只使用被需要的关键字。尽管我们中的多数人想要输入最低限度的命令，但最终我们的代码会转给支持人员，而且它的明确性可以帮助避免琐碎的错误报告。因此，考虑使用INNER JOIN代替JOIN、用LEFT OUTER JOIN或RIGHT OUTER JOIN代替LEFT JOIN和RIGHT JOIN、用FULL OUT JOIN代替FULL JOIN。我在本书中缩短了语法，因为页宽规定命令行仅限70个字符(或者可以要求缩小字体，但是会影响阅读)。

接下来我要分享未遵循语法建议的那些体验。这些建议是1985年我在IBM圣特雷莎实验室(现在的IBM硅谷实验室)的导师教我如何编写SQL(实际上是SQL/DS [Structured Query Language/Data System])的时候告诉我的。他告诉我将逗号放在左边可以节省我搜索漏掉的逗号的时间。我忽略了这个建议，并将它们放到了右边，最终在意识到他是对的之前浪费了好几个月的时间。他在那一周总是对我重复一个准则：“好的编程遵循简单的原则。”

现在在学校，我每个学期都强调这个建议。有些学生接受并使用它，而有一些则不然。那些不接受它的学生在课程中自始至终与语法较劲，因为他们总是尝试找到SQL语句中漏掉的逗号或者组件。SQL不是容易学习的东西，因为它要求创建数据的空间图，这项技能不是所有的开发人员都能立刻掌握的。有时它需要相当多的时间来理清关系型数据库中数据的关系。练习会让这变得简单起来，前提是你努力维护语句的明确性、方法的一致性以及坚持使用可移植SQL语法。

关于工具

本书关注在命令行中编写SQL，因为那是在C++、C#、Java或者PHP程序中的工作方式，但是CASE(Computer-Aided Software Engineering)工具很好。它帮助你发现语法和可能性，假如你不是将它们作为拐杖来使用的话。

最好的开发人员不是那些口若悬河并使用所有煽情的口号来推销自己的商业用户。最好的开发人员是那些知道哪种技术真的能提供最好的解决方案来解决业务问题的人们。

那些应用好的开发技术的人当他们限定自己仅使用CASE工具的时候，他们并不是专属社团的成员。之所以这么说，是因为CASE工具通常只通过拖放界面来解决一般问题。那些主张NoSQL解决方案的人们基本上都是那些永远不知道如何使用数据库或者数据库如何满足日常事务业务需求的人。

简单来说，使用工具进行学习，但是不要成为它的奴隶。要一直询问为什么可以成功和如何能做得更好。如果你这样做了，那么就会发现CASE工具对完成工作是一个福音，而不是一个潜在的绊脚石(正如在过去一些年中发现的那些案例一样)。

PL/SQL存储程序

PL/SQL是一个非常成熟的编程语言。它允许你编写存放在数据库中的程序，用于作为一个完整的事务管理SQL语句的集合。

变量命名约定在一些组织中是有争议的，因为很多开发人员相信变量应当是语义上有意义的。对于命名约定的争论是约定(例如前缀)会降低代码的可读性。这个争论简单来说是想法的冲突。双方都有优点，总有要选择一个比另一个更有逻辑的情况。我的观点是，关键是在提供有意义的变量名和为公司或企业增加稳定性这两者之间找到平衡点。

在本书中，我尽量保持一致并使用前缀。在有些地方，我选择变量名中语义明确(例如第2章中的Oracle会话或者绑定变量:whom)。我相信使用前缀会增加代码的可读性，我建议使用表0-1中的前缀。

某些高级变量数据类型(即复合变量)同时需要前缀和后缀。后缀表明复合变量的类型。这些要求对Oracle数据库都是唯一的。表0-2展示我推荐的Oracle复合变量类型的后缀(带有前导下划线)。表0-2展示后缀的长名和短名版本。

为复合数据类型使用后缀是普遍接受的做法，因为它们是UDT。然而，在PL/SQL编程语言中它不是规则或者要求。

PL/SQL是一种带有声明、执行和异常块的强类型语言。块程序使用关键字开始和结束程序单元，与C++、C#、Java和PHP中使用花括号相反。和在GeSHi(Generic Syntax Highlighter)库中找到的一样，PL/SQL块关键字都是大写字母，我已经在本书中采用那个约定。

表0-1  PL/SQL变量前缀

前    缀   示    例 描    述

cv cv_input_var 表示游标参数变量。这些是PL/SQL存储程序中游标的按值传递输入参数

lv lv_target_var 表示PL/SQL存储程序中定义的局部变量

pv pv_exchange_var 表示PL/SQL存储函数和过程的参数。它们不是专用的输入参数，因为PL/SQL支持存储函数和过程中的输入和输出参数

sv sv_global_var 表示会话变量。它们在客户端连接到数据库存续期间作为全局变量。Oracle允许在变量名前使用冒号(:sv_global_var)来在匿名块之间共享变量值。这也称为绑定变量

表0-2  PL/SQL变量后缀

后    缀 描    述

长 短

_ATABLE

_AARRAY _ATAB

_AA _ATABLE、_AARRAY、_ATAB和_AA用于描述PL/SQL中的关联数组。我的首选是_ATABLE或_ATAB后缀，因为其他后缀不直观而且在代码中需要说明

_CURSOR _CUR

_C _CURSOR、_CUR和_C用于描述基于PL/SQL中局部声明块或者包规范内定义的游标结构的变量。我的首选是_CURSOR或者_C后缀

_EXCEPTION _EXCEPT

_EX

_E _EXCEPTION、_EXCEPT、_EX和_E用于描述PL/SQL中用户自定义的异常。我的首选是_EXCEPTION或者_E后缀

_OBJECT _OBJ

_O _OBJECT、_OBJ和_O用于描述SQL和PL/SQL中的用户自定义类型(UDT)。对象类型可以像PL/SQL RECORD数据类型一样，是记录数据结构。它们不同是因为它们是模式级别SQL UDT而不是专用PL/SQL UDT。对象类型也可以是可实例化对象，例如C++、C#和Java类。我的首选是_OBJECT或者_O后缀

_NTABLE

_TABLE _NTAB

_TAB _NTABLE、_TABLE、_NTAB或者_TAB用于描述嵌套表，它们是SQL和PL/SQL中的集合类型。它们像列表一样，因为集合中元素数量是没有上限的。我的首选是_TABLE或者_TAB后缀，因为嵌套表是集合，像其他编程语言中的列表一样

_RECORD _REC

_R _RECORD、_REC和_R专门用于描述PL/SQL的UDT。它们是记录数据结构的PL/SQL实现。它们可以是PL/SQL集合的元素但不是SQL集合的元素。我的首选是_RECORD或者_R后缀，因为它们是完整描述或者简写，但是很多开发人员选择_REC

(续表)   

后    缀 描    述

长 短

_TYPE _T _TYPE和_T用于描述UDT，这和第4章中介绍过的普通标量数据类型的子类型一样。两个对我来说都很合适，但是_TYPE在代码库中更常使用

_VARRAY _VARR

_VA _VARRAY、_VARR和_VA用于描述VARRAY(对于这个Oracle数据类型，我的记忆是虚数组)。VARRAY集合是更像编程语言中的标准数组的集合，因为它有最大值而且必须有顺序索引值。可以用于定义SQL和PL/SQL集合。我的首选是VARRAY或者_VA后缀，因为_VARR太类似普通变量简写

其他约定

有时代码块需要明确性。在Oracle的PL/SQL和SQL示例中一直提供行号，因为它们是SQL*Plus环境的显示特性。

本书的文本约定包含醒目、斜体和分隔语法。它们在表0-3中介绍。

希望这些约定可以使本书更容易阅读。你也会在本书中的阴影区域中找到补充信息。

表0-3  文本约定

约    定 意    义

粗体字 关注示例程序中的特定代码行

斜体字 关注新单词或者概念

大写形式 表示SQL和PL/SQL中使用的关键字，以及SQL内置函数名

小写形式 表示用户自定义表、视图、列、函数、过程、包和类型的名称

[] 表示可选择的语法并在原型中显示

{} 组合选项列表，使用单个竖线(|)分隔

| 表示选项列表间的逻辑OR运算符

... 表示内容重复或者为了节省空间被移除的部分

数据模型和源代码下载

这个数据模型是一个小的音像店。创建并生成Oracle数据模型的源代码可以在本书的支持网站中找到：

www.OraclePressBook.com

图0-1展示的是在示例程序中使用的基本或核心表。

图0-1  音像店实体关系图(ERD)

模型中的一个表可能需要一些说明，那就是common_lookup表。common_lookup是一个表的表，如图0-2所示。

图0-2  common_lookup表(表的表)

唯一标识行的一组属性(列)是自然键。它由表和列名加上类型组成。类型是由下划线连接的大写字符串，使得查询这些查找集更容易。common_lookup_meaning列提供用于供最终用户在下拉列表框中选择的信息。

common_lookup表的主键是代理键列common_lookup_id(遵循使用表名和_id后缀作为主键列名的做法)。这个值的副本存放在表和列中，例如item和item_type。运用这种设计类型，可以在一个单一的位置将XBOX的显示值修改为Xbox，而所有的代码模块和表值不发生变化。它是一个有用的建模装置，因为它避免在Web表单中放置像性别、种族或者yes/no回答这样的组件(嵌入式选项)，而且它降低了部署之后应用程序的管理成本。

让我们看一下在基于Web的应用程序中利用通用查找表的方法。这个例子开始于存放在两个表(member和contact表)的联接中的数据。内部查找使用contact表中客户的名字(自然键)来查找member表中的成员账户信息。 

 SELECT  m.account_number

,        member_type                 -- A fk to common_lookup table.

,        m.credit_card_number

,        m.credit_card_type          -- A fk to common_lookup table.

,        c.first_name

,        c.middle_name

,        c.last_name

,        c.contact_type               -- A fk to common_lookup table.

FROM     member m INNER JOIN contact c

ON       m.member_id = c.member_id

WHERE   c.first_name = 'Harry'

AND     c.middle_name = 'James'

AND     c.last_name = 'Potter';

当通过第13章中的dbms_sql方法4代码示例(在左边显示列名，在右边显示列值)来运行时，上面的查询返回以下信息。需要注意，member_type、credit_card_type和contact_type列存放基于common_lookup_id代理键列的外键值。

可以选择使用这些值通过联接或者通过函数调用common_lookup表来连接数据。common_lookup表包含在应用软件表单中频繁显示的值。

下面的联接将所有的3个外键连接到common_lookup表中3个不同的行。

 SELECT m.account_number

,       cl1.common_lookup_meaning -- Customer friendly display.

,       m.credit_card_number

,       cl2.common_lookup_meaning -- Customer friendly display.

,       c.first_name

,       c.middle_name

,       c.last_name

,       cl3.common_lookup_meaning -- Customer friendly display.

FROM    member m INNER JOIN contact c

ON      m.member_id = c.member_id JOIN common_lookup cl1

ON      cl1.common_lookup_id = m.member_type JOIN common_lookup cl2

ON      cl2.common_lookup_id = m.credit_card_type JOIN common_lookup cl3

ON      cl3.common_lookup_id = c.contact_type

WHERE   c.first_name = 'Harry'

AND     c.middle_name = 'James'

AND     c.last_name = 'Potter';

上面的查询生成下列有意义的业务信息：

查询返回的数据是对称的，这意味着所有列都返回相同数量的行。上面查询的结果是3个查询动作的业务结果，它们返回的是一个业务用户预先选择的值。然而，这个结果不是你为了表现修改值的能力而想要显示在Web表单中的，例如成员、信用卡或者联系方式。之所以说它们不是要显示的正确的值，是因为你需要当前选择的值以及当最终用户操作应用软件表格(如图0-3所示)的时候可以选择的替换值列表。查询不能提供那个能力，因为结果集限定为对称数据，如上一个查询显示的那种。

图0-3  Web表单选择区域

需要使用外键作为函数调用的参数来获得当前和可能的值，在本示例中实际需要使用表名、列名和当前值来调用。在HTML Web表单中，函数应当返回一组HTML option标记以嵌入HTML select标记中。来自查找的当前选定值应当是选定的HTML option标记，其他可能值应当是未选定的HTML option标记。这个方法可以返回非对称结果集，如下所示：

对于一般的引用值采用这种方法可以使你的应用程序代码更容易地使用可重用的模块。当然，这种函数类型更适合于Oracle Database 12c应用程序中的PL/SQL结果缓存函数。