返回首页
当前位置: 主页 > 数据库 > Oracle教程 >

oracle集合类型当参数

时间:2015-07-14 23:14来源:电脑教程学习网 www.etwiki.cn 编辑:admin

存储过程因其执行效率高、与事务处理的结合、运行更安全等优点,在数据库应用程序中被广泛采用。PL/SQL是用于从各种环境中访问Oracle数据库的一种编程语言,它与数据库服务器集成在一起,PL/SQL编写的存储过程编译效率高,网络系统开销小,同时PL/SQL直观性好,是大多数人的选择。
以Number、Varchar等基本标量类型为输出参数的PL/SQL存 储过程,每个输出参数只能返回一个对应值。而在实际数据库应用程序中,尤其是在进行系统综合查询统计时,往往需要返回二维数组或结果集,这时应考虑在存储 过程中使用集合这种数据结构。对于集合,我们可以一次把许多元素作为一个整体进行操作,也可以对集合中的单个元素进行操作,使用方便、灵活。

2 PL/SQL存储过程
2.1 索引表作为参数


索引表是无约束的,其大小的唯一限制(除可用内存外)就是它的关键字BINARY_INTEGER类型所能表示数值的约束(-2147483647...+2147483647),其元素不需要按任何特定顺序排列。在声明时,我们不需要指定其大小,而且对索引表的元素可以直接赋值,不用初始化,可见使用索引表极其方便。
我们可以在PL/SQL语句块中定义索引表,但作为输出参数的索引表,必须要在包(package)里定义,方法如下:
create or replace package out_param is
---- 定义了元素是varchar2类型的一个索引表类型
type out_index_table_typ is table of varchar2(50) index by binary_integer;
end out_param;
接下来就可以在pl/sql存储过程里引用在包里定义的索引表类型:
create or replace procedure testPro1(in_param in varchar2,o_table out out_param. out_index_table_typ ) is
begin
-------这里略去程序体
end testPro1;
其中,返回的索引表类型前必须用包名加上句点来引用out_param. out_index_table_typ
索引表与数据库表很形似,有key和value两列,但它不是真正的数据库表,不可以存储到数据库中。因此索引表不能使用SQL进行操作,这样它的内容不能通过一个标准的SELECT语句返回游标得到。这一点与嵌套表有很大不同。


2.2 可变数组作为参数


可变数组和另外两种集合类型不同,其元素在内存中是连续存储的,且在大小方面有一个固定的上界。声明时需要指定该数组中元素的最大数目(可变数组的大小可以用EXTEND方法来增加,但不能被扩展超过所声明的极限大小)。

可变数组的元素被赋值之前,必须使用构造器进行初始化。元素插入数组时应从索引1开始,连续插入。


可变数组的定义方法如下:
create or replace type testArray is varray(5) of number(3)
PL/SQL存储过程里调用可变数组作为输出参数:
create or replace function getTestArray return testArray
as
o_data testArray:= testArray ();
begin
for v_count in 1..5 loop
o_data.extend;
o_data(v_count):= v_count;
end loop;
return o_data;
end;

2.3 嵌套表作为输出参数


存储过程中使用嵌套表,并不是直接将嵌套表作为输出参数,而是对嵌套表"造型"后以游标形式输出。嵌套表的基本功能与索引表相同,但嵌套表可以使用SQL进 行操作,其内容可通过SELECT 语句查询并"造型"后以游标形式返回。在大多数的查询统计中,常常需要返回结果集,这时使用嵌套表就尤其方便。笔者在开发过程中深刻体会到使用对象嵌套表 可以解决绝大多数的查询统计问题。下面着重介绍如何在存储过程里利用对象类型的嵌套表。
对于任意的统计分析表格,我们可以将其简化成下面的输出形式:

统计项目1
统计项目2
统计项目3
统计项目4

名称1





名称2





这样我们把每一行看作是一个对象实例,该行的每一列则可以看作是该对象的一个属性,下面通过构造对象,然后对包含对象的嵌套表进行造型,返回游标得到结果集。

首先构造统计对象如下:
create or replace type TestObj as object
(
vname varchar2(20), --名称
item1 number, --统计项目1
item2 number, --统计项目2
item3 number, --统计项目3
item4 number --统计项目4
);
构造包含对象类型的嵌套表:
create or replace type TestNestTable as table of TestObj;
定义对索引表"造型"后的输出的游标类型:
create or replace package out_param is
type out_cur is ref cursor;
下面是嵌套表作为输出参数的存储过程:
create or replace procedure testPro2(o_cur out out_param.out_cur ) is
---- 包含对象的嵌套表变量的声明
v_objTable TestNestTable:= TestNestTable ();
begin
--嵌套表变量的使用
v_objTabl.extend;
v_objTable(1):= TestObj(‘张三',12,123,123,34);
v_objTabl.extend;
v_objTable(2):= TestObj(‘李四,22,223,223,234);
--对嵌套表进行"造型"返回游标
open o_cur for select * from Table(cast (v_objTable as TestNestTable) );
end testPro2;


3 结束语


使用索引表和可变数组,可将返回的集合映射成Java数组。由于索引表会自动分配空间,在声明时不需要指定其大小,而且不需要初始化,使用起来比较方便。但是索引表作为输出参数只能使用oci驱动(返回游标时,可以用瘦客户驱动也可以用oci驱动),所需要的动态连接库文件(ocijdbc9.dll)要在环境变量里进行设置(例如:path=D:\oracle\ora90\BIN),在不同的环境下OCI驱动还可能出现类装载异常,所以返回索引表尽管方便,但偶尔会出现意想不到的错误。可变数组映射成Java数组简单,对于返回小数据量的结果,也是不错的选择,但使用可变数组作为输出参数,声明时必须限定该数组的大小上限,并且需使用构造器初始化。
使用嵌套表,可以对嵌套表进行SQL操作,其内容能通过对标准的SELECT 语句造型后可转化为游标输出。而且嵌套表的内容相当于session变量,当断开连接后即释放内存,但同样存在需要初始化和扩展的问题。
综上所述,究竟采用索引表、嵌套表和可变数组中哪一种作为存储过程的输出要看具体的要求和开发环境。有一点我们需要注意,如果返回的数据量较大,以数组形式 返回,则需一次性取回所有结果,在PL/SQL里为所有结果分配空间并复制,然后将这些数据通过网络发送到客户端,客户端也同样需要分配空间接受这些数 据;而采用游标形式,只要返回一个指针,然后分批返回结果(可自定义每次返回记录的条数),而不是一次性返回所有结果,因此在客户端不需分配大块的空间存 放所有结果。可见,对于大数据量的应用程序,返回游标程序运行效率会更高。
 

------分隔线----------------------------
标签(Tag):Oracle 数据库 oracle数据库
------分隔线----------------------------
推荐内容
猜你感兴趣