Oracle函数返回表类型
一.用自定义类型实现
1、创建表对象类型。
?? 在Oracle中想要返回表对象，必须自定义一个表类型，如下所示：
create or replace type type_table is table of number;?
上面的类型定义好后，在function使用可用返回一列的表，稍后介绍返回多列的
?
2、 创建函数
在函数的定义中,可以使用管道化表函数和普通的方式,下面提供两种使用方式的代码:
1)、管道化表函数方式：
create or replace function f_pipe(s number)return type_table pipelinedas begin for i in 1..s loop pipe row(i); end loop;return;end f_pipe;
??
注意:管道的方式必须使用空的return表示结束.
调用函数的方式如下：
?
2)、 普通的方式:
create or replace function f_normal(s number)return type_tableas rs type_table:= type_table();begin for i in 1..s loop rs.extend; rs(rs.count) := i; end loop;return rs;end f_normal;
?
调用方式如下:
select * from table(f_normal(5));
定义对象类型：
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 pipelinedas 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_tableas 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;
包里面用一个存储过程,返回游标,就可以了
>包的定义
1) 包头
create or replace package mypk as type t_cursor is ref cursor; procedure proc(name varchar2,c out t_cursor,a number); end;
?
2) 包体
create or replace package body mypk as procedure proc(name varchar2,c out t_cursor,a number) as begin open c for select * from test where id=a and name=name; end proc; end;
?
这个方案的局限性太大，无法实现select * from function()的要求
?
调用：
declare cur_out_arg mypk.t_cursor; rec_arg test%rowtype;begin mypk.proc('abc',cur_out_arg,3); --open cur_out_arg; loop --提取一行数据到rec_arg fetch cur_out_arg into rec_arg; --判读是否提取到值，没取到值就退出 --取到值cur_out_arg%notfound 是false --取不到值cur_out_arg%notfound 是true exit when cur_out_arg%notfound; dbms_output.put_line(rec_arg.id||'-'||rec_arg.name); end loop; --关闭游标 close cur_out_arg;end;??
?