oracle pipelined 自定义函数 function 返回table格式

oracle自定义函数返回一个表类型

下面主要是一个简单的例子，复制到oracle数据库的相应位置即可使用

1.定义type行类型

create or replace type t_test as object( id integer, create_time date, object_name varchar2(60));

2.定义行类型的table

create or replace type t_test_table as table of t_test;

3.1创建存储过程，使用数组方式

create or replace function f_test(  n in  number default null)return t_test_tableasv_test t_test_table:=t_test_table();begin  for i in 1 .. n loop    v_test.extend();    v_test(v_test.count) :=t_test(i,sysdate,'name'||i);  end loop;  return v_test;end f_test;

3.2创建存储过程，使用管道方式

create or replace function f_test_pipe(n in number default null)return t_test_tablepipelined asv_test t_test_table:=t_test_table();begin for i in 1 .. nvl(n,100) loop  pipe row(t_test(i,sysdate,'name'||i)); end loop; return ; end f_test_pipe;

4.调用函数

select * from table(f_test_pipe(3));

以上参考此处

下面提工作中用到的，也可以直接使用

先创建此结果的类型行

create or replace type Type_Row_Test as object (  typeId varchar2(50),  typePid varchar2(50),  name varchar2(50)  );

再创建table 用以存储结果

create or replace type Type_Table_Test  as table of Type_Row_Test;

创建函数，循环，将结果放入Type_Table_Test中

create or replace FUNCTION F_Test(pIdIn NVARCHAR2)return Type_Table_Test pipelined --管道as  v Type_Row_Test;begin  for thisrow in (    SELECT  typeId,typePid,name FROM tableName    start with typeId=pIdIn connect by typePid=prior typeId)loop    v:=Type_Row_Test(thisrow.typeId,thisrow.typePid,thisrow.name);    pipe row(v);  end loop;  return;end;

调用函数，进行查询

select * from table(F_Test(paras));

上面是使用管道进行查询，也可以使用数组返回Table格式。

下面用另个一个例子，使用别一种形式，进行操作

定义type 类型行数据

create or replace type 行_解析_字串_参数 as object (    输出字字符 varchar(50)  );

使用行数据，定义表

create or replace type 表_解析_字串_参数  as table of  行_解析_字串_参数;

如果只有一个字段应该也可以使用下面这种方法，网上有这样的写法，有时间可以试一试

create or replace type 表_解析_字串_参数  as table of  nvarchar2(50);

接下来，创建函数

<pre name="code" class="sql">create or replace FUNCTION 解析_字串_参数tttt111111(  p参数输入 NVARCHAR2,p分隔符号 nvarchar2)return 表_解析_字串_参数 asv 表_解析_字串_参数:=表_解析_字串_参数();v_list varchar2(100):=p参数输入; p_sep varchar2(1):=p分隔符号; l_idx varchar(100); begin  loop    l_idx:=instr(v_list,p_sep);    if l_idx>0 then     v.extend();    v(v.count) :=行_解析_字串_参数(ltrim(rtrim(substr(v_list,0,l_idx-1))));      v_list:=substr(v_list,l_idx+length(p_sep));    else      v.extend();    v(v.count) :=行_解析_字串_参数(ltrim(rtrim(v_list)));      exit;    end if;  end loop;  return  v; end;

调用函数，测试结果

select  * from table(解析_字串_参数tttt111111('   你好不,我不好,你呢   ',','));

注：解析字符串的函数，使用pipelined可以编译通过，但没有执行成功，报错PLS-00653:在PL/SQL定义域内不允许有聚集/表函数。有知道如何解决的，望告之。谢谢。

参考点击打开链接