用Oracle用户自定义合计函数来提高代码重用性

　　合计函数（aggregate functions）就是对一组数据行进行处理的函数，如max和min（对一组数据取最大值和最小值）函数，合计函数用来简化开发者执行某个任务的逻辑复杂度。从Oracle 9i开始，开发者可以创建用户自定义的合计函数，它们可以重复使用而无需做任何修改。通过强有力的编程构造，合计函数实际上很容易编写。现在让我们看看它是如何工作的。
　　
　　创建toList
　　
　　
　　为了看看构造合计函数是多么的简单，我举了一个合计函数的例子，即toList函数。该函数的参数为一组记录，它的功能是把保存在这些记录的特定字段的数据组合到一个用逗号做分隔符的清单中（成组）。下面的查询是一个例子供你参考：
　　
　　select author, toList(title) titles
　　from books
　　group by author;
　　
　　
　　上面代码的运行结果如图A所示。
　　
　　图A
　　
　　ToList函数创建了一个书目清单
　　
　　尽管我是使用Oracle的PL/SQL语言来创建我的toList函数的，你可以使用任何Oralce所支持的语言（如C＋＋或者Java）来创建合计函数。所有的代码都可以在SQL*Plus、SQL*Spreadsheet以及其它Oracle SQL应用程序中执行。
　　
　　创建一个合计函数分为五个步骤：规划函数的逻辑、创建函数的对象类型的对象头（object header）、编写对象类型的代码、例化对象类型、最后测试该函数。我将按顺序向你介绍这些步骤。
　　
　　规划逻辑
　　
　　
　　我喜欢正式规划我所写的程序，所以我为toList函数编写了伪代码，如清单A 所示，这些伪代码就是我的逻辑规划。Oracle实际上为我处理了该过程的相当多的部分，也就是说我实际上需要写的代码只包括下面三个部分：宣布list变量、添加逗号的if语句以及组成返回值的字段名。
　　
　　创建对象头（object header）
　　对象头定义为持久变量以及对该对象成员函数的说明。所有的对象必须包含ODCIAggregateInitialize、ODCIAggregateIterate、ODCIAggregateMerge和ODCIAggregateTerminate这四个函数，称之为强制函数（mandatory function），为了易读起见，我在后面将这四个函数分别简称为Initialize,、Iterate、Terminate和Merge函数。在后面介绍这四个函数各自的代码时，我再解释它们的功能。 代码清单B 为toList的对象头。
　　
　　在清单B中，toListImpI是对象类型的名字，而list是我所定义的唯一的持久变量。
　　
　　编写成员函数的代码
　　一旦创建好对象头，你就应该编写函数的代码。 代码清单C 包括了toList成员函数的函数体代码。尽管这个代码清单有些长，但是大多数都是从一个样板出来的，函数之间的差别不大。
　　
　　每个函数都返回一个数来表示运行结果是成功与否。ODCIConst.success表示运行成功。现在你已经看到了这些函数的代码，那么四个强制函数中的三个的功能应该很容易理解了：
　　
　　Initialize函数对数据组各个需要处理的字段各运行一次。自然的，我需要为每一个值准备一个新的清单，所以需要初始化持久变量list，这里初始化值为null。
　　Iterate函数处理返回的行，所以实际上是由它来创建返回的值的清单。先测试list是否为空，如果为空，就把list直接设置为所引入的value值；如果list变量非空，则给list添加一个逗号后再插入value值。
　　Terminate函数在数据组的每个行的感兴趣字段数据被处理后执行。在这个函数中我只需简单的返回清单变量即可。
　　
　　第四个函数，即Merge函数，仅仅是用来返回成功标记的。
　　
　　创建该合计函数
　　一旦你写完所有的这些代码，剩下的唯一事情就是通过运行下面的指令来创建该合计函数（这用到你刚刚创建的toListImp1类型）：
　　
　　create or replace function toList (input STRING) return STRING
　　aggregate USING toListImp1;
　　
　　
　　现在开始进入最有趣的工作环节：测试。你可以用 清单D 中的代码创建一个简单的书目表并载入一些数据进行测试。
　　
　　使函数具有并行处理能力
　　
　　
　　合计函数的另一个优点就是你可以让它们具有Oracle的并行处理特性。为了让你的合计函数具有并行处理能力，你需要编写Merge函数的代码并需要对合计函数的脚本略做修改。注意我已经给你的代码是正确完整的、并带有并行处理选项。
　　
　　当一个合计函数在多个处理器上运行时，Oracle 9i服务器可以在两个或者多个处理器上运行Iterate函数。每个处理器返回一部分结果，Merge函数把这些离散的结果合并到一个变量。Oracle 9i服务器将反复调用Merge函数直到所有的部分结果合并完毕为止。
　　
　　为了让toList函数可以并行处理，你首先需要Merge函数的代码，你可以在清单E中找到它的代码。
　　
　　我还需要在创建该合计函数时宣布它具有并行处理的能力：
　　
　　create or replace function toList (input STRING) return STRING
　　parallel_enable　aggregate USING toListImp1;
　　
　　现在，toList函数已经充分利用了多处理器的优点了。
　　
　　使用技巧
　　如果你不想按字段成组，那么可以按常量进行，例如：
　　
　　Select toList(distinct Author) authors From books Group by ‘1’
　　
　　你也许希望增强你的合计函数的健壮性，就像在其它程序中所作的那样。例如，在我的Merge函数中，也许应该检测合并的两个内容是否超过了list的最大允许字符数（32767）。
　　
　　如果你的Oracle服务器没有配置成支持并行处理的方式，我建议你仍在你的合计函数中考虑添加并行支持功能。因为这只需花费一点点附加时间，但是这就增加了你的函数在其它处理环境中的可移植性和可重用性。
　　
　　最后你还需要注意，Oracle 9i将会允许你创建一些根本就不能运行的合计函数。例如，尽管Oracle允许你创建参数超过一个的合计函数（也就是说，该合计函数将使用多个函数），但是使用该合计函数将会发生错误。同样的，尽管你可以重载合计函数，但是当你尝试使用它们的时候却会导致失败。
　　
　　创建自己的合计函数是扩展Oracle统计和文本处理例程（Oracle本身并没有内建这个功能）的重要途径。使用这个技术的地方几乎数不胜数，所以你需要掌握这个技术。
　　

查看本文来源