PL/SQL集合相关功能探讨

　　Oracle PL/SQL变得更快、更易于使用，特性集也更加丰富了。Oracle数据库10g通过一系列有益的改进，继续保持了PL/SQL在速度、简单易用性和特性扩展方面的传统优势，这些改进包括：
　　
　　大大提高了执行速度,这要归功于透明的性能改进，其中包括一个新的优化编译器、更优的集成化本地编译功能，以及帮助解决数字运算应用程序问题的新的数据类型。
　　
　　FORALL语句更加灵活、更加有用。例如：FORALL现在支持非连续索引。
　　
　　正则表达式以三个新函数（REGEXP_INSTR、REGEXP_REPLACE和REGEXP_SUBSTR）和用于比较的REGEXP_LIKE运算符的形式用于PL/SQL语言中。（要获得更多信息，参见本期杂志中Jonathan Gennick撰写的《一流的表达式》一文。）
　　
　　集合得到了改进，包括比较集合是否相同、支持对嵌套表进行集合运算等。
　　Oracle数据库10g使PL/SQL继续保持其作为Oracle数据库的最高效、最富生产力的编程语言的地位。其在性能方面的大大提升，以及对IEEE算法和正则表达式的支持，如今完全开启了将PL/SQL作为首选语言的新的功能领域。
　　
　　作为介绍Oracle数据库10g中的PL/SQL系列文章的第一篇，本文将对Oracle数据库10g中与集合相关的一些改进进行探讨。
　　
　　比较集合
　　20世纪90年代末期，当我首次对开发人员进行PL/SQL培训的时候，很少有人使用（甚至知道）包（package），而现在，很多人（包括我自己在内）都认为它是任何设计精良的PL/SQL应用程序的基础。如今，打包软件得到了广泛使用。 目前，非常重要但未得到充分利用的PL/SQL特性的前沿领域似乎就是集合的使用了。
　　
　　集合是Oracle中的数组，集合是一维列表。早在Oracle7中，就已经用到了集合（那时，称作“PL/SQL表”），但其功能及性能却很有限。不过，Oracle后来的各个版本都对集合进行了改进。在Oracle数据库10g中，这些数据结构对于几乎所有的复杂PL/SQL应用程序项目来说都是强有力的、快速的和必不可少的。
　　
　　Oracle数据库10g对集合的一个关键性改进是能够比较两个集合内容的相同之处（和不同之处）。在Oracle数据库10g之前，你也可以对两个集合进行比较，但必须为此编写一个函数。编写这样的程序时需要考虑数个复杂因素，包括：
　　
　　必须为正在使用的各个集合类型分别编写程序。即使两个集合中的数据类型相同，但如果它们不是基于完全相同的类型定义的，你都需要使用不同的函数来进行比较。
　　
　　必须对表的内容进行逐行比较，这就意味着必须进行“全集合扫描”。完成这一任务的代码不是很复杂。但是，这一代码冗长乏味，易于出错，特别是比较诸如记录、对象或者XMLType等复杂数据类型的集合时更是如此。
　　
　　你必须决定如何处理NULL。如果两行都包含NULL，那么它们相同吗？Oracle认为：“它们既不是相同，也不是不相同，”但是，你的判断可能会与此不同，你必须编写代码来处理这个问题。
　　这种复杂性导致你不愿意经常编写这类程序，你甚至会在应用程序中回避编写这类程序。
　　
　　在Oracle数据库10g中，collcompare.sql文件包含了这类程序的一个示例，该程序是为基于employee表的记录集合而编写的。
　　
　　假设我在collcompare.sql脚本中安装了emp_coll_pkg.equal函数。我可以按照如下方式来使用它：
　　
　　DECLARE
　　dbas emp_coll_pkg.employee_tt;
　　developers emp_coll_pkg.employee_tt;
　　
　　BEGIN
　　populate_lists (dbas, developers);
　　
　　IF emp_coll_pkg.equal (dbas, developers)
　　THEN
　　DBMS_OUTPUT.PUT_LINE (
　　'Likely a very small IT organization!');
　　END IF;
　　END;
　　
　　这段代码简单明了、可读性好。（你不用再经历编写此类函数的痛苦过程多好啊！）为了让你工作更轻松，Oracle数据库10g现在允许你对两个嵌套表进行“原始”比较（native compare）了。换句话说，你不必再编写任何特定于集合的比较逻辑，而可以直接进行比较，如下所示：
　　
　　DECLARE
　　dbas emp_coll_pkg.employee_tt;
　　developers emp_coll_pkg.employee_tt;
　　BEGIN
　　populate_lists (dbas, developers);
　　
　　IF dbas = developers
　　THEN
　　DBMS_OUTPUT.PUT_LINE (
　　'Likely a very small IT organization!');
　　
　　END IF;
　　END;
　　
　　在这种情况下，集合比较仅适用于嵌套表。换句话说，你还不能直接比较两个关联数组（过去称作"索引表"）或者变长数组的内容。希望Oracle数据库的下一版本会增加关联数组和变长数组的比较功能。
　　
　　集合理论与Multiset Union运算
　　SQL语言很久以前就提供了将集合运算（UNION、INTERSECT和MINUS）用于查询结果集的功能。如今在Oracle数据库10g中，你可以对PL/SQL程序中的嵌套表（且仅限于嵌套表）和在关系表中声明为列的嵌套表使用上述功能强大的高级运算符。
　　
　　现在我们从UNION开始，看看这样做所需的一些语法。
　　
　　首先，我创建一个模式级别的嵌套表类型：
　　
　　CREATE OR REPLACE TYPE strings_nt
　　
　　IS TABLE OF VARCHAR2(100);
　　/
　　
　　然后，我定义一个包，在该包中，我创建并填充两个此种类型的嵌套表，每个嵌套表都包含一些我和我父亲最喜欢的东西：
　　
　　CREATE OR REPLACE PACKAGE favorites_pkg
　　IS
　　my_favorites　　strings_nt
　　:= strings_nt ('CHOCOLATE'
　　, 'BRUSSEL SPROUTS'
　　, 'SPIDER ROLL'
　　);
　　
　　dad_favorites　 strings_nt
　　:= strings_nt ('PICKLED HERRING
　　, 'POTATOES'
　　, 'PASTRAMI'
　　, 'CHOCOLATE'
　　);
　　PROCEDURE show_favorites (
　　title_in　 IN　 VARCHAR2
　　, favs_in　　IN　 strings_nt
　　);
　　END;
　　/
　　
　　在该包中，我还创建了一个用于显示strings_nt 嵌套表内容的过程。下面很快就会用到它。
　　
　　通过在任意程序外的包中定义这些集合，这些集合在我的对话期间会一直保持不变（保持其状态和值），直到我将更改或删除它们为止。这就是说，现在我可以在包外编写程序来对这些集合的内容进行操作了。
　　
　　注意，出于介绍集合功能的目的，我对该包进行了简化。在生产应用程序中，你应该时刻注意“隐藏”包主体中的包数据（如同这些集合一样），然后提供过程和函数来管理这些数据。
　　
　　例如，假设我想把这两个集合合并成一个“我们的最爱”的集合。在Oracle数据库10g出现以前，我必须编写一个将一个集合的内容转移到另一个集合的循环。而现在，我可以依赖MULTISET UNION运算符来实现这一点了，如下所示：
　　
　　DECLARE
　　our_favorites
　　strings_nt := strings_nt ();
　　BEGIN
　　our_favorites :=
　　favorites_pkg.my_favorites
　　MULTISET UNION
　　favorites_pkg.dad_favorites;
　　
　　favorites_pkg.show_favorites (
　　'ME then DAD', our_favorites);
　　
　　END;
　　/
　　
　　此脚本的输出结果为：
　　
　　ME then DAD
　　1 = CHOCOLATE
　　2 = BRUSSEL SPROUTS
　　3 = SPIDER ROLL
　　4 = PICKLED HERRING
　　5 = POTATOES
　　6 = PASTRAMI
　　7 = CHOCOLATE
　　
　　可以看出，两个嵌套表的值被合并到一起了。而且立刻就可以看到，MULTISET UNION运算符不同于SQL UNION运算符（实际上，与SQL的UNION ALL运算符完全相同）。对两个SELECT结果集进行UNION运算时，SQL引擎会自动生成一个惟一的有序结果集。换句话说，如果我的两个嵌套表都是查询，那么UNION将生成这样的结果集：
　　
　　BRUSSEL SPROUTS
　　CHOCOLATE
　　PASTRAMI
　　PICKLED HERRING
　　POTATOES
　　SPIDER ROLL
　　
　　这些数据是按照字母顺序排列的，且CHOCOLATE仅出现一次。为什么会产生不同的结果呢？因为嵌套表是一种多集合（multiset），mathworld.wolfram.com/Multiset.html 对其做了如下定义：
　　
　　“一种类似于集合的对象，在其内部，顺序没有意义，而多重性却具有明确的意义；因此，多集合{1, 2, 3}和{2, 1, 3}是等同的，而{1, 1, 2, 3}和{1, 2, 3}却是不同的。”
　　
　　Oracle文件中说，嵌套表和变长数组的区别在于嵌套表列中存储的数据不保存其顺序，而在变长数组中存储的数据保存其顺序。在Oracle数据库10g之前，这种区别在PL/SQL中没多大意义。现在，有了集合运算符之后，多集合（或嵌套表）的特性便显得极为重要了。
　　
　　为了更好地理解嵌套表数据没有顺序，MULTISET UNION也不会对结果嵌套表应用顺序这一特性，请我们来看看面的这个程序块：
　　
　　DECLARE
　　our_favorites
　　strings_nt := strings_nt ();
　　BEGIN
　　our_favorites :=
　　favorites_pkg.dad_favorites
　　MULTISET UNION
　　favorites_pkg.my_favorites;
　　favorites_pkg.show_favorites (
　　'DAD then ME', our_favorites);
　　END;
　　/
　　
　　与前面的程序块惟一不同的是，我改变了MULTISET UNION运算中嵌套表的顺序。结果就变为：
　　
　　DAD then ME
　　1 = PICKLED HERRING
　　2 = POTATOES
　　
　　3 = PASTRAMI
　　4 = CHOCOLATE
　　5 = CHOCOLATE
　　6 = BRUSSEL SPROUTS
　　7 = SPIDER ROLL
　　
　　如果你不希望合并之后的嵌套表中出现重复项，那么可以使用MULTISET UNION DISTINCT来对前面例子中的UNION运算进行修改，如下所示：
　　
　　favorites_pkg.dad_favorites
　　MULTISET UNION DISTINCT
　　favorites_pkg.my_favorites;
　　
　　输出结果会变为：
　　
　　DAD then ME
　　1 = PICKLED HERRING
　　
　　2 = POTATOES
　　3 = PASTRAMI
　　4 = CHOCOLATE
　　5 = BRUSSEL SPROUTS
　　6 = SPIDER ROLL
　　
　　chocolate不会重复出现了。
　　
　　通过上述这些试验，可以得出关于UNION运算的一些结论：
　　
　　当对嵌套表进行UNION运算的时候，必须在UNION前使用关键字MULTISET。
　　
　　与SQL UNION集合运算符不同，如果不指定DISTINCT子句，MULTISET UNION集合运算符就不会消除重复项。
　　
　　与SQL UNION集合运算符不同，MULTISET UNION集合运算符对运算结果集合中的元素不重新排序。 MULTISET UNION保持各集合中的原有顺序不变，只是把第二个集合的内容追加到第一个集合内容的后面。
　　但Oracle数据库10g中的集合支持不只是提供一个UNION运算符而已。
　　
　　找出共同点
　　假设你要找出两个集合中相同的所有元素。 在SQL中，你可以对两个关系表使用INTERSECT运算符。对于Oracle数据库10g中带有嵌套表的PL/SQL来说，你可以对集合使用MULTISET INTERSECT运算符。当然，如果你已经在是嵌套表的关系表中定义了列，那么你也可以对那些集合应用MULTISET INTERSECT。
　　
　　例如：使用下述代码，我就可以毫不费力地找出我和我父亲都喜爱的所有东西：
　　
　　DECLARE
　　our_favorites
　　strings_nt := strings_nt ();
　　BEGIN
　　our_favorites :=
　　favorites_pkg.my_favorites
　　MULTISET INTERSECT
　　favorites_pkg.dad_favorites;
　　
　　favorites_pkg.show_favorites (
　　'IN COMMON:', our_favorites);
　　END;
　　/
　　
　　输出结果如下：
　　
　　IN COMMON:
　　1 = CHOCOLATE
　　
　　找出不同点
　　如果你要确定不同点，那么可以使用MULTISET EXCEPT（与SQL MINUS运算符类似）。 通过MULTISET EXCEPT，你可以很快找出一个嵌套表中有而另一个嵌套表中没有的行。
　　
　　假设我父亲想要确定只有他自己喜欢的东西（而我并不喜欢的东西），可以用下述代码来实现：
　　
　　DECLARE
　　our_favorites
　　strings_nt := strings_nt ();
　　BEGIN
　　our_favorites :=
　　favorites_pkg.dad_favorites
　　MULTISET EXCEPT
　　favorites_pkg.my_favorites;
　　
　　favorites_pkg.show_favorites (
　　'ONLY DAD'S:', our_favorites);
　　END;
　　
　　/
　　
　　输出结果如下：
　　
　　ONLY DAD'S:
　　1 = HERRING
　　2 = POTATOES
　　3 = PASTRAMI
　　
　　找出惟一值
　　最后一个要介绍的MULTISET函数是SET。SET（没有MULTISET作为前缀）可以消除嵌套表中的重复项。从这点上来说，它与SQL DISTINCT聚合函数非常类似。
　　
　　假设我特别喜欢巧克力，以致于在嵌套表中输入了多次chocolate：
　　
　　CREATE OR REPLACE PACKAGE favorites_pkg
　　IS
　　my_favorites　　strings_nt
　　:= strings_nt ('CHOCOLATE'
　　, 'BRUSSEL SPROUTS'
　　, 'SPIDER ROLL'
　　, 'CHOCOLATE'
　　
　　);
　　END favorites_pkg;
　　
　　如果我只想查看那些惟一值，我只需调用SET函数即可，如下所示：
　　
　　DECLARE
　　keep_it_simple
　　strings_nt := strings_nt ();
　　BEGIN
　　keep_it_simple :=
　　SET (favorites_pkg.my_favorites);
　　
　　favorites_pkg.show_favorites (
　　'DISTINCT SET', keep_it_simple);
　　END;
　　
　　输出结果如下：
　　
　　DISTINCT SET
　　1 = CHOCOLATE
　　2 = BRUSSEL SPROUTS
　　3 = SPIDER ROLL
　　
　　我也可以在能够引用嵌套表本身的任何地方调用SET函数，如下所示：
　　
　　
　　favorites_pkg.show_favorites (
　　'DISTINCT SET',
　　SET (favorites_pkg.my_favorites));
　　
　　采用新特性编写的有趣应用程序
　　
　　PL/SQL使编程更加轻松、集合操作效率更高，这使人们能够创造性地考虑如何解决问题。
　　
　　考虑一下怎样确定两个关系表是否相同的问题。假如它们的结构相同，你如何能够确定它们的信息是否一样呢？一般来说，使用SQL拿出一个合理的解决方案并不太难。按如下所示使用MINUS和UNION差不多就可以解决这一问题：
　　
　　SELECT COUNT (*)
　　FROM ((SELECT *
　　
　　FROM table1
　　MINUS
　　SELECT *
　　
　　FROM table2)
　　UNION
　　(SELECT *
　　FROM table2
　　MINUS
　　
　　SELECT *
　　FROM table1));
　　
　　当此查询返回0行时，这两个表就一定有相同的内容。然而，这几乎没什么效率。现在看一看如何采用Oracle数据库10g中的嵌套表来实现它。
　　
　　首先，用PL/SQL语言声明一个模仿该表结构的嵌套表类型：
　　
　　DECLARE
　　TYPE table1_tt IS TABLE OF table1%ROWTYPE;
　　nt_copy1 table1_tt;
　　nt_copy2 table1_tt;
　　
　　然后以非常高效的方式，使用BULK COLLECT将该表中的所有数据装载到这些集合中：
　　
　　PROCEDURE load1 (e IN OUT table1_tt)
　　IS
　　BEGIN
　　SELECT *
　　BULK COLLECT INTO e
　　FROM table1;
　　END;
　　
　　现在，只需使用下述代码来检查表的等同性：
　　
　　BEGIN
　　load1 (nt_copy1);
　　load2 (nt_copy2);
　　IF nt_copy1 = nt_copy2 THEN ...
　　
　　这确实更简单明了，但是性能如何呢？我在Oracle数据库10g 的10i_tab_compare_with_coll.sql文件中使用DBMS_UTILITY.GET_TIME来计算这两种方法的所用时间。采用每种方法执行了1000次之后，我得出下述结果：
　　
　　嵌套表比较：0.05秒
　　
　　MINUS-UNION比较：5.14秒
　　下一步
　　阅读
　　关于Oracle数据库10g的更多信息
　　oracle.com/database
　　
　　Feuerstein撰写的更多文章
　　otn.oracle.com/oramag/webcolumns/2003
　　oreill.net.com
　　
　　Feuerstein的书籍
　　oreilly.oracle.com
　　
　　性能提升了两个数量级！当然，会有其他方面的一些牺牲。由于使用嵌套表，你会用光运行这段代码的每个会话的程序全面区的内存。如果要比较两个大表的内容，可能会耗用非常大的内存（不包括系统全局区已经占用的内存）。
　　
　　再次考虑嵌套表的运用
　　作为对象-关系模型的一部分，Oracle最早是在Oracle8i中引入了嵌套表和变长数组。那时，我已经非常习惯使用索引表了，感到没有必要改用这些新的类型。我几乎也没有发现哪里需要通过把关系表中的列定义为嵌套表或变长数组来对数据进行不规则处理。此外，在添加行之前也不需要显示初始化索引表或EXTEND。
　　
　　然而，Oracle数据库10g的令人着迷的集合特性出现之后，我需要重新考虑我的偏好了。通过数字索引，你可以像使用关联数组（Oracle9i版本以后，索引表的新名称）那样使用嵌套表。从构造器开始并逐步扩展应用，同时再稍加努力，就可跟上面向对象技术领域中公认标准的发展。
　　
　　这种全自动的集合级比较是使用嵌套表的一个极有说服力的理由。根据应用程序的不同要求，集合运算符可以让你少写好多行代码（少进行很多小时的测试）。
　　
　　

原文转自：http://www.ltesting.net