1 Table类型说明
1.1 Table基本定义
@H_502_5@
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@H_502_5@
在PL/sql中,如果要返回数据的多个行,必须通过返回一个REF CURSOR的游标,或者一个数据集合(如临时表或物理表)来完成,而REF CURSOR的局限于可以从查询中选择的数据,而数据集合的局限性在于必须先CREATETABLE(无论是创建临时表还是物理表)来进行具体化,具体化后,会因为频繁删除表导致大量的碎片。
Oracle 9i开始,引入了管道化表函数,解决了这个问题。
管道化表函数是返回整个行的集合的函数,可以直接在sql中进行查询,他就好像是真正的数据库表一样。他存在于内存中,比物理表速度要快几十倍。管道化表函数必须返回一个集合,在函数中PIPE ROW 语句被用来返回该集合的单个元素,该函数必须以一个空的RETURN语句结束,以表明他已经完成。一旦创建了该函数,就可以使用TABLE()操作符从SQL查询中来调用它。
@H_502_5@2 数据准备
2.1 创建表结构@H_502_5@
create table tuser(
id int,name varchar2(50),sname varchar2(50),primary key(id));
2.2 插入数据@H_502_5@
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行类型@H_502_5@
create or replace type type_user as object
(
id int,sname varchar2(50)
);
3.2 定义table类型@H_502_5@
create or replace type table_user as table of type_user;
3.3 function返回记录表table@H_502_5@
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结果集@H_502_5@
select * from table(sf_table_user(5))
3.5 function 返回Pipelined Table@H_502_5@
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结果集@H_502_5@
select * from table(sf_table_user('张'))
4 Package内定义Table类型(非管道方式)
4.1 创建包和包体@H_502_5@
利用游标存储结果集,然后将结果集一次性插入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结果集@H_502_5@
select * from table(spkg_table_user.pkg_sf_getuserinfo('张'))
报错:ORA-00902 无效数据类型
为什么呢?Oracle中,普通的记录表类型必须先CREATETABLE(无论是创建临时表还是物理表)来进行具体化,具体化后,才能使用。所以在包体内定义的记录表,没有具体化,是无法直接查询使用的
。
4.3 将Table具体化,在package中调用(这里直接使用上面已经具体化的table类型)@H_502_5@
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结果集@H_502_5@
select * from table(spkg_table_user.pkg_sf_getuserinfo('张'))
5 Package内定义Table类型(管道方式)
5.1 创建包和包体@H_502_5@
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中管道化表函数的结果集@H_502_5@
select * from table(spkg_table_user.pkg_sf_getuserinfo('张'))
总结
create table tuser( id int,name varchar2(50),sname varchar2(50),primary key(id));
2.2 插入数据@H_502_5@
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行类型@H_502_5@
create or replace type type_user as object
(
id int,sname varchar2(50)
);
3.2 定义table类型@H_502_5@
create or replace type table_user as table of type_user;
3.3 function返回记录表table@H_502_5@
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结果集@H_502_5@
select * from table(sf_table_user(5))
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行类型@H_502_5@
create or replace type type_user as object
(
id int,sname varchar2(50)
);
3.2 定义table类型@H_502_5@
create or replace type table_user as table of type_user;
3.3 function返回记录表table@H_502_5@
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结果集@H_502_5@
select * from table(sf_table_user(5))
create or replace type type_user as object ( id int,sname varchar2(50) );
3.2 定义table类型@H_502_5@
create or replace type table_user as table of type_user;
3.3 function返回记录表table@H_502_5@
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结果集@H_502_5@
select * from table(sf_table_user(5))
create or replace type table_user as table of type_user;
3.3 function返回记录表table@H_502_5@
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结果集@H_502_5@
select * from table(sf_table_user(5))
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结果集@H_502_5@
select * from table(sf_table_user('张'))
4 Package内定义Table类型(非管道方式)
4.1 创建包和包体@H_502_5@
利用游标存储结果集,然后将结果集一次性插入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结果集@H_502_5@
select * from table(spkg_table_user.pkg_sf_getuserinfo('张'))
报错:ORA-00902 无效数据类型
为什么呢?Oracle中,普通的记录表类型必须先CREATETABLE(无论是创建临时表还是物理表)来进行具体化,具体化后,才能使用。所以在包体内定义的记录表,没有具体化,是无法直接查询使用的
。
4.3 将Table具体化,在package中调用(这里直接使用上面已经具体化的table类型)@H_502_5@
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结果集@H_502_5@
select * from table(spkg_table_user.pkg_sf_getuserinfo('张'))
5 Package内定义Table类型(管道方式)
5.1 创建包和包体@H_502_5@
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中管道化表函数的结果集@H_502_5@
select * from table(spkg_table_user.pkg_sf_getuserinfo('张'))
总结
select * from table(sf_table_user('张'))
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结果集@H_502_5@
select * from table(spkg_table_user.pkg_sf_getuserinfo('张'))
报错:ORA-00902 无效数据类型
为什么呢?Oracle中,普通的记录表类型必须先CREATETABLE(无论是创建临时表还是物理表)来进行具体化,具体化后,才能使用。所以在包体内定义的记录表,没有具体化,是无法直接查询使用的
。
4.3 将Table具体化,在package中调用(这里直接使用上面已经具体化的table类型)@H_502_5@
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结果集@H_502_5@
select * from table(spkg_table_user.pkg_sf_getuserinfo('张'))
5 Package内定义Table类型(管道方式)
5.1 创建包和包体@H_502_5@
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中管道化表函数的结果集@H_502_5@
select * from table(spkg_table_user.pkg_sf_getuserinfo('张'))
总结
select * from table(spkg_table_user.pkg_sf_getuserinfo('张'))报错:ORA-00902 无效数据类型
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结果集@H_502_5@
select * from table(spkg_table_user.pkg_sf_getuserinfo('张'))
5 Package内定义Table类型(管道方式)
5.1 创建包和包体@H_502_5@
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中管道化表函数的结果集@H_502_5@
select * from table(spkg_table_user.pkg_sf_getuserinfo('张'))
总结
select * from table(spkg_table_user.pkg_sf_getuserinfo('张'))
5 Package内定义Table类型(管道方式)
5.1 创建包和包体@H_502_5@
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中管道化表函数的结果集@H_502_5@
select * from table(spkg_table_user.pkg_sf_getuserinfo('张'))
总结
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中管道化表函数的结果集@H_502_5@
select * from table(spkg_table_user.pkg_sf_getuserinfo('张'))
总结
select * from table(spkg_table_user.pkg_sf_getuserinfo('张'))