KingbaseES 如何实现Oracle pipelined 功能

管道函数即是可以返回行集合（可以使嵌套表nested table 或数组 varray）的函数，我们可以像查询物理表一样查询它或者将其赋值给集合变量。KingbaseES 数据库可以用 setof 实现类似 Oracle 的pipelined 功能，C5版本开始，也支持pipeline。

一、Oracle pipelined 使用例子

create or replace type varchar_type as table of varchar(100);

create or replace function func_pipeline_test return varchar_type
pipelined as
begin
  for i in 1..5 loop
    pipe row('Pipeline '||i||' '||systimestamp);
    dbms_lock.sleep(1);
  end loop;
  return;
end;
/

SQL> select * from table(func_pipeline_test);

COLUMN_VALUE
--------------------------------------------------------------------------------
Pipeline 1 01-JUL-21 07.22.21.630651000 PM +08:00
Pipeline 2 01-JUL-21 07.22.22.630975000 PM +08:00
Pipeline 3 01-JUL-21 07.22.23.631054000 PM +08:00
Pipeline 4 01-JUL-21 07.22.24.631381000 PM +08:00
Pipeline 5 01-JUL-21 07.22.25.631216000 PM +08:00

二、KingbaseES

1、pipelined

从V8R6C5 开始，KingbaseES 支持 pipeline，其使用与 Oracle 完全相同。

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22

create or replace type varchar_type as table of varchar(100);   create or replace function func_pipeline_test return varchar_type pipelined as begin   for i in 1..5 loop     pipe row('Pipeline '||i||' '||systimestamp);     perform pg_sleep(1);   end loop;   return; end; /   test=# select * from table(func_pipeline_test());                column_value               ------------------------------------------  Pipeline 1 2021-12-22 15:26:45.572780+08  Pipeline 2 2021-12-22 15:26:45.572780+08  Pipeline 3 2021-12-22 15:26:45.572780+08  Pipeline 4 2021-12-22 15:26:45.572780+08  Pipeline 5 2021-12-22 15:26:45.572780+08 (5 rows)

 

2、setof 使用例子

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47

create or replace function func_pipeline_test1 returns setof text as declare   v_text text; begin   for i in 1..5 loop     return query select 'Pipeline '||i||' '||clock_timestamp() ;     perform sys_sleep(1);   end loop;   return; end /   test=# select func_pipeline_test1();            func_pipeline_test1            ------------------------------------------ Pipeline 1 2021-09-13 17:36:55.571164+08 Pipeline 2 2021-09-13 17:36:56.572743+08 Pipeline 3 2021-09-13 17:36:57.574097+08 Pipeline 4 2021-09-13 17:36:58.575511+08 Pipeline 5 2021-09-13 17:36:59.577169+08   (5 rows)   create or replace function func_pipeline_test2 returns setof t1 as declare   v_t1 t1%rowtype; begin   for v_t1 in (select * from t1) loop     return next v_t1;   end loop;   return; end /<br> test=# select * from func_pipeline_test2(); id | name ----+------ 1 | abc 2 | abc 3 | abc 4 | abc 5 | abc 6 | abc 7 | abc 8 | abc 9 | abc 10 | abc (10 rows)

3、table function

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20

create or replace type int_set as table of integer;   create or replace function func1() return int_set as   v_temp int_set := int_set(1,2,3); begin   return v_temp; end;   test=# select func1();      func1      ----------------  int_set(1,2,3) (1 row)   test=# select * from table(func1());  column_value --------------             1             2             3