PLSQL——10、函数实现返回表

时间：2021-09-19 00:56:22

如果要通过oracle函数实现返回表，应该如何实现呢？

PIPELINED管道函数

从oracle 9i 开始，提供了一个叫做“管道化表函数”的概念，来解决这个问题。

这种类型的函数，必须返回一个集合类型，且标明 pipelined；

这个oracle函数不能返回具体变量，必须以一个空 return 返回；

这个oracle函数中，通过 pipe row () 语句来送出要返回的表中的每一行；

调用这个oracle函数的时候，通过 table() 关键字把管道流仿真为一个数据集。

以下是一个十分简单的实例：

----建表并插入数据
create table tb1(k number, v varchar2(10));
insert into tb1(k, v) values(100,‘aaa‘);
insert into tb1(k, v) values(200,‘bbb‘);
insert into tb1(k, v) values(200,‘ccc‘);

----创建type
create type row_type1 as object(k number, v varchar2(10));
create type table_type1 as table of row_type1;

--创建管道函数
create or replace function fun1 
return table_type1 pipelined
as
   v row_type1;
begin
    for myrow in (select k, v from tb1) loop
      v := row_type1(myrow.k, myrow.v);
      pipe row (v);
    end loop;
   return;
end;

--调用管道函数
select * from table(fun1);

如果oracle函数带参数，可以写法如下：

create or replace function fun1(i_v Int) 
return table_type1 pipelined
as
  v1 row_type1;
begin
  for myrow in (select k, v from tb1 Where k = i_v) loop
  v1 := row_type1(myrow.k, myrow.v);
  pipe row (v1);
  end loop;
  return;
end;

select * from table(fun1(100));

PL/SQL表 table()用法

利用table()函数，我们可以将PL/SQL返回的结果集代替table。

演示如下：

----创建type
create or replace type t_test as object(
id integer,
rq date,
mc varchar2(60)
);

create or replace type t_test_table as table of t_test;

--创建函数
create or replace function f_test_array(n in number default null) return t_test_table
as 
v_test t_test_table := t_test_table();
begin
        for i in 1 .. nvl(n,100) loop
                v_test.extend();
                v_test(v_test.count) := t_test(i,sysdate,‘mc‘||i);
        end loop;
        return v_test;
end f_test_array;
/

select * from table(f_test_array(10));

字符串切分为多行

使用管道函数实现

1.创建type对象

create or replace type type_split as table of varchar2(4000);

2.创建split函数

create or replace function test_split(p_list VARCHAR2, p_sep VARCHAR2) 
return type_split PIPELINED 
IS  
  l_idx  PLS_INTEGER;  
  v_list VARCHAR2(32767) := p_list;  
begin  
  LOOP  
    l_idx := instr(v_list, p_sep);  
    IF l_idx > 0 THEN  
      PIPE ROW(substr(v_list, 1, l_idx - 1));  
      v_list := substr(v_list, l_idx   length(p_sep));  
    ELSE  
      PIPE ROW(v_list);  
      EXIT;  
    END IF;  
  END LOOP;  
  RETURN;  
end test_split;

3.执行该函数(table涵数用法)

SELECT * FROM table(test_split(‘aaa,bbb,ccc‘,‘,‘));

4.输出结果

    COLUMN_VALUE  
---------------------------
1        aaa  
2        bbb  
3        ccc

5.总结

此函数可以在存储过程(procedure)中使用,当遇到需要用逗号进行分隔的字符串,可以用这种方法查询,之后用游标(cursor)来分别提取每个字符串。

相关文章