oracle 函数定义表类型,Oracle函数回到表类型

定义对象类型：

create or replace type type_row as object

(

id int,

name varchar2(50)

)

修改表对象类型的定义语句如下：

create or replace type type_table is table of type_row;

1)、管道化表函数方式：

create or replace function f_pipe(s number)

return type_table pipelined

as

v_type_row type_row;

begin

for i in 1..s loop

v_type_row := type_row(i,to_char(i*i));

pipe row(v_type_row);

end loop;

return;

end f_pipe;

测试：select * from table(f_pipe(5));

2)、 普通的方式:

create or replace function f_normal(s number)

return type_table

as

rs type_table:= type_table();

begin

for i in 1..s loop

rs.extend;

rs(rs.count) := type_row(rs.count,'name'||to_char(rs.count));

--Result(Result.count) := type_row(NULL,NULL);

--rs(rs.count).name := rs(rs.count).name || 'xxxx';

end loop;

return rs;

end f_normal;

测试：select * from table(f_normal(5));

其他代码段

把权限(和)值拆分成多条记录

create or replace type type_table_number is table of number;

create or replace function f_right_table(

rights number

)

--自定义table类型 --pipelined 管道关键字

return type_table_number pipelined

as

begin

--调用方法：select column_value as right from table(f_right_table(power(2,15)*2-2));

for i in 1..15 loop

IF bitand(rights,power(2,i))=power(2,i) THEN

pipe row(power(2,i)); --pipe row 特定写法，输出记录

END IF;

end loop;

return;

end f_right_table;

一种遍历数据的方法，

只是演示myrow，myrow 就相当于一个临时变量

for myrow in (

select 2 as right from dual where bitand(rights,2)=2

union

select 4 as right from dual where bitand(rights,4)=4

) loop

rs.extend;

rs(rs.count) := myrow.right;

end loop;

二.其他实现