Oracle 函数返回游标的方法及应用

Oracle函数返回游标的方法及应用简析

王凤利康俊霞

河北北方学院张家口职业技术学院

【摘要】Oracle函数除了可以返回数值类型和字符类型等常用数据类型的数

据以外，还可以返回游标类型的数据，在某些情况下，返回游标类型的函数为我们

解决一些应用中的难题。本文就是通过一个实例来介绍返回游标函数的创建过程。

【关键字】Oracle，函数，包，游标。

1 问题的提出

在油田信息系统建设过程中，遇到了这样一个问题：输油站泵运行数据表(DHC05)的结构为：时间(SJ)、单位名称(DWMC)、泵号(BH)、泵压(BY1)、电压(DY)、电流(DL)、排量(PL)，主键为：时间、单位名称、泵号，每整点对运行泵取一次数据，不存储不运行泵(运行时间为0)的数据。现要输出如下报表：

报表的查询条件为单位名称和日期(yyyymmdd格式字符串)。运行泵号及泵台数根据各单位实际运行情况而定。

2 系统简介

系统采用B/S三层体系结构，数据库采用Oracle9.2版本，WEB服务器采用IIS6.0版本,客户端采用IE6.0及以上版本。

报表采用了统一的制表解释程序进行输出，该解释程序可以从一个单一的Oracle查询语句中提取数据，报表的输出样式为简单的二维表。

3 解决方案

根据目前的系统现状，要想直接利用当前系统输出本报表是不可能的，经过分析认为共有以下几种解决方案：

a)修改数据表结构

将原始数据表的结构进行调整，把时间、单位名称作为主键，将同一时刻的各个泵的数据逐个列出作为数据列，大致结构为：时间、单位名称、1号泵泵压、1号泵电压、1号泵电流、1号泵排量、2号泵泵压、2号泵电压、2号泵电流、2号泵排量、…。

本方案违背了数据库设计的基本原则，因各个单位的泵数不相等，只能按最大泵数进行数据库结构设计，当站库继续扩大、泵数继续增加时需要对数据库结构和所有用到该数据表

的程序及数据录入界面进行调整，所以本方案不可行。

b)建立中间表

原始数据表不变，增加中间表，中间表以时间、单位名称作为主键，将同一时刻的各个泵的数据逐个列出作为数据列，大致结构为：时间、单位名称、1号泵泵压、1号泵电压、1号泵电流、1号泵排量、2号泵泵压、2号泵电压、2号泵电流、2号泵排量、…。通过存储过程将原始数据导入到中间表中，报表解释程序直接从中间表提取数据。

有一些人采用了本方案，通过建立中间表，制作了一定数量的报表，解决了一些实际问题，但对于本文中提到的问题，本方案有一定的缺陷：随着站库的扩大和泵台数的增加，需要不断调整中间表的结构；当本类需求比较多时，每个报表都要增加一个中间表，没有一个成批处理的解决办法，当这类需求比较少时可以采用本方案来快速完成任务，但当本类需求比较多时则不是一个好的解决办法。

c)单独编写报表输出程序

通过编写一个单独的WEB页面来输出本表。

本方案虽然可以较好地完成本表的输出任务，但不能用本方法来处理大量的同类需求，并且对报表的修改和调试都比较困难，报表制作完成以后不能形成可重复使用的成果，所以本方案也不是一个理想的处理方案。

d)重新编写报表解释程序

重新编写报表解释程序，用以实现多条查询语句结果数据的提取及嵌套报表的输出。

本方案是一个比较彻底地解决报表输出问题的方法，当报表解释程序可以实现多条查询语句结果数据的提取及嵌套报表的输出时，所有的报表制作问题都可以使用该解释程序来处理。但该程序的开发周期长，而且嵌套报表的实现难度大，在经费不增加的前题下不能采用本方案。

e)编写函数并修改报表解释程序

编写一个Oracle函数，将要展示的所有查询结果通过游标返回，将原来在报表解释程序中的查询语句用函数调用来替换，修改报表解释程序中表头处理代码，实现本类报表表头的展示功能，结合返回的结果集，完成报表输出功能。

本方案通过编写一个返回游标类型数据的函数并对报表解释程序进行简单的修改，能够快速圆满地完成本类报表的输出功能，在目前系统环境下算是一个比较好的解决方案。

4 具体实现

根据查询数据的特点，数据查询语句可以使用Left Join语句来实现，查询语名的写法大致为：

SELECT to_char(z.sj, ‘hh:mi’)

a.bh, a.by1, a.dy, a.dl, a.pl,

b.bh, b.by1, b.dy, b.dl, b.pl,

c.bh, c.by1, c.dy, c.dl, c.pl,

…

FROM (SELECT DISTINCT sj, dwdm

FROM dhc05

WHERE dwdm = 数据查询单位代码 and

to_char(sj, ‘yyyymmdd’) = 数据查询日期) z

LEFT JOIN dhc05 a ON a.sj = z.sj and a.dwdm = z.dwdm and a.bh = ‘1号泵’

LEFT JOIN dhc05 a ON b.sj = z.sj and b.dwdm = z.dwdm and b.bh = ‘2

号泵’

LEFT JOIN dhc05 a ON c.sj = z.sj and c.dwdm = z.dwdm and c.bh = ‘3号泵’

…

ORDER BY z.sj;

所以在函数中应首先查询可能的泵号数据，再根据泵号数据，及数据查询条件，拼写出整个查询语句。为了能够实现各种同类应用的需求，将查询语句中的所有可变部份全部以参数的形式进行传递。为了能够实现返回游标的函数，定义了一个包，将函数定义在包中。

5 结论

Oracle游标类型是一个比较特殊的数据类型，该数据类型不仅可以在程序中使用，同其它数据类型一样，游标也可以做为函数返回值的数据类型，通过对游标类型返回值的处理可以为我们解决一些复杂的问题。本文通过一个实际问题的解决过程，介绍了Oracle函数返回游标类型数据的实现及应用方法，为同类问题的解决提供了一种比较有效的解决方法。参考文献

[1] 《Oracle8 PL/SQL 程序计设》机械工业出版社Scott Urman著译友翻译组译

[2] 《PL/SQL User's Guide and Reference 10g Release 1》Oracle Database Documentation 10g

Release 1

Kang Junxia

Department of Computer,Zhangjiakou Technical College

【Abstract】 Oracle function may not only numerical value type and character type ,but also may return to the date of cursor type. In some

case ,the function of returning to cursor type may settle some

difficult subject .This text introduce the course.

【Key word】 Oracle, function, bundle, cursor