oracle package 函数,Oracle 函数function返回table及package内定义table注意事项

1 Table类型说明

1.1 Table基本定义

Table是定义记录表(或索引表)数据类型，它与记录类型相似，但它是对记录类型的扩展。它可以处理多行记录，类似于C语言中的二维数组，使得可以在PL/sql中模仿数据库中的表。

定义记录表Table类型的语法如下：

TYPE TABLE NAME IS TABLE OF ELEMENT_TYPE [NOT NULL]

INDEX BY [BINARY_INTEGER|PLS_INTEGER|VARRAY2];

关键字INDEX BY表示创建一个主键索引，以便引用记录表变量中的特定行。

BINARY_INTEGER的说明：如语句：TYPENUMBERS IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;其作用是,加了”INDEX BYBINARY_INTEGER ”后，NUMBERS类型的下标就是自增长，NUMBERS类型在插入元素时，不需要初始化，不需要每次EXTEND增加一个空间。而如果没有这句话“INDEXBY BINARY_INTEGER”，那就得要显示对初始化，且每插入一个元素到NUMBERS类型的TABLE中时，都需要先EXTEND。

1.2 记录表Table与管道化函数Pipelined Table

在PL/sql中，如果要返回数据的多个行，必须通过返回一个REF CURSOR的游标，或者一个数据集合(如临时表或物理表)来完成，而REF CURSOR的局限于可以从查询中选择的数据，而数据集合的局限性在于必须先CREATETABLE(无论是创建临时表还是物理表)来进行具体化，具体化后，会因为频繁删除表导致大量的碎片。

Oracle 9i开始，引入了管道化表函数，解决了这个问题。

管道化表函数是返回整个行的集合的函数，可以直接在sql中进行查询，他就好像是真正的数据库表一样。他存在于内存中，比物理表速度要快几十倍。管道化表函数必须返回一个集合，在函数中PIPE ROW 语句被用来返回该集合的单个元素，该函数必须以一个空的RETURN语句结束，以表明他已经完成。一旦创建了该函数，就可以使用TABLE()操作符从SQL查询中来调用它。

2 数据准备

2.1 创建表结构

create table tuser(

id int,name varchar2(50),sname varchar2(50),primary key(id));

2.2 插入数据

insert into tuser(id,name,sname) values(1,'张三','zhangs');

insert into tuser(id,sname) values(2,'王雪','wangx');

insert into tuser(id,sname) values(3,'钱江','qianj');

insert into tuser(id,sname) values(4,'徐小艺','xuxy');

insert into tuser(id,sname) values(5,'张三丰','zhangsf');

insert into tuser(id,sname) values(6,'王晓',sname) values(7,'陈张晨','chenzc');

insert into tuser(id,sname) values(8,'章子怡','zhangzy');

insert into tuser(id,sname) values(9,'肖子笑','xiaozx');

insert into tuser(id,sname) values(10,'胡飞','huf');

3 function返回Table

3.1 定义type行类型

create or replace type type_user as object

(

id int,sname varchar2(50)

);

3.2 定义table类型

create or replace type table_user as table of type_user;

3.3 function返回记录表table

create or replace function sf_table_user(l_id number)

return table_user is

v_user_table table_user:=table_user();

begin

select type_user(id,sname) BULK COLLECT INTO v_user_table from (select id,sname from tuser where id=l_id);

return v_user_table;

end;

3.4 查询Table结果集

select * from table(sf_table_user(5))

3.5 function 返回Pipelined Table

create or replace function sf_table_user(v_name varchar2)

return table_user pipelined is

v_type type_user;

begin

for trow in (select id,sname from tuser where name like '%'||v_name||'%')

loop

v_type:=type_user(trow.id,trow.name,trow.sname);

pipe row(v_type);

end loop;

return ;

end;

3.6 查询PipelinedTable结果集

select * from table(sf_table_user('张'))

4 Package内定义Table类型(非管道方式)

4.1 创建包和包体

利用游标存储结果集，然后将结果集一次性插入table中。

create or replace package spkg_table_user

is

--定义记录行和记录表

type user_record is record(

id tuser.id%type,name tuser.name%type,sname tuser.sname%type

);

type p_user_table is table of user_record;

--定义函数

function pkg_sf_getuserinfo(v_name tuser.name%type)

return p_user_table;

end ;

create or replace package body spkg_table_user

is

function pkg_sf_getuserinfo(v_name tuser.name%type)

return p_user_table is

v_table p_user_table:=p_user_table();

cursor cur_table is select id,sname from tuser where name like '%'||v_name||'%';

begin

open cur_table;

fetch cur_table BULK COLLECT INTO v_table;

return v_table;

end pkg_sf_getuserinfo;

end;

4.2 查询Table结果集

select * from table(spkg_table_user.pkg_sf_getuserinfo('张'))

报错：ORA-00902 无效数据类型

为什么呢？Oracle中，普通的记录表类型必须先CREATETABLE(无论是创建临时表还是物理表)来进行具体化，具体化后，才能使用。所以在包体内定义的记录表，没有具体化，是无法直接查询使用的

。

4.3 将Table具体化，在package中调用(这里直接使用上面已经具体化的table类型)

create or replace package spkg_table_user

is

function pkg_sf_getuserinfo(v_name tuser.name%type)

return table_user;

end ;

create or replace package body spkg_table_user

is

function pkg_sf_getuserinfo(v_name tuser.name%type)

return table_user is

v_table table_user:=table_user();

begin

select type_user(id,sname) BULK COLLECT INTO v_table from (select id,sname from tuser where name like '%'||v_name||'%');

return v_table;

end pkg_sf_getuserinfo;

end;

4.4 查询package中具体化后的Table结果集

select * from table(spkg_table_user.pkg_sf_getuserinfo('张'))

5 Package内定义Table类型(管道方式)

5.1 创建包和包体

create or replace package spkg_table_user

is

--定义记录行和记录表

type user_record is record(

id tuser.id%type,sname tuser.sname%type

);

type p_user_table is table of user_record;

--定义函数

function pkg_sf_getuserinfo(v_name tuser.name%type)

return p_user_table pipelined;

end ;

create or replace package body spkg_table_user

is

function pkg_sf_getuserinfo(v_name tuser.name%type)

return p_user_table pipelined is

begin

for r in (select id,sname from tuser where name like '%'||v_name||'%')

loop

pipe row(r);

end loop;

end pkg_sf_getuserinfo;

end;

5.2 查询Package中管道化表函数的结果集

select * from table(spkg_table_user.pkg_sf_getuserinfo('张'))

总结

1、记录表Table使用时，无论pacakge中还是package外，必须先CREATE TABLE，让其具体化后才能直接在sql中查询。

2、管道化函数Pipelined Table,无论package中还是package外，一旦创建了该函数，就可以使用TABLE()操作符从SQL查询中来调用它。

总结

如果觉得编程之家网站内容还不错，欢迎将编程之家网站推荐给程序员好友。

本图文内容来源于网友网络收集整理提供，作为学习参考使用，版权属于原作者。