KingbaseES 如何实现Oracle pipelined 功能
2024-08-31 17:02:03
管道函数即是可以返回行集合（可以使嵌套表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 完全相同。
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 使用例子
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
/
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
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
最新文章
- Connect to EC2 if losing Private Key
- SSH整合之spring整合hibernate
- 帮你深入理解OAuth2.0协议
- rm命令
- JavaIO（03）字节流--OutputStream and InputStream
- LeetCode 319
- ScrollView嵌套ViewPager 时候 Pager左右滑动不流畅
- Windows Developer Day - Windows AI Platform
- Lintcode227 Mock Hanoi Tower by Stacks solution 题解
- FFMPEG类库打开流媒体的方法（需要传参数的时候）
- openstack之cinder_backup对接ceph存储
- redis 单节点安装
- MapReduce：Shuffle过程详解
- 耗时任务DefaultEventExecutorGroup 定时任务
- HTML小工具
- lintcode-144-交错正负数
- 【算法导论C++代码】Strassen算法
- MySQL 的中文乱码问题终结
- 使用妹子UI开发的体验分享
- 显示等待 之 text_to_be_present_in_element 判断元素是否有xx 文本信息 用法