【Oracle】 管道函数pipelined function简单的使用

Oracle 管道函数pipelined function简单的使用

如果在函数(function)中加关键字 pipelined，就表明这是一个oracle管道函数，其返回值类型必为 集合，体现出来的数据结构类似于表，即可以理解成，使用管道函数可以返回一张查询表，可以是单行数据也可以是多行数据，而不是平常函数返回的单行数据

这种返回多行数据在需要大数据量处理的时候很有用，比如这一段SQL可能全部查询出来要一分钟，但是使用这个方法可能很快就有数据，虽然不是全部，但是有的时候不需要一次性获取全部数据的时候是一种好方法

且在复杂的方法下更好地去处理一些复杂的逻辑查询

要使用管道函数的话需要先进行一下类型的类型的创建

首先我们创建一个类型对象

CREATE OR REPLACE Type test_Row_Type As Object
(
  test1 Varchar2(512),
  test2 Varchar2(3000),
  test3 Varchar2(3000),
  test4 Varchar2(3000),
  test5 Varchar2(3000),
  test6 Number,
  test7 Number
)

然后我们创建嵌套的表

CREATE OR REPLACE TYPE test_Row_Type_TABLE AS TABLE OF test_Row_Type;

接下来创建一个临时表，用来测试数据

create table TTTtable
(
  aaa VARCHAR2(50),
  bbb VARCHAR2(50),
  ccc VARCHAR2(50)
)

其中数据如下

然后我们开始写管道函数

CREATE OR REPLACE FUNCTION test_Row_pipelined(p_varchar in varchar2)
  return test_Row_Type_TABLE
  --关键字加在这里
  pipelined as
  p_num integer := 0;
  ret   test_Row_Type;
begin
  --查询临时表数据
  for sub_data in (select t.bbb, t.ccc
                     from TTTtable t
                    where t.aaa = p_varchar) loop
  
    p_num := p_num + 1;
    
    ret := test_Row_Type(p_num,
                             sub_data.bbb,
                             sub_data.ccc,
                             '',
                             '',
                             0,
                             0);
    --将数据放入管道，pipe row()语法被用来返回该集合的单个元素
    pipe row(ret);
  
  end loop;
  --函数使用一个空的return结束
  return;
end;

如果是使用

select test_Row_pipelined('a') from dual

则返回的是<Collection>数据，此时在PL/SQL中打开就是这样的

同时，我们还可以使用这种语法

select * from table(test_Row_pipelined('a')) t

结果也是这样的，不过推荐使用这种方法，因为这种方式是可以使用where条件去进行过滤的

比如我们加上where

select t.* from table(test_Row_pipelined('a')) t where t.test1 = 1

则得到的结果就是这样的

这就是管道函数的最基本的用法

PS:在管道函数中可以进行DML操作，一般要加自治事务处理，但是不能直接对管道函数结果集进行DML操作，如果要对管道函数进行DML操作，建议做成视图再加触发器

使用管道函数制作分割函数的部分放在了这里

具体实现逻辑：

CREATE OR REPLACE FUNCTION test_Row_pipelined(p_insvar    in varchar2,
                                                  p_delimiter in varchar2)
  return test_Row_Type_TABLE
  pipelined as
  p_num     integer := 1;
  p_num1    integer;
  p_length  integer;
  p_start   integer := 1;
  p_varchar varchar2(200);
  ret       test_Row_Type;
begin
  --如果不是以分隔符结尾的，就拼接上去
  select case
           when (select count(1)
                   from dual
                  where p_insvar like '%' || p_delimiter) > 0 then
            p_insvar
           else
            p_insvar || p_delimiter
         end
    into p_varchar
    from dual;
  --整个字符串的长度
  select length(p_varchar) into p_length from dual;
  --当起始长度大于整体长度的时候
  while (p_start <= p_length) loop
    --找到字符串与分隔符的分割后的下标
    select instr(p_varchar, p_delimiter, p_start) into p_num1 from dual;
    --如果没有，则返回全部字符串，说明到了结尾了
    if p_num1 = 0 then
      ret := test_Row_Type(p_num,
                               substr(p_varchar, p_start),
                               p_num1,
                               p_start,
                               p_length,
                               0,
                               0);
    
      pipe row(ret);
    else
      --否则，截取字符传中，从p_start开始找到下标减去p_start的部分
      ret := test_Row_Type(p_num,
                               substr(p_varchar, p_start, p_num1 - p_start),
                               p_num1,
                               p_start,
                               p_length,
                               0,
                               0);
    
      pipe row(ret);
     --新的起始点为下标加1
      p_start := p_num1 + 1;
    
    end if;
    --序号加1
    p_num := p_num + 1;
  end loop;
  return;
end;

20230908更新
管道函数在存储过程中作为循环的数据集使用的时候，debug时可能会在pipe row的时候出现奇怪的报错，比如出现01422（一行返回多行或为空）这种根本没关系的报错

这种情况下，需要查看存储过程中是不是存在这种bug，通常情况下都能在后面语句中找到，不知道为什么会导致报错提前，挺奇怪的