Oracle中的管道函数(pipeline)迁移到MogDB

Oracle中的管道函数(pipeline)迁移到MogDB

原作者：师庆栋

适用范围

Oracle迁移到MogDB

问题概述

迁移一个项目时发现客户的系统中大量的使用了管道函数（pipeline），MogDB目前暂时不支持pipeline。如果迁移为表函数，使用时并不支持函数table()，改动较大。但是MogDB已经支持talbe函数，只要返回数据集即可。因此可以把oracle中的管道函数进行改造，改为返回数据集。

Oracle中源码示例

解释CREATE OR REPLACE TYPE type_emp_row AS OBJECT
(
empno NUMBER(4),
ename VARCHAR2(10),
job VARCHAR2(9),
mgr NUMBER(4),
hiredate DATE,
sal NUMBER(7,2),
comm NUMBER(7,2),
deptno NUMBER(2)
)
/
CREATE OR REPLACE TYPE type_emp IS TABLE OF type_emp_row;
/
CREATE OR REPLACE FUNCTION f_get_emp(p_deptno NUMBER) RETURN type_emp
PIPELINED AS
v_emp type_emp_row;
BEGIN
FOR cur IN (SELECT * FROM scott.emp WHERE deptno = p_deptno) LOOP
v_emp := type_emp_row(cur.empno,
cur.ename,
cur.job,
cur.mgr,
cur.hiredate,
cur.sal,
cur.comm,
cur.deptno);
PIPE ROW(v_emp);
END LOOP;
END;


查询结果如下：

解释SQL> select * From table(f_get_emp(10));

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ----------- ---------- ---------- ----------
7782 CLARK MANAGER 7839 1981/6/9 2450 10
7839 KING PRESIDENT 1981/11/17 5000 10
7934 MILLER CLERK 7782 1982/1/23 1300 10


解决方案

更改方式一

解释CREATE OR REPLACE TYPE type_emp_row AS OBJECT
(
empno NUMBER(4),
ename VARCHAR2(10),
job VARCHAR2(9),
mgr NUMBER(4),
hiredate DATE,
sal NUMBER(7,2),
comm NUMBER(7,2),
deptno NUMBER(2)
);
/
CREATE OR REPLACE TYPE type_emp IS TABLE OF type_emp_row;
/
CREATE OR REPLACE FUNCTION f_get_emp(p_deptno NUMBER) RETURN type_emp
AS
v_emp type_emp_row;
res_emp type_emp := type_emp();
BEGIN
FOR cur IN (SELECT * FROM scott.emp WHERE deptno = p_deptno) LOOP
v_emp := type_emp_row(cur.empno,
cur.ename,
cur.job,
cur.mgr,
cur.hiredate,
cur.sal,
cur.comm,
cur.deptno);
res_emp.extend;
res_emp(res_emp.last)=v_emp;
END LOOP;
return res_emp;
END;
/


查询结果

解释cmsdb=> select * from table(f_get_emp(10));
empno | ename | job | mgr | hiredate | sal | comm | deptno
-------+--------+-----------+------+---------------------+---------+------+--------
7782 | CLARK | MANAGER | 7839 | 1981-06-09 00:00:00 | 2450.00 | | 10
7839 | KING | PRESIDENT | | 1981-11-17 00:00:00 | 5000.00 | | 10
7934 | MILLER | CLERK | 7782 | 1982-01-23 00:00:00 | 1300.00 | | 10
(3 rows)


更改方式二

只有函数部分与上面稍有区别

解释CREATE OR REPLACE TYPE type_emp_row AS OBJECT
(
empno NUMBER(4),
ename VARCHAR2(10),
job VARCHAR2(9),
mgr NUMBER(4),
hiredate DATE,
sal NUMBER(7,2),
comm NUMBER(7,2),
deptno NUMBER(2)
);
/
CREATE OR REPLACE TYPE type_emp IS TABLE OF type_emp_row;
/
CREATE OR REPLACE FUNCTION f_get_emp(p_deptno NUMBER) RETURN type_emp
AS
res_emp type_emp := type_emp();
BEGIN
SELECT * bulk collect into res_emp FROM scott.emp WHERE deptno = p_deptno;
return res_emp;
END;


附：表函数的更改方式

如果不需要使用table()函数，也可以直接改为表函数，同样有几种方式

更改方式一

解释drop function if exists scott.f_get_emp;

CREATE OR REPLACE FUNCTION scott.f_get_emp(p_deptno NUMBER)
RETURNS TABLE (
empno INTEGER,
ename VARCHAR(10),
job VARCHAR(9),
mgr INTEGER,
hiredate DATE,
sal NUMERIC(7,2),
comm NUMERIC(7,2),
deptno INTEGER
) AS $$
BEGIN
FOR cur IN (SELECT * FROM scott.emp WHERE deptno = p_deptno) LOOP
empno :=cur.empno ;
ename :=cur.ename ;
job :=cur.job ;
mgr :=cur.mgr ;
hiredate :=cur.hiredate ;
sal :=cur.sal ;
comm :=cur.comm ;
deptno :=cur.deptno ;
RETURN NEXT;
END LOOP;
END;
$$ LANGUAGE plpgsql;


查询结果:

解释cmsdb=> SELECT * FROM scott.f_get_emp(10);
empno | ename | job | mgr | hiredate | sal | comm | deptno
-------+--------+-----------+------+---------------------+---------+------+--------
7782 | CLARK | MANAGER | 7839 | 1981-06-09 00:00:00 | 2450.00 | | 10
7839 | KING | PRESIDENT | | 1981-11-17 00:00:00 | 5000.00 | | 10
7934 | MILLER | CLERK | 7782 | 1982-01-23 00:00:00 | 1300.00 | | 10
(3 rows)


更改方式二

解释drop function if exists scott.f_get_emp;

CREATE OR REPLACE FUNCTION scott.f_get_emp(p_deptno NUMBER)
RETURNS setof scott.emp AS $$
BEGIN
FOR emp_row IN (SELECT * FROM scott.emp WHERE deptno = p_deptno) LOOP
-- 使用 RETURN NEXT 返回结果集中的一行
RETURN NEXT emp_row;
END LOOP;
END;
$$ LANGUAGE plpgsql;


更改方式三

解释drop function if exists scott.f_get_emp;

CREATE OR REPLACE FUNCTION scott.f_get_emp(p_deptno NUMBER)
RETURNS setof scott.emp AS $$
BEGIN
RETURN QUERY SELECT * FROM scott.emp e WHERE deptno = p_deptno;
END;
$$ LANGUAGE plpgsql;